ADO CommandText with parameter values.

[AndrusM]

I need to get sql command text where parameters are replaced by values. I tried code below but displayed sql statement contains parameter names, not actual values. How to obtain command where parameters are replaced by their values ? Andrus.

static IDataReader ExecuteReader(string command
, out IDbConnection connection
, CommandBehavior behavior
, params IDbDataParameter[] dataParams)
{
connection = ...
connection.Open();
IDbCommand cmd = new SqlCommand(command, connection as
SqlConnection);
foreach (IDbDataParameter p in dataParams)
cmd.Parameters.Add(p);
// How to display replaced parameter values ?
MessageBox.Show(cmd.CommandText);
return cmd.ExecuteReader(behavior |
CommandBehavior.CloseConnection);
}

Andrus

[Giorgi Dalakishvili]

As far as I know you can't get the actual command text that is executed programmatically. With sql server you can use sql server profiler to see the command being executed.

Giorgi Dalakishvili #region signature my articles My blog[^] #endregion

[snifozao]

There is a tricky way to do it. ADO not seems to help providing the SQL query with the proper values prior running. Here my code snippet that will do the job and may be easily converted any other .NET variant. It's a simple example but may be a start. Some basic checks were done regarding quotes and backslashes but for sure this code need some care before use on production enviroment. Note: You will need to change the type SQLiteCommand/SQLiteParameter to the one that matches your ADO methods set.

public static string getQueryFromCommand(SQLiteCommand cmd)  
{  
    string CommandTxt = cmd.CommandText;  
  
    foreach (SQLiteParameter parms in cmd.Parameters)  
    {  
        string val = String.Empty;  
        if (parms.DbType.Equals(DbType.String) || parms.DbType.Equals(DbType.DateTime))  
            val = "'" + Convert.ToString(parms.Value).Replace(@"\", @"\\").Replace("'", @"\'") + "'";  
        if (parms.DbType.Equals(DbType.Int16) || parms.DbType.Equals(DbType.Int32) || parms.DbType.Equals(DbType.Int64) || parms.DbType.Equals(DbType.Decimal) || parms.DbType.Equals(DbType.Double))  
            val = Convert.ToString(parms.Value);  
        string paramname = "@" + parms.ParameterName;  
        CommandTxt = CommandTxt.Replace(paramname, val);  
    }  
    return (CommandTxt);  
}  

Bruno Ratnieks CTO Sniffer.net bruno@sniffer.net