internal virtual void CreateReadAllPagedCommand(IDbCommand cmd, string query, string orderBy, int pageNo, int pageSize, object parameters = null)
{
StringBuilder cmdText = new StringBuilder();
if (this is MsSqlDatabase)
{
if (GetDBVersion(cmd.Connection).Version.Major >= 11) //SQL server 2012 and above supports offset
{
cmdText.Append($@"{query}
ORDER BY {orderBy}
OFFSET {((pageNo - 1) * pageSize)} ROWS
FETCH NEXT {pageSize} ROWS ONLY");
}
else
{
cmdText.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
{
cmdText.Append($@"{query}
ORDER BY {orderBy}
LIMIT {pageSize}
OFFSET {((pageNo - 1) * pageSize)}");
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText.ToString();
ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
}
internal virtual void CreateTextCommand(IDbCommand cmd, string query, string criteria = null, object parameters = null)
{
bool hasWhere = query.ToLowerInvariant().Contains("where");
if (!string.IsNullOrEmpty(criteria))
{
//add WHERE statement if not exists in query or criteria
if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
{
query = query + " WHERE ";
}
}
query = query + " " + criteria;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
ParameterCache.AddParameters(parameters, cmd);
}
internal virtual StringBuilder CreateSelectCommand(IDbCommand cmd, string query, string criteria = null, object parameters = null)
{
bool hasWhere = query.ToLowerInvariant().Contains("where");
StringBuilder cmdText = new StringBuilder(query);
if (!string.IsNullOrEmpty(criteria))
{
//add WHERE statement if not exists in query or criteria
if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
{
cmdText.Append(" WHERE ");
}
cmdText.Append(criteria);
}
ParameterCache.AddParameters(parameters, cmd);
return(cmdText);
}
internal virtual void CreateReadAllPagedNoOffsetCommand <T>(IDbCommand cmd, 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]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo,">")}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
}
else
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, "<")}) 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]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, "<")}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
}
else
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, ">")}) 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);
}
cmd.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
if (tableInfo.PkColumnList.Count > 1)
{
if (!(lastKeyId is T))
{
throw new InvalidOperationException("Entity has multiple primary keys. Pass entity setting Primary Key attributes.");
}
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmd.AddInParameter("@p_" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(lastKeyId, pkCol));
index++;
}
}
else
{
cmd.AddInParameter("@p_" + tableInfo.PkColumn.Name, tableInfo.PkColumn.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
if (tableInfo.PkColumnList.Count > 1)
{
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
pagedOrderBy.Append($",{pkCol.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
}
else
{
pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
}
else
{
if (tableInfo.PkColumnList.Count > 1)
{
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
pagedOrderBy.Append($",{pkCol.Name} {orderByDirection[0]}");
}
}
else
{
pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {orderByDirection[0]}");
}
}
cmd.CommandType = CommandType.Text;
if (this is MsSqlDatabase)
{
cmd.CommandText = $"SELECT * FROM (SELECT TOP {pageSize} * FROM ({query} {pagedCriteria.ToString()}) AS r1 ORDER BY {pagedOrderBy}) AS r2 ORDER BY {orderBy}";
}
else
{
cmd.CommandText = $"SELECT * FROM ({query} {pagedCriteria.ToString()} ORDER BY {pagedOrderBy} LIMIT {pageSize}) AS r ORDER BY {orderBy}";
}
ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
}