I am using Linq to SQL and browse inside a blog publish about closing database connections as quickly as possible. For example, they demonstrated a flexible being transformed into a listing (using .ToList()) rather than really coming back the Linq query. I've 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 the fact that query fine? Or will the database connection remain open for over necessary?

Appreciate any advice

The bond is going to be handled instantly. However, you will find (or at best is often as your comments ought to suggest) additional resouces connected using the DataContext. These assets won't be launched before the DataContext is destroyed through the garbage collector. So, it is almost always easier to make certain that dispose is known as when you do not need the DataContext any longer.

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


    return hasPassword;
}

Here you go made certain that db.Dispose() is known as once the using block exits, thus closing the bond clearly.

Edit: Following a discussion I checked out the DataContext dispose myself (also using Reflector) and located the next code (FW 3.5) which will get known as 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 assets that will get freed:

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

The provider may hold assets that should be disposed (DbConnection and DbTransaction). Even the TextWriter for that log might have to be disposed, based upon what demonstration of the TextWriter the consumer has designated towards the DataContext's logging mechanism, e.g. a FileWriter that then will get closed instantly.

Another qualities hold, so far as I realize them -without searching an excessive amount of into detail - only memory, but this is provided for garbage collection through the dispose method, however, the it's not determined once the memory really will get freed.

So, finally I totally accept casparOne's statement:

Generally, discussing data-access assets like this can be a bad idea.

You need to make your assets to gain access to the DB, perform your procedures, after which get rid of them when done.