What does "opening a connection" actually mean?

Question

I was trying to explain to someone why database connections implement IDisposable, when I realized I don't really know what "opening a connection" actually mean.
So my question is - What does c# practically do when it opens a connection?

Thank you.

Answer 1

There are actually two classes involved in implementing a connection (actually more, but I'm simplifying).

One of these is the IDbConnection implementation (SQLConnection, NpgsqlConnection, OracleConnection, etc.) that you use in your code. The other is a "real" connection object that is internal to the assembly, and not visible to your code. We'll call this "RealConnection" for now, though its actual name differs with different implementations (e.g. in Npgsql, which is the case where I'm most familiar with the implementation, the class is called NpgsqlConnector).

When you create your IDbConnection, it does not have a RealConnection. Any attempt to do something with the database will fail. When you Open() it then the following happens:

1. If pooling is enabled, and there is a RealConnection in the pool, deque it and make it the RealConnection for the IDbConnection.
2. If pooling is enabled, and the total number of RealConnection objects in existence is larger than the maximum size, throw an exception.
3. Otherwise create a new RealConnection. Initialise it, which will involve opening some sort of network connection (e.g. TCP/IP) or file handle (for something like Access), go through the database's protocol for hand-shaking (varies with database type) and authorise the connection. This then becomes the RealConnection for the IDbConnection.

Operations carried out on the IDbConnection are turned into operations the RealConnection does on its network connection (or whatever). The results are turned into objects implementing IDataReader and so on so as to give a consistent interface for your programming.

If a IDataReader was created with CommandBehavior.CloseConnection, then that datareader obtains "ownership" of the RealConnection.

When you call Close() then one of the following happens:

1. If pooling, and if the pool isn't full, then the object is put in the queue for use with later operations.
2. Otherwise the RealConnection will carry out any protocol-defined procedures for ending the connection (signalling to the database that the connection is going to shut down) and closes the network connection etc. The object can then fall out of scope and become available for garbage collection.

The exception would be if the CommandBehavior.CloseConnection case happened, in which case it's Close() or Dispose() being called on the IDataReader that triggers this.

If you call Dispose() then the same thing happens as per Close(). The difference is that Dispose() is considered as "clean-up" and can work with using, while Close() might be used in the middle of lifetime, and followed by a later Open().

Because of the use of the RealConnection object and the fact that they are pooled, opening and closing connections changes from being something relatively heavy to relatively light. Hence rather than it being important to keep connections open for a long time to avoid the overhead of opening them, it becomes important to keep them open for as short a time as possible, since the RealConnection deals with the overhead for you, and the more rapidly you use them, the more efficiently the pooled connections get shared between uses.

Note also, that it's okay to Dispose() an IDbConnection that you have already called Close() on (it's a rule that it should always be safe to call Dispose(), whatever the state, indeed even if it was already called). Hence if you were manually calling Close() it would still be good to have the connection in a using block, to catch cases where exceptions happen before the call to Close(). The only exception is where you actually want the connection to stay open; say you were returning an IDataReader created with CommandBehavior.CloseConnection, in which case you don't dispose the IDbConnection, but do dispose the reader.

Should you fail to dispose the connection, then the RealConnection will not be returned to the pool for reuse, or go through its shut-down procedure. Either the pool will reach its limit, or the number of underlying connections will increase to the point of damaging performance and blocking more from being created. Eventually the finaliser on RealConnection may be called and lead to this being fixed, but finalisation only reduces the damage and can't be depended upon. (The IDbConnection doesn't need a finaliser, as it's the RealConnection that holds the unmanaged resource and/or needs to do the shut-down).

It's also reasonable to assume that there is some other requirement for disposal unique to the implementation of the IDbConnection beyond this, and it should still be disposed of even if analysing the above leads you to believe its not necessary (the exception is when CommandBehavior.CloseConnection passes all disposal burden to the IDataReader, but then it is just as important to dispose that reader).

Answer 2

Good question.

From my (somewhat limited knowledge) of the "under-the-hood" working of a SQL Connection, many steps are involved, such as:

The Steps Under the Hood

1. Physical socket/pipe is opened (using given drivers, eg ODBC)
2. Handshake with SQL Server
3. Connection string/credentials negotiated
4. Transaction scoping

Not to mention connection pooling, i believe there is some kind of alogrithm involved (if the connection string matches one for an already existing pool, the connection is added to the pool, otherwise new one is created)

IDiposable

With regards to SQL Connections, we implement IDisposable so that when we call dispose (either via the using directive, or explicity), it places the connection back into the connection pool. This is in stark contrast with just the plain old sqlConnection.Close() - as all this does is close it temporarily, but reserves that connection for later use.

From my understanding, .Close() closes the connection to the database, whereas .Dispose() calls .Close(), and then releases unmanaged resources.

Those points in mind, at the very least it is good practice to implement IDisposable.

Answer 3

Adding to answers above... The key is that upon "opening the connection" resources may be allocated that will take more than standard garbage collection to recover, namely an open socket/pipe/IPC of somekind. The Dispose() method cleans these up.