SQL Server 2008 R2 SP1 Merge Replication hotfixes

It seems that the recent Cumulative Update 4 to SQL Server 2008 R2 contains a couple of essential hotfixes, if you are using Merge Replication with SQL Server Compact 3.5 against a SQL Server 2008 R2 instance.

The 2 hotfixes are:

FIX: Poor performance when you synchronize the data from a SQL Server 2008 R2 publisher to a SQL Server compact 3.5 subscriber 2616718

FIX: Merge replication changes are missing on a SQL Server Compact subscriber when they subscribe to a partition in SQL Server 2008 R2 2644396

As always, test before applying this Update in production.

Review of “Windows Phone 7.5 Data Cookbook”

The “Windows Phone 7.5 Data Cookbook” is a collection of more that 30 so-called recipes  for working with with data in Windows Phone 7.5 applications. It covers all data related tasks from UI data binding and MVVM to using WCF Data Services.

A recipe is a step-by-step walkthrough of a sample mini application, that covers the subject at hand. All the samples are available for download from the publishers web site for registered users.

The book covers the following topics:
- Data binding, including element binding, DataContext, data templates, converters.
- Isolated storage
- Using XML files
- Consuming OData, and performing CRUD against OData sources
- Using on-device databases, including Perst, SQLite and SQL Server Compact with LINQ to SQL
- Consuming REST feeds, and creating a REST service
- WCF Data Services
- MVVM

The hands-on approach is useful for any developer new to the Windows Phone platform, and can also be used a samples for seasoned developers in need of a quick solution to a problem.

You can also download a sample chapter of the book, Chapter 2 – Isolated Storage to get a feel for the format.

Windows Phone Local Database tip: Initializing the database

There are several options for initializing the database for Window Phone, depending on your approach. By “initializing” I mean preparing the database for first use, just after the application has ben installed.

Whether you create the DataContext by hand or use RAD “Database First” with the SQL Server Compact Toolbox or the ExportSqlCe command line utility is not relevant.

You can read more about Database First here: http://erikej.blogspot.com/2012/01/generating-linq-to-sql-datacontext-with.html and more about Code First here: http://msdn.microsoft.com/en-us/library/hh202876(v=VS.92).aspx

I will assume you have used one of my tools to create the DataContext classes.

Option 1: No database file included

In this case, there is no initial data in the local database, it will be populated either by getting data from the web, or by manual entry by the user. The database must be writable, and must therefore reside in Isolated Storage. The connection string look like this: "Data Source=isostore:/Chinook.sdf"

To create the database based on the definition in the DataContext classes, you can use the following code, for example for each database call, or during app start:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    if (!db.DatabaseExists())
        db.CreateDatabase();
}
OR:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.CreateIfNotExists();
}

Option 2: Database file included, read and write

With this option, you include a database (.sdf file) in your project as en embedded resource:

You database must reside in Isolated Storage to be writable, and it must be copied to Isolated Storage as required. The connection string looks like this: "Data Source=isostore:/Chinook.sdf"

To copy the database to Isolated Storage, you can use the following code, for example for each database call, or during app start:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.CreateIfNotExists();
}

Option 3: Database file included, read only

With this option, you use the database for reference data only, and it can reside in the program files folder as a read-only resource. You include the database (.sdf file) as Content in your project:

You do not need to copy the database anywhere, it is installed with the other content in your application. The connection string looks like this: "Data Source=appdata:/Chinook.sdf;File Mode=Read Only;"

To connect to the database, use the following code:

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionStringReadOnly))
{
    db.LogDebug = true;
    // More data access here...
}

I hope this article gave you an overview over the various options for database initialization with Local Database on Windows Phone.

Generating a LINQ to SQL DataContext for VS Express for Windows Phone

As you may know, you can generate a Windows Phone DataContext based on an existing database for use with Windows Phone Mango “Local Database”, as described here and here. But this feature is only available if you own a paid Visual Studio edition (Pro or higher), as the Express editions do not support add-ins.

But users of Visual Studio 2010 Express for Windows Phone can now also take advantage of all the productivity benefits of the “Database First” approach to Local Database development on Windows Phone.

