![[Valid RSS]](/Graphics/valid-rss.png){kind=small}
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)
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
Can you explain please?