Utility Class for Sync Services How-to Topics
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 static string _clientPassword;
//Get and set the client database password.
public static string Password
{
get { return _clientPassword; }
set { _clientPassword = value; }
}
//Have the user enter a password for the client database file.
public void SetClientPassword()
{
Console.WriteLine("Type a strong password for the client");
Console.WriteLine("database, and then press Enter.");
Utility.Password = Console.ReadLine();
}
//Return the client connection string with the password.
public string ClientConnString
{
get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
}
private static string _serverName = "localhost";
private static string _serverDbName = "SyncSamplesDb";
//Set the server and database to connect to.
public void SetServerAndDb(string serverName, string serverDbName)
{
_serverName = serverName;
_serverDbName = serverDbName;
}
//Return the server connection string.
public string ServerConnString
{
get { return "Data Source=" + _serverName + "; Initial Catalog=" + _serverDbName + "; Integrated Security=True"; }
}
//Return the peer1 connection string.
public string Peer1ConnString
{
get { return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer1; Integrated Security=True"; }
}
//Return the peer2 connection string.
public string Peer2ConnString
{
get { return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer2; Integrated Security=True"; }
}
//Return the peer3 connection string.
public string Peer3ConnString
{
get { return "Data Source=localhost; Initial Catalog=SyncSamplesDb_Peer3; Integrated Security=True"; }
}
//Make server changes that are synchronized on the second
//synchronization.
public void MakeDataChangesOnServer(string tableName)
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
if (tableName == "Customer")
{
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'";
}
else if (tableName == "CustomerContact")
{
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'";
}
else if (tableName == "CustomerAndOrderHeader")
{
//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;
}
else if (tableName == "Vendor")
{
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.'";
}
serverConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
}
//Get a dataset to use for schema creation.
public DataSet CreateDataSetFromServer()
{
DataSet dataSet = new DataSet();
SqlDataAdapter dataAdapter = new SqlDataAdapter();
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand createDataSet = serverConn.CreateCommand();
createDataSet.CommandText =
"SELECT OrderId, CustomerId, OrderDate " +
"FROM Sales.OrderHeader";
serverConn.Open();
dataAdapter.SelectCommand = createDataSet;
dataAdapter.FillSchema(dataSet, SchemaType.Source);
serverConn.Close();
}
return dataSet;
}
//Revert changes that were made during synchronization.
public void CleanUpServer()
{
using(SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand sqlCommand = serverConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_InsertSampleData";
serverConn.Open();
sqlCommand.ExecuteNonQuery();
serverConn.Close();
}
}
//Make peer changes that are synchronized on the second
//synchronization.
public void MakeDataChangesOnPeer(string peerConnString, string tableName)
{
int rowCount = 0;
using (SqlConnection peerConn = new SqlConnection(peerConnString))
{
SqlCommand sqlCommand = peerConn.CreateCommand();
if (tableName == "Customer")
{
if (peerConnString == this.Peer1ConnString)
{
sqlCommand.CommandText =
"INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
"VALUES ('Cycle Mart', 'James Bailey', 'Retail')";
}
else if (peerConnString == this.Peer2ConnString)
{
sqlCommand.CommandText =
"UPDATE Sales.Customer " +
"SET SalesPerson = 'James Bailey' " +
"WHERE CustomerName = 'Tandem Bicycle Store' ";
}
else if (peerConnString == this.Peer3ConnString)
sqlCommand.CommandText =
"DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
}
peerConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
peerConn.Close();
}
Console.WriteLine("Total rows inserted, updated, or deleted at all peers: " + rowCount/2);
}
//Make conflicting peer changes that are synchronized on the second
//synchronization.
public void MakeConflictingChangesOnPeer(string peerConnString, string tableName)
{
int rowCount = 0;
using (SqlConnection peerConn = new SqlConnection(peerConnString))
{
SqlCommand sqlCommand = peerConn.CreateCommand();
if (tableName == "Customer")
{
if (peerConnString == this.Peer1ConnString)
{
sqlCommand.CommandText =
"UPDATE Sales.Customer " +
"SET SalesPerson = 'ChangeFromPeerOne' " +
"WHERE CustomerName = 'Tandem Bicycle Store' ";
}
else if (peerConnString == this.Peer2ConnString)
{
sqlCommand.CommandText =
"UPDATE Sales.Customer " +
"SET SalesPerson = 'ChangeFromPeerTwo' " +
"WHERE CustomerName = 'Tandem Bicycle Store' ";
}
else if (peerConnString == this.Peer3ConnString)
sqlCommand.CommandText =
"UPDATE Sales.Customer " +
"SET SalesPerson = 'ChangeFromPeerThree' " +
"WHERE CustomerName = 'Tandem Bicycle Store' ";
}
peerConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
peerConn.Close();
}
Console.WriteLine("Total rows inserted, updated, or deleted at all peers: " + rowCount/2);
}
public void MakeFailingChangeOnPeer(string peerConnString)
{
int rowCount = 0;
using (SqlConnection peerConn = new SqlConnection(peerConnString))
{
SqlCommand sqlCommand = peerConn.CreateCommand();
if (peerConnString == this.Peer2ConnString)
{
sqlCommand.CommandText =
"DELETE FROM Sales.Customer " +
"WHERE CustomerName = 'Rural Cycle Emporium'";
}
peerConn.Open();
rowCount = sqlCommand.ExecuteNonQuery();
peerConn.Close();
}
Console.WriteLine("Total rows inserted, updated, or deleted at all peers: " + rowCount/2);
}
public void CleanUpPeer(string peerConnString)
{
using(SqlConnection peerConn = new SqlConnection(peerConnString))
{
SqlCommand sqlCommand = peerConn.CreateCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_ResetPeerData";
peerConn.Open();
sqlCommand.ExecuteNonQuery();
peerConn.Close();
}
}
//Create the Customer table on the client.
public void CreateTableOnClient()
{
using(SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
SqlCeCommand createTable = 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();
}
}
//Add DEFAULT constraints on the client.
public void MakeSchemaChangesOnClient(IDbConnection clientConn, IDbTransaction clientTran, string tableName)
{
//Execute the command over the same connection and within
//the same transaction that Synchronization Services uses
//to create the schema on the client.
SqlCeCommand alterTable = new SqlCeCommand();
alterTable.Connection = (SqlCeConnection)clientConn;
alterTable.Transaction = (SqlCeTransaction)clientTran;
alterTable.CommandText = String.Empty;
//Execute the command, then leave the transaction and
//connection open. The client provider will commit and close.
switch (tableName)
{
case "Customer":
alterTable.CommandText =
"ALTER TABLE Customer " +
"ADD CONSTRAINT DF_CustomerId " +
"DEFAULT NEWID() FOR CustomerId";
alterTable.ExecuteNonQuery();
break;
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();
break;
case "OrderDetail":
alterTable.CommandText =
"ALTER TABLE OrderDetail " +
"ADD CONSTRAINT DF_Quantity " +
"DEFAULT 1 FOR Quantity";
alterTable.ExecuteNonQuery();
break;
case "Vendor":
alterTable.CommandText =
"ALTER TABLE Vendor " +
"ADD CONSTRAINT DF_VendorId " +
"DEFAULT NEWID() FOR VendorId";
alterTable.ExecuteNonQuery();
break;
}
}
//Make client changes that are synchronized on the second
//synchronization.
public void MakeDataChangesOnClient(string tableName)
{
int rowCount = 0;
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
SqlCeCommand sqlCeCommand = clientConn.CreateCommand();
clientConn.Open();
if (tableName == "Customer")
{
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();
}
else if (tableName == "Vendor")
{
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();
}
clientConn.Close();
}
Console.WriteLine("Rows inserted, updated, or deleted at the client: " + rowCount);
}
//Make a change at the client that fails when it is
//applied at the server.
public void MakeFailingChangesOnClient()
{
int rowCount = 0;
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
SqlCeCommand sqlCeCommand = clientConn.CreateCommand();
clientConn.Open();
sqlCeCommand.CommandText =
"DELETE FROM Customer " +
"WHERE CustomerName = 'Rural Cycle Emporium'";
rowCount += sqlCeCommand.ExecuteNonQuery();
clientConn.Close();
}
Console.WriteLine("Rows deleted at the client that will fail at the server: " + rowCount);
}
//Make changes at the client and server that conflict
//when they are synchronized.
public void MakeConflictingChangesOnClientAndServer()
{
int rowCount = 0;
using (SqlConnection serverConn = new SqlConnection(this.ServerConnString))
{
SqlCommand 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();
}
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
SqlCeCommand 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();
}
Console.WriteLine("Conflicting rows inserted, updated, or deleted at the client or server: " + rowCount);
}
//Delete the client database.
public void RecreateClientDatabase()
{
using (SqlCeConnection clientConn = new SqlCeConnection(this.ClientConnString))
{
if (File.Exists(clientConn.Database))
{
File.Delete(clientConn.Database);
}
}
SqlCeEngine sqlCeEngine = new SqlCeEngine(this.ClientConnString);
sqlCeEngine.CreateDatabase();
}
}
}
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