Good Day Everyone,
I know, the versions I am talking about are rather obsolete by now but that's the tools I am stuck to work with at the workplace. This is my first question on StackOverflow, and I hope I will get the formatting right hehe ;-) Please pardon me for the long text, I am used to give a lot of details and, in a sense, I feel the more details I provide the more accurate might be the answers ;-)
In almost 10 years working in IT, I've always been able to find answers to my questions (i.e. solutions to my problems) by Googling well chosen keywords and expressions. Well, it looks like the aforementioned Sync Framework is either not very well known to the Internet community, or it is a real pain to try to understand its simplest concepts for most mortals. After extensive research I have to found a single, simple example of syncing SQL Express using Sync Framework 1.0 and the C# language, not even on MSDN! I am fairly new to ASP.NET / C# but I understand the concepts and I have a working web application that successfully stores and retrieve data from an SQL Server 2008 database. It has been in use by clients for two years now. We now have a requirement for the clients to be able to bring their data offline and be able to update it offline and then sync with the server. UPDATEs, INSERTs and DELETEs will occur at both ends.
What I am trying to find is VERY simple (or so I thought): C# code examples that uses SQL Server Change Tracking information (NOT custom Change Tracking) to sync the server (SQL Server 2008) and client computers (SQL Server 2008 Express, NOT Compact Edition). The simplest case would be a single table with few columns. I am fairly confident to understand the SQL Server part and I've prepared both sides of the database to receive sync requests from the client web application (enabled Change Tracking, PrimaryKeyID has data type GUID, the application's user account on the server has VIEW_CHANGE_TRACKING permission, etc. etc.)
I know it's the web application that serves as the interface between the two and that manage the sync session (in C#). I was quite naive to think that the only thing left to do was to provide the two connection strings, tell what tables are to sync and specify a bidirectional sync. Apparently, that's more complicated than that hehe. In a desperate attempt, I have tried to base my code on the following from Microsoft and adapt it to SQL Express (the example is for Compact). I am close to admit defeat and shamefully lower my head :-(
http://msdn.microsoft.com/en-us/library/bb726015%28v=sql.100%29.aspx
Based on the above (second section "Complete Example Using SQL Server Change Tracking"), I removed everything I do not need: things related to the password, the statistics, and the code applying changes to the data. I've also removed MS' numerous comment lines for clarity. I've manually applied changes at the SQL Server itself at both ends executing scripts in SSMS (and therefore there must be Change Tracking info that was generated and that is usable when the web app will request the sync). QUESTION 1: Am I wrong in saying so? Finally, I changed some stuff in an attempt to use objects relevant to SQL Express instead of Compact.
QUESTION 2: The code at Microsoft is apparently able to tell whether it is the initial (first) or subsequent sync of this replica. I don't have a clue how it can!
In the end, the code left in its simplest form is as below (with QUESTIONS 3, 4, 5 ;-), but shows some errors. I thank you VERY MUCH in advance for your help. Any comments and/or suggestions are welcome. I am sure if/when this is resolved it will benefit quite a lot of people out there. I will continue to research on it on my end (the boss won't give me a choice ;-) and I promise I will post the solution here if I ever succeed in syncing!
Thanks and have a great day everyone!
Kindest Regards,
Zyxy
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
//using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
//using Microsoft.Synchronization.Data.SqlServerCe;
namespace some_namespace
{
public class SyncProgram
{
public SyncProgram()
{
// empty constructor
}
public static bool MainSync() // Entry point, say, called by a Sync button on an ASPX page.
{
bool boolSyncRes = false; // tells whether sync was a success or not
// Initial sync: they create a new instance of the Orchestrator.
ZyxySyncOrchestrator zyxySyncOrchestrator = new ZyxySyncOrchestrator();
// Subsequent synchronization.
// They don't. there was only irrelevant stats stuff here.
boolSyncRes = true;
return boolSyncRes;
}
}
public class ZyxySyncOrchestrator : SyncOrchestrator
{
public ZyxySyncOrchestrator()
{
Utility util = new Utility();
this.LocalProvider = new ZyxyServerSyncProvider(); // QUESTION 3: ??? cannot implicitly convert type DbServerSyncProvider to Microsoft.Synchronization.SyncProvider
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new ZyxyServerSyncProvider(); // cannot implicitly convert type DbServerSyncProvider to Microsoft.Synchronization.SyncProvider
// QUESTION 4: Is the following code actually creating the base (user) table ZyxySync
// (as opposed to its change tracking metadata table)??
// I wasn't sure whether this part of the code on Microsoft's webpage was part of
// populating the db with sample data and structure or if it's really meant to deal with
// the change tracking metadata.
SyncTable zyxySyncTable = new SyncTable("ZyxySync");
zyxySyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
zyxySyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(zyxySyncTable);
}
}
//Create a class that is derived from Microsoft.Synchronization.Server.DbServerSyncProvider.
public class ZyxyServerSyncProvider : DbServerSyncProvider
{
public ZyxyServerSyncProvider()
{
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Retrieve a new anchor value from the server. We use a timestamp value
//that is retrieved and stored in the client database.
//During each sync the new and last anchor values are used to determine the set of changes
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = change_tracking_current_version()";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a SyncAdapter for the ZyxySync table by using
//the SqlSyncAdapterBuilder.
// Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
SqlSyncAdapterBuilder zyxyBuilder = new SqlSyncAdapterBuilder(serverConn);
zyxyBuilder.TableName = "dbo.ZyxySync";
zyxyBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
SyncAdapter zyxySyncAdapter = zyxyBuilder.ToSyncAdapter();
zyxySyncAdapter.TableName = "ZyxySync";
this.SyncAdapters.Add(zyxySyncAdapter);
}
}
// Class derived from Microsoft.Synchronization.Data.Server.DbServerSyncProvider
// QUESTION 5: Or should have I used the two below? I believe they only apply to SQL Compact...
//Microsoft.Synchronization.Data.ClientSyncProvider
//Microsoft.Synchronization.Data.ServerSyncProvider
//http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.clientsyncprovider%28v=sql.100%29.aspx
//http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.server.dbserversyncprovider%28d=printer,v=sql.100%29.aspx
public class ZyxyClientSyncProvider : DbServerSyncProvider
{
public ZyxyClientSyncProvider()
{
Utility util = new Utility();
SqlConnection clientConn = new SqlConnection(util.ClientConnString);
this.Connection = clientConn;
}
}
public class Utility
{
public string ClientConnString
{
get { return @"Data Source=localhost\LocalExpressInstance;Initial Catalog=DatabaseName;User ID=UserName;Password=WontTellYou;"; }
}
public string ServerConnString
{
get { return @" Data Source=ServerName\ServerInstance;Initial Catalog=DatabaseName;User ID=UserName;Password=WontTellYou;"; }
}
}
}
the SyncOrchestrator will not work with the DBServerSyncProvider.
In Sync Framework, there are two types of database providers: the offline provider and the peer-to-peer/collaboration providers. (they both work in offline scenario so thats confusing).
the offline provider is used in hub-spoke topologies. Only the client tracks what was synched. The server doesnt even know its part of a sync. this is the same provider used by the Local Database Cache Project item in Visual Studio. The only supported databases out of the box is SqlCeClientSyncProvider and DBServerSyncProvider and uses the SyncAgent to synchronize.
the peer-to-peer provider can be used in a peer-to-peer sync as well as hub-spoke scenarios. Each peer maintains metadata on what was synched. This uses the much newer SyncOrchestrator/SqlCeSyncProvider/SqlSyncProvider(works with SQL Server, Express, LocalDB, and SQL Azure). This uses a custom change tracking.
you can't interchange the providers used by SyncAgent and SyncOrchestrator. You can'r reuse the SQL commands either because they differ in the way they track, select, apply changes and record what was synched.