private dynamic BuildPagedResult(string primaryKeyField = "", string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
{
dynamic result = new ExpandoObject();
var countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField.Split(KeyColSeparator).FirstOrDefault(), TableName);
if (String.IsNullOrEmpty(orderBy))
{
orderBy = string.IsNullOrEmpty(primaryKeyField) ? PrimaryKeyField : primaryKeyField;
}
if (!string.IsNullOrEmpty(where))
{
if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS Row, {0} FROM {2} {3}) AS {2} ", columns, orderBy, TableName, where);
var pageStart = (currentPage - 1) * pageSize;
query += string.Format(" WHERE Row > {0} AND Row <={1}", pageStart, (pageStart + pageSize));
countSQL += where;
result.TotalRecords = Scalar(countSQL, args);
result.TotalPages = result.TotalRecords / pageSize;
if (result.TotalRecords % pageSize > 0)
{
result.TotalPages += 1;
}
result.Items = Query(string.Format(query, columns, TableName), args);
return(result);
}
/// <summary>
/// Returns a single row from the database
/// </summary>
public virtual dynamic Single(string columns, params object[] key)
{
var sql = string.Format("SELECT {0} FROM {1}", columns, TableName);
var primaryKeyElem = PrimaryKeyField.Split(KeyColSeparator).Select(x => x.Trim()).ToArray();
for (var i = 0; i < primaryKeyElem.Length; i++)
{
if (i == 0)
{
sql = sql + " WHERE " + primaryKeyElem[i] + " = @" + i;
}
else
{
sql = sql + " AND " + primaryKeyElem[i] + " = @" + i;
}
}
var items = Query(sql, key).ToList();
return(items.FirstOrDefault());
}