Part 4 – Synchronizing Tables With Different Table Names and Column Names


I remember having answered several times how to sync tables with different schemas and I actually thought I have a blog post on it. The other day, the same question popped up again in the forums :Can we sync two database with different schemas and different tables using Sync Framework

I tried to answer the question by posting links to my previous blog posts related to the question and was surprised to find out I haven’t previously blogged about synchronizing tables with different table and column names. I couldn’t find the sample codes I wrote  either.

So, here you go.

Assuming we have the following tables with different table names and column names.

image

image

Here’s the complete code for synchronizing the above tables.

 

Code Snippet
  1. class Program
  2. {
  3.     static void Main()
  4.     {
  5.         //setup the connections
  6.         var serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncFxLabs; Integrated Security=True");
  7.         var clientConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncFxClient; Integrated Security=True");
  8.  
  9.         //setup scope name
  10.         const string scopeName = "DifferentSchemaScope";
  11.  
  12.         //provision server
  13.         var serverProvision = new SqlSyncScopeProvisioning(serverConn);
  14.         if (!serverProvision.ScopeExists(scopeName))
  15.         {
  16.             var serverScopeDesc = new DbSyncScopeDescription(scopeName);
  17.  
  18.             // add the server table
  19.             var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("SourceOrderTable", serverConn);
  20.  
  21.             // our server and client tables has different names, so let’s setup a common name
  22.             serverTableDesc.GlobalName = "OrderTable";
  23.  
  24.             serverScopeDesc.Tables.Add(serverTableDesc);
  25.  
  26.             serverProvision.PopulateFromScopeDescription(serverScopeDesc);
  27.  
  28.             //apply the scope definition
  29.             serverProvision.Apply();
  30.         }
  31.  
  32.         //provision client
  33.         var clientProvision = new SqlSyncScopeProvisioning(clientConn);
  34.         if (!clientProvision.ScopeExists(scopeName))
  35.         {
  36.             var clientScopeDesc = new DbSyncScopeDescription(scopeName);
  37.  
  38.             // add the client table
  39.             var clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("DestinationOrderTable", clientConn);
  40.             clientTableDesc.GlobalName = "OrderTable";
  41.  
  42.             clientScopeDesc.Tables.Add(clientTableDesc);
  43.  
  44.             // remove the OrderQty column since the Source table doesnt have it
  45.             clientScopeDesc.Tables["OrderTable"].Columns.Remove(clientScopeDesc.Tables["OrderTable"].Columns["OrderQty"]);
  46.  
  47.             clientProvision.PopulateFromScopeDescription(clientScopeDesc);
  48.  
  49.             //apply the scope definition
  50.             clientProvision.Apply();
  51.         }
  52.  
  53.         // create the sync orchestrator
  54.         var syncOrchestrator = new SyncOrchestrator();
  55.  
  56.         //setup providers
  57.         var localProvider = new SqlSyncProvider(scopeName, clientConn);
  58.         var remoteProvider = new SqlSyncProvider(scopeName, serverConn);
  59.  
  60.         // lets intercept the changes so we can rename the columns
  61.         remoteProvider.ChangesSelected += new EventHandler<DbChangesSelectedEventArgs>(remoteProvider_ChangesSelected);
  62.  
  63.         syncOrchestrator.LocalProvider = localProvider;
  64.         syncOrchestrator.RemoteProvider = remoteProvider;
  65.  
  66.         // set the direction of sync session Download
  67.         syncOrchestrator.Direction = SyncDirectionOrder.Download;
  68.  
  69.         // execute the synchronization process
  70.         var syncStats = syncOrchestrator.Synchronize();
  71.  
  72.         // print statistics
  73.         Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
  74.         Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
  75.         Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
  76.         Console.WriteLine(String.Empty);
  77.         Console.ReadKey();
  78.  
  79.     }
  80.  
  81.     static void remoteProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)
  82.     {
  83.         //let’s check if we’re synching the table we’re interested
  84.         if (e.Context.DataSet.Tables.Contains("OrderTable"))
  85.         {
  86.             var dataTable = e.Context.DataSet.Tables["OrderTable"];
  87.  
  88.             //rename the columns to match the destination table’s column names
  89.             dataTable.Columns["OrderId"].ColumnName = "OrderNo";
  90.             dataTable.Columns["OrderDesc"].ColumnName = "OrderDetail";
  91.  
  92.         }
  93.     }
  94. }

Both tables are added to the scope using standard provisioning code. Since we have different table names, we assign a common name to the tables using the GlobalName property (see: Synching tables with different table names)

Unfortunately, there is no such property for column names. So we have to let Sync Framework provision the tables as it is and intercept the changes so we can map the column names to the target table before the changes are applied.

We do this by subscribing to the ChangesSelected event, inspecting the dataset for the changes that was selected, then accessing the DataTable and changing the column names to match the destination table’s column names and let the dataset flow thru to the destination.

So there you go, we’re now able to sync tables with different names and column names.  You can do other transformations as well using the same approach such as  combining multiple columns into a single column (e.g., Firstname + Lastname mapped to Fullname) or even mapping different data types. I’ll try to post  samples for the latter scenarios on subsequent posts.

Hope you find this useful.

Cheers,

JuneT

Advertisements

13 comments

  1. what if the destination is mysql and not sql? your code works great if both the sources are sql… is it easy to extend this code to interop with mysql?

    1. there’s on out-of-the-box sync provider for mySql so you will have to write your own custom sync provider.

  2. it is not working :

    //rename the columns to match the destination table’s column names
    dataTable.Columns[“OrderId”].ColumnName = “OrderNo”;
    dataTable.Columns[“OrderDesc”].ColumnName = “OrderDetail”;

    datatable doesnt include orderid and orderdesc. it has got OrderNo and OrderDetail

    NullReferenceException : Object reference not set to an instance of an object.

    1. are you sure you’re doing it right? without the code or the exact stack trace, hard to tell what’s going on

  3. How can i post my codes?

  4. You can download here my code what is wrong?

    https://app.box.com/s/ssm0x1ufsylwyj2vu23f

    My Sql below:

    id should be primary key….

    CREATE SCHEMA myTest

    CREATE TABLE [myTest].[SourceOrderTable](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [OrderDesc] [nvarchar](50) NULL)

    CREATE TABLE [myTest].[DestinationOrderTable](
    [OrderNo] [int] IDENTITY(1,1) NOT NULL,
    [OrderDetail] [nvarchar](50) NULL,
    [OrderQty] int NULL)

    1. i have already replied to the same on StackOverflow

  5. Did you download my code?

  6. Jared Crosby · · Reply

    Have you written a blog on the different datatypes yet? My scenario is my local SQL Server has int PK values but my Azure DB uses GUIDs for identity columns. My plan is to use what you show in blog post…

    https://jtabadero.wordpress.com/2011/07/20/part-3-synchronizing-tables-where-client-and-server-primary-keys-are-different/

    so that the primary key issue is resolved. The next problem I have is with foreign keys in other tables. They are converted from ints to GUIDs as well and I would need to use a SQL query to transform the ID to link it to the correct GUID.

    1. the article you referred to is not converting PKs from one data type to another. it’s more of “tricking” Sync Fx to use another column as a PK. While you can use the same trick of intercepting and manipulating the change dataset before they can applied, i wouldn’t normally recommend the same approach where the type of manipulation done is more on transforming the data that is best done by other tools.

  7. Failed to execute the command ‘BulkInsertCommand’ for table ‘OrderTable’; the transaction was rolled back. Ensure that the command syntax is correct.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: