Overhead of creating new SqlConnection in c#

Question

A while back I wrote an ORM layer for my .net app where all database rows are represented by a subclass of DatabaseRecord. There are a number of methods like Load(), Save() etc. In my initial implementation I created a connection to the DB in the constructor of DatabaseRecord e.g.

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

I then call Open() and Close() on that SqlConnection at the beginning and end of my methods which access the database. This seemed to me (as someone who was familiar with programming but new to c# and .net) to be the most efficient way to do things - have one connection and open/ close it where necessary within the class.

I've just been doing some reading though and it appears that this pattern is recommended in a number of places:

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

I can see why it's desirable - the connection is automatically Dispose()d even if the stuff you do in the middle causes an uncaught exception. I was just wondering what the overhead is for calling new SqlConnection() potentially many times like this.

Connection Pooling is on so I imagine the overhead is minimal and the second approach should be best practice but I just wanted to make sure my assumptions are right.

Answer 1

Yes, it is best practice. The using makes your call to Close() exception-safe.

And the overhead of creating a (any) object is indeed minimal, and smallest for short-lived objects (that stay in GC generation 0).

Note that you don't have to call Close() at the end of the using-block anymore, it is automatically done for you (Dispose==Close).

Answer 2

This is partially a matter of taste. As long as you employ connection pooling the overhead of creating a new (recycling a pooled connection) will be minimal, so generally the recommended pattern is to create new connection objects as needed.

If you run several commands immediately after each other then I see no reason to create new connections for each of them, but you should avoid holding on to open connections for a long time.

Also, you should note that the Dispose method will close the connection for you. So there is no need to call both Close and Dispose. Since the using clause will call dispose when it ends there is normally no need to call Close.

Answer 3

If you're unsure about the cost of opening/closing connection, have the SqlConnection a member variable of your class, but make the class IDisposable and dispose of the SqlConnection when the class is disposed