It would be strange if you can return a DataTable from WebService.
The DataTable, DataRow, DataView, and DataViewManager objects cannot be serialized and cannot be returned from an XML Web service. To return less than a complete
DataSet, you must copy the data that you want to return to a new DataSet.
You can debug to check if you retrieve all 200 records from DB on server side.
It would be more helpful if you can post relevant code.Thanks.
NOTE:If you find my response contains a reference to a third party World Wide Web site, I am providing this information as a convenience to you.Microsoft does not control these sites and has not tested any software or information found on these sites; therefore,Microsoft cannot make any representations regarding the quality,safety, or suitability of any software or information found there.
__________________________________________________
Sincerely,
Young Fang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Hi, Young
You'd better update your knowledge now, DataTable can be used as return type in a .Net 2.0 WebService. Are you really working in Microsoft? :)
And it's not elegant to mark your reply as answer when it's not the real answer for my question.
Yep, a dataset is serializeable, a datatable is not. as long as you stay in .NET you're fine, because it knows what an unsigned datatable is - but .NET 2.0 or not, a datatable cant be serialized for any other calling app through a webservice that doesnt
know what a datatable is
Hi, Young
You'd better update your knowledge now, DataTable can be used as return type in a .Net 2.0 WebService. Are you really working in Microsoft? :)
And it's not elegant to mark your reply as answer when it's not the real answer for my question.
Thanks for your correction. I double-checked and I believe you were right. In .Net Framework 2.0, DataTable can be used as an parameter or return value by Web Services. It is an enhancement I was not aware of. Sorry about that.
As far as marking is concerned, sometimes we may mark the answers prematurely (by mistake), as we think the problem is resolved. We always encourage you to unmark the answers if they do not solve the problem.
Do you mind sharing a little bit more information on how you resolve the issue? I don’t have much experience on MySql and I am really curious on how the DateTime data type can affect the number of rows returned. I believe your solution can be beneficial
to other community members as well.
Thanks in advance.
NOTE:If you find my response contains a reference to a third party World Wide Web site, I am providing this information as a convenience to you.Microsoft does not control these sites and has not tested any software or information found on these sites; therefore,Microsoft cannot make any representations regarding the quality,safety, or suitability of any software or information found there.
__________________________________________________
Sincerely,
Young Fang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Its key to note here however that while a webservice in the .Net Framework can return a datatable, the calling service must also be from .NET otherwise the datatable will be unserializable.
Before you start calling out members, and fighting with users telling them they are wrong, perhaps you should take a look at the situation.
You are forcing the XML Serializer to serialize your datatable in that example, not returning a datatable TYPE in the webservice. There is a big difference. If I invoke XML serialization I can serialize anything I want as long as I've provided a schema
of some sort for it.
Everyone in this thread please refrain from posting theory code, and untested examples. The MSFT and Diamsorn were both correct when they said a Webservice CANT serialize a datatable as a return type, because it cant. This is the way it's always been,
and the way it continues to be for now, till we hear differently.
Test this code yourself and use it to "invoke" a web service in your browser, you will get the exact same thing I do.
<WebMethod()> _
Public Function TestReturn() As DataTable
Dim myDT As New DataTable
myDT.Columns.Add("item1")
myDT.Columns.Add("item2")
myDT.Columns.Add("item3")
For I As Integer = 1 To 10
Dim DR As DataRow = myDT.NewRow
DR.Item("item1") = I
DR.Item("item2") = I + I
DR.Item("item3") = I * I
myDT.Rows.Add(DR)
Next
Return myDT
End Function
This isnt some "trick". YOUR way, when you get to the other end, you have XML, not a Datatable. When you return a dataset....when you get to the other end you still have a dataset. It's been "serialized" by the web service, not by the XML wrapper.
System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: Cannot serialize the DataTable.
at System.Data.DataTable.WriteXmlSchema(XmlWriter writer, Boolean writeHierarchy)
at System.Data.DataTable.System.Xml.Serialization.IXmlSerializable.WriteXml(XmlWriter writer)
at System.Xml.Serialization.XmlSerializationWriter.WriteSerializable(IXmlSerializable serializable, String name, String ns, Boolean isNullable, Boolean wrapped)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write2_DataTable(Object o)
at Microsoft.Xml.Serialization.GeneratedAssembly.DataTableSerializer.Serialize(Object objectToSerialize, XmlSerializationWriter writer)
at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
at System.Xml.Serialization.XmlSerializer.Serialize(TextWriter textWriter, Object o)
at System.Web.Services.Protocols.XmlReturnWriter.Write(HttpResponse response, Stream outputStream, Object returnValue)
at System.Web.Services.Protocols.HttpServerProtocol.WriteReturns(Object[] returnValues, Stream outputStream)
at System.Web.Services.Protocols.WebServiceHandler.WriteReturns(Object[] returnValues)
at System.Web.Services.Protocols.WebServiceHandler.Invoke()
WebServiceDe...
0 Points
5 Posts
WebService won't return all records
Aug 12, 2007 04:22 AM|LINK
Aloha,
I used a webservice to return some data in my DB, and the return type is datatable, which is new in .Net 2.0.
But the problem is I have 200 records in DB, but WebService won't return all of them, maybe only 15 in all.
Could any one tell me what's wrong?
Thank you.
Web Service DataTable
WebServiceDe...
0 Points
5 Posts
Re: WebService won't return all records
Aug 13, 2007 06:13 AM|LINK
Bump! Anyone knows?[:P]
Samiraek
Member
35 Points
30 Posts
Re: WebService won't return all records
Aug 13, 2007 07:04 AM|LINK
can u copy the code here?
Young Fang -...
All-Star
17147 Points
1620 Posts
Re: WebService won't return all records
Aug 14, 2007 01:34 AM|LINK
Hi,
It would be strange if you can return a DataTable from WebService.
The DataTable, DataRow, DataView, and DataViewManager objects cannot be serialized and cannot be returned from an XML Web service. To return less than a complete DataSet, you must copy the data that you want to return to a new DataSet.
You can debug to check if you retrieve all 200 records from DB on server side.
It would be more helpful if you can post relevant code.Thanks.
__________________________________________________
Sincerely,
Young Fang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
WebServiceDe...
0 Points
5 Posts
Re: WebService won't return all records
Aug 16, 2007 03:54 PM|LINK
Hi, Young
You'd better update your knowledge now, DataTable can be used as return type in a .Net 2.0 WebService. Are you really working in Microsoft? :)
And it's not elegant to mark your reply as answer when it's not the real answer for my question.
My code is quite simple,
DataTable datatable = new DataTable("requestTable");
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter(requestStr, conn);
adapter.Fill(datatable);
return datatable;
}
catch
{
conn.Close();
return datatable;
}
And I have already found the problem, it's all because of the DateTime datatype in MySQL.
But still thank your for your help.
Diamsorn
Contributor
2119 Points
384 Posts
Re: WebService won't return all records
Aug 16, 2007 06:16 PM|LINK
Yep, a dataset is serializeable, a datatable is not. as long as you stay in .NET you're fine, because it knows what an unsigned datatable is - but .NET 2.0 or not, a datatable cant be serialized for any other calling app through a webservice that doesnt know what a datatable is
My Blog
Young Fang -...
All-Star
17147 Points
1620 Posts
Re: WebService won't return all records
Aug 17, 2007 02:19 AM|LINK
Thanks for your correction. I double-checked and I believe you were right. In .Net Framework 2.0, DataTable can be used as an parameter or return value by Web Services. It is an enhancement I was not aware of. Sorry about that.
As far as marking is concerned, sometimes we may mark the answers prematurely (by mistake), as we think the problem is resolved. We always encourage you to unmark the answers if they do not solve the problem.
Do you mind sharing a little bit more information on how you resolve the issue? I don’t have much experience on MySql and I am really curious on how the DateTime data type can affect the number of rows returned. I believe your solution can be beneficial to other community members as well.
Thanks in advance.
__________________________________________________
Sincerely,
Young Fang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Diamsorn
Contributor
2119 Points
384 Posts
Re: WebService won't return all records
Aug 17, 2007 02:54 AM|LINK
Its key to note here however that while a webservice in the .Net Framework can return a datatable, the calling service must also be from .NET otherwise the datatable will be unserializable.
My Blog
WebServiceDe...
0 Points
5 Posts
Re: WebService won't return all records
Aug 18, 2007 04:20 PM|LINK
It's really ridiculous to say anything before you doing some valuable research.
Don't pretend to know the truth when you don't, OK?
The key here is that DataTable in .Net 2.0 is serializable and can be used with none .Net clients.
If you don't believe, please try this sample
DataTable datatable = new DataTable("TestTable");
datatable.Columns.Add("col1");
datatable.Columns.Add("col2");
datatable.Rows.Add("abc", "bcd");
datatable.Rows.Add("efg", "hij");
XmlSerializer formatter = new XmlSerializer(typeof(DataTable));
Stream stream = new FileStream("MyFile.xml",FileMode.Create);
formatter.Serialize(stream, datatable);
And if you know some tool named Reflector, you will find that DataTable in .Net 2.0 is
Well, is it time to refresh your knowledge?
As I said my problem is about MySQL DateTime, and I added "allow zero datetime" in connection string which solved my problem.
Freakyuno
Star
12518 Points
1952 Posts
Re: WebService won't return all records
Aug 19, 2007 12:03 AM|LINK
WebServiceDev,
Before you start calling out members, and fighting with users telling them they are wrong, perhaps you should take a look at the situation.
You are forcing the XML Serializer to serialize your datatable in that example, not returning a datatable TYPE in the webservice. There is a big difference. If I invoke XML serialization I can serialize anything I want as long as I've provided a schema of some sort for it.
Everyone in this thread please refrain from posting theory code, and untested examples. The MSFT and Diamsorn were both correct when they said a Webservice CANT serialize a datatable as a return type, because it cant. This is the way it's always been, and the way it continues to be for now, till we hear differently.
Test this code yourself and use it to "invoke" a web service in your browser, you will get the exact same thing I do.
<WebMethod()> _ Public Function TestReturn() As DataTable Dim myDT As New DataTable myDT.Columns.Add("item1") myDT.Columns.Add("item2") myDT.Columns.Add("item3") For I As Integer = 1 To 10 Dim DR As DataRow = myDT.NewRow DR.Item("item1") = I DR.Item("item2") = I + I DR.Item("item3") = I * I myDT.Rows.Add(DR) Next Return myDT End FunctionThis isnt some "trick". YOUR way, when you get to the other end, you have XML, not a Datatable. When you return a dataset....when you get to the other end you still have a dataset. It's been "serialized" by the web service, not by the XML wrapper.
My Blog