Ensure the sql is rebuilt if altered after sqlFinal is generated

[rupe120]

I was interested in retrieving the overall SQL before I was done constructing what will finally be submitted to the server. How do you feel about the change here?

[rupe120]

The reason I was looking for this change was for paging result sets. I wanted to create the following extension method:

public static Sql BuildCountSql(this Sql sql)
{
    if (sql.SQL.ToLower().Contains("order by"))
        throw new Exception("BuildCountSql failure: Cannot count a query with an Order By clause.");

    var countSql = new Sql("select count(*) from (");
    countSql.Append(sql.SQL, sql.Arguments);
    countSql.Append(") x");

    return countSql;
}

To be able to do the following:

string baseSql = @"
    SELECT
        u.UserId,
        u.UserName,
        u.FirstName,
        u.LastName,
        u.Email
    FROM 
        [User] u";

var sql = new Sql(baseSql).Where("IsDeleted = @0", 0);
sql.ApplyFiltering<UserPaged>(pageRequest);

var countSql = sql.BuildCountSql();

sql.ApplySorting<UserPaged>(pageRequest,"u.LastName Asc, u.FirstName Asc");

return await _database.MsSqlPageAsync<UserPaged>(pageRequest.Page, pageRequest.Count, countSql, sql);

The ApplyFiltering() and ApplySorting() are just custom building parameterized Where and Order By clauses based on a paging request object.

[rupe120]

Oh and the MsSqlPageAsync() method looks like this:

public static Task<Page<T>> MsSqlPageAsync<T>(this Database database, long page, long itemsPerPage, Sql sqlCount, Sql sqlPage)
{
    sqlPage.Append("OFFSET ((@0 - 1) * @1) ROWS FETCH NEXT @1 ROWS ONLY", page, itemsPerPage);

    return database.PageAsync<T>(page, itemsPerPage, sqlCount.SQL, sqlCount.Arguments, sqlPage.SQL, sqlPage.Arguments);
}

[tmenier]

Sorry for the delay. Looks good to me.

[rupe120]

Sweet! Thanks and no worries

[tmenier]

Just realized I never pushed a new package out to nuget with this change. It's there now if you're interested. https://www.nuget.org/packages/AsyncPoco

[rupe120]

No worries, thanks for the update. Btw, nice work on this package overall