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();

It depends on the **actual, concrete database system** you're using - not all relational databases that use **SQL** as their query language work exactly the same way..... please update your tags with the relevant database system! — marc_s, Jun 18 '14 at 16:52
The database system in question is MySQL, updated post and added tags — BioGeek, Jun 18 '14 at 16:54
This depends some on the DB system, but for any DB worth using, the string you're looking for **never exists**, not even on the database server. That's the whole point of parameterized queries: to keep that data separate from the code. That's why query parameters are so key for preventing sql injection attacks: malicious data never some anywhere close to your sql string. — Joel Coehoorn, Jun 18 '14 at 16:59

score 19 · Accepted Answer · edited Jun 18 '14 at 17:14

19

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)));

edited Jun 18 '14 at 17:14

BioGeek

21,897
23
83
145

answered Jun 18 '14 at 16:55

Anuraj

18,859
7
53
79

2

Note that this only works with named parameters. If you're on a database with simple `?` placeholders, you have match on the order instead. – Joel Coehoorn Jun 18 '14 at 17:03
2

There is an issue. If param value is a String type, the value will be with apostrophes, but with example above is not worked. [Here is a more completed solution](http://stackoverflow.com/questions/5356467/from-net-can-i-get-the-full-sql-string-generated-by-a-sqlcommand-object-with-s) – Leon Pro Apr 08 '16 at 12:37
@LeonPro the construct should get you started...it is up to you to fit your need and environment. Tweak and put validations in place to fit your specific requirement. – GoldBishop Mar 20 '18 at 14:42
Good Template to start most on providing a DbCommand ToString with the statement parsed out. – GoldBishop Mar 20 '18 at 14:42
Let's say you had something like `insert into dbo.blabla (param1, param2) values (@param1, @param2)` Your method will return `insert into dbo.blabla ([some_value1], [some_value2]) values (@[some_value1], @[some_value2])`. No good. I mean the idea is clear, but this method needs a bit more checks. – Alexander Jan 27 '20 at 23:07

Getting SQL query string from DbCommand with Parameters

1 Answers1