SQL Statement after adding parameters

Question

Is it possible to see the sql statement after parameters have been replaced?

using(SqlCommand cmdInsert = new SqlCommand("INSERT INTO Table(Value_fkey) VALUES(@ValueKey)", Database.Connection))
{
    cmdInsert.Parameters.AddWithValue("@ValueKey", ValueKey);
    System.Convert.ToInt32(cmdInsert.ExecuteScalar());
}

I'd like it to log my sql statements, so rather not via SQL Server, but I don't mind whether it is before, or after calling the statement.

Parameters don't really get 'replaced', they just get used as parameters. You can loop through the existing parameters and see their values at any time. — cbp
– cbp, Commented Nov 4, 2012 at 13:19
Use the profiler to see what happens. You'll see one event prepare the statement, and another/others using that prepared statement, by ID. There is no substitution. — MatBailie
– MatBailie, Commented Nov 4, 2012 at 13:22
The sql you are looking for does not exist, will never exist. That is the whole point of using query parameters. Because this sql is never assembled, the chance of data in your query being mistakenly interpreted as code is zero. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 5, 2012 at 0:03

Richard Whitehouse · Accepted Answer · 2012-11-04 23:54:02Z

2

This seems to be the simplest way to do it then:

public void OutputSQLToTextFile(SqlCommand sqlCommand)
{
        string query = sqlCommand.CommandText;
        foreach (SqlParameter p in sqlCommand.Parameters)
        {
            query = query.Replace(p.ParameterName, p.Value.ToString());
        }
        OutputToTextFile(query);
    }

answered Nov 4, 2012 at 23:54

Richard Whitehouse

6913 gold badges14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peterboccia Over a year ago

Unfortunately this is wrong when using parameter with composed names such as @Par and @ParAdditional. In this case the Replace method will replace "@Par" and so we could have in the second parameter [valuie]Additional.

jocull Over a year ago

@peterboccia What if you were to sort the parameters from longest to shortest before replacing?

Collectives™ on Stack Overflow

SQL Statement after adding parameters

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related