SQL_CALC_ROWS

[narmontas]

How to add SQL_CALC_ROWS parameter to query?

[maxtoroq]

if you are building the query then it's simple:

var query = SQL
   .SELECT("SQL_CALC_ROWS *")
   ...

if you are getting the query from Database you can use the Insert method to inject the modifier:

var query = db.Products.SQL
   .SELECT_FROM()
   .Insert(6, " SQL_CALC_ROWS")
   ...

same thing if you are using SqlSet:

var query = db.Products.GetDefiningQuery()
   .Insert(6, " SQL_CALC_ROWS")
   ...

[narmontas]

I did the following:

var query = SQL
   .SELECT("SQL_CALC_ROWS firstname").
   _("lastname").
   ...

[narmontas]

Could you give me an example how to format the following query:

... WHERE (a = 1 AND (b = 10 OR c = 11) AND d = 2)

when I have int[] params = new int[] { 10, 11 } with _OR<>() method extension.

I read the documentation but did not understand from Samples.cs example.

[maxtoroq]

var query = SQL
   ...
   .WHERE("(a = 1 AND (b = {0} OR c = {1}) AND d = 2)", params[0], params[1])

The _OR method is for cases when the number of parameters is not known until runtime, is that your case?

[narmontas]

I did the following, but it seems like some workaround.
Btw I use Dapper for mapping and querying. You library for SqlBuilder only, so I cannot use your ParameterValues.

if (q.Types != null && q.Types.Length > 0) {
                    sql.SetNextClause("WHERE", "");
                    sql.Buffer.Append(" AND (");
                    foreach (int type in q.Types) {
                        sql = sql._("type = " + type + "');
                        sql.SetNextClause("WHERE", " OR ");
                    }
                    sql.Buffer.Append(")");
                    sql.SetNextClause("WHERE", " AND ");
                }

[maxtoroq]

This would be the way to do it:

...
   .WHERE()
   ._OR(q.Types, "type = {0}", p => new object[] { p[0] })

The first parameter is the collection of items, the second is the SQL template for each item, and the third is a delegate that returns the parameters used in the template for each item.

To integrate with Dapper you could call ToCommand, then use a helper function to convert the parameters collection to something that Dapper understands. Ideally, you could invoke Dapper by passing a DbCommand, or calling a funciton that converts an IDataRecord to a mapped object.

[narmontas]

Thanks, that worked!
However integrating with Dapper seems like too complex task for me.

[narmontas]

As I was not been able to integrate Dapper so I wrote myself an extension method to return string with parameters already applied. Dapper uses parameters like "type = @type" so they do not collide.

public static class SqlBuilderExtensions
{
    public static string ToSql(this SqlBuilder sb)
    {
        return string.Format(sb.ToString(), sb.ParameterValues.ToArray<object>());
    }
}

[maxtoroq]

Watch out for SQL injection when using string parameters.

[narmontas]

Yes. At the moment only string parameters come from DateTime.ToShortDateString(). User input strings will be passed through Dapper.