How can I obtain the string representation of a SqlParameter, without submitting a SQL statement to the server?

How can I convert a parameterized string such as "(@param1,开发者_运维百科 @param2, @param3)" to a normal SQL statement (like if I were to execute the query)?

This would be helpful to directly replace a set of parameters by their values in a batch (very long) insert query, where the same parameter names could be reused.

I am aware of the following questions, but they do not provide a proper answer (except asserting that it is the server which does the conversions from the parameters it has received separately):

• How to get the SQL actually executed by a parameterized query in .Net (VB or C#)?
• Get SQL statement after parameters added

Not sure if this is what you're looking for, but I created an extension method for SqlCommand called ToSql() that declares all Parameters, sets literal values, and then uses the CommandText to generate a stand-alone SQL statement. I can then copy and paste the statement into SSMS and continue debugging from there.

I wish I could share the code with you, but it's the property of my employer. Besides, it was actually pretty easy to write, and is a fun exercise. I'll just describe how it works.

For example:

var command = new SqlCommand(@"
    SELECT * 
    FROM Table 
    WHERE 
        Column1 = @Param1 
    AND Column2 = @Param2
");
command.Parameters.AddWithValue("@Param1", 555);
command.Parameters.AddWithValue("@Param2", "StackOverflow's cool");
...

Now, while debugging, I simply add a watch for command.ToSQL(), and I get the following text:

DECLARE @Param1   INT            SET @Param1   = 555
DECLARE @Param2   VARCHAR(MAX)   SET @Param2   = 'StackOverflow''s cool'
SELECT *
FROM Table
WHERE
    Column1 = @Param1
AND Column2 = @Param2

For stored procedures, the result looks like this:

DECLARE @Param1   INT            SET @Param1   = 555
DECLARE @Param2   VARCHAR(MAX)   SET @Param2   = 'StackOverflow''s cool'
EXECUTE Some_Stored_Procedure
    @Param1 = @Param1
   ,@Param2 = @Param2

I wouldn't recommend using this code in production, and it might not be safe against SQL injection attacks, but it's an incredibly useful debugging tool!

I've just found something like that for SQLite:

SQLiteCommand cmd = new SQLiteCommand("INSERT INTO `" + rolesTable + "`" +
                " (Rolename, ApplicationName) " +
                " Values($Rolename, $ApplicationName)", conn);

        cmd.Parameters.Add("$Rolename", DbType.String, 255).Value = rolename;
        cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = ApplicationName;