Is my DB connection closed? (Linq to Sql)

Question

Hi,

I'm using Linq to SQL and read in a blog post about closing database connections as soon as possible. As an example, they showed a variable being converted to a list (using .ToList()) instead of actually returning the Linq query. I have the below code:

 public static bool HasPassword(string userId)
 {

    ProjDataContext db = new ProjDataContext();

    bool hasPassword = (from p in db.tblSpecUser
                                    where p.UserID == userId
                                    select p.HasPassword).FirstOrDefault();


    return hasPassword;
 }

Is that query fine? Or will the database connection remain open for longer than necessary?

Thank you for any advice

Answer 1

With Linq-To-SQL you generally don't need to be concerned about specifically opening and closing the connection that is part of the context object (db in your example). About the only time you would have to specifically do this is if you were sending direct SQL calls through the context object, instead of using Linq.

With L2S, you generally want to create your context object, do your unit of work, and then dispose of the object, as quickly as possible. Your code example looks fine to me.

Answer 2

The connection will be managed automatically. However, there are (or at least can be as the comments suggest) additional resouces associated with the DataContext. These resources will not be released until the DataContext is destroyed by the garbage collector. So, it is usually better to make sure that dispose is called when you don't need the DataContext anymore.

using (ProjDataContext db = new ProjDataContext()) {
    bool hasPassword = (from p in db.tblSpecUser
                                    where p.UserID == userId
                                    select p.HasPassword).FirstOrDefault();


    return hasPassword;
}

Here it is ensured that db.Dispose() is called when the using block exits, thus closing the connection explicitly.

Edit: Following the discussion I looked at the DataContext dispose myself (also using Reflector) and found the following code (FW 3.5) which gets called from DataContext.Dispose:

protected virtual void Dispose(bool disposing)
{
    if (disposing)
    {
        if (this.provider != null)
        {
            this.provider.Dispose();
            this.provider = null;
        }
        this.services = null;
        this.tables = null;
        this.loadOptions = null;
    }
}

So there are resources that gets freed:

• The provider which may hold a DbConnection, a log (TextWriter) and a DbTransaction.
• The the CommonDataServices.
• The tables dictionary.
• The LoadOptions.

The provider may hold resources that needs to be disposed (DbConnection and DbTransaction). Also the TextWriter for the log may have to be disposed, depending upon what instance of the TextWriter the user has assigned to the DataContext's logging mechanism, e.g. a FileWriter that then gets closed automatically.

The other properties hold, as far as I understand them -without looking too much into detail - only memory, but this is also made available for garbage collection by the dispose method, however, the it is not determined when the memory actually gets freed.

So, finally I totally agree with casparOne's statement:

In general, sharing data-access resources like this is a bad idea.

You should create your resources to access the DB, perform your operations, and then dispose of them when done.

Answer 3

I think is a good practice to use the using statement. But I think there is nothing bad with your query.

public static bool HasPassword(string userId)
 {

    using(var db = new ProjDataContext())
    {

       bool hasPassword = (from p in db.tblSpecUser
                                    where p.UserID == userId
                                    select p.HasPassword).FirstOrDefault();


        return hasPassword;
    }
}

Answer 4

The database connection will be closed immediately after your db object will no longer exist (disposed) or explicitly closed. In your sample it will get (sooner or later) garbage collected.

Answer 5

From an implementation point of view, no, you don't have anything to worry about. However, it's not due to the query, but to the management of the DataContext itself.

The DataContext class implements the IDisposable interface, so you should call Dispose on the DataContext implementation whenever you are done with it.

Now, it's a well known fact that calling Dispose on DataContext instances do nothing, and therefore are not technically required.

Unfortunately, it's also very bad practice. You should always code against the contract, not the implementation. Because DataContext implements IDisposable, you should close it, even if you know it does nothing because that could absolutely change in future implementations.

Also, if you switch to another LINQ provider, say LINQ-to-Entities, then you must call Dispose when you are done, because the lifetime of database connections in ObjectContext instances (which also implements IDisposable) is very different, and a call to Dispose has an impact on those database connections.

All that being said, you have a bigger concern. If you are sharing one DataContext, you run the risk of tracking too many objects. Unless you have set the ObjectTrackingEnabled property to false, the DataContext is tracking every object selected through it. If you are doing no update operations (or even if you are) over the lifetime of the app, the number of resources being dedicated for object tracking on a shared DataContext could become considerable.

The rules developing using for other database technologies (e.g. the classes in the System.Data.SqlClient namespace) still apply.

In general, sharing data-access resources like this is a bad idea.

You should create your resources to access the DB, perform your operations, and then dispose of them when done.