Thursday, January 27, 2011

Using Linq To SQL with SQL Server Compact 3.5

In my previous post we have discussed how we can use SQL server compact 4.0 with Entity Framework, and turned out its pretty easy, and no challenge what so ever. In this section we are going to see how we can use Linq To SQL with SQL Server Compact 4.0. Lets see if we can find some challenge this time.

We are going to use same NorthWind.sdf database example what we have discussed in previous section. Normal Procedure of using Linq To SQL Data Classes is simple, you got to add a “Linq To SQL Data classes” Template in Project and then drag and drop the data tables from Data connection to the surface of design view of Linq to sql data classes. If you try to do this with SQL Server Compact 4.0 you would have a error like the following screen shot. The main concern is that you can’t add it. And then there is no point of using it.


It appeared you can’t use SQL Server Compact 4.0 with Linq To SQL anymore. So only way to use Microsoft ORM is Entity Framework. But you can use SQL Server Compact 3.5 with Linq To SQL. So lets forget about version 4.0 for now, and try using it with 3.5 sp2. Still you have the same problem, that is no default provider. But this time we have few workarounds.


Alright so we failed to created data classes for linq to sql normally, lets try some other way now in others words every thing have a workaround. There are couple of solutions to our problem but we are going to use one of the most easy one, a tool that will generate the dbml file for us, this is a UI tool build to support sqlmetal.exe.

We will download and install this tool as an external tool in visual studio, I am not going to explain every detail how to install and add it in visual studio as external tool, and assuming you some how know it, After adding it with correct name and location you can get a tool like the bello screen shot.


Select Third option, that is “by Input File (.sdf)” So here you can just browse for the database and then select destinations file in the next step, and click and then browse for dbml, last of of all click ok to generate the file.


Since this GUI tool is just a wrapper for SqlMetal.exe you can also use it via commandline argument, and generate the dbml file.


The Command : SqlMetal /dbml:northwind.dbml northwind.sdf

Copy and pest the generated northwind.dbml file in your project, vs will generate the other two file for you. and then use right connection string when calling the functions.

Northwind northwind = new Northwind(@"Data Source=|DataDirectory|\Northwind.sdf");
List<Employees> employeeses = northwind.Employees.ToList();

On top we have put down two line of sample code for demo. That’s it. Well till next post , best of luck and happy programming.