Книга: C# 2008 Programmer

Deleting Rows

Deleting Rows

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();
}

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 titleauthors and titles tables. If the author has associated rows in the titleauthors and titles tables, these examples cause an exception to be thrown because the deletions violate the referential integrity of the database (see Figure 14-21).

Figure 14-21

Because LINQ to SQL does not support cascade-delete operations, you need to make sure that rows in related tables are also deleted when you delete a row. The following code example shows how to delete a title from the titles and titleauthors tables:

DataClasses1DataContext database = new DataClasses1DataContext(); string titleid_to_remove = "BU5555";

//---find all associated row in Titles table---
var title = from t in database.titles
where t.title_id == titleid_to_remove select t;
//---delete the row in the Titles table---
foreach (var t in title)
 database.titles.DeleteOnSubmit(t);
//---find all associated row in TitleAuthors table---
var titleauthor = from ta in database.titleauthors
where ta.title_id == titleid_to_remove select ta;
//---delete the row in the TitleAuthors table---
foreach (var ta in titleauthor)
 database.titleauthors.DeleteOnSubmit(ta);
//---submit changes to database---
database.SubmitChanges();

Оглавление книги