public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//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.
//<snippetOCS_CS_Basic_NewAnchorCommand>
SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//</snippetOCS_CS_Basic_NewAnchorCommand>
//Create a SyncAdapter for the Customer table by using
//the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// changes are made.
// * Specify download-only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//<snippetOCS_CS_Basic_CustomerAdapterBuilder>
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//</snippetOCS_CS_Basic_CustomerAdapterBuilder>
}
// SqlCeConnection ce = null;
public void CreateInitialLocalDB(string strConnectionString, bool isCreated)
{
try
{
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
clientSyncProvider = new SqlCeClientSyncProvider(strClientConnectionString);
if (!isCreated)
{
SqlCeEngine clientEngine = new SqlCeEngine(strClientConnectionString);
clientEngine.CreateDatabase();
clientEngine.Dispose();
tblCallTable = CreateCallTable();
tblLeadsTable = CreateLeadsTable();
tblCallBackTable = CreateCallBackTable();
}
else
{
tblCallTable = new SyncTable("Call");
tblCallTable.SyncDirection = SyncDirection.UploadOnly;
tblLeadsTable = new SyncTable("Leads");
tblLeadsTable.SyncDirection = SyncDirection.UploadOnly;
tblCallBackTable = new SyncTable("CallBack");
tblCallBackTable.SyncDirection = SyncDirection.UploadOnly;
}
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
serverSyncProvider = new DbServerSyncProvider();
syncAgent = new SyncAgent();
// syncAgent.ServerSyncProvider = serverSyncProvider;
syncAgent.RemoteProvider = serverSyncProvider;
serverConnection = new SqlConnection(VMuktiInfo.MainConnectionString);
serverSyncProvider.Connection = serverConnection;
//SqlCommand cmdAnchor = new SqlCommand();
// cmdAnchor.CommandType = CommandType.Text;
// cmdAnchor.CommandText = "SELECT @@DBTS";
// serverSyncProvider.SelectNewAnchorCommand = cmdAnchor;
// SqlCommand cmdClientId = new SqlCommand();
// cmdClientId.CommandType = CommandType.Text;
// cmdClientId.CommandText = "SELECT 1";
// serverSyncProvider.SelectClientIdCommand = cmdClientId;
//syncAgent.ClientSyncProvider = clientSyncProvider;
syncAgent.LocalProvider = clientSyncProvider;
myGroup = new SyncGroup("DialerGroup");
tblCallTable.SyncGroup = myGroup;
tblLeadsTable.SyncGroup = myGroup;
tblCallBackTable.SyncGroup = myGroup;
//syncAgent.SyncTables.Add(tblCallTable);
//syncAgent.SyncTables.Add(tblLeadsTable);
//syncAgent.SyncTables.Add(tblCallBackTable);
syncAgent.Configuration.SyncTables.Add(tblCallTable);
syncAgent.Configuration.SyncTables.Add(tblLeadsTable);
syncAgent.Configuration.SyncTables.Add(tblCallBackTable);
CallAdapter = new SqlSyncAdapterBuilder();
CallAdapter.Connection = serverConnection;
CallAdapter.SyncDirection = SyncDirection.UploadOnly;
CallAdapter.TableName = "Call";
CallAdapter.DataColumns.Add("ID");
CallAdapter.DataColumns.Add("LeadID");
CallAdapter.DataColumns.Add("CalledDate");
CallAdapter.DataColumns.Add("ModifiedDate");
CallAdapter.DataColumns.Add("ModifiedBy");
CallAdapter.DataColumns.Add("GeneratedBy");
CallAdapter.DataColumns.Add("StartDate");
CallAdapter.DataColumns.Add("StartTime");
CallAdapter.DataColumns.Add("DurationInSecond");
CallAdapter.DataColumns.Add("DespositionID");
CallAdapter.DataColumns.Add("CampaignID");
CallAdapter.DataColumns.Add("ConfID");
CallAdapter.DataColumns.Add("IsDeleted");
CallAdapter.DataColumns.Add("CallNote");
CallAdapter.DataColumns.Add("IsDNC");
CallAdapter.DataColumns.Add("IsGlobal");
CallAdapterSyncAdapter = CallAdapter.ToSyncAdapter();
CallAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallAdapterSyncAdapter);
LeadAdapter = new SqlSyncAdapterBuilder();
LeadAdapter.Connection = serverConnection;
LeadAdapter.SyncDirection = SyncDirection.UploadOnly;
LeadAdapter.TableName = "Leads";
LeadAdapter.DataColumns.Add("ID");
LeadAdapter.DataColumns.Add("PhoneNo");
LeadAdapter.DataColumns.Add("LeadFormatID");
LeadAdapter.DataColumns.Add("CreatedDate");
LeadAdapter.DataColumns.Add("CreatedBy");
LeadAdapter.DataColumns.Add("DeletedDate");
LeadAdapter.DataColumns.Add("DeletedBy");
LeadAdapter.DataColumns.Add("IsDeleted");
LeadAdapter.DataColumns.Add("ModifiedDate");
LeadAdapter.DataColumns.Add("ModifiedBy");
LeadAdapter.DataColumns.Add("DNCFlag");
LeadAdapter.DataColumns.Add("DNCBy");
LeadAdapter.DataColumns.Add("ListID");
LeadAdapter.DataColumns.Add("LocationID");
LeadAdapter.DataColumns.Add("RecycleCount");
LeadAdapter.DataColumns.Add("Status");
LeadAdapter.DataColumns.Add("IsGlobalDNC");
//LeadAdapter.DataColumns.Add("LastEditDate");
//LeadAdapter.DataColumns.Add("CreationDate");
LeadAdapterSyncAdapter = LeadAdapter.ToSyncAdapter();
LeadAdapterSyncAdapter.DeleteCommand = null;
LeadAdapterSyncAdapter.InsertCommand = null;
//LeadAdapterSyncAdapter.ColumnMappings.Add("Status", "Status");
//LeadAdapterSyncAdapter.ColumnMappings.Add("DNCFlag", "DNCFlag");
//LeadAdapterSyncAdapter.ColumnMappings.Add("DNCBy", "DNCBy");
serverSyncProvider.SyncAdapters.Add(LeadAdapterSyncAdapter);
CallBackAdapter = new SqlSyncAdapterBuilder();
CallBackAdapter.Connection = serverConnection;
CallBackAdapter.SyncDirection = SyncDirection.UploadOnly;
CallBackAdapter.TableName = "CallBack";
CallBackAdapter.DataColumns.Add("ID");
CallBackAdapter.DataColumns.Add("CallID");
CallBackAdapter.DataColumns.Add("CallBackDate");
CallBackAdapter.DataColumns.Add("Comment");
CallBackAdapter.DataColumns.Add("IsPublic");
CallBackAdapter.DataColumns.Add("IsDeleted");
CallBackAdapterSyncAdapter = CallBackAdapter.ToSyncAdapter();
CallBackAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallBackAdapterSyncAdapter);
CheckPreviousSyncWithServer();
}
catch (Exception ex)
{
VMuktiAPI.VMuktiHelper.ExceptionHandler(ex, "CreateInitialLocalDB()", "ClsUserDataService.cs");
//MessageBox.Show("CreateInitialLocalDB: " + ex.Message);
}
}
public void CreateInitialLocalDB(string strConnectionString,bool isCreated)
{
try
{
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
clientSyncProvider = new SqlCeClientSyncProvider(strClientConnectionString);
if (!isCreated)
{
SqlCeEngine clientEngine = new SqlCeEngine(strClientConnectionString);
clientEngine.CreateDatabase();
clientEngine.Dispose();
tblCallTable = CreateCallTable();
tblLeadsTable = CreateLeadsTable();
tblCallBackTable = CreateCallBackTable();
tblDispositionTable = CreateDispositionTable();
}
else
{
tblCallTable = new SyncTable("Call");
tblCallTable.SyncDirection = SyncDirection.UploadOnly;
tblLeadsTable = new SyncTable("Leads");
tblLeadsTable.SyncDirection = SyncDirection.UploadOnly;
tblCallBackTable = new SyncTable("CallBack");
tblCallBackTable.SyncDirection = SyncDirection.UploadOnly;
//Creating Disposition Table (Added by Alpa)
tblDispositionTable = new SyncTable("Disposition");
tblDispositionTable.SyncDirection = SyncDirection.UploadOnly;
}
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
serverSyncProvider = new DbServerSyncProvider();
syncAgent = new SyncAgent();
// syncAgent.ServerSyncProvider = serverSyncProvider;
syncAgent.RemoteProvider = serverSyncProvider;
serverConnection = new SqlConnection(VMuktiAPI.VMuktiInfo.MainConnectionString);
serverSyncProvider.Connection = serverConnection;
serverSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(serverSyncProvider_ApplyChangeFailed);
//syncAgent.ClientSyncProvider = clientSyncProvider;
syncAgent.LocalProvider = clientSyncProvider;
myGroup = new SyncGroup("DialerGroup");
tblCallTable.SyncGroup = myGroup;
tblLeadsTable.SyncGroup = myGroup;
tblCallBackTable.SyncGroup = myGroup;
tblDispositionTable.SyncGroup = myGroup;
syncAgent.Configuration.SyncTables.Add(tblCallTable);
syncAgent.Configuration.SyncTables.Add(tblLeadsTable);
syncAgent.Configuration.SyncTables.Add(tblCallBackTable);
syncAgent.Configuration.SyncTables.Add(tblDispositionTable);
CallAdapter = new SqlSyncAdapterBuilder();
CallAdapter.Connection = serverConnection;
CallAdapter.SyncDirection = SyncDirection.UploadOnly;
CallAdapter.TableName = "Call";
// CallAdapter.DataColumns.Add("ID");
CallAdapter.DataColumns.Add("LeadID");
CallAdapter.DataColumns.Add("CalledDate");
CallAdapter.DataColumns.Add("ModifiedDate");
CallAdapter.DataColumns.Add("ModifiedBy");
CallAdapter.DataColumns.Add("GeneratedBy");
CallAdapter.DataColumns.Add("StartDate");
CallAdapter.DataColumns.Add("StartTime");
CallAdapter.DataColumns.Add("DurationInSecond");
CallAdapter.DataColumns.Add("DespositionID");
CallAdapter.DataColumns.Add("CampaignID");
CallAdapter.DataColumns.Add("ConfID");
CallAdapter.DataColumns.Add("IsDeleted");
CallAdapter.DataColumns.Add("CallNote");
CallAdapter.DataColumns.Add("IsDNC");
CallAdapter.DataColumns.Add("IsGlobal");
CallAdapter.DataColumns.Add("RecordedFileName"); //For Recording File Name
CallAdapterSyncAdapter = CallAdapter.ToSyncAdapter();
CallAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallAdapterSyncAdapter);
LeadAdapter = new SqlSyncAdapterBuilder();
LeadAdapter.Connection = serverConnection;
LeadAdapter.SyncDirection = SyncDirection.UploadOnly;
LeadAdapter.TableName = "Leads";
LeadAdapter.DataColumns.Add("ID");
LeadAdapter.DataColumns.Add("PhoneNo");
LeadAdapter.DataColumns.Add("LeadFormatID");
LeadAdapter.DataColumns.Add("CreatedDate");
LeadAdapter.DataColumns.Add("CreatedBy");
LeadAdapter.DataColumns.Add("DeletedDate");
LeadAdapter.DataColumns.Add("DeletedBy");
LeadAdapter.DataColumns.Add("IsDeleted");
LeadAdapter.DataColumns.Add("ModifiedDate");
LeadAdapter.DataColumns.Add("ModifiedBy");
LeadAdapter.DataColumns.Add("DNCFlag");
LeadAdapter.DataColumns.Add("DNCBy");
LeadAdapter.DataColumns.Add("ListID");
LeadAdapter.DataColumns.Add("LocationID");
LeadAdapter.DataColumns.Add("RecycleCount");
LeadAdapter.DataColumns.Add("Status");
LeadAdapter.DataColumns.Add("IsGlobalDNC");
//LeadAdapter.DataColumns.Add("LastEditDate");
//LeadAdapter.DataColumns.Add("CreationDate");
LeadAdapterSyncAdapter = LeadAdapter.ToSyncAdapter();
LeadAdapterSyncAdapter.DeleteCommand = null;
LeadAdapterSyncAdapter.InsertCommand = null;
serverSyncProvider.SyncAdapters.Add(LeadAdapterSyncAdapter);
CallBackAdapter = new SqlSyncAdapterBuilder();
CallBackAdapter.Connection = serverConnection;
CallBackAdapter.SyncDirection = SyncDirection.UploadOnly;
CallBackAdapter.TableName = "CallBack";
CallBackAdapter.DataColumns.Add("ID");
CallBackAdapter.DataColumns.Add("CallID");
CallBackAdapter.DataColumns.Add("CallBackDate");
CallBackAdapter.DataColumns.Add("Comment");
CallBackAdapter.DataColumns.Add("IsPublic");
CallBackAdapter.DataColumns.Add("IsDeleted");
CallBackAdapterSyncAdapter = CallBackAdapter.ToSyncAdapter();
CallBackAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallBackAdapterSyncAdapter);
//Creating Disposition Table in sdf (Added by Alpa)
DispositionAdapter = new SqlSyncAdapterBuilder();
DispositionAdapter.Connection = serverConnection;
DispositionAdapter.SyncDirection = SyncDirection.UploadOnly;
DispositionAdapter.TableName = "Disposition";
DispositionAdapter.DataColumns.Add("ID");
DispositionAdapter.DataColumns.Add("DespositionName");
DispositionAdapter.DataColumns.Add("Description");
DispositionAdapter.DataColumns.Add("IsActive");
DispositionAdapter.DataColumns.Add("IsDeleted");
DispositionAdapter.DataColumns.Add("CreatedDate");
DispositionAdapter.DataColumns.Add("CreatedBy");
DispositionAdapter.DataColumns.Add("ModifiedDate");
DispositionAdapter.DataColumns.Add("ModifiedBy");
DispositionAdapterSyncAdapter = DispositionAdapter.ToSyncAdapter();
DispositionAdapterSyncAdapter.DeleteCommand = null;
DispositionAdapterSyncAdapter.InsertCommand = null;
serverSyncProvider.SyncAdapters.Add(DispositionAdapterSyncAdapter);
ce = new SqlCeConnection(strClientConnectionString);
ce.Open();
CheckPreviousSyncWithServer();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//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 + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// and where changes are made.
// * Specify bidirectional synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table
//<snippetOCS_CS_Events_CustomerAdapterBuilder>
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.Bidirectional;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
customerBuilder.CreationOriginatorIdColumn = "InsertId";
customerBuilder.UpdateOriginatorIdColumn = "UpdateId";
customerBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//</snippetOCS_CS_Events_CustomerAdapterBuilder>
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.Bidirectional;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
orderHeaderBuilder.CreationOriginatorIdColumn = "InsertId";
orderHeaderBuilder.UpdateOriginatorIdColumn = "UpdateId";
orderHeaderBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//OrderDetail table.
SqlSyncAdapterBuilder orderDetailBuilder = new SqlSyncAdapterBuilder(serverConn);
orderDetailBuilder.TableName = "Sales.OrderDetail";
orderDetailBuilder.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone";
orderDetailBuilder.SyncDirection = SyncDirection.Bidirectional;
orderDetailBuilder.CreationTrackingColumn = "InsertTimestamp";
orderDetailBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderDetailBuilder.DeletionTrackingColumn = "DeleteTimestamp";
orderDetailBuilder.CreationOriginatorIdColumn = "InsertId";
orderDetailBuilder.UpdateOriginatorIdColumn = "UpdateId";
orderDetailBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter orderDetailSyncAdapter = orderDetailBuilder.ToSyncAdapter();
orderDetailSyncAdapter.TableName = "OrderDetail";
this.SyncAdapters.Add(orderDetailSyncAdapter);
//Log information for the following events.
this.ChangesSelected += new EventHandler <ChangesSelectedEventArgs>(EventLogger.LogEvents);
this.ChangesApplied += new EventHandler <ChangesAppliedEventArgs>(EventLogger.LogEvents);
//<snippetOCS_CS_Events_ApplyChangeFailedEventHandler>
this.ApplyChangeFailed += new EventHandler <ApplyChangeFailedEventArgs>(EventLogger.LogEvents);
//</snippetOCS_CS_Events_ApplyChangeFailedEventHandler>
//Handle the ApplyingChanges event so that we can
//make changes to the dataset.
this.ApplyingChanges += new EventHandler <ApplyingChangesEventArgs>(SampleServerSyncProvider_ApplyingChanges);
}
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//Create a command to retrieve a new anchor value from
//the server. In this case, we call a stored procedure
//that returns an anchor that can be used with batches
//of changes.
//<snippetOCS_CS_Batching_NewAnchorCommand>
SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.Connection = serverConn;
selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
selectNewAnchorCommand.CommandType = CommandType.StoredProcedure;
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp, 8);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int, 4);
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncMaxReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncNewReceivedAnchor].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Parameters["@" + SyncSession.SyncBatchCount].Direction = ParameterDirection.InputOutput;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
this.BatchSize = 50;
//</snippetOCS_CS_Batching_NewAnchorCommand>
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// and where changes are made.
// * Specify download only synchronization.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//Handle the ChangesSelected event, and display
//information to the console.
this.ChangesSelected += new EventHandler <ChangesSelectedEventArgs>(SampleServerSyncProvider_ChangesSelected);
}
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//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 + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create a filter parameter that will be used in the filter clause for
//all three tables.
//<snippetOCS_CS_Filter_Builder_ProviderSyncParam>
SqlParameter filterParameter = new SqlParameter("@SalesPerson", SqlDbType.NVarChar);
//</snippetOCS_CS_Filter_Builder_ProviderSyncParam>
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// changes are made.
// * Specify download-only synchronization.
// * Specify if you want only certain columns at the client.
// * Specify filter clauses for the base tables and tombstone
// tables.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name that is specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table.
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Specify the columns that you want at the client. If you
//want all columns, this code is not required. In this
//case, we filter out SalesPerson.
//<snippetOCS_CS_Filter_Builder_CustomerColumnFilter>
string[] customerDataColumns = new string[3];
customerDataColumns[0] = "CustomerId";
customerDataColumns[1] = "CustomerName";
customerDataColumns[2] = "CustomerType";
customerBuilder.DataColumns.AddRange(customerDataColumns);
customerBuilder.TombstoneDataColumns.AddRange(customerDataColumns);
//</snippetOCS_CS_Filter_Builder_CustomerColumnFilter>
//Specify a filter clause, which is an SQL WHERE clause
//without the WHERE keyword. Use the parameter that is
//created above. The value for the parameter is specified
//in the SyncAgent Configuration object.
//<snippetOCS_CS_Filter_Builder_CustomerRowFilter>
string customerFilterClause = "SalesPerson=@SalesPerson";
customerBuilder.FilterClause = customerFilterClause;
customerBuilder.FilterParameters.Add(filterParameter);
customerBuilder.TombstoneFilterClause = customerFilterClause;
customerBuilder.TombstoneFilterParameters.Add(filterParameter);
//</snippetOCS_CS_Filter_Builder_CustomerRowFilter>
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Filter properties: extend the filter to the OrderHeader table.
//<snippetOCS_CS_Filter_Builder_OrderHeaderRowFilter>
string orderHeaderFilterClause =
"CustomerId IN (SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson)";
orderHeaderBuilder.FilterClause = orderHeaderFilterClause;
orderHeaderBuilder.FilterParameters.Add(filterParameter);
orderHeaderBuilder.TombstoneFilterClause = orderHeaderFilterClause;
orderHeaderBuilder.TombstoneFilterParameters.Add(filterParameter);
//</snippetOCS_CS_Filter_Builder_OrderHeaderRowFilter>
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//OrderDetail table.
SqlSyncAdapterBuilder orderDetailBuilder = new SqlSyncAdapterBuilder(serverConn);
orderDetailBuilder.TableName = "Sales.OrderDetail";
orderDetailBuilder.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone";
orderDetailBuilder.SyncDirection = SyncDirection.DownloadOnly;
orderDetailBuilder.CreationTrackingColumn = "InsertTimestamp";
orderDetailBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderDetailBuilder.DeletionTrackingColumn = "DeleteTimestamp";
//Filter properties: extend the filter to the OrderDetail table.
string orderDetailFilterClause =
"OrderId IN (SELECT OrderId FROM Sales.OrderHeader " +
"WHERE CustomerId IN " +
"(SELECT CustomerId FROM Sales.Customer " +
"WHERE SalesPerson=@SalesPerson))";
orderDetailBuilder.FilterClause = orderDetailFilterClause;
orderDetailBuilder.FilterParameters.Add(filterParameter);
orderDetailBuilder.TombstoneFilterClause = orderDetailFilterClause;
orderDetailBuilder.TombstoneFilterParameters.Add(filterParameter);
SyncAdapter orderDetailSyncAdapter = orderDetailBuilder.ToSyncAdapter();
orderDetailSyncAdapter.TableName = "OrderDetail";
this.SyncAdapters.Add(orderDetailSyncAdapter);
}
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(util.ServerConnString);
this.Connection = serverConn;
//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 + " = min_active_rowversion() - 1";
selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = serverConn;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
//Create SyncAdapters for each table by using the SqlSyncAdapterBuilder:
// * Specify the base table and tombstone table names.
// * Specify the columns that are used to track when
// and where changes are made.
// * Specify bidirectional synchronization, so that all
// commands are generated.
// * Call ToSyncAdapter to create the SyncAdapter.
// * Specify a name for the SyncAdapter that matches the
// the name specified for the corresponding SyncTable.
// Do not include the schema names (Sales in this case).
//Customer table
SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
customerBuilder.TableName = "Sales.Customer";
customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
customerBuilder.SyncDirection = SyncDirection.Bidirectional;
customerBuilder.CreationTrackingColumn = "InsertTimestamp";
customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
customerBuilder.CreationOriginatorIdColumn = "InsertId";
customerBuilder.UpdateOriginatorIdColumn = "UpdateId";
customerBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
//CustomerContact table.
SqlSyncAdapterBuilder customerContactBuilder = new SqlSyncAdapterBuilder(serverConn);
customerContactBuilder.TableName = "Sales.CustomerContact";
customerContactBuilder.TombstoneTableName = customerContactBuilder.TableName + "_Tombstone";
customerContactBuilder.SyncDirection = SyncDirection.Bidirectional;
customerContactBuilder.CreationTrackingColumn = "InsertTimestamp";
customerContactBuilder.UpdateTrackingColumn = "UpdateTimestamp";
customerContactBuilder.DeletionTrackingColumn = "DeleteTimestamp";
customerContactBuilder.CreationOriginatorIdColumn = "InsertId";
customerContactBuilder.UpdateOriginatorIdColumn = "UpdateId";
customerContactBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter customerContactSyncAdapter = customerContactBuilder.ToSyncAdapter();
customerContactSyncAdapter.TableName = "CustomerContact";
this.SyncAdapters.Add(customerContactSyncAdapter);
//OrderHeader table.
SqlSyncAdapterBuilder orderHeaderBuilder = new SqlSyncAdapterBuilder(serverConn);
orderHeaderBuilder.TableName = "Sales.OrderHeader";
orderHeaderBuilder.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone";
orderHeaderBuilder.SyncDirection = SyncDirection.Bidirectional;
orderHeaderBuilder.CreationTrackingColumn = "InsertTimestamp";
orderHeaderBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderHeaderBuilder.DeletionTrackingColumn = "DeleteTimestamp";
orderHeaderBuilder.CreationOriginatorIdColumn = "InsertId";
orderHeaderBuilder.UpdateOriginatorIdColumn = "UpdateId";
orderHeaderBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter orderHeaderSyncAdapter = orderHeaderBuilder.ToSyncAdapter();
orderHeaderSyncAdapter.TableName = "OrderHeader";
this.SyncAdapters.Add(orderHeaderSyncAdapter);
//OrderDetail table.
SqlSyncAdapterBuilder orderDetailBuilder = new SqlSyncAdapterBuilder(serverConn);
orderDetailBuilder.TableName = "Sales.OrderDetail";
orderDetailBuilder.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone";
orderDetailBuilder.SyncDirection = SyncDirection.Bidirectional;
orderDetailBuilder.CreationTrackingColumn = "InsertTimestamp";
orderDetailBuilder.UpdateTrackingColumn = "UpdateTimestamp";
orderDetailBuilder.DeletionTrackingColumn = "DeleteTimestamp";
orderDetailBuilder.CreationOriginatorIdColumn = "InsertId";
orderDetailBuilder.UpdateOriginatorIdColumn = "UpdateId";
orderDetailBuilder.DeletionOriginatorIdColumn = "DeleteId";
SyncAdapter orderDetailSyncAdapter = orderDetailBuilder.ToSyncAdapter();
orderDetailSyncAdapter.TableName = "OrderDetail";
this.SyncAdapters.Add(orderDetailSyncAdapter);
//Create the schema for the OrderHeader and OrderDetail tables.
//We first create a schema based on a DataSet that contains only
//the OrderHeader table. As with the SyncAdapter, the table name
//must match the SyncTable name. We then add the schema for the
//OrderDetail table; this is the place to map data types if
//your application requires it.
//<snippetOCS_CS_Init_SyncSchema>
DataSet orderHeaderDataSet = util.CreateDataSetFromServer();
orderHeaderDataSet.Tables[0].TableName = "OrderHeader";
this.Schema = new SyncSchema(orderHeaderDataSet);
this.Schema.Tables.Add("OrderDetail");
this.Schema.Tables["OrderDetail"].Columns.Add("OrderDetailId");
this.Schema.Tables["OrderDetail"].Columns["OrderDetailId"].ProviderDataType = "int";
this.Schema.Tables["OrderDetail"].Columns["OrderDetailId"].AllowNull = false;
this.Schema.Tables["OrderDetail"].Columns.Add("OrderId");
this.Schema.Tables["OrderDetail"].Columns["OrderId"].ProviderDataType = "uniqueidentifier";
this.Schema.Tables["OrderDetail"].Columns["OrderId"].RowGuid = true;
this.Schema.Tables["OrderDetail"].Columns["OrderId"].AllowNull = false;
this.Schema.Tables["OrderDetail"].Columns.Add("Product");
this.Schema.Tables["OrderDetail"].Columns["Product"].ProviderDataType = "nvarchar";
this.Schema.Tables["OrderDetail"].Columns["Product"].MaxLength = 100;
this.Schema.Tables["OrderDetail"].Columns["Product"].AllowNull = false;
this.Schema.Tables["OrderDetail"].Columns.Add("Quantity");
this.Schema.Tables["OrderDetail"].Columns["Quantity"].ProviderDataType = "int";
this.Schema.Tables["OrderDetail"].Columns["Quantity"].AllowNull = false;
//The primary key columns are passed as a string array.
string[] orderDetailPrimaryKey = new string[2];
orderDetailPrimaryKey[0] = "OrderDetailId";
orderDetailPrimaryKey[1] = "OrderId";
this.Schema.Tables["OrderDetail"].PrimaryKey = orderDetailPrimaryKey;
//</snippetOCS_CS_Init_SyncSchema>
}
