Very Quick Guide To DLinq: Part 2

The Official Programmer's Heaven Blog

The blog where the Programmer's Heaven team post stuff.

RSS Feed

Author

pheaven

Often knowledgable, sometimes wise, occasionally funny. The Programmer's Heaven blog team post about a whole range of topics, from practical advice on concurrency control to introductions to lesser known concepts such as functional programming. Don't forget to comment on the posts and let them know what you think, like and hate!

Very Quick Guide To DLinq: Part 2

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.