How to create a scalable SQL connection that takes full advantage of connection pooling?

ado.net asp.net c# dapper sql

Question

Here is the current repository connection pattern on an app I am working on.

public class Repository : IDisposable {


public IDbConnection SiteConnection {
  get {
    if (siteConnection == null) {
      siteConnection = new SqlConnection(DataBaseConfig.SiteConnectionString);
      siteConnection.Open();
    }
    return siteConnection;
  }
}


public void Dispose() {

  if (siteConnection != null) {
    siteConnection.Dispose();
    siteConnection = null;
  }
}



}

The repositories then look like this:

public class SomeRepository : Repository {

   public IEnumerable<Object> GetSomeObjects() {
        return SiteConnection.Query<Object>("select statement");
    }

   public IEnumerable<OtherObject> GetSomeOtherObjects() {
        return SiteConnection.Query<OtherObject>("select statement");
    }

}

From what I can tell, Dispose is only called at the very end of the page load. No transactions are being used. In the past, I was under the impression you should always get in late and get out early when it comes to SQL.

public IEnumerable<Object> GetObjects() {
    using(sqlconnection conn = new sqlconnection(connString)) {
        conn.Open();
        //do something with the database
    }
}

Which method is more bulletproof and takes full advantage of connection pooling and uses the least amount of database resources?

Accepted Answer

Have a look at this msdn explanation about connection pooling. Closing the connection as soon as possible returns it to the pool. I think the best advice that has come out of similar discussions is to let ado.net do what it does best and not try to outhink its pooling logic. If you look at SqlConnection in a decompiler, there is a lot going on under the covers. It's incredible how now you can even close a connection while it is part of a larger transaction (with Sql Server 2008).




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why