We have already written about using Linq, but didn't cover DLinq. This post takes a quick look at getting started with DLinq, which allows you to write Linq queries against databases. Once you're up to speed, you can take a database and be writing Linq queries against it in under five minutes!

In future parts in this series of posts, we'll look at doing database updates, writing more complex queries and debugging. In this one, we'll just look at doing some simple queries.

Creating The Linq to SQL Classes

The bad news is that before you can use DLinq, you have to create Linq to SQL classes to represent your database tables. The good news is that Visual Studio 2008 (aside from the Express Edition) can do all of the hard work for you in this.

To get started, right click in the Solution Explorer in your project (or a directory in your project), go to the Add sub-menu and choose "New Item...". In the resulting dialog, choose the "Data" category to the left, then "LINQ to SQL Classes" in the resulting list on the right. Enter a name for the group of classes (collectively known as a Data Context), and press OK.



Next, you need to connect to the database holding the tables that you want to do queries on. To do this, open the Server Explorer (if you don't see it, it's on the View Menu). Right-click on "Data Connections", then choose "Add Connection..." Fill out the fields in the resulting dialog, and click OK. You will then have your database in the list.



Open up the Tables subtree beneath the connection you just created. Select the tables that you want to be able to do queries on (select multiple by holding down Control or Shift). Then drag them to the left pane of the data context. Visual Studio will then create the classes for these tables, and you'll see the tables represented in the data context.



Finally, click in the data context somewhere other than on a table. Go to the properties window and set the namespace and the class name for the class that represents the data context as a whole.



Finally, save this.

Writing Queries

For the sake of this article, I've just created DLinq classes for a couple of bits of the Programmer's Heaven resources database, where we store details of files, links, articles, books and so forth. I'm using a Console Application to play with this.

First, we need to instantiate the data context.

var DB = new ResourcesDB();

The members of DB represent, amongst other things, the tables in the database. We can use these to write queries on those tables.

var Result = from Book in DB.BookResources
             where Book.Published == "1997"
             select Book;

Remember that we use C# syntax rather than SQL syntax; it's all to easy to forget this and write SQL "=" rather than C# "==". Thankfully, if you make this mistake, the compiler will complain. Of course, you don't have to just select the Book; you can instantiate and populate any class that you wish to instead.

Getting At The Query Results

In the previous query we selected all books in our database published in 1997. We can now iterate over the Result collection and print the title of the books.

foreach (var Found in Result)
    Console.WriteLine(Found.Title);

Which produces a list of titles of the books that were found.

The Next Steps

If you have already read our Linq tutorial, you'll have seen how you can write some pretty complex queries over collections of objects. All of that same power is available in DLinq too, so if you want to learn how to write more complex queries then you should refer to that. In future parts of this series, I will look at other DLinq specific bits.

Post Comment Return To Blog