Silverlight 2 Database Updating

• Download source code - 1.03 MB

Introduction

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

With 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 very 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 a 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 five 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	A basic Silverlight application that displays a DataGrid bound to the Northwind Customer table. This table is populated by calling DataWebService.

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 shows how the Silverlight grid treats them.

The solution has two main parts: tracking changes made in the Silverlight application, and submitting 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 the classes exposed through the service. So, if the service contains objects that use the 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 implemented in 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 a 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 adds a reference to the TrackChanges object. The TrackChanges exposes the ChangeTracking class. The class constructor requires three arguments: a table name, an array of table keys, and either an object collection or a 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 is case sensitive, they must match the object property name's 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 the testtable into the data grids. Any changes made are committed to the database when the Save Changes button is clicked.

The below code lists part of the SilverlightUpdateChanges project. Upon loading the form, the customer and testable grids are populated by calling the 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 is 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

The SilverlightUpdateChanges data changes can be committed to the database by calling the DataWebServices web service SubmitChangesAsync method. This method requires the dictionary object of changes and the 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, the table name, and the 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:

Property	Description
ErrorList	List of exceptions from any update errors.
SuccessCounter	Number of successful update statements.
ExecutionCounter	Number of update statements executed.
Delimiter	The key delimiter. The default is the vertical bar '|'.

Issues

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

Pros:

• Only sends the 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 returning 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 it back to the web service has crossed my mind. This would solve a number of these issues. But, the security issues are great; the ability to execute arbitrary SQL statements against your database is too dangerous. An encryption process and secure identification scheme might address some of these issues.

Another issue is the key delimiter, currently set as the 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 the 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 easily fixed by providing a mapping routine.

I am considering a mapping routine to map the lookup values against field names, which would speed up operations as well as 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, and may be related to Silverlight's Beta status. It disappears when Visual Studio is restarted.

Conclusion

Hopefully, this provides a 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 31 2008 - First version.