Bidirectional synchronization with ADO.NET Sync Services and SQL Server 2005

So as you may have come to realize: Visual Studio 2008’s Data Synchronization designer only sets up your code for DownloadOnly Synchronization. You may have tried implementing the OnInitialized partial method to configure your SyncTable’s SyncDirection as Bidirectional. But then you may discover that synchronization fails on data with a master-detail relationship. This failure is due to the incorrect sequence of adding SyncTables to the SyncTable collection of the SyncAgent.Configuration property. So how do you control the order in which your SyncTables are added to the SyncAgent.Configuration.SyncTables collection? Well, with the code generated by Visual Studio 2008 I don’t think there is an easy or effective way. And you obviously don’t want to have to write all the generated code manually just to get control over the sync process. So the good news is, you don’t have to.

 My solution:

I have created T4 templates that automatically generate the code you need for bidirectional synchronization. “T4 is a Microsoft Language for writing Code Generators which is similar to ASP.NET and is used by Microsoft internally to build software and new features for .NET 4.0″. T4 template execution is built into Visual Studio 2008 so you won’t need to download any plug-in. The templates are not going to magically know in which sequence to add the SyncTables to the SyncAgent.Configuration.SyncTables collection, but it at least gives you the ability to change that sequence yourself. The sequence should be (1st – master table , 2nd details table).

T4 templates automatically works in Visual Studio 2008 as it is also used to generate code such as the dbml files used by LINQ. However, to get intellisense for T4 templates you’ll need to download  T4 Tangible Modelling Tools. You may want to customize the templates to conform to the namespaces you use in your project and to use the connectionstrings for your databases.

You’ll find the solution here.

Here is a quick rundown of the code being generated for you:

 
2 Projects

