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;