More efficient database access

Question

I am new to databases and linq, so my problem may be considered trivial. I currently start all my db requests in each class with:

 DataClassesDataContext db = new DataClassesDataContext()

Then I proceed to make whatever linq request I need within the method and carry on with the main application logic.

Now, two interesting queries:

1) I believe I have seen people wrapping db usage within 'using'. Such as:

using (DataClassesDataContext db = new DataClassesDataContext())
{
    ...
}

If this is correct, then doesn't it mean that my class can't use a member 'db' variable anymore, but rather those db requests need to be made within each function call? Also, what exactly would happen if I don't use 'using' within the calls?

2) Running my app with SQL Profiler enabled, I see lots of connections opening and closing. Does this means that each DataClassesDataContext call makes a separate connection? It seems inefficient, so is the right way to actually make the DataClassesDataContext object a static within each class being used?

Answer 1

Since you added the asp.net tag, it means you are using the context within a HTTP call. A static member context is unusable in asp.net because you need to synchronize access to it, and since your data context is required by every call, you can only serve one HTTP response at a time, a scalability fiasco of epic proportions.

This is why data context are created and disposed 'on-the-go'. In fact, the class specifications clearly calls out this use pattern:

In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

For ASP.Net a sensible 'unit-of-work' context is the HTTP call itself. A longer discussion on this topic can be found at Linq to SQL DataContext Lifetime Management.

The issue of connections open/close is a non-issue. Normally the connections are pooled and the 'opening' is nothing but a re-use of a connection from the pool. If you're opening is heavyweight (fully fledged login) then you're using pooling incorrectly. Comparing Logins/sec and Connection Resets/sec counters will quickly reveal if that is indeed the case.

Answer 2

In general, you should use one DataContext per database conversation. Only you can decide exactly what a conversation is, but typically it is a complete request (fetch the user's wish list, or fetch the user's closed orders, for example) that you might think of as a "unit of work."

Typically what happens is something like this:

WishList wishlist;
using(var context = new DataContext(connectionString)) {
    var service = new UserWishListService(context);
    wishlist = service.GetUserWishList();
}

Also, what exactly would happen if I don't use using within the calls?

The DataContext won't be disposed of properly (unless you've wrapped in a try-catch-finally, but generally you should just use using).

Does this means that each DataClassesDataContext call makes a separate connection?

Not quite. Your application will benefit from the SQL Server ADO.NET provider's built-in connection pooling. Don't worry about this, let the provider manage it for you.

It seems inefficient, so is the right way to actually make the DataClassesDataContext object a static within each class being used?

Absolutely not. DataContexts are not thread-safe (in fact, they are thread-unsafe) and this has "there be dragons" written all over it. Additionally, even a single-threaded context, a static DataContext is a bad choice because the DataContext maintains a cache (for object tracking purposes) of all the entities it has pulled from the database. Over time, the memory consumption will become ginormous.