Silverlight 2 Database Updating

• Download SilverlightUpdateDatabaseChanges_Source - 1.06 MB

Introduction

This article is demonstrate a method of updating databases from a Silverlight 2 application.

The recent Silverlight 2 Beta 2 release it has become easier to access and manipulate database data. Since Silverlight applications cannot directly access local resources data access is provided through web services such as Windows Communications Foundation(WCF) or ASMX. There are many examples demonstrating how to read data and populate data objects but few that provide any facility to write changes back to the data source.

One example found here uses ADO.Net Data services (Astoria), which is still in development.

The other solution, found here, uses LINQ and makes a copy of the original data. It sends both the original and modified sets of data back to the server when updating. So if you have 50 rows of data you are sending up to 100 rows back. While this approach works I didn't like the idea of sending so much data if you only wanted to make a few changes. The page includes a video and the source is definitely worth looking at.

This article demonstrates a method of updating database data by only sending changes back. It assumes some basic Silverlight 2 operations knowledge, such as calling a web service and binding data to a control. The article 'My First Silverlight Data Project' provides good introduction to these operations. Another resource is the Silverlight Getting Started page.

The source was written using C# in Visual Studio 2008 and Silverlight 2 Beta 2.

Using the Code

The attached source solution contains 5 projects:

DataWebService	Exposes Northwind tables through WCF using LINQ. This service also contains the logic to update the database with changes passed from the Silverlight client.
UpdateChangesToLINQ	Provides the logic to update changes using a LINQ data context. It is used by the DataWebServices project.
TrackChanges	Tracks object changes. Used in SilverlightUpdateChanges
DemoUpdateClass	Console application that demonstrates data updating. Useful for debugging the UpdateChanges class.
SilverlightUpdateChanges	Basic Silverlight application that displays a datagrid bound to Northwind Customer table. This table is populated by calling DataWebService.

The Before running the samples change the path in DataWebService to point to the included Northwind database mdf file. This is the standard Northwind sample database with an additional testtable table. This table contains most SQL data types and is useful in testing the various types and also how the Silverlight grid treats them.

The solution has two main parts: Tracking changes made in the Silverlight application and submitting them to the submitted the changes to update the database.

Tracking Changes

When adding a Web service reference to a Silverlight project VS 2008 will generate proxy classes. These classes are based on classes exposed through the service. So if the service contains objects that use Customer object, a corresponding Customer proxy object is created on the Silverlight side. These proxy classes are hidden but you can view them by selecting the Show All Files button in Solution Explorer. They are stored in the Reference.cs file, one for each service.

What I noticed in the proxy classes was the data structure implement the INotifyPropertyChanged interface. This requires the implementation of the event handler PropertyChanged that is called whenever a property change is made. By default this event handler is not assigned.

The following code contains a simple Widget class that implements the INotifyPropertyChanged event:

public partial class Widget: object, System.ComponentModel.INotifyPropertyChanged
{
private string _ID;
private string _Name;
private string _Colour;

public string ID
 {
   get
  {
    return this._ID;
  }
   set
  {
    if ((object.ReferenceEquals(this._ID, value) != true))
    {
       this._ID = value;
       this.RaisePropertyChanged("ID");
    }
  }
}

public string Name
 {
   get
  {
    return this._Name;
  }
   set
  {
    if ((object.ReferenceEquals(this._Name, value) != true))
    {
       this._Name= value;
       this.RaisePropertyChanged("Name");
    }
  }
}

public string Colour
 {
   get
  {
    return this._Colour;
  }
   set
  {
    if ((object.ReferenceEquals(this._Colour, value) != true))
    {
       this._Colour= value;
       this.RaisePropertyChanged("Colour");
    }
  }
}

public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected void RaisePropertyChanged(string propertyName)
 {
  System.ComponentModel.PropertyChangedEventHandler propertyChanged = this.PropertyChanged;
   if ((propertyChanged != null))
     propertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName));
 }
}

