Monday, October 7, 2013

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.)

image

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.

image

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:

image

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

image

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

image

(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:

image

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

image

(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

17 comments:

Unknown said...

can i Redistribute the files in the Microsoft.VC9.CRT folder (MSVCR90.dll and manifest)??
if yes where can i find the license to make sure or the redist.txt?

thanks

ErikEJ said...

Nick: Yes, some vague references in the C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0 folder - notice that the C++ files is present, if .NET 3.5 SP1 is installed

Unknown said...

ok, but i want to be sure...
can i find somewhere the license for these two files (MSVCR90.dll and manifest) as i did for
sqlcese40.dll
sqlceqp40.dll
sqlceoledb40.dll
sqlceca40.dll
sqlceme40.dll
sqlcecompact40.dll
sqlceer40en.dll
System.Data.SqlServerCe.dll
System.Data.SqlServerCe.Entity.dll

else u have to find an other method to make my app working with sql compact 4 (private deployment) :)

any other method didnt work to me...

thanks again

ErikEJ said...

You can distribute the files, that is why they are present in the Private folder, not sure I can help you any further, or what your issue is for that matter.

Unknown said...

i'm using vs2012 and .net framework 4
so i dont have the checkbox (Prefer 32-bit. in order to run my app under x64 and x86 i have to set Platform target to x86? or it is better to follow your different guidelines from previous posts?

thanks

ErikEJ said...

Nick: Just force x86

Unknown said...

Hi Erik,

I'm trying to find a clear answer if i can deploy multiple applications on different machines(win desktops) and share the save server compact file via network.
What do you know about this topic?

Thank you,
Dragos.

ErikEJ said...

Dragos: The clear answer is NO, use SQL Express for this scenario

Unknown said...

Thanks you!

That was my hunch also based on what I've read.

Dragos.

Unknown said...

Thanks for your posts Erik, we are moving an app to SQL CE 4.0 SP1 but use Innosetup for our installs. Tried testing a private deployment and just cannot get it to work. Previously (with SQL CE 3.5) we just had the install run the SSCERuntime_x64-ENU.msi in quiet mode and all worked perfectly. I have searched the internet for two days now and cannot find an MSI for 4.0 SP1, do you know where I can get one?? I found the CTP version but not the final version.

ErikEJ said...

Swiftbirder: 4,0 sp1 is here. http://www.microsoft.com/en-us/download/details.aspx?id=30709 but can I ask why private deploy is not working? Do you use EF or Ado.net? What errors do you get causing 4,0 to fail? And why do you need 4,0? What new feature do you need in 4,0?

Tom said...

Hi Erick, great article on SQL CE and with working example. Have a question on how to deploy schema changes after initial deployment using ClickOnce? Thanks.

ErikEJ said...

Unknown: there are a number of tools to do this, but basically it is question of tracking your schema version, and comparing with the one required by your app, then running the required sql statements to get in sync.

Unknown said...

Hi Erik
broken link at line
4: The SQL Server Compact 4.0 SP1 runtime

ErikEJ said...

Paolo: Link Works fine on my PC

Mallikarjun Kalkere said...

Hi Erik,

can you please give some pointers/links on how to track the schema version changes and apply the new schema and data changes?

ErikEJ said...

mallikarjun: Suggest you post a question on my Github repo or on stackoverflow