How Can I Retrieve the Executed SQL Statement from a SqlCommand Object?

Retrieving the SQL Statement from a SqlCommand Object

In various scenarios, developers may encounter the need to retrieve the generated SQL statement from a SqlCommand object. This can serve useful purposes such as logging failed statements or facilitating testing in Enterprise Manager.

Solution:

While the SqlCommand class does not provide a direct method for extracting the generated SQL statement, it is possible to construct the string manually through:

1. Obtain the Command Text: Assign the CommandText property of the SqlCommand object to a string variable.
2. Iterate Over Parameters: Loop through the collection of Parameters in the SqlCommand object. For each parameter, replace its placeholder in the CommandText with its actual value.

StringBuilder query = new StringBuilder(cmd.CommandText);

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

This approach allows you to construct the final SQL statement as a string, enabling its use for logging or testing purposes.

The above is the detailed content of How Can I Retrieve the Executed SQL Statement from a SqlCommand Object?. For more information, please follow other related articles on the PHP Chinese website!