C# 2008 Programmer's Reference

Now save the DataClasses1.dbml file, and Visual Studio 2008 will create the relevant classes to represent the tables and relationships that you just modeled. For every LINQ to SQL file you added to your solution, a DataContext class is generated. You can view this using the Class Viewer (View→Class View; see Figure 14-20). In this case, the name of the DataContext class is DataClasses1DataContext. The name of this class is based on the name of the .dbml file; if you named the .dbml file Pubs, this class is named PubsDataContext.

Figure 14-20 

Querying

With the database modeled using the LINQ to SQL designer, it's time to write some code to query the database. First, create an instance of the DataClasses1DataContext class:

DataClasses1DataContext database = new DataClasses1DataContext();

To retrieve all the authors living in CA, use the following code:

var authors = from a in database.authors
 where (a.state == 'CA')
 select new {
  Name = a.au_fname + ' ' + a.au_lname
 };
foreach (var a in authors)
 Console.WriteLine(a.Name);

To retrieve all the titles in the titles table and at the same time print out the publisher name of each title, you first retrieve all the titles from the titles table:

var titles = from t in database.titles
 select t;

And then you retrieve each title's associated publisher:

foreach (var t in titles) {
 Console.Write('{0} ', t.title1);
 var publisher =
  from p in database.publishers
  where p.pub_id == t.pub_id
  select p;
 if (publisher.Count() < 0)
  Console.WriteLine('({0})', publisher.First().pub_name);
}

The output looks something like this:

Cooking with Computers: Surreptitious Balance Sheets (Algodata Infosystems)
You Can Combat Computer Stress! (New Moon Books)
How to Motivate Your Employees Straight Talk About Computers (Algodata Infosystems)
Silicon Valley Gastronomic Treats (Binnet & Hardley)
The Gourmet Microwave (Binnet & Hardley)
The Psychology of Computer Cooking (Binnet & Hardley)
But Is It User Friendly? (Algodata Infosystems)
Secrets of Silicon Valley (Algodata Infosystems)
Net Etiquette (Algodata Infosystems)
Computer Phobic AND Non-Phobic Individuals: Behavior Variations (Binnet & Hardley)
Is Anger the Enemy? (New Moon Books)
Life Without Fear (New Moon Books)
Prolonged Data Deprivation: Four Case Studies (New Moon Books)
Emotional Security: A New Algorithm (New Moon Books)
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (Binnet & Hardley)
Fifty Years in Buckingham Palace Kitchens (Binnet & Hardley)
Sushi, Anyone? (Binnet & Hardley)

Inserting New Rows

To insert a row into a table, use the InsertOnSubmit() method. For example, the following code inserts a new author into the authors table:

DataClasses1DataContext database = new DataClasses1DataContext();
author a = new author() {
 au_id = '789-12-3456',
 au_fname = 'James',
 au_lname = 'Bond',
 phone = '987654321'
};
//---record is saved to object model---
database.authors.InsertOnSubmit(a);

Note that the InsertOnSubmit() method only affects the object model; it does not save the changes back to the database. To save the changes back to the database, you need to use the SubmitChanges() method:

//---send changes to database---
database.SubmitChanges();

What happens when you need to insert a new book title from a new author? As you saw earlier, the titles table is related to the titleauthors via the title_id field, while the authors table is related to the titleauthors table via the author_id field. Therefore, if you insert a new row into the titles table, you need to insert a new row into the authors and titleauthors tables as well.

To do so, you first create a new author and title row:

DataClasses1DataContext database = new DataClasses1DataContext();
author a = new author() {
 au_id = '123-45-6789',
 au_fname = 'Wei-Meng',
 au_lname = 'Lee',
 phone = '123456789'
};
title t = new title() {
 title_id = 'BU5555',
 title1 = 'How to Motivate Your Employees',
 pubdate = System.DateTime.Now,
 type = 'business'