Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode II

| April 9, 2012 | 6 Comments

In my last article, I walked you through finding, downloading, installing, and configuring SQL Server 2012 Express, SQL Server Compact 3.5 SP2 CU6, and the Sync Server Tools.  With that series of tasks completed, you’re now capable of performing data synchronization with a mobile Windows client.

Open SQL Server 2012 Management Studio and connect to the local SQL Express instance.  You’ll quickly notice the new Visual Studio 2010 IDE look and feel.  Since you’re going to need a database to sync with, right-click on the Databases folder in the Object Explorer and select New Database.  Type ContosoBottling in the Database name text box and click OK.  I want you to create three simple tables for the purposes of this article:

DistributionCenters
Column PK Data Type Nulls Defaults RowGuid
DistributionCenterId Yes uniqueidentifier No newid() Yes
Name nchar(20) Yes No

 

Routes
Column PK Data Type Nulls Defaults RowGuid
RouteId Yes uniqueidentifier No newid() Yes
DistributionCenterId uniqueidentifier Yes No
Name nchar(20) Yes No

 

Drivers
Column PK Data Type Nulls Defaults RowGuid
DriverId Yes uniqueidentifier No newid() Yes
RouteId uniqueidentifier Yes No
FirstName nchar(20) Yes No
LastName nchar(20) Yes No

If you read my last book on Enterprise Data Replication, these tables that support the operations of a delivery driver should look familiar to you.  Since RDA doesn’t support the Identity Range feature of Merge Replication, you’ll be using GUIDs for your primary keys to ensure uniqueness.  Since the offline data capabilities of sync technologies from all vendors are based on the notion of optimistic concurrency, having a globally unique primary key that won’t collide with inserts and updates made by one or more devices is critical to success.  Now it’s time to fill these tables with some sample data to get started:

Right-click on DistributionCenters and select Edit Top 200 Rows.  Type Seattle for the Name in the first row and Redmond for the Name in the second row.  Allow the DistributionCenterId uniqueidentifier values to be automatically created.  It should look something like this:

Distribution Center

 

 

 

 

Now it’s time to tackle the Routes table.  Each Distribution Center will have multiple routes that it supplies products to.  Right-click on Routes and select Edit Top 200 Rows.  Like before, allow the RouteId uniqueidentifier values to be automatically created.  The eight rows of data I want you to enter should be as follows:

  1. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Magnolia.
  2. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Ballard.
  3. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Fremont.
  4. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Wallingford.
  5. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Kirkland.
  6. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Bellevue.
  7. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Issaquah.
  8. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Sammamish.

It should look something like this:

Routes

 

Last but not least, we have the Drivers.  Each of these folks will be assigned to a particular route on any given day.  Right-click on Drivers and select Edit Top 200 Rows.  For each row, allow the DriverId uniqueidentifier values to be automatically created.  I’ll just have you enter a couple of drivers for this table:

  1. The RouteId should equal the related Magnolia value from the Routes table and the FirstName should equal Dave and the LastName should equal Bottomley.
  2. The RouteId should equal the related Kirkland value from the Routes table and the FirstName should equal Khalid and the LastName should equal Siddiqui.

It should look something like this:

Drivers

 

Now that your sample database has some data inside, it’s time to build a sample Windows app so fire up Visual Studio 2010.  Create a Windows Forms or WPF application and call the Solution SimpleSync.  The first thing I want you to do is go to the Solution Explorer, right-click on References, and add a reference to System.Data.SqlServerCe.  To make sure you’re working with the newest bits based on Cumulative Update package 6, in the Add Reference dialog, click the Browse tab and navigate to C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll.

With those pieces in place, it’s time to write some code.

Unlike Merge Replication that automatically creates a SQL Server Compact database for you during the initial sync, with RDA you’ll need to create it in code with the SqlCeEngine object.  So before your app can start synchronizing data, you’ll need to first create a database with the following code:

if (!File.Exists(“ContosoBottling.sdf”))
{
using (SqlCeEngine sqlEngine = new SqlCeEngine())
{
sqlEngine.LocalConnectionString = “Data Source=ContosoBottling.sdf”;
sqlEngine.CreateDatabase();
}
}

