LINQ to SQL...compact!

You are all undoubtedly aware of LINQ to SQL, its fair to say its had a large amount of publicity and coverage in the .net circle.  VS 2008 makes getting going with LINQ to SQL very easy but that is not the case with SQL Compact (yet!).  However its not difficult if you have heard of a wonderful little app called SqlMetal, this little beauty will generate the .dbml files for you...happy days :)

I have a small SQL Compact database to use for this demo, you can see it here in VS 2008 server explorer...

image

Run VS 2008 Command Prompt, navigate to the location of your .sdf file (you can of course run SqlMetal using the full file path to the .sdf but I just prefer the prior) and type...

>SqlMetal SqlCeFileName.sdf /dbml:LinqClassName.dbml

That will create the dbml file in the same directory as the .sdf file, now add it to your project in the normal way.

image

You can open the .dbml file in the same way and you will get the VS 2008 designer because its doesn't care what provider you are using (SQL CE) that will be sorted at runtime - right now you are just looking at a .dbml file.

image

 

You can now use LINQ in the same way as you would for SQL Server...

Tageditor te = new Tageditor(TagEditor.Properties.Settings.Default.TagEditorConnectionString);

Note: I am passing the location to the SQL Compact database file to the constructor of the LINQ class ( "Data Source=|DataDirectory|\\Data\\TagEditor.sdf" ), as I said before, I don't need to specify anywhere that the provider is SqlCE, that is inferred at runtime! :)

You can now use LINQ in the same way to work with your database - I wont go into LINQ here, there are plenty of resource around to view...here are a couple I found useful.

LINQ

ScottGu's 9 part series on LINQ - all you need to get going really.

 

SQL Compact Edition

MS SqlCe Home

SqlCe Blog

SqlCe FAQ by Steve Lasker

 

Technorati Tags: , , ,

Print | posted on Tuesday, November 13, 2007 10:26 AM

Feedback

# re: LINQ to SQL...compact!

Left by Daniel Moth at 11/13/2007 11:03 AM
Gravatar It is probably worth pointing out that this only works for SQL CE on the desktop. I.e. LINQ to SQL is not supported by teh Compact Framework and hence this would not work for SQL CE on Windows Mobile etc...

Wouldn't want to get anyone's hopes up ;-)

# re: LINQ to SQL...compact!

Left by Steve Clements at 11/13/2007 11:08 AM
Gravatar Thanks Daniel, good point well made!

Consider my hopes truly slashed :(

# re: LINQ to SQL...compact!

Left by Eddie at 11/22/2007 4:38 AM
Gravatar I can't manually create dbml files for my SqlServerCE database like the exact way you're doing it. I keep getting "Provider 'System.Data.SqlServerCe.3.5' not installed". Note, I am doing this under the regular command prompt window because I am runing VC#2008 Express Edition as I do not own a copy of the retail stanard version. I just upgraded to VC#2008 and it comes with the Compact 3.5 provider already. I think the problem is because I need to run the the command under VS 2008 command prompt so it'll look into the correct visual studio folder where it'll find the Compact 2.5 Provider but I don't know how I can do this with regular command prompt. I tried looking for VS 2008 command prompt as a seperate single download but I don't think it's available. If you can help me, I would greatly appreaciate it. Thanks.

# re: LINQ to SQL...compact!

Left by Francisco Ramos at 9/16/2008 6:35 PM
Gravatar Eddie and others with the same problem. You have to build for x86, not "Any CPU".
More information is here:

http://msmvps.com/blogs/theproblemsolver/archive/2008/07/28/sql-compact-3-5-on-a-64-bit-windows.aspx

# re: LINQ to SQL...compact!

Left by jasonlaw at 12/17/2008 4:36 AM
Gravatar Can we use dbml built from SQL Server in SQL Compact?

# re: LINQ to SQL...compact!

Left by Ron Nash at 2/2/2009 2:19 PM
Gravatar Hi, I have created a test app using SQLCE and Linq and all works fine. I understand that by coping the required DLL' from the CE directory I could deploy the App with the need to install SQL compact on that machine. The database is said to be local to the App itself. I have the following error when running the App:

system.data.sqlserverCE3.5 not installed

# re: LINQ to SQL...compact!

Left by Simon T at 2/16/2010 3:09 AM
Gravatar Great - thanks! :-D

# re: LINQ to SQL...compact!

Left by Naresh at 1/24/2011 9:57 PM
Gravatar I am trying to generate a dbml file but i am getting error as

Error : The specified password does not match the database password. [ Data Sour
ce = E:\Projects\smc thanigai\Afvc\AFVC_24_1_2011\Module1\Module1\Database\AFVC_
SCT.sdf ]

I dont know wats the problem in this,please help me out guys

# re: LINQ to SQL...compact!

Left by Alexis González at 12/17/2011 4:00 AM
Gravatar I'm so greatful. Thank you so much. You giveme the info i need to complete my app.

Your comment:





 
 

Copyright © Steve Clements

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski