Is there a way to get the full sql text back from SqlCommand after param substitution?

Question

I created a SqlCommand with an SQL query containing parameters. I than add all the parameters to the class.

Is there an easy way to see the resulting SQL query before it is sent off to the db?

This would be handy for debugging purposes. (e.g. copying the entire query and run it in the management studio to try and pinpoint problems)

R

Answer 1

No, because no substitution actually happens. The query is passed to the server as is, and the parameters are passed separately.

However, you can write a method to replace parameter placeholders with the actual parameter values... that's what I did in one of my applications, I'll post the code as soon as I can.

---

Here is the code that I use, but it is for an Oracle Lite database, so it will require some adaptations to use it with another RDBMS.

    public void Log(IDbCommand cmd)
    {
        StringBuilder sb = new StringBuilder(cmd.CommandText);
        for (int i = 0; i < cmd.Parameters.Count; i++)
        {
            int pos = sb.ToString().IndexOf("?");
            if (pos > 0)
                sb.Replace("?", FormatParameter(cmd.Parameters[i]), pos, 1);
        }
        Log(sb.ToString());
    }

    private string FormatParameter(object prm)
    {
        IDbDataParameter p = prm as IDbDataParameter;
        if (p.Value == null || p.Value == DBNull.Value)
            return "NULL";
        switch (p.DbType)
        {
            case DbType.AnsiString:
            case DbType.AnsiStringFixedLength:
            case DbType.String:
            case DbType.StringFixedLength:
                string s = p.Value as string;
                return string.Format("'{0}'", s.Replace("'", "''"));

            case DbType.Binary:
                byte[] b = p.Value as byte[];
                return HexString(b);

            case DbType.Date:
            case DbType.DateTime:
            case DbType.DateTime2:
                DateTime d = (DateTime)p.Value;
                return string.Format("to_date('{0}', 'dd/mm/yyyy hh24:mi')", d.ToString("dd/MM/yyyy HH:mm"));

            default:
                return p.Value.ToString();
        }
    }

    private string HexString(byte[] bytes)
    {
        StringBuilder sb = new StringBuilder();
        for (int i=0; i < bytes.Length; i++)
            sb.AppendFormat("{0:X2}", bytes[i]);
        return sb.ToString();
    }

Answer 2

Well, you can see the entire query in SQL Profiler (admittedly that is after it's been sent to the database) but it gives you an easy way to copy-paste the statement so you can debug with that inside Management Studio.

Just add a new trace, run the code that does the database call, then copy-paste the command from the resulting capture events.