Getting SQL query string from DbCommand with Parameters

Question

Just before executing a SQL query on a MySQL database in C# I want to log the query that will be executed. What I have now obviously only logs SELECT * FROM foo WHERE Id = @Id where I would like to see SELECT * FROM foo WHERE Id = 5. How do I do that?

    DbCommand dbCommand = dbFactory.CreateCommand();
    dbCommand.CommandText = "SELECT * FROM foo WHERE Id = @Id";

    DbParameter param = dbCommand.CreateParameter();
    param.ParameterName = "@Id";
    param.Value = 5;

    dbCommand.Parameters.Add(param);

    dbConnection.Open();
    Log.AddVerbose(String.Format("SQL query: {0}", dbCommand.CommandText));
    dbCommand.ExecuteReader();

Answer 1

If you are trying to log you can do an extension method like this.

public static string GetGeneratedQuery(this SqlCommand dbCommand)
{
    var query = dbCommand.CommandText;
    foreach (var parameter in dbCommand.Parameters)
    {
            query = query.Replace(parameter.ParameterName, parameter.Value.ToString());
    }

    return query;
}

And you can use it like this.

Log.AddVerbose(String.Format("SQL query: {0}", GetGeneratedQuery(dbCommand)));