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.

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