· 9 years ago · Jan 31, 2017, 05:46 PM
1Getting Started: Client and Server Synchronization
2
3SQL Server 2008 R2 Other Versions
4This topic describes a console application that downloads an initial dataset and then a set of incremental changes from a single table. The application is straightforward, but it will introduce you to code that is built on, in many ways, throughout the Sync Framework documentation. If you have read Architecture and Classes for Client and Server Synchronization, you should have an understanding of the main classes that are used in the application.
5You can learn by just reading through the example code. However, it is more instructive to run the application and to see it in action. Before you run the code, make sure that you have the following installed:
6Sync Framework
7The application requires references to Microsoft.Synchronization.Data.dll, Microsoft.Synchronization.Data.Server.dll, and Microsoft.Synchronization.Data.SqlServerCe.dll.
8SQL Server Compact Service Pack 1
9The application requires a reference to System.Data.SqlServerCe.dll.
10A version of SQL Server other than SQL Server Compact to act as the server database.
11The example code uses localhost in connection strings. To use the instance of SQL Server Express that installs with Visual Studio, change localhost to .\sqlexpress. To use a remote server, change localhost to the appropriate server name.
12The Sync Framework sample databases. Execute both scripts that are available in Setup Scripts for Database Provider How-to Topics. We recommend to that you review these scripts to see how change tracking is handled in the server database.
13The application is composed of six classes:
14SampleSyncAgent. This class is derived from SyncAgent and contains a SyncTable.
15SampleServerSyncProvider. This class is derived from DbServerSyncProvider and contains the SyncAdapter.
16SampleClientSyncProvider. This class is derived from SqlCeClientSyncProvider. In this example, this class contains only a connection string to the client database.
17SampleStats. This class uses the statistics that are returned by the SyncAgent.
18Program. This class sets up synchronization and calls methods from the Utility class.
19Utility. This class handles all functionality that is not directly related to synchronization, such as holding connection string information and making changes to the server database. A complete Utility class is used in other topics. The complete class is available from Utility Class for Database Provider How-to Topics.
20Key Parts of the API
21Before you look at the complete code example, we recommend that you review the following examples that illustrate several key sections of the API that are used in this application.
22Creating a SyncTable
23The following code example creates a SyncTable object for the Customer table, specifies the synchronization direction, and specifies how the table should be created on the client. In this case, if the table already exists in the client database, the table will be dropped during the first synchronization.
24C#VB
25SyncTable customerSyncTable = new SyncTable("Customer");
26customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
27customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
28this.Configuration.SyncTables.Add(customerSyncTable);
29
30Using the SqlSyncAdapterBuilder
31Each of the code examples in this section creates a SyncAdapter for the Customer table. The synchronization adapter provides to the server synchronization provider the specific commands that are required to interact with the server database. In this application, the synchronization adapter is created by using the SqlSyncAdapterBuilder. The first example shows how to use the SqlSyncAdapterBuilder with a custom change tracking system. The second example shows how to use the SqlSyncAdapterBuilder with SQL Server change tracking (available in SQL Server 2008). For more information about change tracking, see Tracking Changes in the Server Database.
32For information about how to create commands manually instead of using the builder, see How to: Download Incremental Data Changes to a Client.
33Using a Custom Change Tracking System
34To use a custom change tracking system, specify the following information for the SqlSyncAdapterBuilder and SyncAdapter:
35The name of the table to synchronize and the tombstone table. A tombstone table is used to track delete operations in the server database. For more information, see Tracking Changes in the Server Database. If the tables are in a schema other than dbo, the schema must be specified.
36The direction of synchronization. This controls which commands the SqlSyncAdapterBuilder creates. For more information about commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.
37The tracking columns in the server database. The columns are used to track when changes are made, so that only new changes are downloaded. You can include additional columns to track where changes are made. For more information, see How to: Use a Custom Change Tracking System.
38The name of the SyncAdapter. This must match the name of the SyncTable. Therefore, it should not include the schema name.
39C#VB
40SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
41
42customerBuilder.TableName = "Sales.Customer";
43customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
44customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
45customerBuilder.CreationTrackingColumn = "InsertTimestamp";
46customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
47customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
48
49SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter(false, false, false, false);
50customerSyncAdapter.TableName = "Customer";
51this.SyncAdapters.Add(customerSyncAdapter);
52
53Using SQL Server Change Tracking
54To use SQL Server change tracking, specify the following information for the SqlSyncAdapterBuilder and SyncAdapter:
55The name of the table to synchronize.
56The direction of synchronization. This controls which commands the SqlSyncAdapterBuilder creates. For more information about commands, see How to: Specify Snapshot, Download, Upload, and Bidirectional Synchronization.
57The type of change tracking to use. By default, Sync Framework expects you to specify custom change tracking columns. In this code example, SQL Server change tracking is specified.
58The name of the SyncAdapter. This must match the name of the SyncTable. Therefore, it should not include the schema name.
59C#VB
60SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
61
62customerBuilder.TableName = "Sales.Customer";
63customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
64
65SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
66customerSyncAdapter.TableName = "Customer";
67this.SyncAdapters.Add(customerSyncAdapter);
68
69Specifying the New Anchor Command
70The following code example specifies a command to retrieve a new anchor value from the server. The value is stored in the client database and is used by the commands that synchronize changes. 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.
71In this case, MIN_ACTIVE_ROWVERSION returns a timestamp value from a SQL Server database. (MIN_ACTIVE_ROWVERSION was introduced in SQL Server 2005 Service Pack 2.) A timestamp value is used because the tracking columns that are specified for the SqlSyncAdapterBuilder contain timestamp values. If the tracking columns contained date values, you could use a function such as GETUTCDATE() instead of MIN_ACTIVE_ROWVERSION. For more information about anchors, see Tracking Changes in the Server Database.
72The SyncSession class contains several string constants that can be used in synchronization commands. SyncNewReceivedAnchor is one of these constants. You could also use the literal @sync_new_received_anchor directly in your queries.
73C#VB
74SqlCommand selectNewAnchorCommand = new SqlCommand();
75string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
76selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
77selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
78selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
79selectNewAnchorCommand.Connection = serverConn;
80this.SelectNewAnchorCommand = selectNewAnchorCommand;
81
82Calling the Synchronize Method
83The following code example instantiates SampleSyncAgent and calls the Synchronize method. In the SampleSyncAgent class, the SampleClientSyncProvider is specified as the LocalProvider and the SampleServerSyncProvider is specified as the RemoteProvider, and also the synchronization table that has already been described.
84C#VB
85SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
86SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
87
88In the SampleStats class, statistics that are returned by the SyncAgent are used to provide feedback to the user about the synchronization session. For more information, see How to: Work with Events and Program Business Logic.
89C#VB
90Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
91Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
92Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
93Console.WriteLine(String.Empty);
94
95Complete Code Examples
96Now that you have seen the main sections of code that are involved in synchronization, these sections are combined in a complete application that is thoroughly commented. After you run this application, we recommend that you read the topics in the section Programming Common Client and Server Synchronization Tasks. You will see the same classes that are used in the code examples in this topic. However, they are applied across additional tables in more sophisticated ways.
97Complete Example Using Custom Change Tracking
98C#VB
99using System;
100using System.IO;
101using System.Text;
102using System.Data;
103using System.Data.SqlClient;
104using System.Data.SqlServerCe;
105using Microsoft.Synchronization;
106using Microsoft.Synchronization.Data;
107using Microsoft.Synchronization.Data.Server;
108using Microsoft.Synchronization.Data.SqlServerCe;
109
110namespace Microsoft.Samples.Synchronization
111{
112 class Program
113 {
114 static void Main(string[] args)
115 {
116
117 //The SampleStats class handles information from the SyncStatistics
118 //object that the Synchronize method returns.
119 SampleStats sampleStats = new SampleStats();
120
121 //Delete and re-create the database. The client synchronization
122 //provider also enables you to create the client database
123 //if it does not exist.
124 Utility.SetClientPassword();
125 Utility.RecreateCompactDatabase();
126
127 //Initial synchronization. Instantiate the SyncAgent
128 //and call Synchronize.
129 SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
130 SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
131 sampleStats.DisplayStats(syncStatistics, "initial");
132
133 //Make changes on the server.
134 Utility.MakeDataChangesOnServer();
135
136 //Subsequent synchronization.
137 syncStatistics = sampleSyncAgent.Synchronize();
138 sampleStats.DisplayStats(syncStatistics, "subsequent");
139
140 //Return server data back to its original state.
141 Utility.CleanUpServer();
142
143 //Exit.
144 Console.Write("\nPress Enter to close the window.");
145 Console.ReadLine();
146 }
147 }
148
149 //Create a class that is derived from
150 //Microsoft.Synchronization.SyncAgent.
151 public class SampleSyncAgent : SyncAgent
152 {
153 public SampleSyncAgent()
154 {
155 //Instantiate a client synchronization provider and specify it
156 //as the local provider for this synchronization agent.
157 this.LocalProvider = new SampleClientSyncProvider();
158
159 //Instantiate a server synchronization provider and specify it
160 //as the remote provider for this synchronization agent.
161 this.RemoteProvider = new SampleServerSyncProvider();
162
163 //Add the Customer table: specify a synchronization direction of
164 //DownloadOnly.
165 SyncTable customerSyncTable = new SyncTable("Customer");
166 customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
167 customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
168 this.Configuration.SyncTables.Add(customerSyncTable);
169 }
170 }
171
172 //Create a class that is derived from
173 //Microsoft.Synchronization.Server.DbServerSyncProvider.
174 public class SampleServerSyncProvider : DbServerSyncProvider
175 {
176 public SampleServerSyncProvider()
177 {
178 //Create a connection to the sample server database.
179 Utility util = new Utility();
180 SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
181 this.Connection = serverConn;
182
183 //Create a command to retrieve a new anchor value from
184 //the server. In this case, we use a timestamp value
185 //that is retrieved and stored in the client database.
186 //During each synchronization, the new anchor value and
187 //the last anchor value from the previous synchronization
188 //are used: the set of changes between these upper and
189 //lower bounds is synchronized.
190 //
191 //SyncSession.SyncNewReceivedAnchor is a string constant;
192 //you could also use @sync_new_received_anchor directly in
193 //your queries.
194 SqlCommand selectNewAnchorCommand = new SqlCommand();
195 string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
196 selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";
197 selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);
198 selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
199 selectNewAnchorCommand.Connection = serverConn;
200 this.SelectNewAnchorCommand = selectNewAnchorCommand;
201
202
203 //Create a SyncAdapter for the Customer table by using
204 //the SqlSyncAdapterBuilder:
205 // * Specify the base table and tombstone table names.
206 // * Specify the columns that are used to track when
207 // changes are made.
208 // * Specify download-only synchronization.
209 // * Call ToSyncAdapter to create the SyncAdapter.
210 // * Specify a name for the SyncAdapter that matches the
211 // the name specified for the corresponding SyncTable.
212 // Do not include the schema names (Sales in this case).
213
214 SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
215
216 customerBuilder.TableName = "Sales.Customer";
217 customerBuilder.TombstoneTableName = customerBuilder.TableName + "_Tombstone";
218 customerBuilder.SyncDirection = SyncDirection.DownloadOnly;
219 customerBuilder.CreationTrackingColumn = "InsertTimestamp";
220 customerBuilder.UpdateTrackingColumn = "UpdateTimestamp";
221 customerBuilder.DeletionTrackingColumn = "DeleteTimestamp";
222
223 SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter(false, false, false, false);
224 customerSyncAdapter.TableName = "Customer";
225 this.SyncAdapters.Add(customerSyncAdapter);
226
227 }
228 }
229
230 //Create a class that is derived from
231 //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
232 //You can just instantiate the provider directly and associate it
233 //with the SyncAgent, but you could use this class to handle client
234 //provider events and other client-side processing.
235 public class SampleClientSyncProvider : SqlCeClientSyncProvider
236 {
237
238 public SampleClientSyncProvider()
239 {
240 //Specify a connection string for the sample client database.
241 Utility util = new Utility();
242 this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
243 }
244 }
245
246 //Handle the statistics that are returned by the SyncAgent.
247 public class SampleStats
248 {
249 public void DisplayStats(SyncStatistics syncStatistics, string syncType)
250 {
251 Console.WriteLine(String.Empty);
252 if (syncType == "initial")
253 {
254 Console.WriteLine("****** Initial Synchronization ******");
255 }
256 else if (syncType == "subsequent")
257 {
258 Console.WriteLine("***** Subsequent Synchronization ****");
259 }
260
261 Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
262 Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
263 Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
264 Console.WriteLine(String.Empty);
265
266 }
267 }
268
269 public class Utility
270 {
271
272 private static string _clientPassword;
273
274 //Get and set the client database password.
275 public static string Password
276 {
277 get { return _clientPassword; }
278 set { _clientPassword = value; }
279 }
280
281 //Have the user enter a password for the client database file.
282 public static void SetClientPassword()
283 {
284 Console.WriteLine("Type a strong password for the client");
285 Console.WriteLine("database, and then press Enter.");
286 Utility.Password = Console.ReadLine();
287 }
288
289 //Return the client connection string with the password.
290 public static string ConnStr_SqlCeClientSync
291 {
292 get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
293 }
294
295 //Return the server connection string.
296 public static string ConnStr_DbServerSync
297 {
298
299 get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb; Integrated Security=True"; }
300
301 }
302
303 //Make server changes that are synchronized on the second
304 //synchronization.
305 public static void MakeDataChangesOnServer()
306 {
307 int rowCount = 0;
308
309 using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
310 {
311 SqlCommand sqlCommand = serverConn.CreateCommand();
312 sqlCommand.CommandText =
313 "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
314 "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
315
316 "UPDATE Sales.Customer " +
317 "SET SalesPerson = 'James Bailey' " +
318 "WHERE CustomerName = 'Tandem Bicycle Store' " +
319
320 "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
321
322 serverConn.Open();
323 rowCount = sqlCommand.ExecuteNonQuery();
324 serverConn.Close();
325 }
326
327 Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
328 }
329
330 //Revert changes that were made during synchronization.
331 public static void CleanUpServer()
332 {
333 using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
334 {
335 SqlCommand sqlCommand = serverConn.CreateCommand();
336 sqlCommand.CommandType = CommandType.StoredProcedure;
337 sqlCommand.CommandText = "usp_InsertSampleData";
338
339 serverConn.Open();
340 sqlCommand.ExecuteNonQuery();
341 serverConn.Close();
342 }
343 }
344
345 //Delete the client database.
346 public static void RecreateCompactDatabase()
347 {
348 using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
349 {
350 if (File.Exists(clientConn.Database))
351 {
352 File.Delete(clientConn.Database);
353 }
354 }
355
356 SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
357 sqlCeEngine.CreateDatabase();
358 }
359 }
360}
361
362Complete Example Using SQL Server Change Tracking
363C#VB
364using System;
365using System.IO;
366using System.Text;
367using System.Data;
368using System.Data.SqlClient;
369using System.Data.SqlServerCe;
370using Microsoft.Synchronization;
371using Microsoft.Synchronization.Data;
372using Microsoft.Synchronization.Data.Server;
373using Microsoft.Synchronization.Data.SqlServerCe;
374
375namespace Microsoft.Samples.Synchronization
376{
377 class Program
378 {
379 static void Main(string[] args)
380 {
381
382 //The SampleStats class handles information from the SyncStatistics
383 //object that the Synchronize method returns.
384 SampleStats sampleStats = new SampleStats();
385
386 //Delete and re-create the database. The client synchronization
387 //provider also enables you to create the client database
388 //if it does not exist.
389 Utility.SetClientPassword();
390 Utility.RecreateCompactDatabase();
391
392 //Initial synchronization. Instantiate the SyncAgent
393 //and call Synchronize.
394 SampleSyncAgent sampleSyncAgent = new SampleSyncAgent();
395 SyncStatistics syncStatistics = sampleSyncAgent.Synchronize();
396 sampleStats.DisplayStats(syncStatistics, "initial");
397
398 //Make changes on the server.
399 Utility.MakeDataChangesOnServer();
400
401 //Subsequent synchronization.
402 syncStatistics = sampleSyncAgent.Synchronize();
403 sampleStats.DisplayStats(syncStatistics, "subsequent");
404
405 //Return server data back to its original state.
406 Utility.CleanUpServer();
407
408 //Exit.
409 Console.Write("\nPress Enter to close the window.");
410 Console.ReadLine();
411 }
412 }
413
414 //Create a class that is derived from
415 //Microsoft.Synchronization.SyncAgent.
416 public class SampleSyncAgent : SyncAgent
417 {
418 public SampleSyncAgent()
419 {
420 //Instantiate a client synchronization provider and specify it
421 //as the local provider for this synchronization agent.
422 this.LocalProvider = new SampleClientSyncProvider();
423
424 //Instantiate a server synchronization provider and specify it
425 //as the remote provider for this synchronization agent.
426 this.RemoteProvider = new SampleServerSyncProvider();
427
428 //Add the Customer table: specify a synchronization direction of
429 //DownloadOnly.
430 SyncTable customerSyncTable = new SyncTable("Customer");
431 customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
432 customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
433 this.Configuration.SyncTables.Add(customerSyncTable);
434 }
435 }
436
437 //Create a class that is derived from
438 //Microsoft.Synchronization.Server.DbServerSyncProvider.
439 public class SampleServerSyncProvider : DbServerSyncProvider
440 {
441 public SampleServerSyncProvider()
442 {
443 //Create a connection to the sample server database.
444 Utility util = new Utility();
445 SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync);
446 this.Connection = serverConn;
447
448 //Create a command to retrieve a new anchor value from
449 //the server. In this case, we use a timestamp value
450 //that is retrieved and stored in the client database.
451 //During each synchronization, the new anchor value and
452 //the last anchor value from the previous synchronization
453 //are used: the set of changes between these upper and
454 //lower bounds is synchronized.
455 //
456 //SyncSession.SyncNewReceivedAnchor is a string constant;
457 //you could also use @sync_new_received_anchor directly in
458 //your queries.
459 SqlCommand selectNewAnchorCommand = new SqlCommand();
460 string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
461 selectNewAnchorCommand.CommandText =
462 "SELECT " + newAnchorVariable + " = change_tracking_current_version()";
463 selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.BigInt);
464 selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;
465 selectNewAnchorCommand.Connection = serverConn;
466 this.SelectNewAnchorCommand = selectNewAnchorCommand;
467
468
469 //Create a SyncAdapter for the Customer table by using
470 //the SqlSyncAdapterBuilder:
471 // * Specify the base table name.
472 // * Specify that the server uses SQL Server change tracking.
473 // * Specify download-only synchronization.
474 // * Call ToSyncAdapter to create the SyncAdapter.
475 // * Specify a name for the SyncAdapter that matches the
476 // the name specified for the corresponding SyncTable.
477 // Do not include the schema names (Sales in this case).
478
479 SqlSyncAdapterBuilder customerBuilder = new SqlSyncAdapterBuilder(serverConn);
480
481 customerBuilder.TableName = "Sales.Customer";
482 customerBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
483
484 SyncAdapter customerSyncAdapter = customerBuilder.ToSyncAdapter();
485 customerSyncAdapter.TableName = "Customer";
486 this.SyncAdapters.Add(customerSyncAdapter);
487
488 }
489 }
490
491 //Create a class that is derived from
492 //Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.
493 //You can just instantiate the provider directly and associate it
494 //with the SyncAgent, but you could use this class to handle client
495 //provider events and other client-side processing.
496 public class SampleClientSyncProvider : SqlCeClientSyncProvider
497 {
498
499 public SampleClientSyncProvider()
500 {
501 //Specify a connection string for the sample client database.
502 Utility util = new Utility();
503 this.ConnectionString = Utility.ConnStr_SqlCeClientSync;
504 }
505 }
506
507 //Handle the statistics that are returned by the SyncAgent.
508 public class SampleStats
509 {
510 public void DisplayStats(SyncStatistics syncStatistics, string syncType)
511 {
512 Console.WriteLine(String.Empty);
513 if (syncType == "initial")
514 {
515 Console.WriteLine("****** Initial Synchronization ******");
516 }
517 else if (syncType == "subsequent")
518 {
519 Console.WriteLine("***** Subsequent Synchronization ****");
520 }
521
522 Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
523 Console.WriteLine("Total Changes Downloaded: " + syncStatistics.TotalChangesDownloaded);
524 Console.WriteLine("Complete Time: " + syncStatistics.SyncCompleteTime);
525 Console.WriteLine(String.Empty);
526
527 }
528 }
529
530 public class Utility
531 {
532
533 private static string _clientPassword;
534
535 //Get and set the client database password.
536 public static string Password
537 {
538 get { return _clientPassword; }
539 set { _clientPassword = value; }
540 }
541
542 //Have the user enter a password for the client database file.
543 public static void SetClientPassword()
544 {
545 Console.WriteLine("Type a strong password for the client");
546 Console.WriteLine("database, and then press Enter.");
547 Utility.Password = Console.ReadLine();
548 }
549
550 //Return the client connection string with the password.
551 public static string ConnStr_SqlCeClientSync
552 {
553 get { return @"Data Source='SyncSampleClient.sdf'; Password=" + Utility.Password; }
554 }
555
556 //Return the server connection string.
557 public static string ConnStr_DbServerSync
558 {
559
560 get { return @"Data Source=localhost; Initial Catalog=SyncSamplesDb_ChangeTracking; Integrated Security=True"; }
561
562 }
563
564 //Make server changes that are synchronized on the second
565 //synchronization.
566 public static void MakeDataChangesOnServer()
567 {
568 int rowCount = 0;
569
570 using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
571 {
572 SqlCommand sqlCommand = serverConn.CreateCommand();
573 sqlCommand.CommandText =
574 "INSERT INTO Sales.Customer (CustomerName, SalesPerson, CustomerType) " +
575 "VALUES ('Cycle Mart', 'James Bailey', 'Retail') " +
576
577 "UPDATE Sales.Customer " +
578 "SET SalesPerson = 'James Bailey' " +
579 "WHERE CustomerName = 'Tandem Bicycle Store' " +
580
581 "DELETE FROM Sales.Customer WHERE CustomerName = 'Sharp Bikes'";
582
583 serverConn.Open();
584 rowCount = sqlCommand.ExecuteNonQuery();
585 serverConn.Close();
586 }
587
588 Console.WriteLine("Rows inserted, updated, or deleted at the server: " + rowCount);
589 }
590
591 //Revert changes that were made during synchronization.
592 public static void CleanUpServer()
593 {
594 using (SqlConnection serverConn = new SqlConnection(Utility.ConnStr_DbServerSync))
595 {
596 SqlCommand sqlCommand = serverConn.CreateCommand();
597 sqlCommand.CommandType = CommandType.StoredProcedure;
598 sqlCommand.CommandText = "usp_InsertSampleData";
599
600 serverConn.Open();
601 sqlCommand.ExecuteNonQuery();
602 serverConn.Close();
603 }
604 }
605
606 //Delete the client database.
607 public static void RecreateCompactDatabase()
608 {
609 using (SqlCeConnection clientConn = new SqlCeConnection(Utility.ConnStr_SqlCeClientSync))
610 {
611 if (File.Exists(clientConn.Database))
612 {
613 File.Delete(clientConn.Database);
614 }
615 }
616
617 SqlCeEngine sqlCeEngine = new SqlCeEngine(Utility.ConnStr_SqlCeClientSync);
618 sqlCeEngine.CreateDatabase();
619 }
620 }
621}