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
– marc_s, Commented Jun 18, 2014 at 16:52
The database system in question is MySQL, updated post and added tags — BioGeek
– BioGeek, Commented Jun 18, 2014 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
– Joel Coehoorn, Commented Jun 18, 2014 at 16:59

BioGeek · Accepted Answer · 2014-06-18 17:14:49Z

22

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, 2014 at 17:14

BioGeek

23k23 gold badges90 silver badges156 bronze badges

answered Jun 18, 2014 at 16:55

Anuraj

19.6k7 gold badges61 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joel Coehoorn Over a year ago

Note that this only works with named parameters. If you're on a database with simple ? placeholders, you have match on the order instead.

Leon Pro Over a year ago

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

GoldBishop Over a year ago

@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 Over a year ago

Good Template to start most on providing a DbCommand ToString with the statement parsed out.

Alexander Over a year ago

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.

Collectives™ on Stack Overflow

Getting SQL query string from DbCommand with Parameters

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related