The beauty of using this event handler is it will fire when any change is made to a property, regardless if done through code or a data control. To wire up the event handler assign an event handler method to the object. The following code illustrates how to assign an event handler to a Widget object. Whenever an Widget property changes the event handler is called:

public void TestHandler()
{
//
  Widget testWidget = new Widget();
  testWidget.ID = "100";
  testWidget.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(this.Notifychanges);
  testWidget.Name = "Big Widget";
  testWidget.Colour = "Green";
}
public void Notifychanges(object sender, System.ComponentModel.PropertyChangedEventArgs e)
{
  PropertyInfo p;
  p = sender.GetType().GetProperty(e.PropertyName);
  //get change property value
  string changeValue = p.GetValue(sender, null).ToString();
 
  System.Windows.Browser.HtmlPage.Window.Alert("Changing " + e.PropertyName + " to " + changeValue);
}

The included TrackChanges class library works by tracking object changes using this event handler. Any object property changes will cause the event to fire. The changes are stored in a Dictionary object.

To use this in your project this add a reference to TrackChanges object. The TrackChanges exposes the ChangeTracking class. The class constructor requires 3 arguments: table name, array of table keys and either an object collection or single object instance.

The table name should match the TableName attribute assigned in the web service LINQ data source (see below for more information). The array of keys are case sensitive, they must match the object property names case.

In the following example a single Customer instance is created and assigned to a ChangeTracking object:

Customer testCustomer = new Customer();
//need to assign an ID for tracking to know what to track..
testCustomer.CustomerID = "10000";
//start tracking changes
ChangeTracking customerTrack = new ChangeTracking("dbo.Customers", new string[] { "CustomerID" }, testCustomer);

testCustomer.CompanyName = "Test Company";
testCustomer.ContactName = "Fred Smith";
testCustomer.ContactTitle = "Consultant"; 

The SilverlightUpdateChanges sample project loads all records from the Northwind Customers table and a testtable into data grids. Any changes made are committed to the database when the Save Changes button is clicked.

The below code lists part of SilverlightUpdateChanges project. Upon loading the form the customer and testable grids are populated by calling web service methods. This returns a list of all records from these tables. An instance of the ChangeTrack class is created, passing the table name, array of keys and the object to track changes. Creating this class wires the NotifyChanges event handler for each object to an event handler within the class.

TrackChanges.ChangeTracking customerTracking;
TrackChanges.ChangeTracking testTracking;

 void Page_Loaded(object sender, RoutedEventArgs e)
{
  //create an instance of the web service and get customers
  NorthwindSvc.NorthwindSvcClient nwindClient = new NorthwindSvc.NorthwindSvcClient();

 //add event handler for GetCustomers asynchronous call and call GetCustomersAsync to populate items
 nwindClient.GetCustomersCompleted += new EventHandler<SilverlightUpdateChanges.NorthwindSvc.GetCustomersCompletedEventArgs>(client_GetAllCustomersCompleted);
 nwindClient.GetCustomersAsync();

  nwindClient.GetTestItemsCompleted += new EventHandler<GetTestItemsCompletedEventArgs>(nwindClient_GetTestItemsCompleted);
  nwindClient.GetTestItemsAsync();
}

void nwindClient_GetTestItemsCompleted(object sender, GetTestItemsCompletedEventArgs e)
{
  //get list of test items
  List<testtable> testList = e.Result.ToList();

  //start tracking any changes to testList
  testTracking = new ChangeTracking("dbo.testtable", new string[] { "id" }, testList);

  grdTestItems.ItemsSource = testList;
  grdTestItems.Columns[0].IsReadOnly = true;
}


void client_GetAllCustomersCompleted(object sender, SilverlightUpdateChanges.NorthwindSvc.GetCustomersCompletedEventArgs e)
{
  //get list of customers
  List<Customer> customerList = e.Result.ToList();

  //start tracking any changes to customerList
  customerTracking = new ChangeTracking("dbo.Customers", new string[] { "CustomerID" }, customerList);
  grdCustomers.ItemsSource = customerList;
} 

