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):

  1. VS2008 SP1 (Which includes Sql Express 2008)
  2. Silverlight 3 RTM
  3. .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
     public int EmployeeID { get; set; }
         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; }
     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();
    6:     const int PageSize = 20;

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",
          da = new SqlDataAdapter(
             "SELECT * " +
             "FROM SuperEmployees " +
             "WHERE EmployeeID=" + employeeID,
     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"];
    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: {
    4:     GetSuperEmployee(currentSuperEmployee.EmployeeID);
    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:     }
   13:     var orgEmp = this.ChangeSet.GetOriginal(currentSuperEmployee);
   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);
   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;
    8:     SqlDataAdapter da = new SqlDataAdapter(
    9:         "SELECT * " +
   10:         "FROM SuperEmployees ",
   11:         conn);
   12:     SqlCommandBuilder com = new SqlCommandBuilder(da);
   13:     da.Update(Context, "SuperEmployees");
   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.