SQL Server Compact 4 desktop app with simple Private Deployment and LINQ to SQL

In this post I will describe a simplified approach to SQL Server Compact Private Deployment, for an overview blog post on Private Deployment with SQL Server Compact, see my blog post here.

By forcing your app to run using x86 always (Prefer 32-bit), which is the new default Platform target option  for apps targeting .NET Framework 4.5, deployment of SQL Server Compact with you app becomes simpler, but must follow different guidelines from what I have previously blogged about. (The same approach will also work with apps targeting .NET 4.0, just set the Platform target to x86 in the location shown below. And the same approach will also work with the SQL Server Compact 3.5 DLL files.)

To read more about the new default Platform target option introduced in .NET 4.5, see the MSDN documentation here, and the blog post here.

In addition, I will demonstrate how to use LINQ to SQL with SQL Server Compact 4.0, a low overhead, fast performing ORM.

For the sake of simplicity, and in order to focus attention on the private deployment aspects, I will demonstrate with a console application, but the same approach will also work for WinForms and WPF applications.

Before you get started, make sure you have the following installed:

1: Visual Studio 2010/2012/2013 Pro or higher 

2: SQL Server Compact Toolbox add-in (Install via Tools/Extensions in VS)

3: An existing SQL Server Compact database file, I will use Chinook, which you can download from here. 

4: The SQL Server Compact 4.0 SP1 runtime

(You could also use the free Visual Studio 2010/2012/2013 for Windows Desktop with the standalone SQL Server Compact Toolbox for 4.0, which also supports LINQ to SQL code generation)

With that in place, let us open Visual Studio and get started:

Create new console application

Go to File, New Project, and create a new Windows Console application. Make sure to set the target platform to 4.0 or newer.

Include the SQL Server Compact binaries in your project

Now include the SQL Server Compact 4.0 binaries and ADO.NET Provider as content in your app. Copy C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\System.Data.SqlServerCe.dll to your project folder, and then copy all files and folders in C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\x86 also to your project folder.

In the Solution Explorer, select Show All Files, and include the new folder and the seven files just copied in the Project:

Now mark all the copied files (also the files in the Microsoft.VC9.CRT folder) and mark them as Content, Copy Always:

Finally, add a Reference to the System.Data.SqlServerCe.dll file in your project folder:

(Make sure to check the file location and the Version, should be 4.0.0.1)

Add your database file to the project

Make sure it is also Content, Copy Always – we use the”Database First” workflow here.

Generate the LINQ to SQL DataContext

Next,we will generate a LINQ to SQL DataContext class and related Table classes based on the database, so connect to the database in SQL Server Compact Toolbox, using the Add SQL Server Compact 4.0 Connection menu item:

Then right click the database and select “Add LINQ to SQL DataContext to current project”:

(I am just using ChinookContext as Context name)

Click OK, and a DataContext class file will be added to your project, and the required reference to System.Data.Linq will be added to the project.

Now let us add some test code to the Main method in order to verify that everything works so far, so the Program.cs code looks like this:

using System;
using System.Data.SqlServerCe;

namespace LinqToSqlCePrivateDeploy
{
    class Program
    {
        private const string 
            dbFileName = "Chinook_SqlServerCompact_AutoIncrementPKs.sdf";
        
        private static string dbConnectionString = 
            string.Format("Data Source=|DataDirectory|{0};Max Database Size=4091", dbFileName);
        static void Main(string[] args)
        {
            using (var connection = 
                new SqlCeConnection(dbConnectionString))
            {
                using (var context = new ChinookContext(connection))
                {
                    //To log SQL statements, use:
                    //context.Log = Console.Out;
                    foreach (var album in context.Album)
                    {
                        Console.WriteLine(album.Artist.Name);
                        Console.WriteLine(album.Title);
                    }
                }
            }
            Console.Read();
        }
    }
}

We can now access the database via the generated object model, and do not have to type SQL, but can use LINQ to query the database. In addition, we can update the database (INSERT, UPDATE, DELETE) via methods on the DataContext.

Notice that the DataContext must be constructed with a SqlCeConnection object, in order for LINQ to SQL to work with SQL Server Compact 4.0.

Deploy the database file

The final step will be done to ensure that the database file will be located in a writeable location on the users machine when deployed/installed. We will simply do this in code in order to not depend on any install actions and issues. In addition, we can do this without storing any connection strings in app.config, making the app more self-contained. We will use the same approach that I have already used in my blog post here, which takes advantage of the DataDirectory connection string macro.

private static void CreateIfNotExists(string fileName)
{
    string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
    // Set the data directory to the users %AppData% folder            
    // So the database file will be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\            
    AppDomain.CurrentDomain.SetData("DataDirectory", path);
            
    // Enure that the database file is present
    if (!System.IO.File.Exists(System.IO.Path.Combine(path, fileName)))
    {
        //Get path to our .exe, which also has a copy of the database file
        var exePath = System.IO.Path.GetDirectoryName(
            new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);
        //Copy the file from the .exe location to the %AppData% folder
        System.IO.File.Copy(
            System.IO.Path.Combine(exePath, fileName), 
            System.IO.Path.Combine(path, fileName));
    }
}

Remember to add a call to CreateIfNotExists as the first line in the Main method:

static void Main(string[] args)
{
    CreateIfNotExists(dbFileName);

You can now use ClickOnce, XCopy or an Installer to deploy your app, with no other requirements than the target .NET Framework version.

What we have achieved:

- Simple, self contained deployment of a single user desktop app of any type to any .NET 4.0 or higher platform (not ARM, though)

- No need for special incantations in app.config

- RAD (Rapid App Development) “Database First” access to a well-performing, well-documented and simple ORM.

You can download the complete solution from here; http://sdrv.ms/179QBaa