I was writing this common method that helps in managing SQL Connections. I came to the point of closing and disposing of connections. I guess having a connection object in a using block will not do, since we wont be able to close the connection before it gets disposed. So, I initialized, opened and used it in a try block, and attempted to close it in the finally block, like this
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
connection.Close();
}
I put in this additional validation to check for the connection state, perhaps from my WCF experience. When I explored the possible state of values in the System.Data.ConnectionState enum, it appears that other than Open and Closed, the 4 other states were "reserved for future versions of the product". Well, the only thing this validation protects from now is if the connection is attempted to be closed when it already is closed.
http://msdn.microsoft.com/en-us/library/system.data.connectionstate.aspx
Here's my DataHelper class:
internal class DataHelper
{
private static readonly string sqlConnectionString = "ConnectionStringLiteral Or MethodCall Goes Here";
internal static int ExecuteNonQuery(string storedProcName, SqlParameter[] parameters = null)
{
SqlConnection connection = null;
int rowsAffectedCount;
try
{
connection = new SqlConnection(sqlConnectionString);
using (SqlCommand command = new SqlCommand(storedProcName, connection))
{
SetCommandProperties(command, parameters);
rowsAffectedCount = command.ExecuteNonQuery();
}
}
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
connection.Close();
}
return rowsAffectedCount;
}
internal static DataSet ExecuteQuery(string storedProcName, SqlParameter[] parameters = null)
{
SqlConnection connection = null;
DataSet dataSet = new DataSet();
try
{
connection = new SqlConnection(sqlConnectionString);
using (SqlCommand command = new SqlCommand(storedProcName, connection))
{
SetCommandProperties(command, parameters);
new SqlDataAdapter(command).Fill(dataSet);
}
}
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
connection.Close();
}
return dataSet;
}
private static void SetCommandProperties(SqlCommand command, SqlParameter[] parameters)
{
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
command.Parameters.AddRange(parameters);
}
}
