Export (0) Print
Expand All

Utility Class for Database Provider How-to Topics

The following class is used by the Sync Framework 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, 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 Synchronizing Other ADO.NET Compatible Databases.


Public Class Utility

    ' --------- BEGIN CONNECTION STRINGS ALL FOR SAMPLES ----------- // 

    ' Set the connection strings for samples with servers or clients that 
    ' use DbSyncProvider. 
    Public Shared ReadOnly Property ConnStr_DbSync1() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer1; Integrated Security=True"
        End Get
    End Property


    Public Shared ReadOnly Property ConnStr_DbSync2() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer2; Integrated Security=True"
        End Get
    End Property


    Public Shared ReadOnly Property ConnStr_DbSync3() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer3; Integrated Security=True"
        End Get
    End Property


    ' Set the connection strings for samples with clients that 
    ' use SqlCeSyncProvider. 
    Public Shared ReadOnly Property ConnStr_SqlCeSync1() As String
        Get
            Return "Data Source='SyncSampleClient1.sdf'"
        End Get
    End Property



    Public Shared ReadOnly Property ConnStr_SqlCeSync2() As String
        Get
            Return "Data Source='SyncSampleClient2.sdf'"
        End Get
    End Property


    ' Set the connection strings for samples with servers or clients that 
    ' use SqlSyncProvider. 
    Public Shared ReadOnly Property ConnStr_SqlSync_Server() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_SqlPeer1; Integrated Security=True"
        End Get
    End Property


    Public Shared ReadOnly Property ConnStr_SqlSync_Client() As String
        Get
            Return "Data Source=localhost; Initial Catalog=SyncSamplesDb_SqlPeer2; Integrated Security=True"
        End Get
    End Property


    ' Set the password and connection string for samples with clients 
    ' that use SqlCeClientSyncProvider. 
    Private Shared _clientPassword As String

    Public Shared Property Password_SqlCeClientSync() As String
        Get
            Return _clientPassword
        End Get
        Set(ByVal value As String)
            _clientPassword = value
        End Set
    End Property

    Public Shared Sub SetPassword_SqlCeClientSync()
        Console.WriteLine("Type a strong password for the client")
        Console.WriteLine("database, and then press Enter.")
        Utility.Password_SqlCeClientSync = Console.ReadLine()
    End Sub

    Public Shared ReadOnly Property ConnStr_SqlCeClientSync() As String
        Get
            Return "Data Source='SyncSampleClient.sdf'; Password=" & _
                     Utility.Password_SqlCeClientSync
        End Get
    End Property

    ' Set the connection string for samples with servers that 
    ' use DbServerSyncProvider. 
    Private Shared _serverName As String = "localhost"
    Private Shared _serverDbName As String = "SyncSamplesDb"

    Public Shared Sub SetServerAndDb_DbServerSync(ByVal serverName As String, ByVal serverDbName As String)
        _serverName = serverName
        _serverDbName = serverDbName
    End Sub

    Public Shared ReadOnly Property ConnStr_DbServerSync() As String
        Get
            Return ("Data Source=" & _
                     _serverName & _
                     "; Initial Catalog=") + _serverDbName & _
                     "; Integrated Security=True"
        End Get
    End Property


    ' ----------- END CONNECTION STRINGS ALL FOR SAMPLES ----------- // 




    ' ---- BEGIN CODE FOR SQLSYNCPROVIDER, SQLCESYNCPROVIDER, ---- // 
    ' --------------- AND DBSYNCPROVIDER SAMPLES --------------- 

    Public Shared Sub MakeDataChangesOnNode(ByVal nodeConnString As String, ByVal tableName As String)
        Dim rowCount As Integer = 0

        Using nodeConn As New SqlConnection(nodeConnString)
            Dim sqlCommand As SqlCommand = nodeConn.CreateCommand()

            If tableName = "Customer" Then

                If nodeConnString = Utility.ConnStr_SqlSync_Server 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 nodeConnString = Utility.ConnStr_DbSync1 Then

                    sqlCommand.CommandText = "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " & _
                     "VALUES ('Cycle Mart', 'James Bailey', 'Retail')"

                ElseIf nodeConnString = Utility.ConnStr_DbSync2 Then

                    sqlCommand.CommandText = "UPDATE Sales.Customer " & _
                     "SET SalesPerson = 'James Bailey' " & _
                     "WHERE CustomerName = 'Tandem Bicycle Store' "

                ElseIf nodeConnString = Utility.ConnStr_DbSync3 Then


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

                End If

            ElseIf tableName = "CustomerContact" Then

                If nodeConnString = Utility.ConnStr_SqlSync_Server Then
                    sqlCommand.CommandText = "DELETE FROM Sales.CustomerContact WHERE PhoneType = 'Mobile'"

                End If
            End If

            nodeConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            nodeConn.Close()
        End Using

        ' rowCount/2 is used because a row is inserted or updated in the 
        ' metadata table for every insert, update, or delete in the base table. 
        Console.WriteLine("Total rows inserted, updated, or deleted at all nodes: " & _
                     rowCount / 2)
    End Sub

    Public Shared Sub MakeConflictingChangeOnNode(ByVal nodeConnString As String, ByVal tableName As String)
        Dim rowCount As Integer = 0

        Using nodeConn As New SqlConnection(nodeConnString)
            Dim sqlCommand As SqlCommand = nodeConn.CreateCommand()

            If tableName = "Customer" Then

                If nodeConnString = Utility.ConnStr_SqlSync_Server Then
                    sqlCommand.CommandText = "UPDATE Sales.Customer " & _
                     "SET SalesPerson = 'ChangeFromNodeOne' " & _
                     "WHERE CustomerName = 'Tandem Bicycle Store' "

                ElseIf nodeConnString = Utility.ConnStr_SqlSync_Client Then
                    sqlCommand.CommandText = "UPDATE Sales.Customer " & _
                     "SET SalesPerson = 'ChangeFromNodeTwo' " & _
                     "WHERE CustomerName = 'Tandem Bicycle Store' "

                End If
            End If

            nodeConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            nodeConn.Close()
        End Using

        ' rowCount/2 is used because a row is inserted or updated in the 
        ' metadata table for every insert, update, or delete in the base table. 
        Console.WriteLine("Total rows inserted, updated, or deleted at all nodes: " & rowCount / 2)
    End Sub

    Public Shared Sub MakeFailingChangeOnNode(ByVal nodeConnString As String)
        Dim rowCount As Integer = 0

        Using nodeConn As New SqlConnection(nodeConnString)
            Dim sqlCommand As SqlCommand = nodeConn.CreateCommand()

            If nodeConnString = Utility.ConnStr_SqlSync_Client Then
                sqlCommand.CommandText = "DELETE FROM Sales.Customer " & _
                     "WHERE CustomerName = 'Rural Cycle Emporium'"
            End If

            nodeConn.Open()
            rowCount = sqlCommand.ExecuteNonQuery()
            nodeConn.Close()
        End Using

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

    Public Shared Sub CleanUpNode(ByVal nodeConnString As String)
        Using nodeConn As New SqlConnection(nodeConnString)
            Dim sqlCommand As SqlCommand = nodeConn.CreateCommand()
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "usp_ResetPeerData"

            nodeConn.Open()
            sqlCommand.ExecuteNonQuery()
            nodeConn.Close()
        End Using
