In this part I'm going to take a look at inserts, updates and deletes using DLinq.
The Overall Model
First, to make changes to a database, you instantiate the data context object, just as we did before doing selections in the previous part.
var DB = new ResourcesDB();
You then do the required changes; we'll look at this part in just a moment. However, the inserts, updates and deletions are not performed immediately. They do not take place until you explicitly submit the changes:
DB.SubmitChanges();
This means that you can make many changes, then send them to the database all at once.
Inserting Data Into A Table
To make a single new entry to a database table, use the InsertOnSubmit method on the Table object. This takes a single parameter: an instance of the object that represents a row in the the table. You can use the object initializer syntax to write this very neatly.
DB.BookResources.InsertOnSubmit(new BookResource()
{
Title = "How To Make Fondue",
Author = "Mr S. Cheese"
});
Of course, you can instantiate and set up that object elsewhere. You may end up with many object to insert, in which case you can build a List<BookResource> (or anything else that implements the IEnumerable<T> interface) and then use the InsertAllOnSubmit method.
DB.BookResources.InsertAllOnSubmit(NewBooksThisWeek);
Remember that the inserts will not take place until you call the SubmitChanges method on the data context (you don't call it on the table itself).
Updating Data
To update data, first you need to write a Linq query to get the data that is to be updated. If you want to update just one item, you can use .First() on the query result to just get that item.
var ToEdit = (from Book in DB.BookResources
where Book.BookID == 42
select Book).First();
ToEdit.Author = "Mr Swiss Cheese";
If you have many items that you want to update, select all of them and then write a foreach loop.
var ToEdit = from Book in DB.BookResources
where Book.Author == "Mr S Cheese"
select Book;
foreach (var Book in ToEdit)
Book.Author = "Mr Swiss Cheese";
And once again, don't forget to call SubmitChanges when you've done your changes, else they won't be saved back to the database.
Deleting Data
Deletions follow a similar approach to updates. However, rather than changing the object(s) you select from the database you pass them as a parameter to the table's DeleteOnSubmit for one item:
DB.BookResources.DeleteOnSubmit(
(from Book in DB.BookResources
where Book.Author == "Mr S Cheese"
select Book).First());
Or DeleteAllOnSubmit for many items:
DB.BookResources.DeleteAllOnSubmit(
from Book in DB.BookResources
where Book.Author == "Mr S Cheese"
select Book);
You may assign the selection to a temporary variable and then pass that to delete as you wish. Call SubmitChanges to actually delete these items in the database.
Next Time
Next time we will look at how to handle various special queries that are less obvious how to express using Linq, including the SQL LIKE and IN operators.
