Wei-Meng Lee - C# 2008 Programmer's Reference

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)

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 titlestable is related to the titleauthorsvia the title_idfield, while the authorstable is related to the titleauthorstable via the author_idfield. Therefore, if you insert a new row into the titles table, you need to insert a new row into the authorsand titleauthorstables as well.

To do so, you first create a new authorand titlerow:

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"

};

Then, add a new titleauthorrow by associating its authorand titleproperties with the new titleand authorrow you just created:

titleauthor ta = new titleauthor() {

author = a,

title = t

};

Finally, save the changes to the object model and submit the changes to the database:

//---record is saved to object model---

database.titleauthors.InsertOnSubmit(ta);

//---send changes to database---

database.SubmitChanges();

Notice that you do not need to worry about indicating the title_idand author_idfields in the titleauthorstable; LINQ to SQL does those for you automatically.

Updating rows using LINQ to SQL is straightforward — you retrieve the record you need to modify:

DataClasses1DataContext database = new DataClasses1DataContext();

title bookTitle = (from t in database.titles

where (t.title_id == "BU5555")

select t).Single();

The Single()method returns the only element of a sequence, and throws an exception if there is not exactly one element in the sequence.

Modify the field you want to change:

bookTitle.title1 = "How to Motivate Your Staff";

And submit the changes using the SubmitChanges()method:

database.SubmitChanges();

The query can alternatively be written using the method syntax, like this:

title bookTitle =

database.titles.Single(t => t.title_id == "BU5555");

To delete a row, you first retrieve the row to delete:

DataClasses1DataContext database =

new DataClasses1DataContext(); //---find author ---

var author = from a in database.authors

where a.au_id == "789-12-3456"

select a;

Then, locate the row to delete by using the First()method, and finally call the DeleteOnSubmit()method to delete the row:

if (author.Count() > 0) {

database.authors.DeleteOnSubmit(author.First());

database.SubmitChanges();

}

The First()method returns the first element of a sequence.

If you have multiple rows to delete, you need to delete each row individually, like this:

//---find author---

var authors = from a in database.authors

where a.au_id == "111-11-1111" ||

a.au_id == "222-22-1111"

select a;

foreach (author a in authors) {

database.authors.DeleteOnSubmit(a);

}

database.SubmitChanges();

So far the deletion works only if the author to be deleted has no related rows in the titleauthorsand titlestables. If the author has associated rows in the titleauthorsand titlestables, these examples cause an exception to be thrown because the deletions violate the referential integrity of the database (see Figure 14-21).