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> }
private SyncAdapter createSyncAdapter(ContentCategories obj, Enums.QueryTypes type, Guid?userId) { var syncObj = new SyncAdapter(); var gen = new SyncCodeGenerator(); string script = null; if (type == Enums.QueryTypes.Insert) { script = gen.CreateInsertQuery(obj, Enums.ClientsTableNames.ContentCategories.ToString(), new List <SyncAction <ContentCategories> >() { new SyncAction <ContentCategories>(x => x.Id), new SyncAction <ContentCategories>(x => x.CongressId), new SyncAction <ContentCategories>(x => x.Title), new SyncAction <ContentCategories>(x => x.Description), new SyncAction <ContentCategories>(x => x.OrderCategory), new SyncAction <ContentCategories>(x => x.Image) }); } else if (type == Enums.QueryTypes.Update) { script = gen.CreateUpdateQuery(obj, Enums.ClientsTableNames.ContentCategories.ToString(), new List <SyncAction <ContentCategories> >() { new SyncAction <ContentCategories>(x => x.Description), new SyncAction <ContentCategories>(x => x.Title), new SyncAction <ContentCategories>(x => x.OrderCategory), new SyncAction <ContentCategories>(x => x.Image) }, new List <SyncAction <ContentCategories> >() { new SyncAction <ContentCategories>(x => x.Id) }); } else if (type == Enums.QueryTypes.Delete) { script = gen.CreateDeleteQuery(obj, Enums.ClientsTableNames.ContentCategories.ToString(), new List <SyncAction <ContentCategories> >() { new SyncAction <ContentCategories>(x => x.Id) }); } syncObj.Id = Guid.NewGuid(); syncObj.SourceId = obj.Id.ToString(); syncObj.CongressId = obj.CongressId; syncObj.Deprecated = false; syncObj.RecordDate = DateTime.Now; //syncObj.Script = new SyncCodeGenerator().GetContentCategoryQuery(type, obj); syncObj.Script = script; syncObj.TableName = Enums.ClientsTableNames.ContentCategories.ToString(); syncObj.UserId = userId; syncObj.Type = (int)type; return(syncObj); }
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 SyncAdapter for each table, and then define //the command to select rows from the table. With the Snapshot //option, you do not download incremental changes. However, //you still use the SelectIncrementalInsertsCommand to select //the rows to download for each snapshot. The commands include //only those columns that you want on the client. //Customer table. //<snippetOCS_CS_Snapshot_CustomerIncrInsert> SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer"; customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; this.SyncAdapters.Add(customerSyncAdapter); //</snippetOCS_CS_Snapshot_CustomerIncrInsert> //OrderHeader table. SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader"); SqlCommand orderHeaderIncrInserts = new SqlCommand(); orderHeaderIncrInserts.CommandText = "SELECT OrderId, CustomerId, OrderDate, OrderStatus " + "FROM Sales.OrderHeader"; orderHeaderIncrInserts.Connection = serverConn; orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts; this.SyncAdapters.Add(orderHeaderSyncAdapter); //OrderDetail table. SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail"); SqlCommand orderDetailIncrInserts = new SqlCommand(); orderDetailIncrInserts.CommandText = "SELECT OrderDetailId, OrderId, Product, Quantity " + "FROM Sales.OrderDetail"; orderDetailIncrInserts.Connection = serverConn; orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts; this.SyncAdapters.Add(orderDetailSyncAdapter); }
/// <summary> /// Create a new BatchInfo, containing all BatchPartInfo /// </summary> public BatchInfo(bool isInMemory, SyncSet inSchema, string rootDirectory = null, string directoryName = null) { this.InMemory = isInMemory; // We need to create a change table set, containing table with columns not readonly foreach (var table in inSchema.Tables) { SyncAdapter.CreateChangesTable(inSchema.Tables[table.TableName, table.SchemaName], this.SanitizedSchema); } // If not in memory, generate a directory name and initialize batch parts list if (!this.InMemory) { this.DirectoryRoot = rootDirectory; this.BatchPartsInfo = new List <BatchPartInfo>(); this.DirectoryName = string.IsNullOrEmpty(directoryName) ? string.Concat(DateTime.UtcNow.ToString("yyyy_MM_dd_ss"), Path.GetRandomFileName().Replace(".", "")) : directoryName; } }
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. //<snippetOCS_CS_DownloadOnly_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_DownloadOnly_NewAnchorCommand> //Create a SyncAdapter for the Customer table, and then define //the commands to synchronize changes: //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //Create the SyncAdapter. SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); //Select inserts from the server. SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE (InsertTimestamp > @sync_last_received_anchor " + "AND InsertTimestamp <= @sync_new_received_anchor)"; customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //Select updates from the server. //<snippetOCS_CS_DownloadOnly_CustomerIncrUpdate> SqlCommand customerIncrUpdates = new SqlCommand(); customerIncrUpdates.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE (UpdateTimestamp > @sync_last_received_anchor " + "AND UpdateTimestamp <= @sync_new_received_anchor " + "AND NOT (InsertTimestamp > @sync_last_received_anchor))"; customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; //</snippetOCS_CS_DownloadOnly_CustomerIncrUpdate> //Select deletes from the server. SqlCommand customerIncrDeletes = new SqlCommand(); customerIncrDeletes.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer_Tombstone " + "WHERE (@sync_initialized = 1 " + "AND DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor)"; customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Connection = serverConn; customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes; //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(customerSyncAdapter); }
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_SessionVars_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_SessionVars_NewAnchorCommand> //Create a command that enables you to pass in a //client ID (a GUID) and get back the orginator ID (an integer) //that is defined in a mapping table on the server. //<snippetOCS_CS_SessionVars_ClientIdCommand> SqlCommand selectClientIdCommand = new SqlCommand(); selectClientIdCommand.CommandType = CommandType.StoredProcedure; selectClientIdCommand.CommandText = "usp_GetOriginatorId"; selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); selectClientIdCommand.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output; selectClientIdCommand.Connection = serverConn; this.SelectClientIdCommand = selectClientIdCommand; //</snippetOCS_CS_SessionVars_ClientIdCommand> //Create a SyncAdapter for the Vendor table, and then define //the commands to synchronize changes: //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply // to the server the changes that the client provider has selected // from the client. //Create the SyncAdapter SyncAdapter vendorSyncAdapter = new SyncAdapter("Vendor"); //Select inserts from the server. //This command includes three session variables: //@sync_last_received_anchor, @sync_new_received_anchor, //and @sync_originator_id. The anchor variables are used with //SelectNewAnchorCommand to determine the set of changes to //synchronize. In other example code, the commands use //@sync_client_id instead of @sync_originator_id. In this case, //@sync_originator_id is used because the SelectClientIdCommand //is specified. SqlCommand vendorIncrInserts = new SqlCommand(); vendorIncrInserts.CommandText = "SELECT VendorId, VendorName, CreditRating, PreferredVendor " + "FROM Sales.Vendor " + "WHERE (InsertTimestamp > @sync_last_received_anchor " + "AND InsertTimestamp <= @sync_new_received_anchor " + "AND InsertId <> @sync_originator_id)"; vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); vendorIncrInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int); vendorIncrInserts.Connection = serverConn; vendorSyncAdapter.SelectIncrementalInsertsCommand = vendorIncrInserts; //Apply inserts to the server. //This command includes @sync_row_count, which returns //a count of how many rows were affected by the //last database operation. In SQL Server, the variable //is assigned the value of @@rowcount. The count is used //to determine whether an operation was successful or //was unsuccessful due to a conflict or an error. SqlCommand vendorInserts = new SqlCommand(); vendorInserts.CommandText = "INSERT INTO Sales.Vendor (VendorId, VendorName, CreditRating, PreferredVendor, InsertId, UpdateId) " + "VALUES (@VendorId, @VendorName, @CreditRating, @PreferredVendor, @sync_originator_id, @sync_originator_id) " + "SET @sync_row_count = @@rowcount"; vendorInserts.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier); vendorInserts.Parameters.Add("@VendorName", SqlDbType.NVarChar); vendorInserts.Parameters.Add("@CreditRating", SqlDbType.NVarChar); vendorInserts.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar); vendorInserts.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int); vendorInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); vendorInserts.Connection = serverConn; vendorSyncAdapter.InsertCommand = vendorInserts; //Select updates from the server //<snippetOCS_CS_SessionVars_VendorIncrUpdate> SqlCommand vendorIncrUpdates = new SqlCommand(); vendorIncrUpdates.CommandText = "SELECT VendorId, VendorName, CreditRating, PreferredVendor " + "FROM Sales.Vendor " + "WHERE (UpdateTimestamp > @sync_last_received_anchor " + "AND UpdateTimestamp <= @sync_new_received_anchor " + "AND UpdateId <> @sync_originator_id " + "AND NOT (InsertTimestamp > @sync_last_received_anchor " + "AND InsertId <> @sync_originator_id))"; vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); vendorIncrUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int); vendorIncrUpdates.Connection = serverConn; vendorSyncAdapter.SelectIncrementalUpdatesCommand = vendorIncrUpdates; //</snippetOCS_CS_SessionVars_VendorIncrUpdate> //Apply updates to the server. //This command includes @sync_force_write, which can //be used to apply changes in case of a conflict. //<snippetOCS_CS_SessionVars_VendorUpdate> SqlCommand vendorUpdates = new SqlCommand(); vendorUpdates.CommandText = "UPDATE Sales.Vendor SET " + "VendorName = @VendorName, CreditRating = @CreditRating, " + "PreferredVendor = @PreferredVendor, " + "UpdateId = @sync_originator_id " + "WHERE (VendorId = @VendorId) " + "AND (@sync_force_write = 1 " + "OR (UpdateTimestamp <= @sync_last_received_anchor " + "OR UpdateId = @sync_originator_id)) " + "SET @sync_row_count = @@rowcount"; vendorUpdates.Parameters.Add("@VendorName", SqlDbType.NVarChar); vendorUpdates.Parameters.Add("@CreditRating", SqlDbType.NVarChar); vendorUpdates.Parameters.Add("@PreferredVendor", SqlDbType.NVarChar); vendorUpdates.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int); vendorUpdates.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier); vendorUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); vendorUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); vendorUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); vendorUpdates.Connection = serverConn; vendorSyncAdapter.UpdateCommand = vendorUpdates; //</snippetOCS_CS_SessionVars_VendorUpdate> //Select deletes from the server. //This command includes @sync_initialized, which is //used to determine whether a client has been //initialized already. If this variable returns 0, //this is the first synchronization for this client ID //or originator ID. //<snippetOCS_CS_SessionVars_VendorIncrDelete> SqlCommand vendorIncrDeletes = new SqlCommand(); vendorIncrDeletes.CommandText = "SELECT VendorId, VendorName, CreditRating, PreferredVendor " + "FROM Sales.Vendor_Tombstone " + "WHERE (@sync_initialized = 1 " + "AND DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor " + "AND DeleteId <> @sync_originator_id)"; vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); vendorIncrDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int); vendorIncrDeletes.Connection = serverConn; vendorSyncAdapter.SelectIncrementalDeletesCommand = vendorIncrDeletes; //</snippetOCS_CS_SessionVars_VendorIncrDelete> //Apply deletes to the server. SqlCommand vendorDeletes = new SqlCommand(); vendorDeletes.CommandText = "DELETE FROM Sales.Vendor " + "WHERE (VendorId = @VendorId) " + "AND (@sync_force_write = 1 " + "OR (UpdateTimestamp <= @sync_last_received_anchor " + "OR UpdateId = @sync_originator_id)) " + "SET @sync_row_count = @@rowcount " + "IF (@sync_row_count > 0) BEGIN " + "UPDATE Sales.Vendor_Tombstone " + "SET DeleteId = @sync_originator_id " + "WHERE (VendorId = @VendorId) " + "END"; vendorDeletes.Parameters.Add("@VendorId", SqlDbType.UniqueIdentifier); vendorDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); vendorDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); vendorDeletes.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int); vendorDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); vendorDeletes.Connection = serverConn; vendorSyncAdapter.DeleteCommand = vendorDeletes; //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(vendorSyncAdapter); }
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 SyncAdapter for each table, and then define //the commands to synchronize changes: //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //* Specify if you want only certain columns at the client by // using the SELECT statement in the command. //* Filter rows by using the WHERE clause in the command. // In this case, we filter out SalesPerson. // //Customer table // //Create the SyncAdapter SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); //Select inserts from the server //<snippetOCS_CS_Filter_Manual_CustomerColumnRowFilter> SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "SELECT CustomerId, CustomerName, CustomerType " + "FROM Sales.Customer " + "WHERE SalesPerson = @SalesPerson " + "AND (InsertTimestamp > @sync_last_received_anchor " + "AND InsertTimestamp <= @sync_new_received_anchor " + "AND InsertId <> @sync_client_id)"; customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //</snippetOCS_CS_Filter_Manual_CustomerColumnRowFilter> //Select updates from the server SqlCommand customerIncrUpdates = new SqlCommand(); customerIncrUpdates.CommandText = "SELECT CustomerId, CustomerName, CustomerType " + "FROM Sales.Customer " + "WHERE SalesPerson = @SalesPerson " + "AND (UpdateTimestamp > @sync_last_received_anchor " + "AND UpdateTimestamp <= @sync_new_received_anchor " + "AND UpdateId <> @sync_client_id " + "AND NOT (InsertTimestamp > @sync_last_received_anchor " + "AND InsertId <> @sync_client_id))"; customerIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; //Select deletes from the server SqlCommand customerIncrDeletes = new SqlCommand(); customerIncrDeletes.CommandText = "SELECT CustomerId, CustomerName, CustomerType " + "FROM Sales.Customer_Tombstone " + "WHERE SalesPerson = @SalesPerson " + "AND (@sync_initialized = 1 " + "AND DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor " + "AND DeleteId <> @sync_client_id)"; customerIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrDeletes.Connection = serverConn; customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes; //Add the SyncAdapter to the server synchronization provider this.SyncAdapters.Add(customerSyncAdapter); // //OrderHeader table // //Create the SyncAdapter SyncAdapter orderHeaderSyncAdapter = new SyncAdapter("OrderHeader"); //Select inserts from the server //<snippetOCS_CS_Filter_Manual_OrderHeaderColumnRowFilter> SqlCommand orderHeaderIncrInserts = new SqlCommand(); orderHeaderIncrInserts.CommandText = "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " + "FROM Sales.OrderHeader oh " + "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " + "WHERE c.SalesPerson = @SalesPerson " + "AND (oh.InsertTimestamp > @sync_last_received_anchor " + "AND oh.InsertTimestamp <= @sync_new_received_anchor " + "AND oh.InsertId <> @sync_client_id)"; orderHeaderIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); orderHeaderIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); orderHeaderIncrInserts.Connection = serverConn; orderHeaderSyncAdapter.SelectIncrementalInsertsCommand = orderHeaderIncrInserts; //</snippetOCS_CS_Filter_Manual_OrderHeaderColumnRowFilter> //Select updates from the server SqlCommand orderHeaderIncrUpdates = new SqlCommand(); orderHeaderIncrUpdates.CommandText = "SELECT oh.OrderId, oh.CustomerId, oh.OrderDate, oh.OrderStatus " + "FROM Sales.OrderHeader oh " + "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " + "WHERE c.SalesPerson = @SalesPerson " + "AND (oh.UpdateTimestamp > @sync_last_received_anchor " + "AND oh.UpdateTimestamp <= @sync_new_received_anchor " + "AND oh.UpdateId <> @sync_client_id " + "AND NOT (oh.InsertTimestamp > @sync_last_received_anchor " + "AND oh.InsertId <> @sync_client_id))"; orderHeaderIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); orderHeaderIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); orderHeaderIncrUpdates.Connection = serverConn; orderHeaderSyncAdapter.SelectIncrementalUpdatesCommand = orderHeaderIncrUpdates; //Select deletes from the server SqlCommand orderHeaderIncrDeletes = new SqlCommand(); orderHeaderIncrDeletes.CommandText = "SELECT oht.OrderId, oht.CustomerId, oht.OrderDate, oht.OrderStatus " + "FROM Sales.OrderHeader_Tombstone oht " + "JOIN Sales.Customer c ON oht.CustomerId = c.CustomerId " + "WHERE c.SalesPerson = @SalesPerson " + "AND (@sync_initialized = 1 " + "AND oht.DeleteTimestamp > @sync_last_received_anchor " + "AND oht.DeleteTimestamp <= @sync_new_received_anchor " + "AND oht.DeleteId <> @sync_client_id)"; orderHeaderIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); orderHeaderIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); orderHeaderIncrDeletes.Connection = serverConn; orderHeaderSyncAdapter.SelectIncrementalDeletesCommand = orderHeaderIncrDeletes; //Add the SyncAdapter to the server synchronization provider this.SyncAdapters.Add(orderHeaderSyncAdapter); // //OrderDetail table // //Create the SyncAdapter SyncAdapter orderDetailSyncAdapter = new SyncAdapter("OrderDetail"); //Select inserts from the server SqlCommand orderDetailIncrInserts = new SqlCommand(); orderDetailIncrInserts.CommandText = "SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " + "FROM Sales.OrderDetail od " + "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " + "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " + "WHERE SalesPerson = @SalesPerson " + "AND (od.InsertTimestamp > @sync_last_received_anchor " + "AND od.InsertTimestamp <= @sync_new_received_anchor " + "AND od.InsertId <> @sync_client_id)"; orderDetailIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); orderDetailIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); orderDetailIncrInserts.Connection = serverConn; orderDetailSyncAdapter.SelectIncrementalInsertsCommand = orderDetailIncrInserts; //Select updates from the server SqlCommand orderDetailIncrUpdates = new SqlCommand(); orderDetailIncrUpdates.CommandText = "SELECT od.OrderDetailId, od.OrderId, od.Product, od.Quantity " + "FROM Sales.OrderDetail od " + "JOIN Sales.OrderHeader oh ON od.OrderId = oh.OrderId " + "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " + "WHERE SalesPerson = @SalesPerson " + "AND (od.UpdateTimestamp > @sync_last_received_anchor " + "AND od.UpdateTimestamp <= @sync_new_received_anchor " + "AND od.UpdateId <> @sync_client_id " + "AND NOT (od.InsertTimestamp > @sync_last_received_anchor " + "AND od.InsertId <> @sync_client_id))"; orderDetailIncrUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); orderDetailIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); orderDetailIncrUpdates.Connection = serverConn; orderDetailSyncAdapter.SelectIncrementalUpdatesCommand = orderDetailIncrUpdates; //Select deletes from the server SqlCommand orderDetailIncrDeletes = new SqlCommand(); orderDetailIncrDeletes.CommandText = "SELECT odt.OrderDetailId, odt.OrderId, odt.Product, odt.Quantity " + "FROM Sales.OrderDetail_Tombstone odt " + "JOIN Sales.OrderHeader oh ON odt.OrderId = oh.OrderId " + "JOIN Sales.Customer c ON oh.CustomerId = c.CustomerId " + "WHERE SalesPerson = @SalesPerson " + "AND (@sync_initialized = 1 " + "AND odt.DeleteTimestamp > @sync_last_received_anchor " + "AND odt.DeleteTimestamp <= @sync_new_received_anchor " + "AND odt.DeleteId <> @sync_client_id)"; orderDetailIncrDeletes.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); orderDetailIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); orderDetailIncrDeletes.Connection = serverConn; orderDetailSyncAdapter.SelectIncrementalDeletesCommand = orderDetailIncrDeletes; //Add the SyncAdapter to the server synchronization provider 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. // * 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> }
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 BigInt value //from the change tracking table. //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()"; selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt); selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output; selectNewAnchorCommand.Connection = serverConn; this.SelectNewAnchorCommand = selectNewAnchorCommand; //Create a SyncAdapter for the Customer table, and then define //the commands to synchronize changes: //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply // to the server the changes that the client provider has selected // from the client. //* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand // are used to detect if there are conflicts on the server during // synchronization. //The commands reference the change tracking table that is configured //for the Customer table. //Create the SyncAdapter. SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); //Select inserts from the server. SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "IF @sync_initialized = 0 " + "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " + "FROM Sales.Customer LEFT OUTER JOIN " + "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) " + "ELSE " + "BEGIN " + "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " + "FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION " + "<= @sync_new_received_anchor " + "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " + "> @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again' " + ",16,3,@sync_table_name) " + "END"; customerIncrInserts.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //Apply inserts to the server. SqlCommand customerInserts = new SqlCommand(); customerInserts.CommandText = ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " + "INSERT INTO Sales.Customer ([CustomerId], [CustomerName], [SalesPerson], [CustomerType]) " + "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType) " + "SET @sync_row_count = @@rowcount; " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name)"; customerInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerInserts.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output; customerInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerInserts.Connection = serverConn; customerSyncAdapter.InsertCommand = customerInserts; //Select updates from the server. SqlCommand customerIncrUpdates = new SqlCommand(); customerIncrUpdates.CommandText = "IF @sync_initialized > 0 " + "BEGIN " + "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " + "FROM Sales.Customer JOIN " + "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION " + "<= @sync_new_received_anchor " + "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " + "> @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name) " + "END"; customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; //Apply updates to the server. SqlCommand customerUpdates = new SqlCommand(); customerUpdates.CommandText = ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " + "UPDATE Sales.Customer " + "SET [CustomerName] = @CustomerName, [SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType " + "FROM Sales.Customer " + "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (@sync_force_write = 1 " + "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " + "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " + "SET @sync_row_count = @@rowcount; " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name)"; customerUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerUpdates.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output; customerUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerUpdates.Connection = serverConn; customerSyncAdapter.UpdateCommand = customerUpdates; //Select deletes from the server. SqlCommand customerIncrDeletes = new SqlCommand(); customerIncrDeletes.CommandText = "IF @sync_initialized > 0 " + "BEGIN " + "SELECT CT.[CustomerId] FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_VERSION " + "<= @sync_new_received_anchor " + "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " + "> @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name) " + "END"; customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerIncrDeletes.Connection = serverConn; customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes; //Apply deletes to the server. SqlCommand customerDeletes = new SqlCommand(); customerDeletes.CommandText = ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " + "DELETE Sales.Customer FROM Sales.Customer " + "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (@sync_force_write = 1 " + "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " + "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " + "SET @sync_row_count = @@rowcount; " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name)"; customerDeletes.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerDeletes.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output; customerDeletes.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerDeletes.Connection = serverConn; customerSyncAdapter.DeleteCommand = customerDeletes; //This command is used if @sync_row_count returns //0 when changes are applied to the server. SqlCommand customerUpdateConflicts = new SqlCommand(); customerUpdateConflicts.CommandText = "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType], " + "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " + "FROM Sales.Customer JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId]"; customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdateConflicts.Connection = serverConn; customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts; //This command is used if the server provider cannot find //a row in the base table. SqlCommand customerDeleteConflicts = new SqlCommand(); customerDeleteConflicts.CommandText = "SELECT CT.[CustomerId], " + "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " + "FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "WHERE (CT.[CustomerId] = @CustomerId AND CT.SYS_CHANGE_OPERATION = 'D')"; customerDeleteConflicts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeleteConflicts.Connection = serverConn; customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts; //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(customerSyncAdapter); }
public TestDynamicsCrmServerSyncProvider(string crmConnectionString) { LoadColumnInfo(); var serverConn = new CrmDbConnection(crmConnectionString); this.Connection = serverConn; // Sql strings for all sync commands. var entityName = TestEntityName; var idColumn = IdAttributeName; var sqlStringGetAnchor = string.Format("select top 1 {0} from {1} order by {0} desc", SyncColumnInfo.RowVersionAttributeName, entityName); // just get records with a creation version greater than last anchor and less than or equal to current anchor. var selectInsertsColumns = string.Join(",", SelectColumns.Select(s => s.AttributeName)); var sqlStringGetInserts = string.Format("SELECT {0} FROM {1} WHERE ({2} > @{3} AND {2} <= @{4}) AND ({5} <> @{6})", selectInsertsColumns, entityName, SyncColumnInfo.CreatedRowVersionAttributeName, SyncSession.SyncLastReceivedAnchor, SyncSession.SyncNewReceivedAnchor, SyncColumnInfo.CreatedBySyncClientIdAttributeName, SyncSession.SyncClientId); var sqlSelectUpdateColumns = string.Join(",", SelectColumns.Select(s => s.AttributeName)); var sqlStringGetUpdates = string.Format("SELECT {0} FROM {1} WHERE ({2} > @{3} AND {2} <= @{4})", sqlSelectUpdateColumns, entityName, SyncColumnInfo.RowVersionAttributeName, SyncSession.SyncLastReceivedAnchor, SyncSession.SyncNewReceivedAnchor); var sqlInsertColumns = string.Join(",", InsertColumns.Select(s => s.AttributeName)); var sqlInsertParameters = string.Join(",", InsertColumns.Select(a => a.BoundParameterName)); var sqlStringApplyInsert = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", entityName, sqlInsertColumns, sqlInsertParameters); var sqlUpdateColumns = string.Join(",", UpdateColumns.Select(a => a.AttributeName)); var sqlStringApplyUpdate = string.Format("UPDATE {0} SET {1} WHERE ({2} = @{2})", entityName, sqlUpdateColumns, idColumn); DbCommand selectNewAnchorCommand = new AnchorDbCommandAdapter(serverConn.CreateCommand() as CrmDbCommand); var anchorParam = selectNewAnchorCommand.CreateParameter(); anchorParam.ParameterName = "@" + SyncSession.SyncNewReceivedAnchor; selectNewAnchorCommand.CommandText = sqlStringGetAnchor; selectNewAnchorCommand.Parameters.Add(anchorParam); anchorParam.Direction = ParameterDirection.Output; selectNewAnchorCommand.Connection = serverConn; this.SelectNewAnchorCommand = selectNewAnchorCommand; //Create the SyncAdapter. //TODO: Create EntitySyncAdapter subclass of SyncAdapter; var customerSyncAdapter = new SyncAdapter(entityName); var customerIncrInserts = new SelectIncrementalCreatesCommand(serverConn.CreateCommand() as CrmDbCommand); customerIncrInserts.CommandText = sqlStringGetInserts; // "AND crmsync_insertedbyclientid <> @sync_client_id)"; var lastAnchorParam = customerIncrInserts.CreateParameter(); lastAnchorParam.ParameterName = "@" + SyncSession.SyncLastReceivedAnchor; lastAnchorParam.DbType = DbType.Int64; customerIncrInserts.Parameters.Add(lastAnchorParam); var thisAnchorParam = customerIncrInserts.CreateParameter(); thisAnchorParam.ParameterName = "@" + SyncSession.SyncNewReceivedAnchor; thisAnchorParam.DbType = DbType.Int64; customerIncrInserts.Parameters.Add(thisAnchorParam); var clientIdParam = customerIncrInserts.CreateParameter(); clientIdParam.ParameterName = "@" + SyncSession.SyncClientId; clientIdParam.DbType = DbType.Guid; customerIncrInserts.Parameters.Add(clientIdParam); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //Apply inserts to the server. // string[] insertParamNames = (from a in InsertColumns select "@" + a.Key).ToArray(); DbCommand contactInserts = new InsertEntityDbCommandAdapter(serverConn.CreateCommand() as CrmDbCommand); contactInserts.CommandText = sqlStringApplyInsert; foreach (var insertColumn in InsertColumns) { AddParameter(contactInserts, insertColumn.BoundParameterName, insertColumn.Type); } // AddParameter(contactInserts, SyncSession.SyncClientId, DbType.Guid); var param = AddParameter(contactInserts, SyncSession.SyncRowCount, DbType.Int32); param.Direction = ParameterDirection.Output; contactInserts.Connection = serverConn; customerSyncAdapter.InsertCommand = contactInserts; //Select updates from the server. var customerIncrUpdates = new SelectIncrementalUpdatesCommand(serverConn.CreateCommand() as CrmDbCommand); customerIncrUpdates.CommandText = sqlStringGetUpdates; AddParameter(customerIncrUpdates, SyncSession.SyncLastReceivedAnchor, DbType.Int64); AddParameter(customerIncrUpdates, SyncSession.SyncNewReceivedAnchor, DbType.Int64); AddParameter(customerIncrUpdates, SyncSession.SyncClientId, DbType.Guid); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; var customerUpdates = new UpdateEntityDbCommandAdapter(serverConn.CreateCommand() as CrmDbCommand); customerUpdates.CommandText = sqlStringApplyUpdate; foreach (var col in UpdateColumns) { AddParameter(contactInserts, col.BoundParameterName, col.Type); } AddParameter(customerUpdates, SyncSession.SyncClientId, DbType.Guid); AddParameter(customerUpdates, SyncSession.SyncForceWrite, DbType.Boolean); AddParameter(customerUpdates, SyncSession.SyncLastReceivedAnchor, DbType.Int64); AddParameter(customerUpdates, SyncSession.SyncRowCount, DbType.Int32); customerUpdates.Connection = serverConn; customerSyncAdapter.UpdateCommand = customerUpdates; //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(customerSyncAdapter); }
// 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 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_View_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_View_NewAnchorCommand> //Create a SyncAdapter for the CustomerInfo table. The CustomerInfo //table on the client is a combination of the Customer and CustomerContact //tables on the server. This table is download-only, as specified in //SampleSyncAgent. //<snippetOCS_CS_View_CustomerInfoSyncAdapter> SyncAdapter customerInfoSyncAdapter = new SyncAdapter("CustomerInfo"); //</snippetOCS_CS_View_CustomerInfoSyncAdapter> //Specify synchronization commands. The CustomerInfo table //is download-only, so we do not define commands to apply changes to //the server. Each command joins the base tables or tombstone tables //to select the appropriate incremental changes. For this application, //the logic is as follows: //* Select all inserts for customers that have contact information. // This results in more than one row for a customer if that customer // has more than one phone number. //* Select all updates for customer and contact information that has // already been downloaded. //* Select all deletes for customer and contact information that has // already been downloaded. If a customer has been deleted, delete // all of the rows for that customer. If a phone number has been // deleted, delete only that row. //Select inserts. //<snippetOCS_CS_View_CustomerInfoIncrInsert> SqlCommand customerInfoIncrementalInsertsCommand = new SqlCommand(); customerInfoIncrementalInsertsCommand.CommandType = CommandType.Text; customerInfoIncrementalInsertsCommand.CommandText = "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " + "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " + "c.CustomerId = cc.CustomerId " + "WHERE ((c.InsertTimestamp > @sync_last_received_anchor " + "AND c.InsertTimestamp <= @sync_new_received_anchor) OR " + "(cc.InsertTimestamp > @sync_last_received_anchor " + "AND cc.InsertTimestamp <= @sync_new_received_anchor))"; customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp); customerInfoIncrementalInsertsCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp); customerInfoIncrementalInsertsCommand.Connection = serverConn; customerInfoSyncAdapter.SelectIncrementalInsertsCommand = customerInfoIncrementalInsertsCommand; //</snippetOCS_CS_View_CustomerInfoIncrInsert> //Select updates. SqlCommand customerInfoIncrementalUpdatesCommand = new SqlCommand(); customerInfoIncrementalUpdatesCommand.CommandType = CommandType.Text; customerInfoIncrementalUpdatesCommand.CommandText = "SELECT c.CustomerId, c.CustomerName, c.SalesPerson, cc.PhoneNumber, cc.PhoneType " + "FROM Sales.Customer c JOIN Sales.CustomerContact cc ON " + "c.CustomerId = cc.CustomerId " + "WHERE ((c.UpdateTimestamp > @sync_last_received_anchor " + "AND c.UpdateTimestamp <= @sync_new_received_anchor " + "AND c.InsertTimestamp <= @sync_last_received_anchor) " + "OR (cc.UpdateTimestamp > @sync_last_received_anchor " + "AND cc.UpdateTimestamp <= @sync_new_received_anchor " + "AND cc.InsertTimestamp <= @sync_last_received_anchor))"; customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp); customerInfoIncrementalUpdatesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp); customerInfoIncrementalUpdatesCommand.Connection = serverConn; customerInfoSyncAdapter.SelectIncrementalUpdatesCommand = customerInfoIncrementalUpdatesCommand; //Select deletes. //<snippetOCS_CS_View_CustomerInfoIncrDelete> SqlCommand customerInfoIncrementalDeletesCommand = new SqlCommand(); customerInfoIncrementalDeletesCommand.CommandType = CommandType.Text; customerInfoIncrementalDeletesCommand.CommandText = "SELECT c.CustomerId, cc.PhoneType " + "FROM Sales.Customer_Tombstone c JOIN Sales.CustomerContact cc ON " + "c.CustomerId = cc.CustomerId " + "WHERE (@sync_initialized = 1 " + "AND (DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor)) " + "UNION " + "SELECT CustomerId, PhoneType " + "FROM Sales.CustomerContact_Tombstone " + "WHERE (@sync_initialized = 1 " + "AND (DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor))"; customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_initialized", SqlDbType.Bit); customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_last_received_anchor", SqlDbType.Timestamp); customerInfoIncrementalDeletesCommand.Parameters.Add("@sync_new_received_anchor", SqlDbType.Timestamp); customerInfoIncrementalDeletesCommand.Connection = serverConn; customerInfoSyncAdapter.SelectIncrementalDeletesCommand = customerInfoIncrementalDeletesCommand; //</snippetOCS_CS_View_CustomerInfoIncrDelete> //Add the SyncAdapter to the provider. this.SyncAdapters.Add(customerInfoSyncAdapter); }
/// <summary> /// Get changes from /// </summary> internal async Task <HttpMessageSendChangesResponse> ApplyThenGetChangesAsync(HttpMessageSendChangesRequest httpMessage, SessionCache sessionCache, int clientBatchSize, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) { // Overriding batch size options value, coming from client // having changes from server in batch size or not is decided by the client. // Basically this options is not used on the server, since it's always overriden by the client this.Options.BatchSize = clientBatchSize; // Get if we need to serialize data or making everything in memory var clientWorkInMemory = clientBatchSize == 0; // Get context from request message var ctx = httpMessage.SyncContext; // Set the context coming from the client this.SetContext(ctx); // Check schema. // If client has stored the schema, the EnsureScope will not be called on server. if (this.Schema == null || !this.Schema.HasTables || !this.Schema.HasColumns) { var serverScopeInfo = await this.EnsureSchemaAsync(cancellationToken, progress).ConfigureAwait(false); this.Schema = serverScopeInfo.Schema; this.Schema.EnsureSchema(); } // ------------------------------------------------------------ // FIRST STEP : receive client changes // ------------------------------------------------------------ // We are receiving changes from client // BatchInfo containing all BatchPartInfo objects // Retrieve batchinfo instance if exists // Get batch info from session cache if exists, otherwise create it if (sessionCache.ClientBatchInfo == null) { sessionCache.ClientBatchInfo = new BatchInfo(clientWorkInMemory, Schema, this.Options.BatchDirectory); } // create the in memory changes set var changesSet = new SyncSet(); foreach (var table in httpMessage.Changes.Tables) { SyncAdapter.CreateChangesTable(Schema.Tables[table.TableName, table.SchemaName], changesSet); } changesSet.ImportContainerSet(httpMessage.Changes, false); // If client has made a conversion on each line, apply the reverse side of it if (this.ClientConverter != null && changesSet.HasRows) { AfterDeserializedRows(changesSet, this.ClientConverter); } // add changes to the batch info await sessionCache.ClientBatchInfo.AddChangesAsync(changesSet, httpMessage.BatchIndex, httpMessage.IsLastBatch, this); // Clear the httpMessage set if (!clientWorkInMemory && httpMessage.Changes != null) { httpMessage.Changes.Clear(); } // Until we don't have received all the batches, wait for more if (!httpMessage.IsLastBatch) { return new HttpMessageSendChangesResponse(httpMessage.SyncContext) { ServerStep = HttpStep.SendChangesInProgress } } ; // ------------------------------------------------------------ // SECOND STEP : apply then return server changes // ------------------------------------------------------------ // get changes var(remoteClientTimestamp, serverBatchInfo, _, clientChangesApplied, serverChangesSelected) = await this.ApplyThenGetChangesAsync(httpMessage.Scope, sessionCache.ClientBatchInfo, cancellationToken, progress).ConfigureAwait(false); // Save the server batch info object to cache if not working in memory if (!clientWorkInMemory) { sessionCache.RemoteClientTimestamp = remoteClientTimestamp; sessionCache.ServerBatchInfo = serverBatchInfo; sessionCache.ServerChangesSelected = serverChangesSelected; sessionCache.ClientChangesApplied = clientChangesApplied; } // Get the firt response to send back to client return(await GetChangesResponseAsync(ctx, remoteClientTimestamp, serverBatchInfo, clientChangesApplied, serverChangesSelected, 0)); }
/// <summary> /// Create a response message content based on a requested index in a server batch info /// </summary> private async Task <HttpMessageSendChangesResponse> GetChangesResponseAsync(SyncContext syncContext, long remoteClientTimestamp, BatchInfo serverBatchInfo, DatabaseChangesApplied clientChangesApplied, DatabaseChangesSelected serverChangesSelected, int batchIndexRequested) { // 1) Create the http message content response var changesResponse = new HttpMessageSendChangesResponse(syncContext); changesResponse.ServerChangesSelected = serverChangesSelected; changesResponse.ClientChangesApplied = clientChangesApplied; changesResponse.ServerStep = HttpStep.GetMoreChanges; changesResponse.ConflictResolutionPolicy = this.Options.ConflictResolutionPolicy; // If nothing to do, just send back if (serverBatchInfo.InMemory || serverBatchInfo.BatchPartsInfo.Count == 0) { if (this.ClientConverter != null && serverBatchInfo.InMemoryData != null && serverBatchInfo.InMemoryData.HasRows) { BeforeSerializeRows(serverBatchInfo.InMemoryData, this.ClientConverter); } changesResponse.Changes = serverBatchInfo.InMemoryData == null ? new ContainerSet() : serverBatchInfo.InMemoryData.GetContainerSet(); changesResponse.BatchIndex = 0; changesResponse.IsLastBatch = true; changesResponse.RemoteClientTimestamp = remoteClientTimestamp; return(changesResponse); } // Get the batch part index requested var batchPartInfo = serverBatchInfo.BatchPartsInfo.First(d => d.Index == batchIndexRequested); // if we are not in memory, we set the BI in session, to be able to get it back on next request // create the in memory changes set var changesSet = new SyncSet(); foreach (var table in Schema.Tables) { SyncAdapter.CreateChangesTable(Schema.Tables[table.TableName, table.SchemaName], changesSet); } await batchPartInfo.LoadBatchAsync(changesSet, serverBatchInfo.GetDirectoryFullPath(), this); // if client request a conversion on each row, apply the conversion if (this.ClientConverter != null && batchPartInfo.Data.HasRows) { BeforeSerializeRows(batchPartInfo.Data, this.ClientConverter); } changesResponse.Changes = batchPartInfo.Data.GetContainerSet(); changesResponse.BatchIndex = batchIndexRequested; changesResponse.IsLastBatch = batchPartInfo.IsLastBatch; changesResponse.RemoteClientTimestamp = remoteClientTimestamp; changesResponse.ServerStep = batchPartInfo.IsLastBatch ? HttpStep.GetMoreChanges : HttpStep.GetChangesInProgress; // If we have only one bpi, we can safely delete it if (batchPartInfo.IsLastBatch) { // delete the folder (not the BatchPartInfo, because we have a reference on it) if (this.Options.CleanFolder) { var shouldDeleteFolder = true; if (!string.IsNullOrEmpty(this.Options.SnapshotsDirectory)) { var dirInfo = new DirectoryInfo(serverBatchInfo.DirectoryRoot); var snapInfo = new DirectoryInfo(this.Options.SnapshotsDirectory); shouldDeleteFolder = dirInfo.FullName != snapInfo.FullName; } if (shouldDeleteFolder) { serverBatchInfo.TryRemoveDirectory(); } } } return(changesResponse); }
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 BigInt value //from the change tracking table. //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_ChangeTracking_NewAnchorCommand> 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; //</snippetOCS_CS_ChangeTracking_NewAnchorCommand> //Create a SyncAdapter for the Customer table, and then define //the commands to synchronize changes: //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply // to the server the changes that the client provider has selected // from the client. //* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand // are used to detect if there are conflicts on the server during // synchronization. //The commands reference the change tracking table that is configured //for the Customer table. //Create the SyncAdapter. SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); //Select inserts from the server. //<snippetOCS_CS_ChangeTracking_CustomerIncrInsert> SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "IF @sync_initialized = 0 " + "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " + "FROM Sales.Customer LEFT OUTER JOIN " + "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) " + "ELSE " + "BEGIN " + "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " + "FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION " + "<= @sync_new_received_anchor " + "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " + "> @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again' " + ",16,3,@sync_table_name) " + "END"; customerIncrInserts.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //</snippetOCS_CS_ChangeTracking_CustomerIncrInsert> //Apply inserts to the server. SqlCommand customerInserts = new SqlCommand(); customerInserts.CommandText = ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " + "INSERT INTO Sales.Customer ([CustomerId], [CustomerName], [SalesPerson], [CustomerType]) " + "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType) " + "SET @sync_row_count = @@rowcount; " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name)"; customerInserts.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerInserts.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output; customerInserts.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerInserts.Connection = serverConn; customerSyncAdapter.InsertCommand = customerInserts; //Select updates from the server. //<snippetOCS_CS_ChangeTracking_CustomerIncrUpdate> SqlCommand customerIncrUpdates = new SqlCommand(); customerIncrUpdates.CommandText = "IF @sync_initialized > 0 " + "BEGIN " + "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] " + "FROM Sales.Customer JOIN " + "CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION " + "<= @sync_new_received_anchor " + "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " + "> @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name) " + "END"; customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; //</snippetOCS_CS_ChangeTracking_CustomerIncrUpdate> //Apply updates to the server. //<snippetOCS_CS_ChangeTracking_CustomerUpdate> SqlCommand customerUpdates = new SqlCommand(); customerUpdates.CommandText = ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " + "UPDATE Sales.Customer " + "SET [CustomerName] = @CustomerName, [SalesPerson] = @SalesPerson, [CustomerType] = @CustomerType " + "FROM Sales.Customer " + "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (@sync_force_write = 1 " + "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " + "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " + "SET @sync_row_count = @@rowcount; " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name)"; customerUpdates.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerUpdates.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output; customerUpdates.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerUpdates.Connection = serverConn; customerSyncAdapter.UpdateCommand = customerUpdates; //</snippetOCS_CS_ChangeTracking_CustomerUpdate> //Select deletes from the server. SqlCommand customerIncrDeletes = new SqlCommand(); customerIncrDeletes.CommandText = "IF @sync_initialized > 0 " + "BEGIN " + "SELECT CT.[CustomerId] FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "WHERE (CT.SYS_CHANGE_OPERATION = 'D' AND CT.SYS_CHANGE_VERSION " + "<= @sync_new_received_anchor " + "AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) " + "> @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name) " + "END"; customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerIncrDeletes.Connection = serverConn; customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes; //Apply deletes to the server. SqlCommand customerDeletes = new SqlCommand(); customerDeletes.CommandText = ";WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) " + "DELETE Sales.Customer FROM Sales.Customer " + "JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId] " + "WHERE (@sync_force_write = 1 " + "OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor " + "OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) " + "SET @sync_row_count = @@rowcount; " + "IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(@sync_table_name)) > @sync_last_received_anchor " + "RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. " + "To recover from this error, the client must reinitialize its local database and try again'" + ",16,3,@sync_table_name)"; customerDeletes.Parameters.Add("@" + SyncSession.SyncClientIdBinary, SqlDbType.Binary); customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerDeletes.Parameters["@" + SyncSession.SyncRowCount].Direction = ParameterDirection.Output; customerDeletes.Parameters.Add("@" + SyncSession.SyncTableName, SqlDbType.NVarChar); customerDeletes.Connection = serverConn; customerSyncAdapter.DeleteCommand = customerDeletes; //This command is used if @sync_row_count returns //0 when changes are applied to the server. //<snippetOCS_CS_ChangeTracking_SelectConflictUpdatedRowsCommand> SqlCommand customerUpdateConflicts = new SqlCommand(); customerUpdateConflicts.CommandText = "SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType], " + "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " + "FROM Sales.Customer JOIN CHANGETABLE(VERSION Sales.Customer, ([CustomerId]), (@CustomerId)) CT " + "ON CT.[CustomerId] = Sales.Customer.[CustomerId]"; customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdateConflicts.Connection = serverConn; customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts; //</snippetOCS_CS_ChangeTracking_SelectConflictUpdatedRowsCommand> //This command is used if the server provider cannot find //a row in the base table. //<snippetOCS_CS_ChangeTracking_SelectConflictDeletedRowsCommand> SqlCommand customerDeleteConflicts = new SqlCommand(); customerDeleteConflicts.CommandText = "SELECT CT.[CustomerId], " + "CT.SYS_CHANGE_CONTEXT, CT.SYS_CHANGE_VERSION " + "FROM CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT " + "WHERE (CT.[CustomerId] = @CustomerId AND CT.SYS_CHANGE_OPERATION = 'D')"; customerDeleteConflicts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt); customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeleteConflicts.Connection = serverConn; customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts; //</snippetOCS_CS_ChangeTracking_SelectConflictDeletedRowsCommand> //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(customerSyncAdapter); }
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 SyncAdapter for the Customer table, and then define //the commands to synchronize changes: //* SelectConflictUpdatedRowsCommand SelectConflictDeletedRowsCommand // are used to detect if there are conflicts on the server during // synchronization. //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply // to the server the changes that the client provider has selected // from the client. //Create the SyncAdapter. //<snippetOCS_CS_Conflicts_CustomerSyncAdapter> SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); //This command is used if @sync_row_count returns //0 when changes are applied to the server. //<snippetOCS_CS_Conflicts_SelectConflictUpdatedRowsCommand> SqlCommand customerUpdateConflicts = new SqlCommand(); customerUpdateConflicts.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE CustomerId = @CustomerId"; customerUpdateConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdateConflicts.Connection = serverConn; customerSyncAdapter.SelectConflictUpdatedRowsCommand = customerUpdateConflicts; //</snippetOCS_CS_Conflicts_SelectConflictUpdatedRowsCommand> //This command is used if the server provider cannot find //a row in the base table. //<snippetOCS_CS_Conflicts_SelectConflictDeletedRowsCommand> SqlCommand customerDeleteConflicts = new SqlCommand(); customerDeleteConflicts.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer_Tombstone " + "WHERE CustomerId = @CustomerId"; customerDeleteConflicts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeleteConflicts.Connection = serverConn; customerSyncAdapter.SelectConflictDeletedRowsCommand = customerDeleteConflicts; //</snippetOCS_CS_Conflicts_SelectConflictDeletedRowsCommand> //Select inserts from the server. //<snippetOCS_CS_Conflicts_CustomerIncrInsert> SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE (InsertTimestamp > @sync_last_received_anchor " + "AND InsertTimestamp <= @sync_new_received_anchor " + "AND InsertId <> @sync_client_id)"; customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //</snippetOCS_CS_Conflicts_CustomerIncrInsert> //Apply inserts to the server. //<snippetOCS_CS_Conflicts_CustomerInsert> SqlCommand customerInserts = new SqlCommand(); customerInserts.CommandType = CommandType.StoredProcedure; customerInserts.CommandText = "usp_CustomerApplyInsert"; customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerInserts.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerInserts.Connection = serverConn; customerSyncAdapter.InsertCommand = customerInserts; //</snippetOCS_CS_Conflicts_CustomerInsert> //Select updates from the server. //<snippetOCS_CS_Conflicts_CustomerIncrUpdate> SqlCommand customerIncrUpdates = new SqlCommand(); customerIncrUpdates.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE (UpdateTimestamp > @sync_last_received_anchor " + "AND UpdateTimestamp <= @sync_new_received_anchor " + "AND UpdateId <> @sync_client_id " + "AND NOT (InsertTimestamp > @sync_last_received_anchor " + "AND InsertId <> @sync_client_id))"; customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; //</snippetOCS_CS_Conflicts_CustomerIncrUpdate> //Apply updates to the server. //<snippetOCS_CS_Conflicts_CustomerUpdate> SqlCommand customerUpdates = new SqlCommand(); customerUpdates.CommandType = CommandType.StoredProcedure; customerUpdates.CommandText = "usp_CustomerApplyUpdate"; customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerUpdates.Connection = serverConn; customerSyncAdapter.UpdateCommand = customerUpdates; //</snippetOCS_CS_Conflicts_CustomerUpdate> //Select deletes from the server. //<snippetOCS_CS_Conflicts_CustomerIncrDelete> SqlCommand customerIncrDeletes = new SqlCommand(); customerIncrDeletes.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer_Tombstone " + "WHERE (@sync_initialized = 1 " + "AND DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor " + "AND DeleteId <> @sync_client_id)"; customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrDeletes.Connection = serverConn; customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes; //</snippetOCS_CS_Conflicts_CustomerIncrDelete> //Apply deletes to the server. //<snippetOCS_CS_Conflicts_CustomerDelete> SqlCommand customerDeletes = new SqlCommand(); customerDeletes.CommandType = CommandType.StoredProcedure; customerDeletes.CommandText = "usp_CustomerApplyDelete"; customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output; customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeletes.Connection = serverConn; customerSyncAdapter.DeleteCommand = customerDeletes; //</snippetOCS_CS_Conflicts_CustomerDelete> //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(customerSyncAdapter); //</snippetOCS_CS_Conflicts_CustomerSyncAdapter> //Handle the ApplyChangeFailed and ChangesApplied events. //This allows us to respond to any conflicts that occur, and to //make changes that are downloaded to the client during the same //session. this.ApplyChangeFailed += new EventHandler <ApplyChangeFailedEventArgs>(SampleServerSyncProvider_ApplyChangeFailed); this.ChangesApplied += new EventHandler <ChangesAppliedEventArgs>(SampleServerSyncProvider_ChangesApplied); }
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_Bidirectional_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_Bidirectional_NewAnchorCommand> //Create a SyncAdapter for the Customer table, and then define //the commands to synchronize changes: //* SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, // and SelectIncrementalDeletesCommand are used to select changes // from the server that the client provider then applies to the client. //* InsertCommand, UpdateCommand, and DeleteCommand are used to apply // to the server the changes that the client provider has selected // from the client. //Create the SyncAdapter. SyncAdapter customerSyncAdapter = new SyncAdapter("Customer"); //Select inserts from the server. SqlCommand customerIncrInserts = new SqlCommand(); customerIncrInserts.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE (InsertTimestamp > @sync_last_received_anchor " + "AND InsertTimestamp <= @sync_new_received_anchor " + "AND InsertId <> @sync_client_id)"; customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrInserts.Connection = serverConn; customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts; //Apply inserts to the server. SqlCommand customerInserts = new SqlCommand(); customerInserts.CommandText = "INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType, InsertId, UpdateId) " + "VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType, @sync_client_id, @sync_client_id) " + "SET @sync_row_count = @@rowcount"; customerInserts.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerInserts.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerInserts.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerInserts.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerInserts.Connection = serverConn; customerSyncAdapter.InsertCommand = customerInserts; //Select updates from the server. //<snippetOCS_CS_Bidirectional_CustomerIncrUpdate> SqlCommand customerIncrUpdates = new SqlCommand(); customerIncrUpdates.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer " + "WHERE (UpdateTimestamp > @sync_last_received_anchor " + "AND UpdateTimestamp <= @sync_new_received_anchor " + "AND UpdateId <> @sync_client_id " + "AND NOT (InsertTimestamp > @sync_last_received_anchor " + "AND InsertId <> @sync_client_id))"; customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrUpdates.Connection = serverConn; customerSyncAdapter.SelectIncrementalUpdatesCommand = customerIncrUpdates; //</snippetOCS_CS_Bidirectional_CustomerIncrUpdate> //Apply updates to the server. //<snippetOCS_CS_Bidirectional_CustomerUpdate> SqlCommand customerUpdates = new SqlCommand(); customerUpdates.CommandText = "UPDATE Sales.Customer SET " + "CustomerName = @CustomerName, SalesPerson = @SalesPerson, " + "CustomerType = @CustomerType, " + "UpdateId = @sync_client_id " + "WHERE (CustomerId = @CustomerId) " + "AND (@sync_force_write = 1 " + "OR (UpdateTimestamp <= @sync_last_received_anchor " + "OR UpdateId = @sync_client_id)) " + "SET @sync_row_count = @@rowcount"; customerUpdates.Parameters.Add("@CustomerName", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@SalesPerson", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@CustomerType", SqlDbType.NVarChar); customerUpdates.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerUpdates.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerUpdates.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerUpdates.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerUpdates.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerUpdates.Connection = serverConn; customerSyncAdapter.UpdateCommand = customerUpdates; //</snippetOCS_CS_Bidirectional_CustomerUpdate> //Select deletes from the server. SqlCommand customerIncrDeletes = new SqlCommand(); customerIncrDeletes.CommandText = "SELECT CustomerId, CustomerName, SalesPerson, CustomerType " + "FROM Sales.Customer_Tombstone " + "WHERE (@sync_initialized = 1 " + "AND DeleteTimestamp > @sync_last_received_anchor " + "AND DeleteTimestamp <= @sync_new_received_anchor " + "AND DeleteId <> @sync_client_id)"; customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Bit); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp); customerIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerIncrDeletes.Connection = serverConn; customerSyncAdapter.SelectIncrementalDeletesCommand = customerIncrDeletes; //Apply deletes to the server. SqlCommand customerDeletes = new SqlCommand(); customerDeletes.CommandText = "DELETE FROM Sales.Customer " + "WHERE (CustomerId = @CustomerId) " + "AND (@sync_force_write = 1 " + "OR (UpdateTimestamp <= @sync_last_received_anchor " + "OR UpdateId = @sync_client_id)) " + "SET @sync_row_count = @@rowcount " + "IF (@sync_row_count > 0) BEGIN " + "UPDATE Sales.Customer_Tombstone " + "SET DeleteId = @sync_client_id " + "WHERE (CustomerId = @CustomerId) " + "END"; customerDeletes.Parameters.Add("@CustomerId", SqlDbType.UniqueIdentifier); customerDeletes.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit); customerDeletes.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp); customerDeletes.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier); customerDeletes.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int); customerDeletes.Connection = serverConn; customerSyncAdapter.DeleteCommand = customerDeletes; //Add the SyncAdapter to the server synchronization provider. this.SyncAdapters.Add(customerSyncAdapter); }