How to use Linq and SQL Compact 3.5?

Peter Fenyvesi, Damien Noonan, Tom McArdle

January 2008

Applies to:

·        Visual Studio 2008 Code Name "Orcas"

·        .Net Framework 3.5

·        SQL Compact 3.5

Summary: This walkthrough gives a quick overview on how to use .NET Language-Integrated Query (LINQ) to retrive and manipulate realtional data. It also demonstrates some of the new features of  SQL Compact 3.5 which is used as the relational engine in this example.
We were really excited when we’ve heard about Linq because it can fill the gap that SQL Compact’s lack of stored procedure support exposes. And of course many more (11 printed pages)

SQL Compact

Linq

Project setup

Database access

Refinements

And this is where the fun begins

Summary

SQL Compact

SQL Compact Edition (SQL CE)is a slimmed down version of the SQL server family. It can run on a low-end PC or even on a PDA. The database engine’s footprint is really tiny (2Mb) and though provides only very basic sql functionality, there were important improvements here as well in the 3.5 release. This version is shipped with VS2008, but available as a separate, free download as well. Now for example we can use subqueries or we can define foreign key constraints on a graphical user interface and do not have to write sql statements for this type of task. There are still a lot of limitations there compared e.g. to SQLExpress, but the target here is quite different. I think of SQL CE as a very reliable, tiny, relational alternative to text files and simple spreadsheets. And indeed, in the real word we used SQL CE to replace modules of older applications where simple text files were used. But even if SQL CE provides much more functionality then a simple text file, sooner or later the lack of stored procedures would cause headaches.

And this is where Linq comes into play. Up until now, working with SQL CE meant to work with nasty embedded SQL statements which always made me uncomfortable. Using SP’s were the preferred way of doing things, so we were a bit reluctant to go back to the embedded SQL spaghetti. But up until now we had no choice.

With Linq the things are quite different.

Linq

Linq stands for “Language Integrated Query”. This means that you can write SQL-ish C# (or VB) code, to query and modify data. “Data” basically means any kind of data – not limited to databases, but including objects, xml, ect. To keep focus, I just want to give a brief introduction on “Linq to SQL” and will not cover the rest in this article (Linq to XML, Linq to object).

So in our walk-through article we’ll create a tiny desktop application that will store some records about the department’s MSDN disks in a SQL Compact database. The app will use Linq to SQL to access and modify the data in the database. This small internal project meant to address the problem that the management of our huge amount of heavily used MSDN disks expose (what exactly do we have, who borrowed, what, when, when is that due back, etc). Some kind of DVD inventory.

Project setup

First of all let’s create a new Windows Forms application called MSDNDB. Let’s add a local database (SQL CE 3.5) to the project:

When this is added, the “Data Source Configuration Wizard” appears, as Visual Studio assumes, we want to add a dataset as well to the project. Since we do not, just cancel the dialog.

Instead, open the new database file in server explorer and let’s add the required tables to it. We’ll need a table to store some details about the MSDN DVD’s…

…and an other one for the categories.

Once it’s done, we can define the relationships between the two tables. As I mentioned before, now foreign-key constraints can be defined on a GUI, which is new in VS2008.

To complete the initial database we can add a couple of categories to the corresponding table.

Database access

Well, Linq. At first people would think that by adding a dbml file to the project and dragging’n’dropping the required tables from server explorer would work. And it does with SQLExpress and with the bigger editions, but wouldn’t with SQL CE. If you have a try, you’ll get a “The selected object(s) use an unsupported data provider.” error as you can see below.

But fortunately Microsoft provides a nice little tool called SQLMetal that can help to solve the problem. This command-line utility can be used to generate the dbml file we need from any sort of database, including the SQL Compact edition. This could generate strait the CS codefile as well, but once we’ve got the dbml, that can be added to the project and Visual Studio can handle the rest.

So just execute

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\sqlmetal" /conn:"Data Source=C:\Work\MS\MSDNDB\MSDNDB.sdf" /dbml:MSDNDB.dbml /provider:SQLCompact

from the command prompt and add the dbml to the project. You’ll see immediately as the MSDNDB.designer.cs appears which is the generated CS code for the database tables. This contains the mapping between the .net objects and the SQL tables, between the object properties and table columns and serves as the source for IntelliSense to show column names, relations, etc when writing Linq expressions.

Here you have to change the Inheritance Modifier to none. Without this the generated class would be abstract, so we would need to inherit our own from that, but in this simple scenario this is not necessary. It is also advisable to change the name property, which is the name of the DataContext class. By default it contains the full path to the sdf file, like: “C__Work_MS_MSDNDB_MSDNDB_Sdf”.

