Using block around a static/singleton resource reference

Question

This is interesting (to me anyway), and I'd like to see if anyone has a good answer and explanation for this behavior.

Say you have a singleton database object (or static database object), and you have it stored in a class Foo.

public class Foo
{
    public static SqlConnection DBConn = new SqlConnection(ConfigurationManager.ConnectionStrings["BAR"].ConnectionString);
}

Then, lets say that you are cognizant of the usefulness of calling and disposing your connection (pretend for this example that its a one-time use for purposes of illustration). So you decide to use a 'using' block to take care of the Dispose() call.

using (SqlConnection conn = Foo.DBConn)
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "SP_YOUR_PROC";

        cmd.ExecuteNonQuery();
    }
    conn.Close();
}

This fails, throwing an exception on the call to open the connection, stating that the "ConnectionString property is not initialized". It's not an issue with pulling the connection string from the app.config/web.config. When you investigate in a debug session you see that Foo.DBConn is not null, but contains empty properties.

Why is this?

Answer 1

Perhaps you do not have the corresponding connectionStrings node in you web/app.config?

<connectionStrings>
<add name="BAR"
     connectionString="Data Source=localhost\sqlexpress;Initial Catalog=mydatabase;User Id=myuser;Password=mypassword;" />

Answer 2

A little out of topic and not really answering your question but why using a singleton for SqlConnection when ADO.NET already uses a connection pool? Your code could have very well looked like this:

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BAR"].ConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.Connection = conn;
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "SP_YOUR_PROC";
    cmd.ExecuteNonQuery();
}

And one less thing to worry about in your program: connection lifecycle

Answer 3

The static field is evaluated sometime before it is used (not deterministically). See beforefieldinit for more detail. So the system may not be ready for creating an SQL-connection when it is called or maybe even properly create the static field after you use it.

Additionally, how would you handle a second SQL-command after you closed the first one? I don't know exactly how SqlConnection works, but after closing (note that this cals Dispose) and disposing the connection, your static Foo.DBConn should be gone, i.e. it won't be reevaluated.

If you want to keep your basic infrastructure, I would replace the static field with a static property which returns a new SqlConnection on get:

public static SqlConnection DBConn
{
    get
    {
        return new SqlConnection(ConfigurationManager.ConnectionStrings["BAR"].ConnectionString);
    }
}