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> }