How to return datatable from a typed dataset using WCF service and Jun CTP ?
How to return datatable from a typed dataset using WCF service/
Hi,
In one of my application I am using the Typed Dataset that contains many tables. I want to return a particular Datatable form the dataset as following.
--------------------------------------------------------------------------------------------------------
Case 1. The following code is working, but the problem is I can not use .EnforceConstraints = false property as this property only available with Dataset. (I want to use this property because I want to return only selected fields from the table and rest of the fields may have not null columns)
mydataset.vemployeedatatable itypedstest.getdata()
{
mydataset.vemployeedatatable mdt = new mydataset.vemployeedatatable();
sqldataadapter adapter = new sqldataadapter(
"select * from HumanResources.Employee",
"data source=(local);integrated " +
"security=sspi;initial catalog=adventureworks");
adapter.fill(mdt);
return mdt;
}
--------------------------------------------------------------------------------Case 2. In the following code, I can use the EnforceConstraints property of the dataset but this code is not returning any data to client side. Althouh the server is having the data after query execution but on client side I did not receive any data.
MyDataSet.vEmployeeDataTable ITypeDSTest.GetData()
{
MyDataSet mds = new MyDataSet();
mds.EnforceConstraints = false;
SqlDataAdapter adapter = new SqlDataAdapter(
"Select EmployeeID from HumanResources.Employee",
"Data Source=(local);Integrated " +
"Security=SSPI;Initial Catalog=AdventureWorks");
adapter.Fill(mds.vEmployee);
return mds.vEmployee;
}
Please let me know if anyone has any idea.
Thanks,
Answers
The best way is to use custom collection of custom object.
If you're using 2.0, then you can use generic collection instead of custom collection to reduce code.
How?
1. Create a custom data object (containing only private fields and public properties for each field) that is similar to each row in the datatable.
2. Create a layer that will do all database (in this case, dataset) access and translation to the custom object.
3. All client code will access that layer.
Here's a sample:
You have a User Table containing UserId and Name
You create a custom User class with the following code:
public class User
private string userId
private string name
public string UserId
{
get { return this.userId; }
set { this.userId = value; }
}
public string Name
{
get { return this.name; }
set { this.name = value; }
}
}
The layer should expose methods like AddUser, UpdateUser, DeleteUser, GetUser.
An example for GetUser looks like the following:
public System.Collections.Generic.ObjectModel.Collection<User> GetUser
{
System.Collection.Generic.ObjectModel.Collection<User> getUser = new System.Collection.Generic.ObjectModel.Collection<User>();
foreach (DataSet.UserRow userRow in dataset.UserTable)
{
getUser.Add(ConvertToObject(userRow))
}
}
It also needs to have that ConvertToObject method. It basically just a mapper. Sample:
public User ConvertToObject(DataSet.UserRow row)
{
User user = new User();
user.UserId = row.UserId
user.Name = row.Name
}
Hope helps!
All Replies
First of all, it's not a good practice to send a DataTable over a service.
Why, one reason because it is a huge object, you will experience a perf hit when doing that.
Second, it is important that you recall the "Share schema, not class" principles of SOA. That way, your clients may not have the same behavior than your services.
IMO, you will need to create a custom type for transporting data between machines, AND you will always check for constraints server-side. This is because you will never have any control over what's get transported by the client (assuming that you are going to expose this service as a public service).
If you aren't going to expose the service to public (a.k.a. you create the clients yourself), then it might be better to reference the custom type by using /r parameter on the svcutil command line.
Still, sending a whole DataTable is not a good practice (sorry for not answering the problem).
Hi,
Thanks for your reply, I know it will hit the performance a bit if i will transfer the datatable or dataset. In My case I am having the control on both side (client and server with netTcpBinding) and I need to transfer the datatable or dataset with few records.
Right now my problem is not with the dataset or datatable, my problem is with the behavior of WCF which in some one case allow me to transfer data in a datatable and in another case it is not allowing me. The case in which WCF service not allow me to transfer data is having advantage as it is having enforceconstraint property.
Please let me know if some one have any idea about this.
Thanks,
I agree with previous comments about "not sending DataSet or DataTable" like objects through a service boundary...however...if you "have to" I think you should not be using DataTable. Use the DataSet with the DataTable result inside. I'm not sure why you are separating the table from the dataset, but this is probably part of your problem because the dataset is rich in relational information that perhaps the table needs to be passed around. We probably need a dataset/datatable guru to comment further...but try the dataset approach since you probalby have one from the query.
Hi,
I require sending the Dataset or Datatable because in my client server architecture client (in a .net windows form with WCF NetTcpBinding) will request the data from the server(WCF NetTcpBinding ) and will modify that data on client side and send the changes (in the dataset) back to server and server will update the database. If you are not recommending the Dataset or Datatable due to performance what are the other way to do the same?
Is their any example or help available on this?
Please let me know your view or any help site/ example.
Thanks,
- When you work with the DataSet directly, did that also fail? Or, only when you use the DataTable?
I am not having any problem with dataset and with datatable in a particular case(as i mention in my earlier post) I am having the problem, but as you suggested that transferring the dataset or datatable will hit the performance(I am agree with this), i want to know the alternate of this approach. Please let me know your idea, any help site or code or document if available.
Thanks,
The best way is to use custom collection of custom object.
If you're using 2.0, then you can use generic collection instead of custom collection to reduce code.
How?
1. Create a custom data object (containing only private fields and public properties for each field) that is similar to each row in the datatable.
2. Create a layer that will do all database (in this case, dataset) access and translation to the custom object.
3. All client code will access that layer.
Here's a sample:
You have a User Table containing UserId and Name
You create a custom User class with the following code:
public class User
private string userId
private string name
public string UserId
{
get { return this.userId; }
set { this.userId = value; }
}
public string Name
{
get { return this.name; }
set { this.name = value; }
}
}
The layer should expose methods like AddUser, UpdateUser, DeleteUser, GetUser.
An example for GetUser looks like the following:
public System.Collections.Generic.ObjectModel.Collection<User> GetUser
{
System.Collection.Generic.ObjectModel.Collection<User> getUser = new System.Collection.Generic.ObjectModel.Collection<User>();
foreach (DataSet.UserRow userRow in dataset.UserTable)
{
getUser.Add(ConvertToObject(userRow))
}
}
It also needs to have that ConvertToObject method. It basically just a mapper. Sample:
public User ConvertToObject(DataSet.UserRow row)
{
User user = new User();
user.UserId = row.UserId
user.Name = row.Name
}
Hope helps!
Hi,
Thanks for the example code. I will give a try what you suggested but I am also having few questions regarding:
1. Will it not increase the development time?
2. When you have join on multiple tables than will it not make life a bit complicated for the developer when we have large number of column in the Table and many methods for the database operation?
3. For every dataset or data table the server process time will increase little bit as first we need to fetch the dataset or datatable from the database and then we have to process that dataset or datatable to convert into the collection object.
Please let me know your thought about these.
Thanks,
1. Yes, but given only the mapper logic needs to be rewritten, it's not a big gain. Other logic like Add, Update, Delete is basically the same for every table, only the data types are different. This is extremely easy when you already developed your own template, just paste it using Code Snippet. I do that.
2. Mm... I never experienced any "big" problem when using joined tables. My current project uses a table which is joined to 5 other table, with one being a double join.
3. Yes. IMHO: using dataset for lots of data is no speedier than using custom collection for the same amount of data, if not slower. So if you do a lot of operations outside the database, you should use custom objects.
I have once written an app which have a lot of foreach for collections, it really slows down when you have thousands of rows to process. When upgrading the said app to 2.0, generics really help out reducing the code AND improving performance. Since moving to generic collections, I think performance hit is negligible.
If you do have time and resources, you should skip the DataSet altogether. This will further improve performance.
For as long as DataSets have existed developers have been debating their need to exist and be used. Typed DataSets in .NET 2.0 are a completely different animal then previous versions. Sometimes Type DataSets are useful. A good example of when is for building Smart Clients that require disconnected ability. Typed DataSets have built in support for optimistic concurrency and for merging disconnected updates.
But aren't Typed DataSets heavy? Well this depends. First, why transfer the schema across the wire. The following code will prevent the sending of the schema across the wire.
typedDataSet.EnforceConstraints = false;
typedDataSet.SchemaSerializtionMode = SchemaSerializationMode.ExcludeSchema;Next, you can use binary serialization to save tons of bandwidth. To implement this feature you set RemotingFormat = SerializationFormat.Binary.
Aren't Typed DataSets a non SOA strategy. While it is true that using a Typed DataSet as a return type for a Web Service, WCF service, etc. will limit the clients that can consume this service this may not be a bad thing. What is your need? If you are building a service layer for a smart client then it really doesn't matter. If you are build reusable services for your entire enterprise then this is a horrible strategy and using Typed DataSets is a bad idea. The point is it depends.
- Hello,
Here a post about typed DataTable and WCF: http://blogs.msdn.com/lifenglu/archive/2007/08/01/passing-datatable-across-web-wcf-services.aspx For as long as DataSets have existed developers have been debating their need to exist and be used. Typed DataSets in .NET 2.0 are a completely different animal then previous versions. Sometimes Type DataSets are useful. A good example of when is for building Smart Clients that require disconnected ability. Typed DataSets have built in support for optimistic concurrency and for merging disconnected updates.
But aren't Typed DataSets heavy? Well this depends. First, why transfer the schema across the wire. The following code will prevent the sending of the schema across the wire.
typedDataSet.EnforceConstraints = false;
> The reason they are heavy is the creation cost and the way datasets store the information in memory ... The serialization issues ( still present in DataTables) were fixed in .NET 2.0. For short lived objects like sevrices this is an issue as the service will get the data , serialize it and the destroy the dataset... And then you recreate it in the client.
typedDataSet.SchemaSerializtionMode = SchemaSerializationMode.ExcludeSchema;Next, you can use binary serialization to save tons of bandwidth. To implement this feature you set RemotingFormat = SerializationFormat.Binary.
>yep , note only the Wcf xmlbinary format is good for this . The .net binary serializer is a bit bloated.
Aren't Typed DataSets a non SOA strategy. While it is true that using a Typed DataSet as a return type for a Web Service, WCF service, etc. will limit the clients that can consume this service this may not be a bad thing. What is your need? If you are building a service layer for a smart client then it really doesn't matter. If you are build reusable services for your entire enterprise then this is a horrible strategy and using Typed DataSets is a bad idea. The point is it depends.
> In some ways typed datasets are very suitable for SOA as you can get the xsd and generate the wsdl in this case the the contents of the dataste is the message. You then funnel this into workflows . I have not played around too much with this due to its heavyness but in theory typed resultset are a very good match for SOA . At present they are IMHO too heavy but we will see what will happen witrh dlinq and xlinq.
.
Regards,
Ben
I agree that many serialization issue related to Datasets were fixed in .NET 2.0. However, I disagree that the "Creation Cost" is the reason datasets are heavy. Datasets are considered heavy objects b/c they contain DiffGrams, Relationships, etc. The diffgram is used to check the original values vs. saved values.
Hello,
Yes they are HEAVY because they have these things ( and default dataviews , old rows etc etc) , these all take time to create.
This means they have a heavy memory foot print and creating and disposing all these objects consumes a lot of time. Especially if you then just serialize it and throw it away./
This also means they have a lot of functionality but the serrver has to pay the cost of this while never using it.
Regards,
Ben
totally agreed.
I now is working on some issues for which I should pass some dataset across service boundaries. there are so much problems involved. fortunately, I am just building the solution for a demo.
- Hi Dileep,
Please give a tablename for your datatable before transfering the data to client.
ex: DataTable objDT = new DataTable("SDT");
The WCF will throw a InvalidOperationException if name is not set. The following error message will thrown "Cannot serialize the DataTable. DataTable name is not set."
Regards
Prabhu
AdrianGodong , Do you have a working sample ? It would be nice if you put it here for community reference!
Thanks.- Hi,
I had the same problem ,
and I solved it by using a serialized object like DataSet instead of DataTable.
and then I read the tables from the dataset by using for each loop :
For Each Table As DataTable In oDT.Tables
Next
...........
Liron Prihar.