Provided you have a SQL Server Compact Database ready, designed according to the guidelines here, and possibly created by using a tool, you can follow this simple procedure to generate and add the DataContext to your Windows Phone solution.

Anyone using the Express tools will notice that SQLMetal (which is used behind the scenes to create the DataContext file) is not included. You can get it from the Microsoft SDK bundle http://www.microsoft.com/download/en/details.aspx?id=8279. You only need to choose the following options to get the required files.

Then go to the download page for my ExportSqlCe tool, and download the latest version of the Exportsqlce.exe command line utility.

You can now generate a DataContext file from the command line, as documented here.

In this example, I will generate a DataContext based on the Chinook sample database.

exportsqlce wpdc "Data Source=C:\projects\Chinook\Chinook.sdf" C:\temp\Chinook.cs

Notice that the format of the database must be SQL Server Compact 3.5.

In Visual Studio, open your Windows Mango project, and from Solution Explorer, select Add, Existing Item, and browse to the file the was just created.

Remember to add a reference to System.Data.Linq to your project.

You can now start using the generated DataContext in your project!

using (ChinookContext db = new ChinookContext(ChinookContext.ConnectionString))
{
    db.LogDebug = true;

    var artist = db.Artists.FirstOrDefault();
    artist.Name = Guid.NewGuid().ToString();

    db.SubmitChanges();
}

Happy coding…

SQL Server Compact Toolbox 2.6.1–Visual Guide of new features

After more than 85.000 downloads, version 2.6.1 of my SQL Server Compact Toolbox extension for Visual Studio 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker

Edit Table Data

This feature, which allows you to edit the table data in a grid, and allows column sorting, image import/export and quick search, has been enhanced with the following new features:

The grid now only loads the first 200 rows, to better handle large tables. The number of rows initially loaded can be configured via the Options dialog:

In addition, you can use any SELECT statement as the basis for the grid, by entering this statement using the SQL button at the bottom of the grid:

Pressing this button will give you access to enter any SQL SELECT statement:

Windows Phone DataContext

The Windows Phone DataContext feature allows you generate a DataContext for use in Windows Phone projects, based on an existing database, saving you a large coding effort. This feature has been enhanced as follows:

You can now generate a DataContext in VB code, in addition to C# code:

The generated DataContext contains to new useful properties:

using (PostCodesContext db = new PostCodesContext(PostCodesContext.ConnectionStringReadOnly))
{
    if (System.Diagnostics.Debugger.IsAttached)
    {
        db.LogDebug = true;
    }
    _allItems = db.PostCode.ToList();
}

ConnectionStringReadOnly allows you to include a database for reference data, and using the appdata:// syntax on the connection string, this database can reside in your programs folder, no need to copy to Isolated Storage.

LogDebug allows you to log any SQL statement generated to the Visual Studio debug output window, as described in my blog post here. A big thanks to Nicolò Carandini, who insipred me by mail and this blog post to add this feature.

Explorer Tree

The Tables node now has a Refresh menu item, allowing you to refresh only objects in a single database:

In addition, when running ALTER, DROP and CREATE scripts, a refresh will be attempted.

Object Descriptions

You can now edit all the descriptions for a table in a single window, thanks to new contributor giddy:

Scripting entire database – table selection

When scripting an entire SQL Server or SQL Server Compact database, you now have the option to exclude tables from being scripted:

Other minor improvements

All Dialogs cleaned up for VS UI compliance ( Thanks to new contributor giddy )


Stopped handling unhandled exceptions, had undesired side effects


Warning when Primary Keys were missing when creating DataContext and EDMX


Using a "nicer" property grid

See this overview of the work items in this release.

CSV to SQL Server Compact scripting utility

My SSMS and Visual Studio add-ins for SQL Server Compact includes the option to import CSV files. But this GUI based feature does not work so well with larger CSV files, as the generated INSERT script can become very large. So I have now made available a command line utility that performs the same functionality, but works better with larger CSV files (and allows you to automate the process).

Please give the utility a try, and provide feedback here.