It has been made clear by the SQL Server Compact team that version 4.0 does not support Sync technologies like Merge Replication and Sync Framework.
Imagine a scenario where you have a desktop or web app, where the data is mix of the user’s own operational data and lookup data from a central data source. How would you enable this using SQL Server Compact 4.0?
RDA (Remote Data Access) is a simple and proven technology, that allows you to “Pull” an entire table from a SQL Server over http (take a snapshot), without much coding effort. I decided to test if this technology would still work with 4.0, and lo an behold, it does. (Notice that RDA support will be removed from SQL Server Compact in a future release)
That means that you can pull down lookup data from a central server to your SQL Server Compact 4.0 database file as needed. I configured my SQL Server Compact ISAPI agent DLL according to my post here, and was able to pull a table to my version 4.0 database file. (Remember to DROP the local table before you Pull).
Here is the small amount of code required (Console application):
using System;
using System.Data.SqlServerCe;
namespace TestRDA40
{
class Program
{
static void Main(string[] args)
{
// Connection String to the SQL Server
//
string rdaOleDbConnectString = "Data Source=(local);Initial Catalog=ABC; " +
"User Id=xxx;Password=yyy";
// Initialize RDA Object
//
SqlCeRemoteDataAccess rda = null;
try
{
// Try the Pull Operation
//
rda = new SqlCeRemoteDataAccess(
"http://localhost/ssce35/sqlcesa35.dll",
@"Data Source=C:\data\sqlce\test\nw40.sdf");
rda.Pull("ELMAH", "SELECT * FROM dbo.ELMAH_Error", rdaOleDbConnectString);
}
catch (SqlCeException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
rda.Dispose();
}
}
}
}
Hope you find this useful.
