using statement for SQL command

Question

I have for longer than I can remember not used using blocks when doing queries, however I now have an issue with locking tables and it looks like the queries are not disposing properley.

However I need to re-write the following code into a using block.

sql = conn.CreateCommand();
sql.CommandText = query;
sql.Parameters.Add("@p_DateFrom", SqlDbType.VarChar).Value = datefrom.ToString("yyyy-MM-dd");
sql.CommandType = CommandType.Text;

int rowCount = (Int32)sql.ExecuteScalar();
sql.Parameters.Clear();
sql.Dispose();
return rowCount;

How would I go about re-writing this. Would I have to do the following:

int rowCount = 0;
using (sql = conn.CreateCommand())
{
    sql.CommandText = query;
    sql.Parameters.Add("@p_DateFrom", SqlDbType.VarChar).Value = datefrom.ToString("yyyy-MM-dd");
    sql.CommandType = CommandType.Text;
    rowCount = (Int32)sql.ExecuteScalar();
}           
return rowCount;

Answer 1

You don't have to do it quite like that - you could use something like dapper:

int rowCount = conn.Query<int>(query, new {p_DateFrom = datefrom}).Single();

job done; all disposed nicely, no messing with parameters, and it doesn't make the mistake of passing a datetime as a string (it should be passed as a datetime).