The first thing the above code does is to check and see if a SQL Server Compact database already exists.  If not, then the SqlCeEngine object is instantiated.  The LocalConnectionString property is set to the path of where you want the database to reside.  In this case, I didn’t enter a path so the database will be created in the same folder as the app’s exe.  Keep in mind that a number of other parameters can be used for this property to support password protection and encryption among others.  Next, you just call the CreateDatabase() method and that’s all there is to it.  You will now have an empty shell of a database that typically weighs in at 20 KB.

With a local database created, you can begin retrieving data.  In my next article I’ll discuss how to RDA filters and pulls both data and indexes, enables local change-tracking, and pushes new data and updates back to SQL Server.

If you’re ready to go deep on this sync technology to build enterprise apps that run on Windows tablets and laptops, click this link to check out and purchase my book, “Keeping Windows 8 Tablets in Sync with SQL Server 2012.”

-Rob

Tags: , , , ,

Category: Sync

About the Author ()

A mobile strategist and cloud architect at Microsoft, Rob has spent his career as an entrepreneur, advisor, teacher, developer, speaker, and author of bestselling books on mobile and wireless technologies. A pioneer of the smartphone revolution, he drove the development of the mobile app ecosystem from its earliest days and co-founded the world’s first cloud-based mobile device management company.

Comments (6)

Trackback URL | Comments RSS Feed

  1. Terry Balcom says:

    Rob,

    I am running in to an issue with merge replication and SQL 2012. I have a Standard installation of SQL 2012 on our server with a Merge Publication setup. On a remote laptop, I have sql 2008 R2 Express installed (using windows XP). When attempting to create the subscription on the 2008 R2 express, it tells me that the subscriber cannot be a newer version than the publisher. However, its not. The subscriber is 2008 R2 and the publisher is 2012. Any ideas?

    Thanks

    Terry Balcom

    • Rob Tiffany says:

      Hi Terry. I haven’t tried out merge on SQL 2012 yet. That’s definitely a counterintuitive error message.

      - Rob

  2. lisa says:

    Hi Rob,
    I am hoping that you can help me. I have gone through your tutorial, have tested that I receive “Microsoft SQL Server Compact Server Agent” when entering the localhost address, and have created a compact database using the code you outlined above. However, when it comes to the pull, I get an error that RDA cannot connect to my database! I am using this same database in another application with an appconfig of: . For this exercise, I have used: string rdaConnection = @”System.Data.SqlClient;Data Source=MyComputer\\SQLEXPRESS;Initial Catalog=MyTable;Integrated Security=True”;
    string rdaOleDbConnectString = @”Provider=SQLOLEDB;Data Source=MyComputer\\SQLEXPRESS;Initial Catalog=MyTable;User Id = MYDomain\\myUsername;Password =mypassword;”; …as well as many other variations using both integrated security and username/password. I have made sure that anonymous access is enabled in IIS on the new RDA folder. I have NOT set up merge replication in SQLEXPRESS, as I didn’t think I had to when using RDA (and SQLEXPRESS doesn’t support it anyway, right?). I cannot figure out why RDA is not connecting to my Express instance of SQL server…any ideas? Oh, and I am using SQL Server 2008…could that be the difference? If so, is there anything I can do to get it to work on 2008? Thanks for any help you can give…
    Lisa

  3. lisa says:

    Sorry, onces posted I realized the app config string didn’t show up…here it is: connectionString=”Data Source=MyComputer\SQLEXPRESS;Initial Catalog=MyTable;Integrated Security=True” providerName=”System.Data.SqlClient”
    Thanks!

  4. abid says:

    Hi. Rob i need your help urgent.
    I m trying to make a database synchronization software. and devices are my laptop as server and Mobile device (cell phone or PDA) as data collector. so for this purpose i m trying make client side software in Visual studio 2010(mobile project). but when i try to add reference as u mentioned above it is giving me error that this reference can’t be loaded in mobile project.
    i have tried many things for this.like as i use an other windows project and add that reference in that and then i try to use p2p reference but is is not working in that condition also.
    Please tell me wht should i do.????????

    • Rob Tiffany says:

      Hi Abid,
      In the 3 articles on RDA with SQL 2012, my instructions and guidance were specifically limited to building Windows apps for desktops, laptops and tablets. They didn’t cover Windows Mobile 6.x development. The bits needed when adding a reference would therefore be different then what I discussed.

      -Rob

Leave a Reply