internal virtual void CreateReadAllPagedCommand(IDbCommand command, string query, string orderBy, int pageNo, int pageSize, object parameters = null)
{
StringBuilder commandText = new StringBuilder();
if (this is MsSqlDatabase)
{
if (GetDBVersion(command.Connection).Version.Major >= 11) //SQL server 2012 and above supports offset
{
commandText.Append($@"{query}
ORDER BY {orderBy}
OFFSET {((pageNo - 1) * pageSize)} ROWS
FETCH NEXT {pageSize} ROWS ONLY");
}
else
{
commandText.Append($@"SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS rownumber,
* FROM ({query}) as sq
) AS q
WHERE (rownumber between {((pageNo - 1) * pageSize) + 1} AND {pageNo * pageSize})");
}
}
else
{
commandText.Append($@"{query}
ORDER BY {orderBy}
LIMIT {pageSize}
OFFSET {((pageNo - 1) * pageSize)}");
}
command.CommandType = CommandType.Text;
command.CommandText = commandText.ToString();
if (parameters != null)
{
ParameterCache.GetFromCache(parameters, command).Invoke(parameters, command);
}
}
internal virtual StringBuilder CreateSelectCommand(IDbCommand command, string query, string criteria = null, object parameters = null)
{
bool hasWhere = query.ToLowerInvariant().Contains("where");
StringBuilder commandText = new StringBuilder(query);
if (!string.IsNullOrEmpty(criteria))
{
//add WHERE statement if not exists in query or criteria
if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
{
commandText.Append(" WHERE ");
}
commandText.Append(criteria);
}
if (parameters != null)
{
ParameterCache.GetFromCache(parameters, command).Invoke(parameters, command);
}
return(commandText);
}
internal virtual void CreateReadAllPagedNoOffsetCommand <T>(IDbCommand command, string query, string orderBy, int pageSize, PageNavigationEnum navigation, object[] lastOrderByColumnValues = null, object lastKeyId = null, object parameters = null)
{
string[] orderByColumns = orderBy.Split(',');
string[] orderByDirection = new string[orderByColumns.Length];
for (int i = 0; i < orderByColumns.Length; i++)
{
if (orderByColumns[i].ToLowerInvariant().Contains("desc"))
{
orderByDirection[i] = "DESC";
orderByColumns[i] = orderByColumns[i].ToLowerInvariant().Replace("desc", "").Trim();
}
else
{
orderByDirection[i] = "ASC";
orderByColumns[i] = orderByColumns[i].ToLowerInvariant().Replace("asc", "").Trim();
}
}
if (orderByColumns.Length == 0)
{
throw new MissingMemberException("Orderby column(s) is missing");
}
if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastOrderByColumnValues.Length != orderByColumns.Length)
{
throw new MissingMemberException("For Next and Previous Navigation Length of Last Values must be equal to orderby columns length");
}
if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastKeyId == null)
{
throw new MissingMemberException("For Next and Previous Navigation Last KeyId is required");
}
TableAttribute tableInfo = EntityCache.Get(typeof(T));
bool hasWhere = query.ToLowerInvariant().Contains("where");
StringBuilder pagedCriteria = new StringBuilder();
StringBuilder pagedOrderBy = new StringBuilder();
if (!hasWhere)
{
pagedCriteria.Append(" WHERE 1=1");
}
for (int i = 0; i < orderByColumns.Length; i++)
{
string applyEquals = (i <= orderByColumns.Length - 2 ? "=" : "");
if (navigation == PageNavigationEnum.Next)
{
//when multiple orderbycolumn - apply '>=' or '<=' till second last column
if (orderByDirection[i] == "ASC")
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} AND {tableInfo.PrimaryKeyColumn.Name} > @p_{tableInfo.PrimaryKeyColumn.Name}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
}
else
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} AND {tableInfo.PrimaryKeyColumn.Name} < @p_{tableInfo.PrimaryKeyColumn.Name}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
}
}
else if (navigation == PageNavigationEnum.Previous)
{
if (orderByDirection[i] == "ASC")
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} AND {tableInfo.PrimaryKeyColumn.Name} < @p_{tableInfo.PrimaryKeyColumn.Name}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
}
else
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} AND {tableInfo.PrimaryKeyColumn.Name} > @p_{tableInfo.PrimaryKeyColumn.Name}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
}
}
if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
{
//add Parameter for Last value of ordered column
DbType dbType;
//see if column exists in TableInfo
tableInfo.Columns.TryGetValue(orderByColumns[i], out ColumnAttribute orderByColumn);
if (orderByColumn != null)
{
dbType = orderByColumn.ColumnDbType;
}
else
{
TypeCache.TypeToDbType.TryGetValue(lastOrderByColumnValues[i].GetType(), out dbType);
}
command.AddInParameter("@p_" + orderByColumns[i], dbType, lastOrderByColumnValues[i]);
}
if (i > 0)
{
pagedOrderBy.Append(",");
}
if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
{
//reverse sort as we are going backward
pagedOrderBy.Append($"{orderByColumns[i]} {(orderByDirection[i] == "ASC" ? "DESC" : "ASC")}");
}
else
{
pagedOrderBy.Append($"{orderByColumns[i]} {orderByDirection[i]}");
}
}
//add keyfield parameter for Next and Previous navigation
if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
{
//add LastKeyId Parameter
command.AddInParameter("@p_" + tableInfo.PrimaryKeyColumn.Name, tableInfo.PrimaryKeyColumn.ColumnDbType, lastKeyId);
}
//add keyfield in orderby clause. Direction will be taken from 1st orderby column
if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
{
//reverse sort as we are going backward
pagedOrderBy.Append($",{tableInfo.PrimaryKeyColumn.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
else
{
pagedOrderBy.Append($",{tableInfo.PrimaryKeyColumn.Name} {orderByDirection[0]}");
}
command.CommandType = CommandType.Text;
if (this is MsSqlDatabase)
{
command.CommandText = $"SELECT * FROM (SELECT TOP {pageSize} * FROM ({query} {pagedCriteria.ToString()}) AS r1 ORDER BY {pagedOrderBy}) AS r2 ORDER BY {orderBy}";
}
else
{
command.CommandText = $"SELECT * FROM ({query} {pagedCriteria.ToString()} ORDER BY {pagedOrderBy} LIMIT {pageSize}) AS r ORDER BY {orderBy}";
}
if (parameters != null)
{
ParameterCache.GetFromCache(parameters, command).Invoke(parameters, command);
}
}
