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

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 received SQL query before it is sent to db?

This would be convenient for debugging purposes. (for example, copy the entire request and run it in the management studio to try to identify problems)

R

Answer 1

No, because no replacement is taking place. The request is transmitted to the server as is, and the parameters are transferred separately.

, ... , , , .

---

, , Oracle Lite, .

    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

, SQL Profiler ( , ), - , Management Studio.

, , , .

Answer 3

, . , , .net, . :

T-SQL, . , :

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

, , - , , , , . :

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

, . , @Debug , 0, , 2, .