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

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.