LINQ to SQL...compact!

November 13, 2007 · 2 min read

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

This article is part of the GWB Archives. Original Author: Steve Clements

New on Geeks with Blogs

  • We Won The One Award I Actually Care About

    Full Scale made the Inc. 5000 for the fifth year straight, the 12th listing across my three companies. Here is why the one award you cannot buy is worth stopping for.

  • Your Customers Build the Features Now

    I let a tool I liked sit dead for a year rather than build the features I wanted. An MCP server meant I never had to, and your customers can do the same to your product.

  • Get the Size of a Directory in Linux the Easy Way

    du -sh for the quick answer, ncdu for the cleanup, df for the disk itself: every command for checking directory size in Linux, plus why du and df never agree.

  • Vim Search and Replace: The Ultimate Guide

    One :%s command replaces every match in a file before a find dialog would even open. The Vim substitute patterns worth the muscle memory: flags, ranges, capture groups, and multi-file edits.