Get the generated SQL statement from a SqlCommand object?

c# vb.net ado.net
dummy picture dummy · Nov 5, 2008 · Viewed 166.7k times · Source

I have the following code:

Using cmd As SqlCommand = Connection.CreateCommand
    cmd.CommandText = "UPDATE someTable SET Value = @Value"
    cmd.CommandText &= " WHERE Id = @Id"
    cmd.Parameters.AddWithValue("@Id", 1234)
    cmd.Parameters.AddWithValue("@Value", "myValue")
    cmd.ExecuteNonQuery
End Using

I wonder if there is any way to get the final SQL statment as a String, which should look like this:

UPDATE someTable SET Value = "myValue" WHERE Id = 1234

If anyone wonders why I would do this:

  • for logging (failed) statements
  • for having the possibility to copy & paste it to the Enterprise Manager for testing purposes

Answer

Kon picture Kon · Nov 5, 2008

For logging purposes, I'm afraid there's no nicer way of doing this but to construct the string yourself:

string query = cmd.CommandText;

foreach (SqlParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}

Related questions

What 'length' parameter should I pass to SqlDataReader.GetBytes()

I have a SqlDataReader and need to read a varbinary(max) column from it using the SqlDataReader.GetBytes() method. This method populates a byte array and therefore needs to know what length of data to read. This is where I …

Read More
Is a Dataset the same as a Recordset

I used to work in VB.net and used Dataset's all the time when working with ADO, but now i'm Working in C# and the Research is showing me a lot of Recordsets. Are they the same thing? If no …

Read More
What is a NullReferenceException, and how do I fix it?

I have some code and when it executes, it throws a NullReferenceException, saying: Object reference not set to an instance of an object. What does this mean, and what can I do to fix this error?

Read More