Hi all,
I have been trying to make use of the sync framework v2 to implement the following:
SQL 2008 Express <-> SQL 2008 Express
Both with change tracking ON.
Also will just be working with bidirectional sync.
With the example so far, i tried to make the best out of the given examples.
But it seems that i have hit a road block, as the examples are quite confusing, same with the documentation.
I have provided my code here for my main sync, the other file that is required is the SqlExpressClientSyncProvider that came along with the example.
That I have yet to touch on, and it is also where my main headache lies.
Looking for kind souls to guide me along.
Thanks
For full code:
http://www.speedyshare.com/193443982.html
My main code here:
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
namespace Logic
{
public class SQL2008ChangeTracking
{
public event EventHandler<SyncProgressEventArgs> SyncProgress;
public event EventHandler<ApplyChangeFailedEventArgs> ClientApplyChangeFailed;
public event EventHandler<ApplyChangeFailedEventArgs> ServerApplyChangeFailed;
public string Synchronize(string serverCS, string clientCS, string syncMethod, string warehouse, DateTime currentDate)
{
// 1. Create instance of the sync components (client, agent, server)
// This demo illustrates direct connection to server database. In this scenario,
// sync components - client provider, sync agent and server provider - reside at
// the client side. On the server, each table might need to be extended with sync
// related columns to store metadata. This demo adds three more columns to the
// orders and order_details tables for bidirectional sync. The changes are illustrated
// in demo.sql file.
SqlConnection serverConn = new SqlConnection(serverCS);
SqlConnection clientConn = new SqlConnection(clientCS);
DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();
serverSyncProvider.Connection = serverConn;
SqlExpressClientSyncProvider clientSyncProvider = new SqlExpressClientSyncProvider();
clientSyncProvider.Connection = clientConn;
SyncAgent mySyncAgent = new SyncAgent();
mySyncAgent.LocalProvider = clientSyncProvider;
mySyncAgent.RemoteProvider = serverSyncProvider;
// 2. Create SyncTables and SyncGroups
// To sync a table, a SyncTable object needs to be created and setup with desired properties:
// TableCreationOption tells the agent how to initialize the new table in the local database
// SyncDirection is how changes from with respect to client {Download, Upload, Bidirectional or Snapshot}
SyncTable mtn0130SyncTable = new SyncTable("mtn0130");
mtn0130SyncTable.CreationOption = TableCreationOption.UseExistingTableOrFail;
mtn0130SyncTable.SyncDirection = SyncDirection.Bidirectional;
// 3. Sync changes for both tables as one bunch, using SyncGroup object
// This is important if the tables has PK-FK relationship, grouping will ensure that
// and FK change won't be applied before its PK is applied
SyncGroup allGroup = new SyncGroup("AllChanges");
mtn0130SyncTable.SyncGroup = allGroup;
// 4. Add SyncTables to SyncAgent
mySyncAgent.Configuration.SyncTables.Add(mtn0130SyncTable);
// 5. Create sync adapter for each sync table and attach it to the agent
// Following DataAdapter style in ADO.NET, SyncAdapter is the equivalent for
// Sync. The code below shows how to create SyncAdapter objects for orders
// and order_details using stored procedures stored on the server side. In case
// the adding code to server database is not allowed, the sprocs call can
// be replaced with TSQL statements.
//
// Sync framework defines a set of sync-specific built in parameters; a quick overview
// of the one i used in this sample:
// SyncClientIdHash: Each client has a unique guid as its id; this parameter is the hash value of this guid,
// it is of type int. I use it to setup originator id column
// SyncLastReceivedAnchor: This is a marker of type timestamp that indicates the last time the client
// synchronized with the server.
// SyncNewReceivedAnchor: This is a marker that is generated on ther server (using SelectNewAnchorCommand)
// that calculate the new marker of the new sync.
// Both last and new received anchor values defines the window of changes to enumerated from the server. That
// said, the SelectIncrementalInserts\Updates\Deletes commands will need to select all changes in this window
// during sync as you can see in the sprocs code.
// SyncRowCount: The framwork needs to know if the attempt to apply insert, update or delete has succeeded or
// not, this parameter is an out parameter and used to indicate success or failure.
SqlSyncAdapterBuilder mtn0130BuilderServer = new SqlSyncAdapterBuilder(serverConn);
mtn0130BuilderServer.TableName = "mtn0130";
mtn0130BuilderServer.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
mtn0130BuilderServer.SyncDirection = SyncDirection.Bidirectional;
SyncAdapter mtn0130SyncAdapterServer = mtn0130BuilderServer.ToSyncAdapter();
mtn0130SyncAdapterServer.TableName = "mtn0130";
serverSyncProvider.SyncAdapters.Add(mtn0130SyncAdapterServer);
// 5b. Create sync adapter for each sync table and attach it to the agent
// Following DataAdapter style in ADO.NET, SyncAdapter is the equivalent for
// Sync. The code below shows how to create SyncAdapter objects for orders
// and order_details using stored procedures stored on the server side. In case
// the adding code to server database is not allowed, the sprocs call can
// be replaced with TSQL statements.
// This code is for the Client-side
SqlSyncAdapterBuilder mtn0130BuilderClient= new SqlSyncAdapterBuilder(clientConn);
mtn0130BuilderClient.TableName = "mtn0130";
mtn0130BuilderClient.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
mtn0130BuilderClient.SyncDirection = SyncDirection.Bidirectional;
SyncAdapter mtn0130SyncAdapterClient = mtn0130BuilderClient.ToSyncAdapter();
mtn0130SyncAdapterClient.TableName = "mtn0130";
clientSyncProvider.SyncAdapters.Add(mtn0130SyncAdapterClient);
// 6. Create a command to retrieve a new anchor value from
// the server. In this case, we use a timestamp value
// that is retrieved and stored in the client database.
// During each synchronization, the new anchor value and
// the last anchor value from the previous synchronization
// are used: the set of changes between these upper and
// lower bounds is synchronized.
//
// SyncSession.SyncNewReceivedAnchor is a string constant;
// you could also use @sync_new_received_anchor directly in
// your queries.
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
"SELECT " + newAnchorVariable + " = change_tracking_current_version()"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
serverSyncProvider.SelectNewAnchorCommand = selectNewAnchorCommand;
clientSyncProvider.SelectNewAnchorCommand = selectNewAnchorCommand;
// client ID command
// No need to setup this command since we are using the client id hash values
// Unsure if this is required?
// 7. Kickoff sync process
serverSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(serverSyncProvider_SyncProgress);
serverSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(serverSyncProvider_ApplyChangeFailed);
clientSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(clientSyncProvider_ApplyChangeFailed);
SyncStatistics syncStatistics = mySyncAgent.Synchronize();
return DisplayStats(syncStatistics);
}
public string DisplayStats(SyncStatistics syncStatistics)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("****** Begin Synchronization ******");
sb.AppendLine("Start Time: " + syncStatistics.SyncStartTime);
sb.AppendLine("Complete Time: " + syncStatistics.SyncCompleteTime);
sb.AppendLine("----------------------------------");
sb.AppendLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
sb.AppendLine("Download Changes Applied: " + syncStatistics.DownloadChangesApplied);
sb.AppendLine("Download Changes Failed: " + syncStatistics.DownloadChangesFailed);
sb.AppendLine("----------------------------------");
sb.AppendLine("Total Changes Uploaded: " + syncStatistics.TotalChangesUploaded);
sb.AppendLine("Upload Changes Applied: " + syncStatistics.UploadChangesApplied);
sb.AppendLine("Upload Changes Failed: " + syncStatistics.UploadChangesFailed);
sb.AppendLine("****** End Synchronization ******");
return sb.ToString();
}
void clientSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)
{
if (ClientApplyChangeFailed != null)
{
ClientApplyChangeFailed(sender, e);
}
}
void serverSyncProvider_ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)
{
if (ServerApplyChangeFailed != null)
{
ServerApplyChangeFailed(sender, e);
}
}
void serverSyncProvider_SyncProgress(object sender, SyncProgressEventArgs e)
{
if (SyncProgress != null)
{
SyncProgress(this, e);
}
}
}
}
ic3dwabit
