NullifyNetwork

The blog and home page of Simon Soanes
Skip to content
[ Log On ]

[CodeBlog] Using LINQ with SQL Compact Edition - by simon at Sun, 20 Jan 2008 14:25:35 GMT

Create a .NET 3.5 Console, Windows Forms or WPF project, add an SQL Compact Edition local database, I left the default name of Database1.sdf for the example. Just right click and open it from solution explorer and add some tables. I added two tables:  Person and Pet, with a relationship between the two (right click the child table, choose properties to get the add relationship option).

Next you want to add a command to visual studios tools menu, using Tools, External Tools… This command will allow you to generate a Linq To SQL DBML file which describes the contents of the database. Once we have that Linq is perfectly compatible with SQL Compact Edition – but the Visual Studio design tools aren’t so we need to do this manually.
Title:  Make &Linq classes for Database
Command:  C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe
Arguments: $(ItemPath) /dbml:$(ItemFileName).dbml
Initial Directory: $(ItemDir)
Now this tool will let you generate your DBML, select your Database1.sdf file then choose Tools, “Make Linq classes for Database”. It should pop up and seemingly do something in a command prompt.
Now right click your project, choose Add existing item and then change the filetype to either Data Files or All files. You should see there’s a new file called Database1.dbml – select this file and add it to your solution.
Bingo! It is now available to be edited in Linq – you can double click this dbml file and you’ll get the designer up – it should show your classes (NOTE: At this point I should add that relationships weren’t automatically generated for compact edition).
Now it’s time to use Linq to actually connect and query/save some data. This is where Linq takes a lot of the hassle out of building software that talks to databases, it simply works.
static void Main(string[] args)
{
       //Connect to the database itself.
       using (Database1 db = new Database1("Database1.sdf"))
       {
              //This is easy because we used SqlMetal to generate
              //the dbml targetting an SQL Compact edition database.
              //Normally you'd have to specify a full connection string.
 
              //obviously remove these two lines if you don't want to start with an empty database each time.
              db.Person.DeleteAllOnSubmit(db.Person);
              db.SubmitChanges();
 
              //Create a couple of Person entities
              Person simon = new Person();
              simon.Name = "Simon";
              simon.EMail = "me@myhost";
 
              //and a cat for me
              Pet cat = new Pet();
              cat.Name = "Fluffy";
              simon.Pets.Add(cat);
 
              db.Person.InsertOnSubmit(simon);
 
              Person fred = new Person();
              fred.Name = "Fred";
              fred.EMail = "them@myhost";
              db.Person.InsertOnSubmit(fred);
 
              //now actually add them to the database
              db.SubmitChanges();
 
              //Select the names of some people                                   
              var names = from Person p in db.Person
                                  select p.Name;
 
              //Print those names
              foreach (string name in names)
              {
                     Console.WriteLine(name);
              }
 
              //but you can also get back the entities instead of just names
              //this is handy if you require more than just one item for a particular person
              var people = from Person p in db.Person
                                   select p;
 
              foreach (Person person in people)
              {
                     Console.WriteLine(String.Format("{0} has an e-mail adddress of {1}", person.Name, person.EMail));
              }
 
              //but what if you want multiple items not all in one person?
              //well you can use anonymous classes
              var peopleAndPets = from Person p in db.Person
                                                orderby p.Pets.Count descending
                                                select new { p.Name, p.Pets.Count };
 
              //this is where the var keyword becomes essential. peopleAndPets
              //is not of a type that can be described before compilation.
              foreach (var quantity in peopleAndPets)
              {
                     Console.WriteLine(String.Format("{0} has {1} pets", quantity.Name, quantity.Count.ToString()));
              }
       }
 
       Console.WriteLine("The example is over!");
       Console.ReadKey();
}
Permalink  1 Comments