public void AddOrderBy(string tableName, string columnName, OrderByType sortType) { OrderByItem orderBy = new OrderByItem { Table = tableName, Name = columnName, Type = sortType }; OrderByPart.Add(orderBy); }
private string _buildHasPage(SqlFilter filter) { foreach (string select in filter.Selects) { SelectMethods[select](); } BuildOrderBy(filter.Orders); string strWhere = "", strFrom = "", orderPart = ""; StringBuilder sql = new StringBuilder(); sql.Append("with result as ( "); sql.Append("select "); if (filter.Limit > 0 && filter.Page > 0) { if (OrderByPart.Count > 0) { sql.Append(" ROW_NUMBER() OVER (ORDER BY "); for (int i = 0; i < OrderByPart.Count; i++) { OrderByItem item = OrderByPart[i]; if (i > 0) { sql.Append(","); } switch (item.Type) { case OrderByType.ASC: sql.Append(item.Table).Append(".").Append(item.Name); sql.Append(" asc "); break; case OrderByType.DESC: sql.Append(item.Table).Append(".").Append(item.Name); sql.Append(" desc "); break; case OrderByType.RANDOM: sql.Append(" newid() "); break; } } sql.Append(" ) AS 'RowNumber' "); } } sql.Append(string.Join(",", SelectPart.ToArray())); _buildFromAndWhere(filter, out strFrom, out strWhere); sql.Append(strFrom).Append(strWhere); sql.Append(" ); "); //end with result sql.Append(" select * from result where RowNumber between ").Append(((filter.Page - 1) * 15) + 1).Append(" and ").Append(filter.Page * 15); return(sql.ToString()); }
private string _buildNonePage(SqlFilter filter) { foreach (string select in filter.Selects) { SelectMethods[select](); } BuildOrderBy(filter.Orders); BuildGroupBy(filter.Groups); BuildJoin(filter.Joins); string strWhere = "", strFrom = ""; StringBuilder sql = new StringBuilder(); sql.Append("select "); if (filter.Limit > 0 && filter.Page == 0) { sql.Append(" top " + filter.Limit.ToString() + " "); } sql.Append(string.Join(",", SelectPart.ToArray())); _buildFromAndWhere(filter, out strFrom, out strWhere); sql.Append(strFrom).Append(strWhere); if (GroupByPart.Count > 0) { sql.Append(" group by "); for (int i = 0; i < GroupByPart.Count; i++) { GroupByItem item = GroupByPart[i]; if (i > 0) { sql.Append(","); } if (!string.IsNullOrEmpty(item.Table)) { sql.Append(item.Table).Append(".").Append(item.Name); } else { sql.Append(item.Name); } } } if (OrderByPart.Count > 0) { sql.Append(" order by "); for (int i = 0; i < OrderByPart.Count; i++) { OrderByItem item = OrderByPart[i]; if (i > 0) { sql.Append(","); } switch (item.Type) { case OrderByType.ASC: sql.Append(item.Table).Append(".").Append(item.Name); sql.Append(" asc "); break; case OrderByType.DESC: sql.Append(item.Table).Append(".").Append(item.Name); sql.Append(" desc "); break; case OrderByType.RANDOM: sql.Append(" newid() "); break; } } } return(sql.ToString()); }