Using Microsoft Synchronization Services For ADO.NET (Introducing Microsoft SQL Server Compact Edition (Part II))

In the previous part of the tutorial we learnt how to use SQL Server Compact Edition in our application. In this part we are going to learn how to synchronize the data between Compact Edition and a regular verson of SQL Server.

Visual Studio 2008 has built-in Synchronization Services that we’ll be using in our project.

Create a new Windows Forms Application. In Server Explorer create a new database; create a table with just two fields:

ID
SampleColumn

Then, go to Project->Add Component->Data->Local Database Cache

Find the newly created database and choose the only table. Click ‘Ok’. When it will ask you to create a DataSet, just click ‘Cancel’, we won’t use any datasets in this project. As you can see it adds to field to our table, one is responsible for the last created item and the other one for the last update item. Hopefully, it also provides the SQL scripts for undoing these changes.

It’s necessary to make the synchonization bi-directional, so that it allows us to syncrhonize in both directions. Unfortunately it cannot be done in the design mode, so we have to write some code. Right-click on the .sync file -> View Code, it will create a new file containing a partial class. Add this piece of code:

namespace SyncServices1 {
    public partial class LocalDataCache2SyncAgent {
        partial void OnInitialized(){
            SampleTable.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional; // if the table is called SampleTable
        }
    }
}

Then, let’s make a very simple form. Place two ListBoxes, one will be for the remote data and one for the local. Place a button as well that will trigger a syncrhonization process. Put also a textbox and a button that will be used for inserting data.

Now add some the code that will retrieve the data from the remote database and fill the approprate list, do the same for the local data.

using (SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=SyncServices1;Integrated Security=True;"))
{

    con.Open();
    SqlCommand comSelect = new SqlCommand("SELECT SampleColumn FROM SampleTable", con);

    SqlDataReader reader = comSelect.ExecuteReader();

    listServer.Items.Clear();
    while (reader.Read())
    {
        listServer.Items.Add(reader["SampleColumn"].ToString());
    }

    con.Close();
}

Then, go to the design view of the *.sync file, hit the ‘code’ button’ and copy the code. Add this code to the button OnClick event. It updates the databases only, so we have to add some code to update the view (form) of our application, the do to section hints at that.

private void btnSync_Click(object sender, EventArgs e)
{
    LocalDataCache2SyncAgent syncAgent = new LocalDataCache2SyncAgent();
    Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();

    FillServerList();
    FillLocalList();
}

So, let’s add some sample data to our remote server. Then, compile the project. See that the left box has the data, while the right one is empty, hit the ‘Sync’ button, and they will become synchonized.

Now, add some code that will handle inserting data in our local database.

Let’s add some text and click the ‘Sync’ button, so that the server will also get updated.

Conclususion

Microsoft Synchronization Services for ADO.NET is a great tool when you need to develop occasionally connected applications that require data synchronization. In the tutorial we saw how it's easy to peform such a synchronization.

Resources

Microsoft Sync Framework Developer Center

Microsoft Synchronization Services for ADO.NET Books Online

Tags: databases

Posted by Mike Borozdin on Tuesday, July 22, 2008 5:20 PM GMT

Permalink | Comments (1) | Post RSS RSS comment feed

Introducing Microsoft SQL Server Compact Edition (Part I)

Microsoft SQL Server Compact Edition is an embeded database that allows you to integrate it in your destkop and mobile applications. It can be used in a variety of scenarios, just some examples:

a single-user application that still stores its data in the SQL database
a local cache of a remote SQL Server database,
a mobile application that synchronizes its local compact database with a remote database from time to time.

SQL Server Compact Edition takes about 1.5 MB on HDD and consumes about 5 MB of RAM. It’s free to use and free to distribute with your application.

The 3.5 version is shipped with Visual Studio 2008, however if you don’t have it, you can download it here.

In this part of the tutorial we will learn how to:

Create a desktop application that interacts with SQL Server CE by using stronly-typed datasets
Deploy a desktop application with emdeded SQL Server Compact Edition
Use SQL queries with SQL Server Compact Edition
Use LINQ with SQL Server Compact Edition

Desktop Application and DataSets

Create a new project, choose Windows Forms Application. Then go to Project -> Add Component and choose Local Database.

We will be prompted to create a DataSet, let’s do that.

Then find the newly created database in the Solution Explorer, it must be called Database1.sdf depending on the name you gave it, of course, double click on it, Server Explorer gets expanded, let’s create a table.

It looks exactly like if we were working with a regular SQL database.

Now to Data Sources, right-click on the only avaliable DataSet and choose Configure DataSet with Wizard

Add the only table. Then right-click on it, select Details and drag the Products table on the form.

You must get the form like this

Compile the project and test it. It works :-)!

Deployment

Ok, now we are going to make an installer for our project that will also include the data file and the files necessary for SQL Server Compact Edition.
Let’s add the setup wizard project to our solution. It is located it Other Project Types -> Setup and Deployment -> Setup Wizard

Then we select Setup for Windows application and Primary Output for our project. We should include the Database file – Datase1.sdf and the files necessary for running SQL Server Compact Edition. Open C:\Program Files\Microsoft SQL Server Compact Edition\3.5 and select all the DLLs.

Click Finish then. Build the application and the setup project and try to install it, it must be working.

SQL Queries
In the previous example we didn’t write a single line of code. But in real project we have to. Let’s see, how we canuse SQL with SQL Server Compact Edition. This time we are going to create a console application.
Create or include the previously created SDF database.
You have to add a reference to Compact Edition assembly that is called System.Data.SqlServerCe and of course you have to add the necessary namespace. Executing SQL queries with Compact Edition is no harder than doing the same with SQL Server, you just have to use the classed that have ‘Ce’ at the end.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace SqlCeConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlCeConnection con = new SqlCeConnection("Data Source=Db.sdf");
            con.Open();

            SqlCeCommand comInsert = new SqlCeCommand("INSERT INTO Products(Title, Category) VALUES('Ferrari F40', 'Sport cars')", con);
            comInsert.ExecuteNonQuery();

            SqlCeCommand comSelect = new SqlCeCommand("SELECT * FROM Products", con);
            SqlCeDataReader reader = comSelect.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine("{0} {1}", reader["Title"], reader["Category"]);
            }

            con.Close();
        }
    }
}

LINQ

You can use LINQ with SQL Server CE, as well, the main difference is that you cannot use the visual designer which generates an error when dragging CE tables, however you can use a command line utilily called SqlMetal, just type:

SqlMetal Database1.sdf /dbml:Database1.dbml

Then include the generated file in your project and Visual Studio will easly pick it up, then you can write LINQ queries as usual.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace SqlCeConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            Db db = new Db(@"Data source=Db.sdf");

            var result = from p in db.Products
                         select p;

            foreach (var product in result)
            {
                Console.WriteLine(product.Title);
            }
        }
    }
}