12

I develop a Windows C# application which can work in Online and Offline mode.
When in Online mode it connects to a SQL Server. In Offline mode it connects to a local DB.

I use the Microsoft Sync Framework 2.1 to sync the 2 databases on demand.

Until now I used a LocalDB instance of SQL Server as the local database. But it is a pain to setup the system automatically during the installation process of my application. So I tought to use SQL Server Compact 3.5 or 4.0 which is very easy to distribute (comes in a single file).

But I cannot get it to even compile the provisioning code of the Compact DB:

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("MyScope");
SqlCeConnection clientConn = new SqlCeConnection(OfflineConnectionString);
var clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
clientProvision.Apply();

which I used before (without the Ce classes) but SqlCeSyncScopeProvisioning cannot be resolved.

Something is terribly wrong here.
How can I sync my CompactDB to distribute this as my local database?

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Have you tried distributing LocalDB database files? You can connect to a particular LocalDB file. E.g. `connectionString="Data Source=(LocalDb)\v12.0;AttachDbFilename=C:\MyDatabase.mdf;Initial Catalog=MyDatabase;Integrated Security=True"`. I haven't distributed my apps on client PCs, so I don't know if installing *SqlLocalDB* is troublesome or not, but you can give it a shot. – Gabrielius Sep 21 '16 at 09:24
  • Installing SqlLocalDB is not so easy. Especially to get it working when I have to remove an old existing instance and install the new one. But if the Compact approach fails I have to continue to work on that. Thanks. – juergen d Sep 21 '16 at 11:17
  • Why remove the old one? Is it yours? You can add a new instance with whatever name you want. I guess, I am advocating to LocalDB, because CE is kinda old and will die eventually. – Gabrielius Sep 21 '16 at 11:23
  • Yes, I have to replace old instances of my applcation.So I have to replace the local instance and install the new one with the latest DB schema. But I want it to have the same instance name. – juergen d Sep 21 '16 at 11:24
  • Do you use some kind of ORM? Like EF or NHibernate? If yes, maybe database migration would be a solution? – Gabrielius Sep 21 '16 at 15:22

1 Answers1

7

First ensure, you have successfully installed the sdk?

After this, make sure you have added the following references:

  • Microsoft.Synchronization.Data.dll,

  • Microsoft.Synchronization.Data.Server.dll

  • Microsoft.Synchronization.Data.SqlServer.dll
  • Microsoft.Synchronization.Data.SqlServerCe.dll

Also in my case it worked with adding

  • System.Data.SqlServerCe - 4.0

EDIT

Against your comment, this is only working if you use SQL Server CE 4.

I've tried it now with SQL Server CE 3.5 and indeed I could reproduce your issue.

Switching to SQL Server CE 4.0 fixed it.

ExampleTable 4.0

Table

TestCode

 var scopeDesc = new DbSyncScopeDescription("MyScope");
 var tbl = new DbSyncTableDescription("TestTable");
 var pkColumn = new DbSyncColumnDescription("Id", "int");
 pkColumn.IsPrimaryKey = true;
 tbl.Columns.Add(pkColumn);
 tbl.Columns.Add(new DbSyncColumnDescription("Name", "nvarchar(254)"));
 scopeDesc.Tables.Add(tbl);
 var clientConn = new SqlCeConnection(@"Data Source=test.sdf;Persist Security Info=False;");
 var clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
 clientProvision.Apply();

Result

Everything compiles nicely. After following the above steps, you should be able to easily migrate your code to SQL Server CE

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lokusking
  • 7,396
  • 13
  • 38
  • 57
  • Thanks for the answer. I use VS2013. The SDK is installed correctly since I already used it before with the LocalDB instance and it worked. I only tried to change to CE and now it does not compile the code which of course I had to adjust like from using `SqlSyncScopeProvisioning` to now using `SqlCeSyncScopeProvisioning`. I also use SQL Compact 3.5 since 4.0 is not supported with the latest sync framework. All DLLs are added to the solution. – juergen d Sep 21 '16 at 11:14
  • @juergend Updated my Answer – lokusking Sep 21 '16 at 12:13
  • Using the Version 3.1 instead of 3.5 of Microsoft.Synchronization.Data.SqlServerCe.dll soved it for me. I did not try it with Compact 4.0 because you can read at various sites that it is not supported. – juergen d Sep 21 '16 at 17:17