Stashing ConnectionString in class

Question

I have a class that initializes with a Connection String that various functions in the class use to access the SQL Database referenced. Currently the class stashes the passed in ConnectionString in a private readonly string.

As I am writing the using statement for the hundredth time in the functions:

using (SqlConnection SQLCon = new SqlConnection(_ConnectionString))

I am wondering if it would be better to actually stash the connection string in a private readonly SqlConnection instead of the string form? I am guessing this would save the cost of the SqlConnection creation multiple times, at the cost of slightly more memory usage for the SqlConnection object vs string?

Would this also benefit the pooling of the connection and garbage cleanup processes running in the background?

Am I correct that this would be the better way regardless of whether there was benefits to pooling and GC??

If I did this, would it still be beneficial to have the using statement for actually utilizing the connection or would this be viewed as dumb and I should jsut .Open() and .Close() each time I want to use it?

using(_SqlCon)
{
   _SqlCon.Open();
   ...
}

Answer 1

No, it is not a good idea to try to re-use the same connection object. For one reason, after the using block ends the connection is disposed and is not usable again. For another if forces you to run all queries in the app in serial, with limited possibility of parallel work. So then you might be tempted not to have using blocks at all, and that is also a mistake.

What you can do instead is have a shortcut method to return a new connection:

public static class DB
{
    public static SqlConnection CreateCn()
    {
        return new SqlConnection(_connectionString);
    }
}

And then the first snippet in the question can reduce to this:

using var SQLCon = DB.CreateCn();

I might also spell out the full Connection in the name, since you can get completion in the IDE to keep typing fast.

But I tend to take it a step further and also make that kind of method private, such that all database access is forced to take place as part of this class. Then I'll build a public method here to wrap each query, so the rest of the application is calling code like DB.SomeQuery() and you never see SQL strings in the main application. As the app grows I may also break it out into a separate assembly, so I can have additional classes for logically grouping different areas, but still limit access to the core connection.