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