Some time back I authored an ORM layer for my .internet application where all database rows are symbolized with a subclass of DatabaseRecord. You will find numerous techniques like Load(), Save() etc. During my initial implementation I produced an association towards the DB within the constructor of DatabaseRecord e.g.

connection = new SqlConnection(
    ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString
);

Then i call Open() and Close() on that SqlConnection at the start and finish of my techniques which access the database. This appeared in my experience (as somebody who was acquainted with programming but a new comer to c# and .internet) to become the best method of doing things - have one connection and open/ close it where necessary inside the class.

I have been doing a bit of reading through though also it seems this pattern is suggested in many places:

using (var connection = new SqlConnection(...)) {
    connection.Open();
    // Stuff with the connection
    connection.Close();
}

I can tell why it's desirable - the bond is instantly Dispose()d even when the items you do in the centre causes an uncaught exception. I had been just wondering exactly what the overhead is perfect for calling new SqlConnection() potentially many occasions such as this.

Connection Pooling is on and so i think of the overhead is minimal and also the second approach ought to be best practice however i wanted to make certain my presumptions are right.

Yes, it's best practice. The using makes your call to shut() exception-safe.

And also the overhead of making a (any) object is definitely minimal, and littlest for brief-resided objects (that remain in GC generation ).

Observe that it's not necessary to call Close() in the finish from the using-block any longer, it's instantly accomplished for you (Dispose==Close).

This really is partly dependent on taste. As lengthy while you employ connection pooling the overhead of making a brand new (recycling a put connection) is going to be minimal, so usually the suggested pattern would be to create new connection objects when needed.

Should you run several instructions soon after one another i quickly see pointless to produce new connections for all of them, however, you should avoid holding onto open connections for any very long time.

Also, you need to observe that the Dispose method will close the bond for you personally. So there's you don't need to call both Close and Dispose. Because the using clause will call dispose if this finishes there's normally you don't need to call Close.

If you are unsure about the price of opening/closing connection, possess the SqlConnection an associate variable of the class, but result in the class IDisposable and get rid of the SqlConnection once the class is disposed