End Sub

Public Shared Sub CleanUpSqlNode(ByVal nodeConnString As String)
    Using nodeConn As New SqlConnection(nodeConnString)
        Dim sqlCommand As SqlCommand = nodeConn.CreateCommand()
        sqlCommand.CommandType = CommandType.StoredProcedure
        sqlCommand.CommandText = "usp_CleanupAfterAppRun"

        nodeConn.Open()
        sqlCommand.ExecuteNonQuery()
        nodeConn.Close()
    End Using
End Sub

    ' ---- The rest of the code in this section is related to backup 
    ' ---- and restore of a SQL Server database. 

    ' Return the path to the SQL Server backup file (.bak). Change this path if your 
    ' backup directory is set to something other than the default. 
    Public Shared ReadOnly Property DatabaseBackupFilePath() As String
        Get
        Return "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\SyncSamplesDb_SqlPeer1.bak"
        End Get
    End Property

    Public Shared Sub CreateDatabaseBackup()

        Console.Write("BACKING UP SERVER DATABASE...")

        ' Connect to SyncSamplesDb_SqlNode2 (Utility.ConnStr_SqlSync_Client) and back up 
        ' SyncSamplesDb_SqlNode1 by calling usp_SampleDbBackupRestore. 

        Using conn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
            conn.Open()

            Dim backupCommand As New SqlCommand()
            backupCommand.CommandType = CommandType.StoredProcedure
            backupCommand.CommandText = "usp_SampleDbBackupRestore"
            backupCommand.Parameters.Add("@Operation", SqlDbType.VarChar).Value = "BACKUP"
            backupCommand.Connection = conn
            backupCommand.ExecuteNonQuery()

            conn.Close()
            conn.Dispose()
        End Using

        Console.WriteLine("COMPLETED")

        Console.WriteLine([String].Empty)
    End Sub

    Public Shared Sub RestoreDatabaseFromBackup()

        Console.Write("RESTORING SERVER DATABASE...")

        ' Connect to SyncSamplesDb_SqlNode2 (Utility.ConnStr_SqlSync_Client) and restore 
        ' SyncSamplesDb_SqlNode1 by calling usp_SampleDbBackupRestore. This procedure 
        ' was created in SyncSamplesDb_SqlNode2 because holding a connection to SyncSamplesDb_SqlNode1 
        ' does not allow the database to be restored. 
        Using conn As New SqlConnection(Utility.ConnStr_SqlSync_Client)
            conn.Open()

            Dim backupCommand As New SqlCommand()
            backupCommand.CommandType = CommandType.StoredProcedure
            backupCommand.CommandText = "usp_SampleDbBackupRestore"
            backupCommand.Parameters.Add("@Operation", SqlDbType.VarChar).Value = "RESTORE"
            backupCommand.Connection = conn
            backupCommand.ExecuteNonQuery()

            conn.Close()
            conn.Dispose()
        End Using


        Console.WriteLine("COMPLETED")
    End Sub

    Public Shared Sub DeleteDatabaseBackup()
        If File.Exists(Utility.DatabaseBackupFilePath) Then
            File.Delete(Utility.DatabaseBackupFilePath)
        End If
    End Sub

    ' ---- END CODE FOR SQLSYNCPROVIDER, SQLCESYNCPROVIDER, ---- // 
    ' --------------- AND DBSYNCPROVIDER SAMPLES --------------- 



    ' ---------- BEGIN CODE RELATED TO SQL SERVER COMPACT --------- // 

    Public Shared Sub DeleteAndRecreateCompactDatabase(ByVal sqlCeConnString As String, ByVal recreateDatabase As Boolean)

        Using clientConn As New SqlCeConnection(sqlCeConnString)
            If File.Exists(clientConn.Database) Then
                File.Delete(clientConn.Database)
            End If
        End Using

        If recreateDatabase = True Then
            Dim sqlCeEngine As New SqlCeEngine(sqlCeConnString)
            sqlCeEngine.CreateDatabase()

        End If
    End Sub

    ' ---------- END CODE RELATED TO SQL SERVER COMPACT --------- // 



    ' ---------- BEGIN CODE FOR DBSERVERSYNCPROVIDER AND --------- // 
    ' ---------- SQLCECLIENTSYNCPROVIDER SAMPLES --------- 

    Public Shared Sub MakeDataChangesOnServer(ByVal tableName As String)
        Dim rowCount As Integer = 0

        Using serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
            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. 
                sqlCommand.CommandText = "usp_InsertCustomerAndOrderHeader "
                sqlCommand.CommandType = CommandType.StoredProcedure
                sqlCommand.Parameters.Add("@customer_inserts", SqlDbType.Int)
                sqlCommand.Parameters("@customer_inserts").Value = 13
                sqlCommand.Parameters.Add("@orderheader_inserts", SqlDbType.Int)
                sqlCommand.Parameters("@orderheader_inserts").Value = 33
                sqlCommand.Parameters.Add("@sets_of_inserts", SqlDbType.Int)
                sqlCommand.Parameters("@sets_of_inserts").Value = 2

            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()
        End Using

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

    'Get a dataset to use for schema creation. 
    Public Shared Function CreateDataSetFromServer() As DataSet
        Dim dataSet As New DataSet()
        Dim dataAdapter As New SqlDataAdapter()

        Using serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
            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()
        End Using

        Return dataSet
    End Function

    'Revert changes that were made during synchronization. 
    Public Shared Sub CleanUpServer()
        Using serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
            Dim sqlCommand As SqlCommand = serverConn.CreateCommand()
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "usp_InsertSampleData"

            serverConn.Open()
            sqlCommand.ExecuteNonQuery()
            serverConn.Close()
        End Using
    End Sub


    'Create the Customer table on the client. 
    Public Shared Sub CreateTableOnClient()
        Using clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)
            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()
        End Using
    End Sub

    'Add DEFAULT constraints on the client. 
    Public Shared 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 Sync Framework uses 
        'to create the schema on the client. 
        Dim alterTable As New SqlCeCommand()
        alterTable.Connection = DirectCast(clientConn, SqlCeConnection)
        alterTable.Transaction = DirectCast(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()
                Exit Select

            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()
                Exit Select

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

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

        End Select
    End Sub

    Public Shared Sub MakeDataChangesOnClient(ByVal tableName As String)
        Dim rowCount As Integer = 0

        Using clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)

            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()
        End Using

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

    'Make a change at the client that fails when it is 
    'applied at the server. 
    Public Shared Sub MakeFailingChangeOnClient()
        Dim rowCount As Integer = 0

        Using clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)

            Dim sqlCeCommand As SqlCeCommand = clientConn.CreateCommand()

            clientConn.Open()

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


            clientConn.Close()
        End Using

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

    'Make changes at the client and server that conflict 
    'when they are synchronized. 
    Public Shared Sub MakeConflictingChangesOnClientAndServer()
        Dim rowCount As Integer = 0

        Using serverConn As New SqlConnection(Utility.ConnStr_DbServerSync)
            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()
        End Using

        Using clientConn As New SqlCeConnection(Utility.ConnStr_SqlCeClientSync)

            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()
        End Using

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

    ' ---------- END CODE FOR DBSERVERSYNCPROVIDER AND --------- // 
    ' ---------- SQLCECLIENTSYNCPROVIDER SAMPLES --------- 

End Class


See Also

Show:
© 2014 Microsoft