Debugging SQL in C#

If you write C# code, then you’ve probably felt the frustration of trying to debug a SQL Command. Especially as it has lots of parameters. There doesn’t seem to be any native way of getting out an exact SQL command for you to execute yourself in SQL Management Studio.

Well, fret no more. I just threw together the following function for exactly that purpose:

public string sqlCommandToString(SqlCommand c,Boolean bVerbose)
{
  string retval = c.CommandText + " ";
  SqlParameter p;
  for (int i = 0; i < c.Parameters.Count; i++)
  {
    p = c.Parameters[i];
    if (bVerbose)
    {
      retval = retval + p.ParameterName + "(" + p.SqlDbType.ToString() + ")=" + p.Value.ToString() + ", ";
    }
    else
    {
      retval = retval + "'" + p.Value.ToString() + "',";
    }
  }
  return retval;
}

Just pass in a SQLCommand object. If you want more info, such as the parameter name and type then make the second argument True.