All changes are stored in a Dictionary object in the ChangeTracking class. The dictionary key contains the key for the record and the field/property being changed. These values are separated by a delimiter. The default delimiter is the vertical bar ('|'). So if the Northwind Customers table CustomerName property was changed for the company record 'Around the Horn' the key would look like this: AROUT|CustomerName . The changed value is stored in the corresponding dictionary value property.

Updating Changes

SilverlightUpdateChanges data changes can be committed to database by calling the DataWebServices web service SubmitChangesAsync method. This method requires the dictionary object of changes and table name.

The DataWebServices project uses the UpdateChanges class to submit the database changes. The UpdateChanges constructor requires no arguments. The UpdateChanges class exposes the SubmitChanges method which submits the changes to be made. SubmitChanges requires the LINQ DatabaseContext object for the data you want to change, table name and dictionary object of changes.

The table name argument must match the Table name attribute assigned to the LINQ class. You can view the attributes by looking at the class definition in the LINQ DBML file. It will look something like this:

[Table(Name="dbo.Customers")]
Public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged

You must include the schema if it is the attribute. The following code snippet calls the SubmitChanges method passing a dictionary of changes to update the dbo.Customers table:

  NorthwindDataContext northwindDB = new NorthwindDataContext(connectionString);
  //create a new UpdateChanges object and submit changes to the Customers database using the northwindDB LINQ Northiwind provider
  UpdateChanges testChanges = new UpdateChanges();
  return (testChanges.SubmitChanges(northwindDB, "dbo.Customers", changesDictionary));

The SubmitChanges method will execute an UPDATE statement against each changed record. It will only execute one UPDATE statement per record. If you have made more than one change to a given record it will combine the updates in a single statement.

SubmitChanges returns a string showing how many successful updates were made and how many statements were executed.

The UpdateChanges class also exposes the following properties:

Issues

There are few pros and cons using these classes in their current form.

Pros

• Only sends changes back

• Fast database execution and minimal database traffic using direct UPDATE statements

Cons

• provides no optimistic locking mechanism

• only works against SQL Server

• currently updates only - no deletions or additions
  

Record deletion would be relatively easy. Insertion is also possible but a mechanism for return the keys of new values would need to be implemented. Adding optimistic locking would require some or all of the fields to be passed back.

The option of building a SQL string from the Silverlight side and sending back to the web service has crossed my mind. This would solve number of these issues. But the security issues are great, the ability to execute arbitrary SQL statements against your database is to dangerous. An encryption process and secure identification scheme might address some of those issues.

Another issue is the key delimiter, currently set as a vertical bar |. If any changed properties contain this value the update operation will fail. This can be easily fixed by changing this to a more obscure value (both tracking and update classes can change this through the Delimiter property). If the property name isn't the same as the corresponding table field name then it won't work correctly. This can be also easily fixed by providing a mapping routine.

I am considering a mapping routine to map lookup values against field names, which would speed up operations as well transmit less data.

Quirks

I've encountered a few quirks working with Silverlight 2. One is the default startup project. One would assume this would be the Silverlight project, but it's actually the Web service. If the Silverlight project is made to be the start up you can get communication errors when starting the project in debug mode ( pressing F5). But running in non debug (Ctrl-F5) mode works.

Another is a warning that sporadically appears: Custom tool warning: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information. G:\Data\VS\VS2008\Silverlight\SilverlightUpdateChanges\SilverlightUpdateChanges\Service References\NorthwindSvc\Reference.svcmap
This doesn't seem to affect the execution but is odd, may be related to Silverlight's Beta status. It disappears when Visual Studio is restarted.

Conclusion

Hopefully this provides solution to updating databases from Silverlight. I intend to improve the current functionality addressing some of the issues raised earlier. Any feedback is appreciated, including (constructive) criticism. If you are going to rate less than 3 I'd appreciate feedback.

History

August 31st 2008 First version