Business Apps Example for Silverlight 3 RTM and .NET RIA Services July Update: Part 12: DataSet
More from my Mix09 talk “building business applications with Silverlight 3”. Many customers have told me that they love Entity Framework and LinqToSql, but that they are not always able to use them in their projects just yet. In fact the number of folks that are using ADO.NET DataSet, DataReader, etc is very high. So I wanted to show taking my Mix demo and changing it to use the standard ADO.NET classic model of data access.
This allows you to use DataSet with Silverlight AND take advantage of all the cool new features RIA Services offers around data validation, paging, etc.
For the context, you can watch the original video of the full session
The demo requires (all 100% free and always free):
- VS2008 SP1 (Which includes Sql Express 2008)
- Silverlight 3 RTM
- .NET RIA Services July '09 Preview
Also, download the full demo files and check out the running application.
First, we can remove the Entity Framework model from our project…. we are going to use DataSet as our data access model in this demo. Notice this pattern likely makes the most sense if you already have a lot of infascture built up around DataSet… if not, then using DataReader\Writer might be a good choice.
First, we need to create a type that we return to the client.
public class SuperEmployee
{
[ReadOnly(true)]
[Key]
public int EmployeeID { get; set; }
[RegularExpression("^(?:m|M|male|Male|f|F|female|Female)$",
ErrorMessage = "Gender must be 'Male' or 'Female'")]
public string Gender { get; set; }
[Range(0, 10000,
ErrorMessage = "Issues must be between 0 and 1000")]
public Nullable<int> Issues { get; set; }
public Nullable<DateTime> LastEdit { get; set; }
[Required]
[StringLength(100)]
public string Name { get; set; }
public string Origin { get; set; }
public string Publishers { get; set; }
public string Sites { get; set; }
}
Notice here that we are able to put the validation metadata directly on the type we are returning. Now we just need to fill up this type from the database..
Let’s start by defining a DomainService
1: [EnableClientAccess()]
2: public class SuperEmployeeDomainService : DomainService
3: {
4: DataSet Context = new DataSet();
5:
6: const int PageSize = 20;
7:
Notice here we are driving directly from DomainService… there is no need to use the EF or L2SDomainService.. We then setup the Context to be a DataSet.. we will populate this DataSet in the methods on the DomainService. Then we define a PageSize for our data.. this gives us a standard chunk to access from the database.
Then I wrote some fairly simply code to deal with populating the DataSet… I’d guess it would be easy to change this to work with whatever pattern you are using to full up DataSets today.
void FillSuperEmployees(DataSet ds, int page, int employeeID)
{
var conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
SqlDataAdapter da;
if (employeeID == -1)
{
da = new SqlDataAdapter(
"SELECT * " +
"FROM SuperEmployees",
conn);
}
else
{
da = new SqlDataAdapter(
"SELECT * " +
"FROM SuperEmployees " +
"WHERE EmployeeID=" + employeeID,
conn);
}
if (page == -1) da.Fill(ds, "SuperEmployees");
else da.Fill(ds, page * PageSize, PageSize, "SuperEmployees");
}
Next we write a query method..
1: public IQueryable<SuperEmployee> GetSuperEmployees(int pageNumber)
2: {
3: Context = new DataSet();
4: FillSuperEmployees(Context, pageNumber,-1);
5: DataTable superEmployees =
6: Context.Tables["SuperEmployees"];
7:
8: var query = from row in
9: superEmployees.AsEnumerable()
10: select new SuperEmployee
11: {
12: EmployeeID = row.Field<int>("EmployeeID"),
13: Name = row.Field<string>("Name"),
14: Gender = row.Field<string>("Gender"),
15: Issues = row.Field<int?>("Issues"),
16: LastEdit = row.Field<DateTime>("LastEdit"),
17: Origin = row.Field<string>("Origin"),
18: Publishers = row.Field<string>("Publishers"),
19: Sites = row.Field<string>("Sites"),
20: };
21: return query.AsQueryable();
22: }
In line 4 we fill up the DataSet then in line 8-20, we use some LinqToDataSet support to make it easier to create a projection of our DataSet. If you’d rather not use Linq here, no problem, you can simply write a copy method to such the data out the DataSet and into our SuperEmployee type. Any collection can be returned as an IQuerable. Notice we are taking the page number here.. we are going to follow the same explicit paging pattern I introduced in the WCF example.
Then let’s take a look at Update… this method is called when there is a change to one of the fields in our SuperEmployee instance…
1: public void UpdateSuperEmployee(SuperEmployee currentSuperEmployee)
2: {
3:
4: GetSuperEmployee(currentSuperEmployee.EmployeeID);
5:
6: DataRow updateRow = null;
7: foreach (DataRow row in Context.Tables["SuperEmployees"].Rows) {
8: if (row.Field<int>("EmployeeID") == currentSuperEmployee.EmployeeID) {
9: updateRow = row;
10: }
11: }
12:
13: var orgEmp = this.ChangeSet.GetOriginal(currentSuperEmployee);
14:
15: if (orgEmp.Gender != currentSuperEmployee.Gender)
16: updateRow.SetField("Gender", currentSuperEmployee.Gender);
17: if (orgEmp.Issues != currentSuperEmployee.Issues)
18: updateRow.SetField("Issues", currentSuperEmployee.Issues);
19: if (orgEmp.LastEdit != currentSuperEmployee.LastEdit)
20: updateRow.SetField("LastEdit", currentSuperEmployee.LastEdit);
21: if (orgEmp.Name != currentSuperEmployee.Name)
22: updateRow.SetField("Name", currentSuperEmployee.Name);
23: if (orgEmp.Origin != currentSuperEmployee.Origin)
24: updateRow.SetField("Origin", currentSuperEmployee.Origin);
25: if (orgEmp.Publishers != currentSuperEmployee.Publishers)
26: updateRow.SetField("Publishers", currentSuperEmployee.Publishers);
27: if (orgEmp.Sites != currentSuperEmployee.Sites)
28: updateRow.SetField("Sites", currentSuperEmployee.Sites);
29:
30: }
First we need to get the DataRow to update. In line 4, we load it up from the Database, then in line 6-11 we find it in the current DataSet (remember, we are doing batch processing so their could be several updates already done in the the DataSet).
Notice the general pattern here is that we compare the original results that the client last saw (from line 13) to what is being sent up from the client. This ensure that we only change the fields that are actually updated. Otherwise we could overwrite another clients changes. This is very much like the code we did in the DTO example.
Finally, in Submit, we need to actually commit these changes to the database.
1: public override void Submit(ChangeSet changeSet)
2: {
3: base.Submit(changeSet);
4: var conn = new SqlConnection();
5: conn.ConnectionString = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
6:
7:
8: SqlDataAdapter da = new SqlDataAdapter(
9: "SELECT * " +
10: "FROM SuperEmployees ",
11: conn);
12: SqlCommandBuilder com = new SqlCommandBuilder(da);
13: da.Update(Context, "SuperEmployees");
14:
15: }
Looking at this Submit override gives us some good insights into how RIA Services really works. Submit is called when a request first comes in from the client. It could contain several adds, deletes or updates in the changeSet. calling base.Submit() breaks the changeset out and calls the appropriate update\add\delete methods for each change. Those changes should leave the DataSet populated with the changes we need to commit to the database. Line 13 takes care of that. Notice this is also a really good place to set a breaking point when you are trying to debug your DomainService.
The only real changes to the client are to accommodate the explicit paging pattern we saw in the WCF example… which is great.. that means you can move from this DataSet model, to EF with very minimal changes to the client.
This example showed how to use existing code dealing with DataSet and expose it to Silverlight clients via .NET RIA Services.
Enjoy!
Comments
Anonymous
July 27, 2009
How could I write a generic non-typed Dataset class for the client. Since it appears that data binding cannot bind to an indexed object, the dataset class, I guessing, needs to dynamically create a strong typed class from the xml meta data. Do have have to use Ruby/Python for this? Anyone have any ideas?Anonymous
July 27, 2009
BTW, thank-you for posting on how to use datasets. Up to now it seems that MS is not too helpful in providing samples on how to use datasets with Silverlight. I would like to see how to get the validation data from the db too. Basically, I'm setting up a project in which data is selected dynamically on the client. Data from the server comes in 2 parts.
- Meta data such as column names in language of the user, etc.
- Actual data.
Anonymous
July 27, 2009
Domald.. Sure... we have a pluggable system where you can get your metadata from anywhere.. This example shows getting hte metadata from a Xml file, but you could easily update it to pull from the database.. http://code.msdn.microsoft.com/RiaServices/Release/ProjectReleases.aspx?ReleaseId=2659Anonymous
July 27, 2009
Donald - Yup, I'd suggest creating a strongly typed object to bind against in that case..Anonymous
July 28, 2009
Nice Approach. So we can use this kind of methodology for supporting custom frameworks. Thanks, ThaniAnonymous
July 28, 2009
Dang, Brad, I've been bundling the posts in this series into an ebook file so I can read it offline on my phone.. and you keep adding to it, so I have to keep updating my file.. (not complaining).. Thanks, for the extensive info (now if I could just find some time to really dig in) Jay KimbleAnonymous
July 28, 2009
Thanigainathan - That is right! We are hoping folks will be able to fit tihs to any sort of data access model they use today with very little work.Anonymous
August 21, 2009
Hi guys, Here is a solution for you: http://silverlightdataset.codeplex.com Enjoy your coding VitalyAnonymous
August 24, 2009
Brad: Great stuff. Appreciate your hard work. This is exactly what I hav been looking for. Quick question: This is the approach I should use to leverage a number of stored procedures I have, correct? Can I use the usually method for adding a parameters collection to the command object, etc. like I always have? Thanks, SeanAnonymous
October 04, 2009
If you don't want to have to lay out the datacontract and all of that you can use the tools from http://www.silverlightds.com.