방법: 클라이언트 데이터베이스 초기화 및 테이블 스키마 사용
이 항목에서는 SQL Server Compact 클라이언트 데이터베이스를 초기화하고 해당 데이터베이스에서 스키마를 사용하는 방법에 대해 설명합니다. 이 항목의 예제에서는 다음과 같은 Sync Framework 형식 및 이벤트를 중점적으로 설명합니다.
샘플 코드를 실행하는 방법에 대한 자세한 내용은 일반적인 클라이언트 및 서버 동기화 태스크 프로그래밍의 "방법 항목의 예제 응용 프로그램"을 참조하십시오.
초기화 이해
클라이언트 데이터베이스를 초기화할 때는 보통 사용자 스키마 및 데이터를 데이터베이스로 복사하고 데이터베이스에서 변경 추적을 사용하도록 설정합니다. 스냅숏 동기화의 경우에는 변경 추적을 사용하도록 설정하지 않습니다. 자세한 내용은 클라이언트 및 서버 동기화에 대한 아키텍처 및 클래스를 참조하십시오. 기본적으로 사용자 스키마 및 데이터는 서버 동기화 공급자에 의해 서버 데이터베이스에서 복사된 다음 클라이언트 동기화 공급자에 의해 클라이언트 데이터베이스에 적용됩니다. 업로드 전용 시나리오의 경우에도 기본적으로 스키마는 클라이언트에서 만들어집니다. 클라이언트 데이터베이스를 수동으로 만들고 동기화가 수행될 때 데이터베이스의 테이블이 보존되도록 지정할 수도 있습니다.
기본적으로 FOREIGN KEY 제약 조건, UNIQUE 제약 조건 및 DEFAULT 제약 조건은 클라이언트로 복사되지 않습니다. 예제에 나와 있는 것처럼 응용 프로그램에 필요한 경우 이러한 제약 조건을 추가할 수 있습니다. 또한 일부 열 형식은 서버와 클라이언트에서 다른 방식으로 처리됩니다. 자세한 내용은 데이터 형식 매핑 및 고려 사항을 참조하십시오.
클라이언트 데이터베이스에서 스키마를 초기화하고 나면 스냅숏, 다운로드 전용 및 양방향 동기화에 참여하는 테이블의 초기 데이터가 SelectIncrementalInsertsCommand 속성에 대해 지정되는 쿼리를 사용하여 다운로드됩니다. 또한 클라이언트 데이터베이스에 클라이언트 ID가 할당됩니다. 이 ID는 ClientId 속성을 통해 노출되는 GUID이며 서버에 대해 클라이언트를 고유하게 식별합니다. 클라이언트 데이터베이스를 만든 후에는 응용 프로그램이 배포의 일부분으로 포함된 데이터베이스 복사본의 패키지를 만들 수 있습니다. 클라이언트 ID는 첫 번째 동기화 동안 각 클라이언트에서 다시 생성됩니다.
예제
기본적으로 클라이언트 데이터베이스를 초기화할 때는 추가 코드가 필요하지 않습니다. 예제를 보려면 방법: 클라이언트에 데이터 스냅숏 다운로드의 코드를 참조하십시오. 그러나 API를 사용하면 작업을 훨씬 융통성 있게 수행할 수 있습니다. 다음 예제에서는 클라이언트 데이터베이스를 초기화하고 클라이언트 데이터베이스의 테이블 스키마를 사용하는 네 가지 방법을 설명합니다.
Customer테이블은Utility클래스에서 메서드를 호출하여 초기화합니다. 이 메서드는 SQL을 사용하여 클라이언트에 스키마를 만듭니다.Utility클래스는 데이터베이스 공급자용 유틸리티 클래스 방법 항목에 나와 있습니다. 서버에 없는SalesNotes열이 테이블에 추가됩니다. 이 열은 클라이언트에서 사용할 수 있지만 데이터는 동기화되지 않습니다.Customer테이블에 대해SyncTable을 정의하면 UseExistingTableOrFail이 CreationOption 속성에 대해 지정됩니다. 그러면 첫 번째 동기화 중에 테이블을 덮어쓰지 않습니다.CustomerContact테이블은 기본적으로 초기화됩니다. 이 테이블과 다음 두 테이블에 대해 DropExistingOrCreateNewTable 값이 지정됩니다.OrderHeader테이블은 SyncSchema 생성자에 데이터 집합을 전달하여 초기화됩니다. 데이터 집합은Utility클래스에서 메서드를 호출하여 만듭니다.OrderDetail테이블은OrderHeader데이터 집합을 사용하여 만든SyncSchema개체에 테이블 및 해당 열을 추가하여 초기화합니다. 코드에 나와 있는 것처럼 클라이언트에서 스키마를 만드는 방법을 다양하게 제어할 수 있습니다. Sync Framework를 사용하면 ADO.NET에서 노출하는 전체 스키마 관련 속성을 지정할 수 있습니다.
코드에는 스키마 만들기와 관련된 두 SqlCeClientSyncProvider 이벤트도 나와 있습니다.
CreatingSchema. 이 이벤트는 스키마를 만들기 전에 발생하며 API를 통해 스키마를 변경하는 데 사용됩니다.SchemaCreated. 이 이벤트는 스키마를 만든 후에 발생하며 SQL을 통해 스키마를 변경하는 데 사용됩니다.
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.Server;
using Microsoft.Synchronization.Data.SqlServerCe;
namespace Microsoft.Samples.Synchronization
{
class Program
{
static void Main(string[] args)
{
//The SampleStats class handles information from the SyncStatistics
//object that the Synchronize method returns.
SampleStats sampleStats = new SampleStats();
//Request a password for the client database, and delete
//and re-create the database. The client synchronization
//provider also enables you to create the client database
//if it does not exist.
Utility.SetPassword_SqlCeClientSync();
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, true);
//Create the Customer table on the client by using SQL. We add
//a SalesNotes column that will not be synchronized.
//When we create the Customer SyncTable, we specify that
//Sync Framework should use an existing table.
Utility.CreateTableOnClient();
//Initial synchronization. Instantiate the SyncAgent
//and call Synchronize.
SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "initial");
//Make changes on the server and client.
Utility.MakeDataChangesOnServer("Customer");
Utility.MakeDataChangesOnClient("Customer");
//Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize();
sampleStats.DisplayStats(syncStatistics, "subsequent");
//Return the server data back to its original state.
Utility.CleanUpServer();
//Exit.
Console.Write("\nPress Enter to close the window.");
Console.ReadLine();
}
}
//Create a class that is derived from
//Microsoft.Synchronization.SyncAgent.
public class SampleSyncAgent : SyncAgent
{
public SampleSyncAgent()
{
//Instantiate a client synchronization provider and specify it
//as the local provider for this synchronization agent.
this.LocalProvider = new SampleClientSyncProvider();
//Instantiate a server synchronization provider and specify it
//as the remote provider for this synchronization agent.
this.RemoteProvider = new SampleServerSyncProvider();
//Create two SyncGroups so that changes to OrderHeader
//and OrderDetail are made in one transaction. Depending on
//application requirements, you might include Customer
//and CustomerContact in the same group.
SyncGroup customerSyncGroup = new SyncGroup("Customer");
SyncGroup orderSyncGroup = new SyncGroup("Order");
//Add each table: specify a synchronization direction of
//Bidirectional. We create the Customer table before sync:
//we set CreationOption to UseExistingTableOrFail so
//we are sure that the table exists.
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.UseExistingTableOrFail;
customerSyncTable.SyncDirection = SyncDirection.Bidirectional;
customerSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerSyncTable);
SyncTable customerContactSyncTable = new SyncTable("CustomerContact");
customerContactSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerContactSyncTable.SyncDirection = SyncDirection.Bidirectional;
customerContactSyncTable.SyncGroup = customerSyncGroup;
this.Configuration.SyncTables.Add(customerContactSyncTable);
SyncTable orderHeaderSyncTable = new SyncTable("OrderHeader");
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderHeaderSyncTable.SyncDirection = SyncDirection.Bidirectional;
orderHeaderSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderHeaderSyncTable);
SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.Bidirectional;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
this.Configuration.SyncTables.Add(orderDetailSyncTable);
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Server.DbServerSyncProvider.
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
//Create a connection to the sample server database.
Utility util = new Utility();
SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
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.
DataSet orderHeaderDataSet = Utility.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;
}
}
//Create a class that is derived from
//Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
//You can just instantiate the provider directly and associate it
//with the SyncAgent, but here we use this class to handle client
//provider events.
public class SampleClientSyncProvider : SqlCeClientSyncProvider
{
public SampleClientSyncProvider()
{
//Specify a connection string for the sample client database.
Utility util = new Utility();
this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
//We use the CreatingSchema event to change the schema
//by using the API. We use the SchemaCreated event
//to change the schema by using SQL.
//Note that both schema events fire for the Customer table,
//even though we already created the table. This allows us
//to work with the table at this point if we need to.
this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);
this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);
}
private void SampleClientSyncProvider_CreatingSchema(object sender, CreatingSchemaEventArgs e)
{
string tableName = e.Table.TableName;
Console.Write("Creating schema for " + tableName + " | ");
if (tableName == "OrderHeader")
{
//Set the RowGuid property because it is not copied
//to the client by default. This is also a good time
//to specify literal defaults with .Columns[ColName].DefaultValue,
//but we will specify defaults like NEWID() by calling
//ALTER TABLE after the table is created.
e.Schema.Tables["OrderHeader"].Columns["OrderId"].RowGuid = true;
}
if (tableName == "OrderDetail")
{
//Add a foreign key between the OrderDetail and OrderHeader tables.
e.Schema.Tables["OrderDetail"].ForeignKeys.Add("FK_OrderDetail_OrderHeader", "OrderHeader", "OrderId", "OrderDetail", "OrderId");
}
}
private void SampleClientSyncProvider_SchemaCreated(object sender, SchemaCreatedEventArgs e)
{
string tableName = e.Table.TableName;
Utility util = new Utility();
//Call ALTER TABLE on the client. This must be done
//over the same connection and within the same
//transaction that Sync Framework uses
//to create the schema on the client.
if (tableName == "OrderHeader")
{
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, "OrderHeader");
}
if (tableName == "OrderDetail")
{
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, "OrderDetail");
}
Console.WriteLine("Schema created for " + tableName);
}
}
//Handle the statistics that are returned by the SyncAgent.
public class SampleStats
{
public void DisplayStats(SyncStatistics syncStatistics, string syncType)
{
Console.WriteLine(String.Empty);
if (syncType == "initial")
{
Console.WriteLine("****** Initial Synchronization ******");
}
else if (syncType == "subsequent")
{
Console.WriteLine("***** Subsequent Synchronization ****");
}
Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStatistics.TotalChangesUploaded);
Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
Console.WriteLine(String.Empty);
}
}
}
Imports System
Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Server
Imports Microsoft.Synchronization.Data.SqlServerCe
Class Program
Shared Sub Main(ByVal args() As String)
'The SampleStats class handles information from the SyncStatistics
'object that the Synchronize method returns.
Dim sampleStats As New SampleStats()
'Request a password for the client database, and delete
'and re-create the database. The client synchronization
'provider also enables you to create the client database
'if it does not exist.
Utility.SetPassword_SqlCeClientSync()
Utility.DeleteAndRecreateCompactDatabase(Utility.ConnStr_SqlCeClientSync, True)
'Create the Customer table on the client by using SQL. We add
'a SalesNotes column that will not be synchronized.
'When we create the Customer SyncTable, we specify that
'Sync Framework should use an existing table.
Utility.CreateTableOnClient()
'Initial synchronization. Instantiate the SyncAgent
'and call Synchronize.
Dim sampleSyncAgent As New SampleSyncAgent()
Dim syncStatistics As SyncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "initial")
'Make changes on the server and client.
Utility.MakeDataChangesOnServer("Customer")
Utility.MakeDataChangesOnClient("Customer")
'Subsequent synchronization.
syncStatistics = sampleSyncAgent.Synchronize()
sampleStats.DisplayStats(syncStatistics, "subsequent")
'Return the server data back to its original state.
Utility.CleanUpServer()
'Exit.
Console.Write(vbLf + "Press Enter to close the window.")
Console.ReadLine()
End Sub 'Main
End Class 'Program
'Create a class that is derived from
'Microsoft.Synchronization.SyncAgent.
Public Class SampleSyncAgent
Inherits SyncAgent
Public Sub New()
'Instantiate a client synchronization provider and specify it
'as the local provider for this synchronization agent.
Me.LocalProvider = New SampleClientSyncProvider()
'Instantiate a server synchronization provider and specify it
'as the remote provider for this synchronization agent.
Me.RemoteProvider = New SampleServerSyncProvider()
'Create two SyncGroups so that changes to OrderHeader
'and OrderDetail are made in one transaction. Depending on
'application requirements, you might include Customer
'and CustomerContact in the same group.
Dim customerSyncGroup As New SyncGroup("Customer")
Dim orderSyncGroup As New SyncGroup("Order")
'Add each table: specify a synchronization direction of
'Bidirectional. We create the Customer table before sync:
'we set CreationOption to UseExistingTableOrFail so
'we are sure that the table exists.
Dim customerSyncTable As New SyncTable("Customer")
customerSyncTable.CreationOption = TableCreationOption.UseExistingTableOrFail
customerSyncTable.SyncDirection = SyncDirection.Bidirectional
customerSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerSyncTable)
Dim customerContactSyncTable As New SyncTable("CustomerContact")
customerContactSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
customerContactSyncTable.SyncDirection = SyncDirection.Bidirectional
customerContactSyncTable.SyncGroup = customerSyncGroup
Me.Configuration.SyncTables.Add(customerContactSyncTable)
Dim orderHeaderSyncTable As New SyncTable("OrderHeader")
orderHeaderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderHeaderSyncTable.SyncDirection = SyncDirection.Bidirectional
orderHeaderSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderHeaderSyncTable)
Dim orderDetailSyncTable As New SyncTable("OrderDetail")
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable
orderDetailSyncTable.SyncDirection = SyncDirection.Bidirectional
orderDetailSyncTable.SyncGroup = orderSyncGroup
Me.Configuration.SyncTables.Add(orderDetailSyncTable)
End Sub 'New
End Class 'SampleSyncAgent
'Create a class that is derived from
'Microsoft.Synchronization.Server.DbServerSyncProvider.
Public Class SampleServerSyncProvider
Inherits DbServerSyncProvider
Public Sub New()
'Create a connection to the sample server database.
Dim util As New Utility()
Dim serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
Me.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.
Dim selectNewAnchorCommand As New SqlCommand()
Dim newAnchorVariable As String = "@" + SyncSession.SyncNewReceivedAnchor
With selectNewAnchorCommand
.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1"
.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp)
.Parameters(newAnchorVariable).Direction = ParameterDirection.Output
.Connection = serverConn
End With
Me.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
Dim customerBuilder As New SqlSyncAdapterBuilder(serverConn)
With customerBuilder
.TableName = "Sales.Customer"
.TombstoneTableName = customerBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.Bidirectional
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
.CreationOriginatorIdColumn = "InsertId"
.UpdateOriginatorIdColumn = "UpdateId"
.DeletionOriginatorIdColumn = "DeleteId"
End With
Dim customerSyncAdapter As SyncAdapter = customerBuilder.ToSyncAdapter()
customerSyncAdapter.TableName = "Customer"
Me.SyncAdapters.Add(customerSyncAdapter)
'CustomerContact table.
Dim customerContactBuilder As New SqlSyncAdapterBuilder(serverConn)
With customerContactBuilder
.TableName = "Sales.CustomerContact"
.TombstoneTableName = customerContactBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.Bidirectional
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
.CreationOriginatorIdColumn = "InsertId"
.UpdateOriginatorIdColumn = "UpdateId"
.DeletionOriginatorIdColumn = "DeleteId"
End With
Dim customerContactSyncAdapter As SyncAdapter = customerContactBuilder.ToSyncAdapter()
customerContactSyncAdapter.TableName = "CustomerContact"
Me.SyncAdapters.Add(customerContactSyncAdapter)
'OrderHeader table.
Dim orderHeaderBuilder As New SqlSyncAdapterBuilder(serverConn)
With orderHeaderBuilder
.TableName = "Sales.OrderHeader"
.TombstoneTableName = orderHeaderBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.Bidirectional
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
.CreationOriginatorIdColumn = "InsertId"
.UpdateOriginatorIdColumn = "UpdateId"
.DeletionOriginatorIdColumn = "DeleteId"
End With
Dim orderHeaderSyncAdapter As SyncAdapter = orderHeaderBuilder.ToSyncAdapter()
orderHeaderSyncAdapter.TableName = "OrderHeader"
Me.SyncAdapters.Add(orderHeaderSyncAdapter)
'OrderDetail table.
Dim orderDetailBuilder As New SqlSyncAdapterBuilder(serverConn)
With orderDetailBuilder
.TableName = "Sales.OrderDetail"
.TombstoneTableName = orderDetailBuilder.TableName + "_Tombstone"
.SyncDirection = SyncDirection.Bidirectional
.CreationTrackingColumn = "InsertTimestamp"
.UpdateTrackingColumn = "UpdateTimestamp"
.DeletionTrackingColumn = "DeleteTimestamp"
.CreationOriginatorIdColumn = "InsertId"
.UpdateOriginatorIdColumn = "UpdateId"
.DeletionOriginatorIdColumn = "DeleteId"
End With
Dim orderDetailSyncAdapter As SyncAdapter = orderDetailBuilder.ToSyncAdapter()
orderDetailSyncAdapter.TableName = "OrderDetail"
Me.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.
Dim orderHeaderDataSet As DataSet = Utility.CreateDataSetFromServer()
orderHeaderDataSet.Tables(0).TableName = "OrderHeader"
Me.Schema = New SyncSchema(orderHeaderDataSet)
With Me.Schema
.Tables.Add("OrderDetail")
.Tables("OrderDetail").Columns.Add("OrderDetailId")
.Tables("OrderDetail").Columns("OrderDetailId").ProviderDataType = "int"
.Tables("OrderDetail").Columns("OrderDetailId").AllowNull = False
.Tables("OrderDetail").Columns.Add("OrderId")
.Tables("OrderDetail").Columns("OrderId").ProviderDataType = "uniqueidentifier"
.Tables("OrderDetail").Columns("OrderId").RowGuid = True
.Tables("OrderDetail").Columns("OrderId").AllowNull = False
.Tables("OrderDetail").Columns.Add("Product")
.Tables("OrderDetail").Columns("Product").ProviderDataType = "nvarchar"
.Tables("OrderDetail").Columns("Product").MaxLength = 100
.Tables("OrderDetail").Columns("Product").AllowNull = False
.Tables("OrderDetail").Columns.Add("Quantity")
.Tables("OrderDetail").Columns("Quantity").ProviderDataType = "int"
.Tables("OrderDetail").Columns("Quantity").AllowNull = False
End With
'The primary key columns are passed as a string array.
Dim orderDetailPrimaryKey(1) As String
orderDetailPrimaryKey(0) = "OrderDetailId"
orderDetailPrimaryKey(1) = "OrderId"
Me.Schema.Tables("OrderDetail").PrimaryKey = orderDetailPrimaryKey
End Sub 'New
End Class 'SampleServerSyncProvider
'Create a class that is derived from
'Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
'You can just instantiate the provider directly and associate it
'with the SyncAgent, but here we use this class to handle client
'provider events.
Public Class SampleClientSyncProvider
Inherits SqlCeClientSyncProvider
Public Sub New()
'Specify a connection string for the sample client database.
Dim util As New Utility()
Me.ConnectionString = Utility.ConnStr_SqlCeClientSync
'We use the CreatingSchema event to change the schema
'by using the API. We use the SchemaCreated event
'to change the schema by using SQL.
'Note that both schema events fire for the Customer table,
'even though we already created the table. This allows us
'to work with the table at this point if we need to.
AddHandler Me.CreatingSchema, AddressOf SampleClientSyncProvider_CreatingSchema
AddHandler Me.SchemaCreated, AddressOf SampleClientSyncProvider_SchemaCreated
End Sub 'New
Private Sub SampleClientSyncProvider_CreatingSchema(ByVal sender As Object, ByVal e As CreatingSchemaEventArgs)
Dim tableName As String = e.Table.TableName
Console.Write("Creating schema for " + tableName + " | ")
If tableName = "OrderHeader" Then
'Set the RowGuid property because it is not copied
'to the client by default. This is also a good time
'to specify literal defaults with .Columns[ColName].DefaultValue,
'but we will specify defaults like NEWID() by calling
'ALTER TABLE after the table is created.
e.Schema.Tables("OrderHeader").Columns("OrderId").RowGuid = True
End If
If tableName = "OrderDetail" Then
'Add a foreign key between the OrderHeader and OrderDetail tables.
e.Schema.Tables("OrderDetail").ForeignKeys.Add("FK_OrderDetail_OrderHeader", "OrderHeader", "OrderId", "OrderDetail", "OrderId")
End If
End Sub 'SampleClientSyncProvider_CreatingSchema
Private Sub SampleClientSyncProvider_SchemaCreated(ByVal sender As Object, ByVal e As SchemaCreatedEventArgs)
Dim tableName As String = e.Table.TableName
Dim util As New Utility()
'Call ALTER TABLE on the client. This must be done
'over the same connection and within the same
'transaction that Sync Framework uses
'to create the schema on the client.
If tableName = "OrderHeader" Then
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, "OrderHeader")
End If
If tableName = "OrderDetail" Then
Utility.MakeSchemaChangesOnClient(e.Connection, e.Transaction, "OrderDetail")
End If
Console.WriteLine("Schema created for " + tableName)
End Sub 'SampleClientSyncProvider_SchemaCreated
End Class 'SampleClientSyncProvider
'Handle the statistics that are returned by the SyncAgent.
Public Class SampleStats
Public Sub DisplayStats(ByVal syncStatistics As SyncStatistics, ByVal syncType As String)
Console.WriteLine(String.Empty)
If syncType = "initial" Then
Console.WriteLine("****** Initial Synchronization ******")
ElseIf syncType = "subsequent" Then
Console.WriteLine("***** Subsequent Synchronization ****")
End If
Console.WriteLine("Start Time: " & syncStatistics.SyncStartTime)
Console.WriteLine("Total Changes Uploaded: " & syncStatistics.TotalChangesUploaded)
Console.WriteLine("Total Changes Downloaded: " & syncStatistics.TotalChangesDownloaded)
Console.WriteLine("Complete Time: " & syncStatistics.SyncCompleteTime)
Console.WriteLine(String.Empty)
End Sub 'DisplayStats
End Class 'SampleStats