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); } }