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.

Answer 3

I just re-read your question before posting this. While this technique probably can't be directly applied to .net, you might be able to work out something similar. So:

I've recently had to do a lot of dynamic coding in T-SQL, and I've come up with the following routine. Assume you've got a chunk of code like so:

DECLARE
  @Command    nvarchar(max)
 ,@SearchFor  int

SET @Command = 'SELECT * from MyTable where PrimaryKey = @SearchFor'
SET @SearchFor = 1

EXECUTE sp_executesql
  @Command
 ,N'@SearchFor int'
 ,@SearchFor

This is of course very simplisitc--if you need to do dynamic code, you're going to have complex to wildly complex queries, and as you've discovered these can be very difficult to debug. Here's how I'd rewrite the above code:

DECLARE
  @Command    nvarchar(max)
 ,@SearchFor  int
 ,@Debug      int
    --  0 = Run it
    --  1 = Run and display it
    --  2 = Display it 

SET @Command = 'SELECT * from MyTable where PrimaryKey = @SearchFor'
SET @SearchFor = 1
SET @Debug = 1

IF @Debug > 0
    --  Show the command that would be run
    PRINT replace(@Command, '@SearchFor', cast(@SearchFor as varchar(10)))

IF @Debug < 2
    --  Run it
    EXECUTE sp_executesql
      @Command
     ,N'@SearchFor int'
     ,@SearchFor

This is extra code to write and debug, but once it's in place and working it can be invaluable in debugging situations. If it's part of a stored procedure, make @Debug a parameter that defaults to 0, and make sure that if set to 2, the procedure doesn't actually do anything.