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