Debugging SQL in C#

1 by Duane Jackson - Founder & CEO on December 12, 2008

---

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.

One Response to Debugging SQL in C#

1. March 13, 2009 at 5:15 pm

   Stuart McLean

   Personally I run the sql server profiler on the database all the time so I can see exactly what was run on the database.

   You can copy and paste the ran on your database into management studio.

   Invaluable in a production problem – not that we ever have any.

   Reply