MS Sync Framework and SQL Server Compact

Question

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?

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
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
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
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
Do you use some kind of ORM? Like EF or NHibernate? If yes, maybe database migration would be a solution? — Gabrielius

lokusking lokusking · Accepted Answer · 2016-09-21T05:36:11

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

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

MS Sync Framework and SQL Server Compact

1 Answers