Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Utility Class for Sync Services How-to Topics

Synchronization Services - ADO.NET 1.0 SP1

The following class is used by the Sync Services how-to topics. The class handles functionality that is not directly related to synchronization, such as holding connection string information and making changes to the server and client databases. This class has been tested with Visual Studio 2008, SQL Server Compact 3.5, SQL Server 2005 Service Pack 2, and SQL Server 2008. For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks or Programming Common Peer-to-Peer Synchronization Tasks.

Public Class Utility

    Private Shared _clientPassword As String

    'Get and set the client database password.  
    Public Shared Property Password() As String
        Get
            Return _clientPassword
        End Get
        Set(ByVal value As String)
            _clientPassword = value
        End Set
    End Property

    'Have the user enter a password for the client database file.
    Public Sub SetClientPassword()
        Console.WriteLine("Type a strong password for the client")
        Console.WriteLine("database, and then press Enter.")
        Utility.Password = Console.ReadLine()

    End Sub 'SetClientPassword

    'Return the client connection string with the password.  
    Public ReadOnly Property ClientConnString() As String
        Get
            Return "Data Source='SyncSampleClient.sdf'; Password=" & Utility.Password
        End Get
    End Property

    Private Shared _serverName As String = "localhost"
    Private Shared _serverDbName As String = "SyncSamplesDb"

    'Set the server and database to connect to. 
    Public Sub SetServerAndDb(ByVal serverName As String, ByVal serverDbName As String)
        _serverName = serverName
        _serverDbName = serverDbName
    End Sub

    'Return the server connection string. 
    Public ReadOnly Property ServerConnString() As String
        Get
            Return "Data Source=" & _serverName & "; Initial Catalog=" & _serverDbName & "; Integrated Security=True"
        End Get
    End Property

    'Return the peer1 connection string. 
    Public ReadOnly Property Peer1ConnString() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer1; Integrated Security=True"
        End Get
    End Property

    'Return the peer2 connection string. 
    Public ReadOnly Property Peer2ConnString() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer2; Integrated Security=True"
        End Get
    End Property

    'Return the peer3 connection string. 
    Public ReadOnly Property Peer3ConnString() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer3; Integrated Security=True"
        End Get
    End Property


    'Make server changes that are synchronized on the second 
    'synchronization.
    Public Sub MakeDataChangesOnServer(ByVal tableName As String)

        Dim rowCount As Integer = 0

        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()

            If tableName = "Customer" Then

                sqlCommand.CommandText = _
                    "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " _
                  & "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " _
                  & "UPDATE Sales.Customer " _
                  & "SET  SalesPerson = 'James Bailey' " _
                  & "WHERE CustomerName = 'Tandem Bicycle Store' " _
                  & "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"

            ElseIf tableName = "CustomerContact" Then

                sqlCommand.CommandText = _
                    "DECLARE @CustomerId uniqueidentifier " _
                  & "DECLARE @InsertString nvarchar(1024) " _
                  & "SELECT @CustomerId = CustomerId FROM Sales.Customer " _
                  & "WHERE CustomerName = 'Tandem Bicycle Store' " _
                  & "SET @InsertString = " _
                  & "'INSERT INTO Sales.CustomerContact (CustomerId, PhoneNumber, PhoneType) " _
                  & "VALUES (''' + CAST(@CustomerId AS nvarchar(38)) + ''', ''959-555-2021'', ''Fax'')' " _
                  & "EXECUTE sp_executesql @InsertString " _
                  & "SELECT @CustomerId = CustomerId FROM Sales.Customer " _
                  & "WHERE CustomerName = 'Rural Cycle Emporium' " _
                  & "SET @InsertString = " _
                  & "'UPDATE Sales.CustomerContact SET PhoneNumber = ''158-555-0142'' " _
                  & "WHERE (CustomerId = ''' + CAST(@CustomerId AS nvarchar(38)) + ''' AND PhoneType = ''Business'')' " _
                  & "EXECUTE sp_executesql @InsertString " _
                  & "DELETE FROM Sales.CustomerContact WHERE PhoneType = 'Mobile'"

            ElseIf tableName = "CustomerAndOrderHeader" Then

                'Specify the number of rows to insert into the Customer
                'and OrderHeader tables.
                With sqlCommand
                    .CommandText = "usp_InsertCustomerAndOrderHeader "
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.Add("@customer_inserts", SqlDbType.Int)
                    .Parameters("@customer_inserts").Value = 13
                    .Parameters.Add("@orderheader_inserts", SqlDbType.Int)
                    .Parameters("@orderheader_inserts").Value = 33
                    .Parameters.Add("@sets_of_inserts", SqlDbType.Int)
                    .Parameters("@sets_of_inserts").Value = 2
                End With

            ElseIf tableName = "Vendor" Then

                sqlCommand.CommandText = _
                    "INSERT INTO Sales.Vendor (VendorName, CreditRating, PreferredVendor) " _
                  & "VALUES ('Victory Bikes', 4, 0) " _
                  & "UPDATE Sales.Vendor " _
                  & "SET CreditRating = 2 " _
                  & "WHERE VendorName = 'Metro Sport Equipment'" _
                  & "DELETE FROM Sales.Vendor WHERE VendorName = 'Premier Sport, Inc.'"

            End If


            serverConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

        Console.WriteLine("Rows inserted, updated, or deleted at the server: " & rowCount)

    End Sub 'MakeDataChangesOnServer


    'Get a dataset to use for schema creation.
    Public Function CreateDataSetFromServer() As DataSet

        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlDataAdapter()

        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim createDataSet As SqlCommand = serverConn.CreateCommand()
            createDataSet.CommandText = _
                "SELECT OrderId, CustomerId, OrderDate " _
              & "FROM Sales.OrderHeader"

            serverConn.Open()
            dataAdapter.SelectCommand = createDataSet
            dataAdapter.FillSchema(dataSet, SchemaType.Source)
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

        Return dataSet

    End Function 'CreateDataSetFromServer


    'Revert changes that were made during synchronization.
    Public Sub CleanUpServer()

        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "usp_InsertSampleData"

            serverConn.Open()
            sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

    End Sub 'CleanUpServer

    'Make peer changes that are synchronized on the second 
    'synchronization.
    Public Sub MakeDataChangesOnPeer(ByVal peerConnString As String, ByVal tableName As String)
        Dim rowCount As Integer = 0

        Dim peerConn As New SqlConnection(peerConnString)
        Try
            Dim sqlCommand As SqlCommand = peerConn.CreateCommand()

            If tableName = "Customer" Then

                If peerConnString = Me.Peer1ConnString Then
                    sqlCommand.CommandText = "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " + "VALUES ('Cycle Mart', 'James Bailey', 'Retail')"
                ElseIf peerConnString = Me.Peer2ConnString Then
                    sqlCommand.CommandText = "UPDATE Sales.Customer " + "SET  SalesPerson = 'James Bailey' " + "WHERE CustomerName = 'Tandem Bicycle Store' "
                ElseIf peerConnString = Me.Peer3ConnString Then

                    sqlCommand.CommandText = "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'"
                End If
            End If

            peerConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            peerConn.Close()
        Finally
            peerConn.Dispose()
        End Try

        Console.WriteLine("Total rows inserted, updated, or deleted at all peers: " & rowCount / 2)

    End Sub 'MakeDataChangesOnPeer


    'Make conflicting peer changes that are synchronized on the second 
    'synchronization.
    Public Sub MakeConflictingChangesOnPeer(ByVal peerConnString As String, ByVal tableName As String)

        Dim rowCount As Integer = 0

        Dim peerConn As New SqlConnection(peerConnString)
        Try
            Dim sqlCommand As SqlCommand = peerConn.CreateCommand()

            If tableName = "Customer" Then

                If peerConnString = Me.Peer1ConnString Then
                    sqlCommand.CommandText = _
                        "UPDATE Sales.Customer " _
                      & "SET  SalesPerson = 'ChangeFromPeerOne' " _
                      & "WHERE CustomerName = 'Tandem Bicycle Store'"
                ElseIf peerConnString = Me.Peer2ConnString Then
                    sqlCommand.CommandText = _
                        "UPDATE Sales.Customer " _
                      & "SET  SalesPerson = 'ChangeFromPeerTwo' " _
                      & "WHERE CustomerName = 'Tandem Bicycle Store'"
                ElseIf peerConnString = Me.Peer3ConnString Then
                    sqlCommand.CommandText = _
                        "UPDATE Sales.Customer " _
                      & "SET  SalesPerson = 'ChangeFromPeerThree' " _
                      & "WHERE CustomerName = 'Tandem Bicycle Store'"
                End If
            End If

            peerConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            peerConn.Close()
        Finally
            peerConn.Dispose()
        End Try

        Console.WriteLine("Total rows inserted, updated, or deleted at all peers: " & rowCount / 2)

    End Sub 'MakeConflictingChangesOnPeer


    Public Sub MakeFailingChangeOnPeer(ByVal peerConnString As String)

        Dim rowCount As Integer = 0

        Dim peerConn As New SqlConnection(peerConnString)
        Try
            Dim sqlCommand As SqlCommand = peerConn.CreateCommand()

            If peerConnString = Me.Peer2ConnString Then
                sqlCommand.CommandText = _
                    "DELETE FROM Sales.Customer " _
                  & "WHERE CustomerName = 'Rural Cycle Emporium'"
            End If

            peerConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            peerConn.Close()
        Finally
            peerConn.Dispose()
        End Try

        Console.WriteLine("Total rows inserted, updated, or deleted at all peers: " & rowCount / 2)

    End Sub 'MakeFailingChangeOnPeer


    Public Sub CleanUpPeer(ByVal peerConnString As String)
        Dim peerConn As New SqlConnection(peerConnString)
        Try
            Dim sqlCommand As SqlCommand = peerConn.CreateCommand()
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "usp_ResetPeerData"

            peerConn.Open()
            sqlCommand.ExecuteNonQuery()
            peerConn.Close()
        Finally
            peerConn.Dispose()
        End Try

    End Sub 'CleanUpPeer

    'Create the Customer table on the client.
    Public Sub CreateTableOnClient()

        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try
            Dim createTable As SqlCeCommand = clientConn.CreateCommand()
            createTable.CommandText = _
                "CREATE TABLE Customer( " _
              & "CustomerId uniqueidentifier NOT NULL " _
              & "PRIMARY KEY DEFAULT NEWID(), " _
              & "CustomerName nvarchar(100) NOT NULL, " _
              & "SalesPerson nvarchar(100) NOT NULL, " _
              & "CustomerType nvarchar(100) NOT NULL, " _
              & "SalesNotes nvarchar(1000) NULL)"

            clientConn.Open()
            createTable.ExecuteNonQuery()
            clientConn.Close()
        Finally
            clientConn.Dispose()
        End Try

    End Sub 'CreateTableOnClient


    'Add DEFAULT constraints on the client.
    Public Sub MakeSchemaChangesOnClient(ByVal clientConn As IDbConnection, ByVal clientTran As IDbTransaction, ByVal tableName As String)

        'Execute the command over the same connection and within
        'the same transaction that Synchronization Services uses
        'to create the schema on the client.
        Dim alterTable As New SqlCeCommand()
        alterTable.Connection = CType(clientConn, SqlCeConnection)
        alterTable.Transaction = CType(clientTran, SqlCeTransaction)
        alterTable.CommandText = String.Empty

        'Execute the command, then leave the transaction and 
        'connection open. The client provider will commit and close.
        Select Case tableName
            Case "Customer"
                alterTable.CommandText = _
                    "ALTER TABLE Customer " _
                  & "ADD CONSTRAINT DF_CustomerId " _
                  & "DEFAULT NEWID() FOR CustomerId"
                alterTable.ExecuteNonQuery()

            Case "OrderHeader"
                alterTable.CommandText = _
                     "ALTER TABLE OrderHeader " _
                   & "ADD CONSTRAINT DF_OrderId " _
                   & "DEFAULT NEWID() FOR OrderId"
                alterTable.ExecuteNonQuery()

                alterTable.CommandText = _
                    "ALTER TABLE OrderHeader " _
                  & "ADD CONSTRAINT DF_OrderDate " _
                  & "DEFAULT GETDATE() FOR OrderDate"
                alterTable.ExecuteNonQuery()

            Case "OrderDetail"
                alterTable.CommandText = _
                    "ALTER TABLE OrderDetail " _
                  & "ADD CONSTRAINT DF_Quantity " _
                  & "DEFAULT 1 FOR Quantity"
                alterTable.ExecuteNonQuery()

            Case "Vendor"
                alterTable.CommandText = _
                    "ALTER TABLE Vendor " _
                  & "ADD CONSTRAINT DF_VendorId " _
                  & "DEFAULT NEWID() FOR VendorId"
                alterTable.ExecuteNonQuery()

        End Select

    End Sub 'MakeSchemaChangesOnClient


    'Make client changes that are synchronized on the second 
    'synchronization.
    Public Sub MakeDataChangesOnClient(ByVal tableName As String)

        Dim rowCount As Integer = 0

        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try

            Dim sqlCeCommand As SqlCeCommand = clientConn.CreateCommand()

            clientConn.Open()

            If tableName = "Customer" Then

                sqlCeCommand.CommandText = _
                    "INSERT INTO Customer (CustomerName, SalesPerson, CustomerType) " _
                  & "VALUES ('Cycle Merchants', 'Brenda Diaz', 'Wholesale') "
                rowCount = sqlCeCommand.ExecuteNonQuery()

                sqlCeCommand.CommandText = _
                    "UPDATE Customer " _
                  & "SET SalesPerson = 'Brenda Diaz' " _
                  & "WHERE CustomerName = 'Exemplary Cycles'"
                rowCount += sqlCeCommand.ExecuteNonQuery()

                sqlCeCommand.CommandText = _
                    "DELETE FROM Customer " _
                  & "WHERE CustomerName = 'Aerobic Exercise Company'"
                rowCount += sqlCeCommand.ExecuteNonQuery()

            ElseIf tableName = "Vendor" Then

                sqlCeCommand.CommandText = _
                    "INSERT INTO Vendor (VendorName, CreditRating, PreferredVendor) " _
                  & "VALUES ('Cycling Master', 2, 1) "
                rowCount = sqlCeCommand.ExecuteNonQuery()

                sqlCeCommand.CommandText = _
                    "UPDATE Vendor " _
                  & "SET CreditRating = 2 " _
                  & "WHERE VendorName = 'Mountain Works'"
                rowCount += sqlCeCommand.ExecuteNonQuery()

                sqlCeCommand.CommandText = _
                    "DELETE FROM Vendor " _
                  & "WHERE VendorName = 'Compete, Inc.'"
                rowCount += sqlCeCommand.ExecuteNonQuery()

            End If

            clientConn.Close()
        Finally
            clientConn.Dispose()
        End Try

        Console.WriteLine("Rows inserted, updated, or deleted at the client: " & rowCount)

    End Sub 'MakeDataChangesOnClient


    'Make a change at the client that fails when it is
    'applied at the server.
    Public Sub MakeFailingChangesOnClient()

        Dim rowCount As Integer = 0

        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try

            Dim sqlCeCommand As SqlCeCommand = clientConn.CreateCommand()

            clientConn.Open()

            sqlCeCommand.CommandText = _
                "DELETE FROM Customer " _
              & "WHERE CustomerName = 'Rural Cycle Emporium'"
            rowCount += sqlCeCommand.ExecuteNonQuery()

            clientConn.Close()
        Finally
            clientConn.Dispose()
        End Try

        Console.WriteLine("Rows deleted at the client that will fail at the server: " & rowCount)

    End Sub 'MakeFailingChangesOnClient


    'Make changes at the client and server that conflict
    'when they are synchronized.
    Public Sub MakeConflictingChangesOnClientAndServer()

        Dim rowCount As Integer = 0

        Dim serverConn As New SqlConnection(Me.ServerConnString)
        Try
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandText = _
                "INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson, CustomerType) " _
              & "VALUES ('009aa4b6-3433-4136-ad9a-a7e1bb2528f7', 'Cycle Merchants', 'Brenda Diaz', 'Wholesale') " _
              & "DELETE FROM Sales.Customer " _
              & "WHERE CustomerName = 'Aerobic Exercise Company' " _
              & "UPDATE Sales.Customer " _
              & "SET SalesPerson = 'James Bailey' " _
              & "WHERE CustomerName = 'Sharp Bikes' " _
              & "UPDATE Sales.Customer " _
              & "SET CustomerType = 'Distributor' " _
              & "WHERE CustomerName = 'Exemplary Cycles'"

            serverConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        Finally
            serverConn.Dispose()
        End Try

        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try

            Dim sqlCeCommand As SqlCeCommand = clientConn.CreateCommand()

            clientConn.Open()

            sqlCeCommand.CommandText = _
                "INSERT INTO Customer (CustomerId, CustomerName, SalesPerson, CustomerType) " _
              & "VALUES ('009aa4b6-3433-4136-ad9a-a7e1bb2528f7', 'Cycle Merchants', 'James Bailey', 'Wholesale') "
            rowCount += sqlCeCommand.ExecuteNonQuery()

            sqlCeCommand.CommandText = _
                "UPDATE Customer " _
              & "SET CustomerType = 'Retail' " _
              & "WHERE CustomerName = 'Aerobic Exercise Company'"
            rowCount += sqlCeCommand.ExecuteNonQuery()

            sqlCeCommand.CommandText = _
                "DELETE FROM Customer WHERE CustomerName = 'Sharp Bikes'"
            rowCount += sqlCeCommand.ExecuteNonQuery()

            sqlCeCommand.CommandText = _
                "UPDATE Customer " _
              & "SET CustomerType = 'Wholesale' " _
              & "WHERE CustomerName = 'Exemplary Cycles'"
            rowCount += sqlCeCommand.ExecuteNonQuery()

            clientConn.Close()
        Finally
            clientConn.Dispose()
        End Try

        Console.WriteLine("Conflicting rows inserted, updated, or deleted at the client or server: " & rowCount)

    End Sub 'MakeConflictingChangesOnClientAndServer


    'Delete the client database.
    Public Sub RecreateClientDatabase()
        Dim clientConn As New SqlCeConnection(Me.ClientConnString)
        Try
            If File.Exists(clientConn.Database) Then
                File.Delete(clientConn.Database)
            End If
        Finally
            clientConn.Dispose()
        End Try

        Dim sqlCeEngine As New SqlCeEngine(Me.ClientConnString)
        sqlCeEngine.CreateDatabase()

    End Sub 'RecreateClientDatabase
End Class 'Utility

See Also

Show:
© 2015 Microsoft