Get raw sql statement with DbExtensions SqlBuilder

问题:

I'm using the SqlBuilder to build a dynamic sql statement where the SELECT and WHERE clauses vary. The query is built like this:

 SqlBuilder sb = new SqlBuilder();

 sb.SELECT("id, name");
 sb.FROM("products");
 sb.WHERE("name LIKE {0}", new object[] { "a%" });

Once I've got the SqlBuilder ready, I would like to get the raw sql statement. However, the ToString() method returns a string which might look like this:

SELECT id, name FROM products WHERE name LIKE {0}

I need the raw sql with the parameters set, ie:

SELECT id, name FROM products WHERE name LIKE 'a%'

Is it possible using DbExtensions SqlBuilder?

回答1:

You need to call ToCommand passing a provider factory or a connection instance, e.g:

DbCommand command = query.ToCommand(SqlClientFactory.Instance);

In v6, you have to use Database:

var db = new Database();
IDbCommand command = db.CreateCommand(query);

回答2:

The DbExtensions help page has this code:

var query = new SqlBuilder()
   .SELECT("*")
   .FROM("Products")
   .WHERE("Name LIKE {0}", "A%")

This is different to yours. (I know the method has params object[] as the signature, but params don't necessarily work as you think.) Change your WHERE to match theirs.