What is Best Approach for Opening/Closing SqlConnection in C#

Question

I would like to know what could be best approach to open a SqlConnection with Sql Server 2008R2 Express Edition Database. This Version of Sql has Limitations of RAM Usage and CPU Usage so we must adopt something best to open a SqlConnection.

Right Now i am Checking Connection on Start and End of each and every Method. Here is an example of that.

   private void CheckValidId(string Id)
    {
        CheckConnectionStatus();

        try
        {
            sqlConnection.Open();
            sqlCommand = new SqlCommand("select * from ValidId where id=@id", sqlConnection);
            sqlCommand.Parameters.AddWithValue("@id", Id);
            sqlDataReader = sqlCommand.ExecuteReader();
            While (sqlDataReader.Read())
            {
               string Test = sqlDataReader["Id"].toString();
               MessageBox.Show("Value of Id : " , Test);
            }
        }
        catch (Exception exp)
        {
            MessageBox.Show(exp.Message.ToString(), "Exception in CheckValidId");
        }
        finally
        {
            CheckConnectionStatus();
        }
    }

Here is CheckConnectionStatus Method

    private void CheckConnectionStatus()
    {
        if (sqlConnection.State == ConnectionState.Open)
        {
            sqlConnection.Close();
        }
    }

What is best approach to perform this operation.

Thanks

Answer 1

Just use using as it disposes of the connection once done.

 using(SqlConnection conn = new SqlConnection("Connection string")){
  //do sql stuff
  conn.Open(); 
  //etc etc
  conn.Close();
 }

Answer 2

You'll want to make use of the disposable pattern to ensure everything is closed and disposed properly:

var query = "select * from ValidId where id=@id";

using (var conn = new System.Data.SqlClient.SqlConnection(usingConnectionString))
using (var command = new System.Data.SqlClient.SqlCommand(query, conn))
{
    command.Parameters.Add("@id", SqlDbType.Int).Value = Id;
    conn.Open;

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            string Test = reader["Id"].ToString();
        }
    }

    command.Parameters.Clear();
}

You don't need to check the connection state; it will close when it's being disposed.

One thing to note: it's best practice to explicitly specify your parameter data types. I've assumed SqlDbType.Int in your case, but you can change it to whatever it really is.

Another thing to note: you don't want to do too much inside the reader while loop. You want to build your collection or whatever and get out of there. The shorter your connection is open, the better. That's because you could potentially be holding a read lock on some of the rows in the database that might affect other users and their applications.

Answer 3

Your pattern for open and close is correct. However you must note that this doesn't open and close the connection to the SQL Server so doesn't really address your concerns over memory usage and CPU - in fact it wont make any difference.

What Open and Close does is lease and return a connection to the ADO Connection Pool on the client PC. This means that Closing an ADO connection is not guaranteed (and in most cases will not) close and release the connection to the SQL Server. This is becasue establishing and authenticating a connection is relatively expensive and slow, so the ADO connection pool keeps your connections in a pool, still open, just in case you want to re-establish a connection.

What makes the difference to SQL Server is the number of concurrent queries it needs to execute - and the dataset size of the queries, and the total size of the data in the database.

Concurrent queries squeeze CPU, and the datasets returned squeeze the RAM available. Obviously the bigger your database the less can be cached in RAM and so the less likely you are to get a cache hit when querying.

In practice my experience with SQL Express editions is that you wont notice any difference between it and the full edition of SQL Server unless you are doing some very specific things;

1) Writing a BI style tool which allows the user to construct user-defined or user-scoped queries. 2) Writing terrible SQL - "big SQL" may mask your bad query syntax, but Express wont be able to because it has less available RAM to play with.

If you write efficient, constrained SQL, you probably wont actually ever hit any of SQL Express's limitations.