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.