Thursday, November 29, 2012

SQL Server Compact Merge Replication Library alpha released

I have just published a new Codeplex project, that contains a library to help with SQL Server Merge Replication using SQL Server Compact 3.5 SP2.

This library simplifies the code and configuration to do Merge Replication from a SQL Server Compact 3.5 SP2 desktop client, with a number of useful helper methods.
Features:

  • Is intended for use from a WinForms or WPF application, and the Synchronize method runs async.
  • Implements best practices for optimal performance, and attempt to properly detect expired subscriptions, by throwing a PublicationMayHaveExpiredException.
  • Will create the database file for you as required, so an existing database file is not required.
  • Optionally logs sync status to a SyncLog table (which is a part of the publication)
  • Generate INSERT script in order to rescue local data in case of a disaster (for example publication expiry)
  • Validate a Publication, for example after initial Sync
  • Properly format a SqlCeException as a string to get all available error information
  • Source includes a demo form to test app.config parameters and see the library in action
using ErikEJ.SqlCeMergeLib;
using System.Data.SqlServerCe;
...
string sdfFile = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "MergeTest.sdf");
conn = new SqlCeConnection(string.Format("Data Source={0}", sdfFile));

DateTime syncDate = sync.GetLastSuccessfulSyncTime(conn);
textBox1.Text = "Last Sync: " + syncDate.ToString();

sync.Completed += SyncCompletedEvent;
sync.Progress += SyncProgressEvent;
sync.Synchronize(conn, 1002);

Other useful methods:
Generate INSERT script for the local database (for disaster recovery):

public string GenerateInsertScripts (
SqlCeConnection connection,
List<string> tableNames
)

Format a SqlCeException as a String:

public string ShowErrors (
SqlCeException e
)

Validate that the local database is properly Merge Replicated;

public bool Validate (
SqlCeConnection connection
)

Configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="InternetLogin" value=""/>
<add key="InternetPassword" value=""/>
<add key="InternetUrl" value="http://erik-pc/ssce35sync/sqlcesa35.dll"/>
<add key="Publication" value="PubPostCodes"/>
<add key="Publisher" value="Erik-PC\SQL2008R2"/>
<add key="PublisherDatabase" value="PostCodes"/>
<add key="PublisherLogin" value="sa"/>
<add key="PublisherPassword" value="pw"/>
<add key="UseNT" value="false"/>
</appSettings>
</configuration>

repl.jpg

Hope you will find it useful, and please post any bugs and suggestion via the Issue Tracker on CodePlex.


Note, that it appears that Merge Replication against SQL Server 2012 with SP1 or later is currently broken (but works with SQL Server 2012 RTM).

Tuesday, November 27, 2012

SQL Server Compact Toolbox 3.1.1 with support for Windows Phone 8 and VS 2012 released

Just a short note to let you know, that the SQL Server Compact Toolbox add-in has been updated beginning of this month to support the following new features:

Generation of a LINQ to SQL DataContext for Windows Phone 8 projects in Visual Studio 2012. (Like Windows Phone 7.5, Windows Phone 8 supports a so-called “Local Database”, which is a SQL Server Compact Database accessible only via LINQ to SQL). This feature will allow you to generate a Phone specific DataContext, I have blogged about this earlier as you can see under the heading ErikEJ on this page. 

image

In addition, the Toolbox is now able to use sqlmetal.exe on “clean” Windows 8 systems, with only Visual Studio 2012 Pro or higher installed.

Also note, that the Toolbox supports SQL Server Compact 3.5 database files even under VS 2012, despite the fact, that the VS 2012 Server Explorer no longer supports SQL Server Compact 3.5 (this requires the SQL Server Compact 3.5 SP2 desktop MSI to be installed, of course – download from here.