Vorgehensweise: Initialisieren der Clientdatenbank und Arbeiten mit dem Tabellenschema

Es wird erläutert, wie Sie eine SQL Server Compact-Clientdatenbank initialisieren und mit dem Schema in dieser Datenbank arbeiten können. Die Beispiele in diesem Thema beziehen sich auf die folgenden Sync Framework-Typen und -Ereignisse:

• SyncSchema

• SyncTable

• TableCreationOption

• CreatingSchema und CreatingSchemaEventArgs

• SchemaCreated und SchemaCreatedEventArgs

Informationen zum Ausführen von Beispielcode finden Sie unter "Beispielanwendungen in den Themen zur Vorgehensweise" in Programmieren von allgemeinen Client- und Serversynchronisierungsaufgaben.

Initialisierung

Zum Initialisieren der Clientdatenbank müssen Sie in der Regel das Benutzerschema und die Daten in die Datenbank kopieren und in der Datenbank die Änderungsnachverfolgung aktivieren. (Für Momentaufnahmensynchronisierungen wird die Änderungsnachverfolgung nicht aktiviert.) Weitere Informationen finden Sie unter Architektur und Klassen für die Client- und Serversynchronisierung. Standardmäßig werden das Benutzerschema und die Daten durch den Serversynchronisierungsanbieter von der Serverdatenbank kopiert und dann durch den Clientsynchronisierungsanbieter für die Clientdatenbank übernommen. Selbst in Nur-Upload-Szenarien wird das Schema standardmäßig beim Client erstellt. Sie können die Clientdatenbank auch manuell erstellen und festlegen, dass die Tabellen in der Datenbank bei der Synchronisierung erhalten bleiben sollen.

Die folgenden Einschränkungen werden standardmäßig nicht auf den Client kopiert: Fremdschlüsseleinschränkungen (FOREIGN KEY), eindeutige Einschränkungen (UNIQUE) und Standardeinschränkungen (DEFAULT). Wie im Beispiel gezeigt, können Sie diese Einschränkungen hinzufügen, wenn die Anwendung das erfordert. Darüber hinaus werden einige Spaltentypen auf dem Client anders behandelt als auf dem Server. Weitere Informationen finden Sie unter Datentypmapping und Überlegungen.

Nach der Initialisierung des Schemas in der Clientdatenbank werden die anfänglichen Daten für Tabellen heruntergeladen, die an Momentaufnahmen-, Nur-Download- und bidirektionalen Synchronisierungen beteiligt sind. Dazu wird die für die SelectIncrementalInsertsCommand-Eigenschaft angegebene Abfrage verwendet. Der Clientdatenbank wird außerdem eine Client-ID zugewiesen. Diese ID ist eine GUID, die durch die ClientId-Eigenschaft verfügbar gemacht wird und die den Client auf dem Server eindeutig identifiziert. Nach dem Erstellen der Clientdatenbank können Sie ein Paket mit einer Kopie der Datenbank erstellen, die die Anwendung als Teil Ihrer Bereitstellung enthält. Die Client-ID wird bei der ersten Synchronisierung auf jedem Client neu generiert.

Beispiel

Standardmäßig ist für das Initialisieren der Clientdatenbank kein zusätzlicher Code erforderlich. Sehen Sie sich z. B. den Code unter Vorgehensweise: Herunterladen einer Momentaufnahme der Daten auf einen Client an. Die API bietet aber ein hohes Maß an Flexibilität. Im folgenden Beispiel werden vier Möglichkeiten gezeigt, wie Sie die Clientdatenbank initialisieren und mit dem Tabellenschema in der Clientdatenbank arbeiten können:

• Die Customer-Tabelle wird initialisiert, indem eine Methode in der Utility-Klasse aufgerufen wird. Bei dieser Methode wird das Schema auf dem Client mittels SQL erstellt. Die Utility-Klasse finden Sie in 'Utility'-Klasse für Datenbankanbieter - Themen zur Vorgehensweise. Der Tabelle wird die SalesNotes-Spalte hinzugefügt, die auf dem Server nicht vorhanden ist. Diese Spalte kann auf dem Client verwendet werden, wobei die Daten aber nicht synchronisiert werden. Wenn für die Customer-Tabelle eine SyncTable definiert ist, wird für die CreationOption-Eigenschaft der Wert UseExistingTableOrFail angegeben. Damit ist sichergestellt, dass die Tabelle bei der ersten Synchronisierung nicht überschrieben wird.

• Die CustomerContact-Tabelle wird standardmäßig initialisiert. Für diese Tabelle und die nächsten beiden Tabellen wird der Wert DropExistingOrCreateNewTable angegeben.

• Die OrderHeader-Tabelle wird initialisiert, indem ein Dataset an den SyncSchema-Konstruktor übergeben wird. Das Dataset wird durch Aufrufen einer Methode in der Utility-Klasse erstellt.

• Die OrderDetail-Tabelle wird initialisiert, indem die Tabelle und deren Spalten dem SyncSchema-Objekt hinzugefügt werden, das unter Verwendung des OrderHeader-Datasets erstellt wurde. Wie im Code gezeigt, haben Sie viele Möglichkeiten zu steuern, wie das Schema auf dem Client erstellt wird. Mit Sync Framework können Sie die gesamte Palette schemabezogener Eigenschaften angeben, die in ADO.NET verfügbar sind.

Der Code enthält auch die folgenden beiden SqlCeClientSyncProvider-Ereignisse, die mit der Schemaerstellung verknüpft sind:

• CreatingSchema. Dieses Ereignis wird ausgelöst, bevor das Schema erstellt wird. Es wird zum Ändern des Schemas mithilfe der API verwendet.

• SchemaCreated. Dieses Ereignis wird ausgelöst, nachdem das Schema erstellt wurde. Es wird zum Ändern des Schemas mithilfe von SQL verwendet.

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

Siehe auch

Konzepte

Programmieren von allgemeinen Client- und Serversynchronisierungsaufgaben