SQL Server Compact Code Snippet of the Week #12 : get the SQL Server Compact runtime version

Your app/web site may require a specific build version of the SQL Server Compact runtime, due to dependency on a bug fix, for example. This weeks code snippet will demonstrate how to get that information. I have an older blog post here, that goes into deep details about the various ways to get SQL Server Compact related version information.

To get the SQL Server Compact build version for 3.5 SP2 and 4.0, you can simply use:

var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion;

MSDN docs here.

For SQL Server Compact 4.0 SP1, this returns: 4.0.8876.1.

And for SQL Server Compact 3.5 SP2 CU6, the value would be: 3.5.8088.0

Generate a Windows Phone 8 Local Database DataContext from an existing database

Despite my tooling for this having been available for more than 18 months, I have never posted a dedicated blog post for this subject. This post intends to remedy this.

The Local Database (a SQL Server Compact database accessed via LINQ to SQL) is a data access API available on Windows Phone 7.5 and 8.

The Microsoft documentation (listed here on my blog, together with many other useful Windows Phone Local Database links) always describes a Code First workflow, which makes it cumbersome to reuse existing effort in creating a SQL Server Compact database schema for Windows Mobile or desktop, and  also makes it hard to distribute a database prepopulated with data together with your app. My tooling, which is available with the SQL Server Toolbox Visual Studio add-in, and also available in a simpler form with the new standalone edition of the Toolbox for SQL Server Compact 3.5 (currently in alpha), enables both scenarios. The standalone edition is useful for VS Express users and when you do not wish to install Visual Studio on a PC (it is a single .exe file, so very simple to distribute)

In the following walkthrough, using Visual Studio 2012, I will demonstrate how to use the SQL Server Compact Toolbox to take an existing SQL Server database and use it as an included Windows Phone database in an new (or existing) Windows Phone 8 App. The process to do this requires these steps:

- Create the SQL Server Compact database from the server database and add it to the Windows Phone project
- Generate the LINQ to SQL DataContext and releated classes.
- Use the database from code

I assume you have Visual Studio 2012 Pro or higher with the Windows Phone 8 SDK installed.

Create the SQL Server Compact database

I have created a new Windows Phone Databound App for this sample, and selected Windows Phone OS 8.0 as the target OS.

I then use the Toolbox to create a new SQL Server Compact 3.5 database in the folder where the Phone project resides, (you can determine the folder from by using the “Open Folder in File Explorer” context menu item).

I then click Create, navigate to the project folder, and type PostCodes.sdf, press OK.

Click OK, and a new, empty database will be added to the database list in the Toolbox:

Now we need to connect to the SQL Server database, and script it, then run the script against the new, empty database.

Create and save the database script using the Toolbox menu item above, and then open the SQL Editor against the PostCodes.sdf database file:

Use the Open button in the editor to load the script, and the press the Execute button to run the script.

Now the database contains a PostCode table (the script is available here), which has all Danish postcodes.

The final step is adding the database file to the Phone project. In Solution Explorer, select “Show all files”, and include PostCodes.sdf. In this sample scenario, we would like the database to become writable on the Phone, so include it a “Embedded Resource” – it could also be included as Content, if it was just a read-only database, read more here.

 

Generate the LINQ to SQL DataContext

In order to generate the DataContext based on the database, right click it in the Toolbox, and select “Add Windows Phone DataContext to current project”.
If this menu item is disabled, verify that the database file is in 3.5 format, and that the SQL Server Compact 3.5 SP2 runtime is properly installed, you can check this via the About dialog. “Yes” is required in both places, if that is not the case, repair/re-install.

Let’s walk through the various options on this dialog:

Context name: The name of the generated DataContext class

Namespace: Allows you to specify another namespace for the generated code

Language: You can generate C# or VB code.

Pluralize: If checked, will rename tables (Person => People) etc.

Create a file per table: Normally, just a single file is created

Advanced options:

Add schema version table: If you would like to include the database file a a writeable file, and allow use of the DatabaseSchemaUpdater class in a future app version select this option .