• GlobalCode (I moved all C# code to this library to make debugging easier)
• SyncServCodeGen (These are the T4 templates which generates the code)

01_GeneratedSQLToConfigureServerDB.tt

This template generates the SQL statements you use to configure your server database for synchronization (adding tombstone tables and LastEditDate + CreationDate columns). The code generated by the templates knows not to sync the LastEditDate en CreationDate columns to the client, as this will cause unnecessary conflicts with data on the server.

Customization: Change the values for serverName, databaseName to point to your database.

Important: Please backup your server database before running these scripts. I have not tested these scripts thoroughly.

02_GenerateCRUDProcs.tt

This template generates the stored procedures for your SQL Server 2005 server database to speed up data synchronization access to the database.

Customization: Change the values for serverName, databaseName to point to your database.

03_GenerateSyncAdapterHelper.tt

This template generates a class containing all the SyncAdapters for all the tables that are going to take part in synchronization.

Customization: Change the values for serverName, databaseName to point to your database.

04_GenerateServerSyncProvider.tt

This template generates the ServerSyncProvider class where all the SyncAdapters are added to.

Customization: Change the values for serverName, databaseName to point to your database.

05_GenerateWCFServiceInterface.tt

This template generates the interface (Service Contract) used for the WCF web service to sync data to and from the server.

06_GenerateWCFService.tt

This template generates the WCF web service that you can expose through your web server (IIS).

07_GenerateSyncTableDeclarations.tt

This template generates all the SyncTables for all the tables that are going to take part in synchronization. These classes are used when the tables on the client are created.

Customization: Change the values for serverName, databaseName to point to your database.

08_GenerateClientSyncProvider.tt

This template generates the ClientSyncProvider class which only determines the connectionstring on the client.

09_GenerateSyncAgent.tt

This is the most important template. This is also the one that you want to customize for you exact needs.  I use one SyncGroup for all tables. The SyncGroup represents a transaction boundary for synchronization. All my tables relate to one another directly or indirectly so one SyncGroup makes sense in my case (and most cases). You can change the sequence in which the SyncTables are added to the SyncAgent.Configuration.SyncTables collection in the InitializeSyncTables() function.

Note: I used the name “WCFSyncServiceProxy” for the web reference name. If you name the web reference something else, just update this template accordingly.

Debugging WPF bindings

Binding in WPF is extremely powerful and because of this, it is used extensively in most WPF applications. But Visual Studio or Expression Blend doesn’t moan when a binding is set incorrectly, and then you can’t figure out why your application isn’t working properly. By using an IValueConverter you can at least see if the source object, source property and value is set correctly. Here is the code for it:

public class DebugConvertor : IValueConverter
{
    #region IValueConverter Members

    public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
    {
        Debugger.Break();
        return value;
    }
    public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
    {
        Debugger.Break();
        return value;
    }

    #endregion
}

Reselecting DataGrid row after refreshing data

I’m currently busy with a LOB (Line of Business) application and so the DataGrid features a lot in my project. I keep my rows in the DataGrid readonly though, so editing is allowed only via a seperate dialog when the user doubleclicks on a row in the list dialog. In the edit dialog the user can change or delete a record which would effect the data displayed in the list dialog.

But when you refresh the list dailog’s data you loose your selected item since it is a brand new ICollectionView wrapped around the list data.

Here is some code to reselect the item that was selected before the refresh.

public class EmployeeListViewModel
{
    private ICollectionView cvwEmployees;
    private ObservableCollection<Employee> _obcEmployees;

    public ObservableCollection<Employee> Employees
    {
        get
        {
            return _obcEmployees;
        }
        set
        {
            if (_obcEmployees != value)
            {
                _obcEmployees = value;
                OnPropertyChanged(“Employees”);
            }
        }
    }

    public void RefreshEmployees()
    {
        //Save item to reselect after refresh
        Guid? gdSelectedItem_EmployeeID = null;
        if (cvwEmployees != null)
        {
            gdSelectedItem_EmployeeID = ((Employee)cvwEmployees.CurrentItem).EmployeeID;
        }

        this.Employees = DAL.GetEmployees();
        cvwEmployees = CollectionViewSource.GetDefaultView(this.Employees);

        //Reselect item previously selected
        if (gdSelectedItem_EmployeeID.HasValue)
        {
            IEnumerator etrEmployee = cvwEmployees.GetEnumerator();

            bool bFound = false;
            while (etrEmployee.MoveNext() && (!bFound))
            {
                Employee emp = (Employee)etrEmployee.Current;
                if (emp.EmployeeID == gdSelectedItem_EmployeeID.Value)
                {
                    cvwEmployees.MoveCurrentTo(emp);
                    bFound = true;
                }
            }
        }
    }

Applying Command Binding to any control and any event

Binding the Command property of a Button on a View to an ICommand property exposed by the ViewModel really allows you to keep your business logic separate from the UI logic. However, not all controls can use this pattern. In fact, only controls implementing the ICommandSource interface support Command Binding. The team who developed Expression Blend 3 came up with a library of code that enables developers to write units of code and apply it to a control with a drag and drop fashion inside Expression Blend. They call these behaviors. One of the classes inside the library is the TargetedTriggerAction class. By inheriting from this class, Tor Langlo created a class called CommandAction which allows any control to use Command Binding with any event on that control. I used his code and refactored it a bit to create my version of it here. Please note that this source project has been updated to use the final release dlls from Expression Blend 3 as apposed to the beta dlls used before.

Here is the link to Tor Langlo’s post.

MVVM Intercommunication between View and ViewModel

I have found that the MVVM pattern significantly simplifies the development of a WPF application, especially as the complexity of an application increases. The simplicity is brought about by introducing seperation of concerns to your project. The UI logic sits inside the View and the business logic sits inside the ViewModel. And databinding (which is a first class citizen in WPF) is the glue between the two. It also really simplifies things to have only one DataContext being bound to the View; and then binding the controls inside the View is as simple as:

• Exposing data to bind to as a property in your ViewModel (whether it is a collection or single value)
• Binding the appropriate control to this property.

RelayCommand

The RelayCommand that Josh Smith introduced was great since it allows you to bind a Command property of a button straight to a ICommand Property defined in your ViewModel. This removed the need for you to write code in the View’s code behind. I really started to enjoy using this pattern and it felt natural and intuitive to develop in this matter.

Interacting with the user from the ViewModel

As I made some progress in my project, it became apparent that there is no easy way of back and forth communication between the View and the ViewModel.

Say for example you click on a item in a DataGrid and then on a Delete button to delete the item. This fires off the ICommand’s Execute event handler inside your ViewModel, right?  But now you want to get confirmation from the user before you really delete this item. This means you somehow have to go back to the UI layer (View) to display a MessageBox with an appropriate message that comes from the ViewModel and then get the answer from the View back to the ViewModel to continue with the deletion. What I have found frustrating is that there is no easy way to accomplish this as yet.

So I have come up with a way of simplifying this intercommunication between the View and the ViewModel.

MVVMIntercom

The basic idea behind it is farely simple and an analogy would be playing tennis or ping pong between the View and ViewModel:

The tennis ball (or ping pong ball) would be something that I call a CommsUnit.
My definition of a CommsUnit is: A contained unit of information for triggering code execution on both the View and ViewModel in turn (also providing any data each side would need) in a sequential or workflow manner. This sequence is driven by a CommsState enum property inside the CommsUnit. The different states drive the sequence of triggering code execution.

Let’s make it practical. In my example of confirming an item deletion mentioned above: I would declare a ConfirmedDeleteCommsUnit like this:

    public enum ConfirmedDeleteState
    {
        RequestingMessagePrompt,
        ReturningDialogResult
    }

    public class ConfirmedDeleteCommsUnit : BaseCommsUnit
    {
        public ConfirmedDeleteState CommsState
        {
            get;
            set;
        }
        public int RecordID
        {
            get;
            set;
        }
        public string MsgBoxTitle
        {
            get;
            set;
        }
        public string MsgBoxMessage
        {
            get;
            set;
        }
        public MessageBoxResult MsgBoxResult
        {
            get;
            set;
        }
    }

The View would contain 2 DependencyProperties that are TwoWay-bound to 2 INotifyPropertyChanged enabled properties on the ViewModel. The one is named ”ToViewCommsUnit” and the other “ToViewModelCommsUnit”. As the names imply: ToViewCommsUnit is used to send information from the ViewModel to the View and ToViewModelCommsUnit is used to send information back.

The ViewModel would initiate the intercommunication process by creating a new ConfirmedDeleteCommsUnit and assigning it to it’s ToViewCommsUnit property. Because of the twoway binding applied to this property, the View’s ToViewCommsUnit would detect a change and raise an event which lets the View know that the ball is in its court. The View will assess the state of the CommsUnit and discover it is in “RequestingMessagePrompt” state which it knows how to handle. The state would trigger a messagebox and get the title and message from the CommsUnit that is needed to display the messagebox. The result is stored back inside the CommsUnit and the CommsState is changed to “ReturningDialogResult”, to be send back to the ViewModel. The View takes the same CommsUnit (which now contains the MessageBox result and new state) and assigns it to its ToViewModelCommsUnit property which, because of twoway binding, changes the ToViewModelCommsUnit property of the ViewModel. This in effect lets the ViewModel know that the ball is back in its court. The ViewModel discovers that the CommsUnit is in the “ReturningDialogResult” state which it knows how to handle. This will trigger the actual deletion to continue.

The Demo solution that I have written has a BaseView and BaseViewModel class that impliment this pattern or behaviour or what ever you want to call it. These base classes make this pattern easy to use inside all the Views and ViewModels of your project. Declaring base classes for your Views and ViewModels is really standard practice for WPF applications that would benefit from the MVVM pattern anyway.

Things to note about the Demo solution is: I am using Sql CE 3.5 as the datasource and apparently Expression Blend doesn’t support running applications with a sdf database file. VS 2008 works fine with it though. The only thing you need to change for the app to run in VS 2008 is the connection string defined under Settings of the project. SQL CE 3.5 in VS 2008 needs a hard coded path to work properly so update it according to where you have extracted the solution. That’s it.

Please let me know if you have any suggestions on how to improve this intercommunication process or if there is a better way that you have discovered.