Debugging SQL in C#

December 12th, 2008 by Duane Jackson - CEO

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.

Tags: C#, SQL

This entry was posted on Friday, December 12th, 2008 at 6:42 pm and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.