Add rowversion column to all tables: Checking this will ensure that all tables have a rowversion column (formerly timestamp), which enhances performance when doing UPDATE and DELETE (see my blog posts here and here)

Include ConnectionStringBuilder: Will add a LocalDatabaseConnectionStringBuilder class to the project, to help with building connection strings in a strongly typed fashion.

For this sample project, just click OK, and a PostCodesContext.cs file will be added to the project, and we are done.

Use the database from code

Finally, to demonstrate that we are able to include data with the app, alter the DataTemplate in MainPage.xaml as follows:

<DataTemplate>
  <StackPanel Margin="0,0,0,17">
      <TextBlock Text="{Binding Zip}" TextWrapping="Wrap" Style="{StaticResource PhoneTextExtraLargeStyle}"/>
      <TextBlock Text="{Binding City}" TextWrapping="Wrap" Margin="12,-6,12,0" Style="{StaticResource PhoneTextSubtleStyle}"/>
  </StackPanel>
</DataTemplate>

Replace the OnNavigatedTo event handler in MainPage.xaml.cs with the following code:

        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString))
            {
                ctx.CreateIfNotExists();
                ctx.LogDebug = true;
                MainLongListSelector.ItemsSource = ctx.PostCode.ToList();                
            }
        }

This code initialize a new PostCodesContext instance (embraced in “using”, as it is Disposable). The CreateIfNotExists method extracts the PostCodes.sdf embedded resource from the project, and copies it to isolated storage (feel free to look at the code). Setting LogDebug to true will show all SQL statements as text in the Debug window while debugging:

SELECT [t0].[Id], [t0].[Zip], [t0].[City], [t0].[Street], [t0].[Company], [t0].[IsProvince], [t0].[rowguid] AS [Rowguid], [t0].[ts] AS [Ts]
FROM [PostCode] AS [t0]

And finally, calling ToList() will execute the SELECT and return a list of PostCode objects, that is the bound to the ItemsSource property of the LongListSelector.

Result:

Summary

Let us finish with a summary of advantages of this approach:
- Use desktop database tools for data population and schema design
- Saves time doing 1:1 mapping between database tables and DataContext classes
- DataContext class and entity classes are partial and can be extended
- The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windows Phone extension)
- The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage
- The generated DataContext includes the LogDebug property, that allows you to see all SQL statements generated on the device in the debug window
- Optionally split the generated Data Context classes into multiple files
- Optionally add a Version table if you include the table with your app, and want to enable use of the schema updater functionality.
- Optionally add rowversion columns to improve UPDATE and DELETE performance
- Optionally include a ConnectionStringBuilder class to build a valid connection string in a strongly typed way,  using advanced connection string options (see some of my Phone blog posts for candidates)

Hope you find it useful.

UPDATE: Comments have now been closed, please contact me for any issues via the Codeplex issue tracker here: http://sqlcetoolbox.codeplex.com/issues/list

SQL Server Compact Code Snippet of the Week #11 : detect if SQL Server Compact is available

It can sometimes by useful to find out if SQL Server Compact is available on a system, for example in installers and if your application supports several database products. This week’s code snippet allows you to detect if the ADO.NET Provider is available and properly installed (it does not detect if the unmanaged DLL files are present). The code detects both if the AD.NET Provider is present, and if the DbProvider interface is properly registered (either in machine.config or in a local config file)

public bool IsV40Installed()
{
    try
    {
        System.Reflection.Assembly.Load("System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
    }
    catch (System.IO.FileNotFoundException)
    {
        return false;
    }
    try
    {
        var factory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0");
    }
    catch (System.Configuration.ConfigurationException)
    {
        return false;
    }
    catch (System.ArgumentException)
    {
        return false;
    }
    return true;
}

Replace Version=4.0.0.0 with Version=3.5.1.0 and use System.Data.SqlServerCe.3.5 for the DbProvider invariant name for SQL Server Compact 3.5