You might have noticed by now the way the new ctrl+tab works in Visual Studio 2008. Now instead of purely showing the names of the open files that are in various windows, you can even see the window’s content as a small screenshot which makes navigation between the open windows much easier.

At this stage we’re ready to drop a DataGridView onto our form, anchor it to the 4 sides of the form and write a few lines of code to make it a working application!

In the code file create a member variable with the type of our own DataContext. This will be responsible for all the interaction with the database, including opening the connection to it, executing commands, etc.

/// <summary>

/// the Linq.DataContext instance which helps to track changes

/// we make on records and generates the

/// correct SQL statements to persist those changes into the database.

/// </summary>

private MSDNDB db;

Since DataContext works with database, it implements IDisposable to clean up all the resources at the end of it’s lifecycle. So we should respect this and should call Dispose() at the right time. The easiest way to do this is to move the form’s Dispose function from the MediaList.Designer.cs file into the MediaList.cs (so Visual Studio will never overwrite our changes) and add the two highlighted lines to it.

/// <summary>

/// Clean up any resources being used.

/// </summary>

/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>

protected override void Dispose(bool disposing)

{

      if (disposing && (components != null))

      {

            components.Dispose();

      }

      if (disposing)

            db.Dispose();

      base.Dispose(disposing);

}

To just grab all the data from the tblMSDN table and display that in a grid is really simple. Add the 3 lines below to the MediaList_Load method:

//Specify the connection string for the DataContext's constructor.

//The "Data Source" is the path to the sdf file, which in this

//case should be the in the same folder as the exe.

db = new MSDNDB("Data Source=MSDNDB.sdf");

//At first just grab all the data from the tblMSDN table.

//This is probably the simplest Linq expression.

var q = db.TblMSDN.AsQueryable<TblMSDN>();

//Bind it to the grid

this.dataGridView1.DataSource = q;

If you even call SubmitChanges after the row was changed and validated, you should have a working application that supports adding, updating and deleting records.

/// <summary>

/// When a record was changed, let's call the SubmitChanges

/// method and let Linq to generate and execute the SQL statement

/// required to persist the data into the database

/// </summary>

private void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)

{

      db.SubmitChanges();

}

So try to build and run it!

Well, if you’re on a 64 bit OS, you’ll run into an issue. Namely “Provider ‘System.Data.SqlServer.Ce.3.5’ not installed”.

This can be a bit misleading, but basically this is because SQLCE is not supported on 64 bit OS. You can easily overcome this by targeting only the x86 platform. The form is available from the solution properties or from the toolbar.

So at this stage we’ve got a working application which can create, read, update and delete database records!

Refinements

To make our little application a bit more user-friendly, let’s define the columns and the column types in the grid first.

Here we can use Linq to SQL again to populate the “category” dropdown list. To do this extend the MediaList_Load method with:

//Populate the dropdown list

DataGridViewComboBoxColumn col =

      (DataGridViewComboBoxColumn)(this.dataGridView1.Columns["CategoryId"]);

var qCat = db.TblCategory.AsQueryable();

col.DataSource = qCat;

col.ValueMember = "CategoryId";

col.DisplayMember = "Category";

And since now we specified the columns manually, we should disable the automatic generation of columns, like this:

//Bind it to the grid

this.dataGridView1.AutoGenerateColumns = false;

this.dataGridView1.DataSource = q;

And this is where the fun begins

Now we’ve got a working skeleton application. It’s time to add more stuffs to it and explore the capabilities of Linq!

For example I’ve added a textBoxBarCode and a buttonRefresh controls to the form to add optional filtering capability. I also added the following method which is called from both the buttonRefresh_Click and from the MediaList_Load event handlers.

private void PopulateGridView()

{

      //Select all the records

      var q = from m in db.TblMSDN

                  select m;

      //add a filter by BarCode, if this criteria was specified.

      if (textBoxBarCode.Text != "")

            q = from m in q

                  where m.BarCode == textBoxBarCode.Text

                  select m;

      //add ordering to the query

      q = from m in q

            orderby m.MSDNId

            select m;

      //Bind it to the grid

      this.dataGridView1.AutoGenerateColumns = false;

      this.dataGridView1.DataSource = q;

}

I’m sure you can add more filters and other features form here by yourself.

So with Linq you can select, filter, order, shape the data the same way as you would do in a stored procedure, but with the added benefit of the intellisense and compile-time check support. So in a where clause for example, you can just select the available fields from a list and you do not have to type in the column names. Also, the fields are strongly typed and the expressions are validated at compile time – just to name a few benefits.

Summary

In this little walk-trough we’ve tried to demonstrate how Linq and SQL Compact can be used from Visual Studio 2008 to build database-driven applications. Obviously this article only scratched the surface and just tried to wet your appetite. You can find a lot of other Linq examples and more specific documentations in MSDN.