protected string BuildCountCommand(Expression expression = null)
{
var table = _viewName.ToString();
var column = "COUNT(1)";
var where = BuildWhereExpression();
var group = BuildGroupExpression();
if (group.Length > 0)
{
column = group.Remove(0, 10);
}
else if (expression != null)
{
column = new SelectExpressionResovle(_isSingleTable, expression).Resovle();
}
var sql = $"SELECT {column} FROM {table}{where}{group}";
if (group.Length > 0)
{
sql = $"SELECT COUNT(1) FROM ({sql}) as t";
return(sql);
}
return(sql);
}
private string ResovleCount()
{
var table = GetTableMetaInfo().TableName;
var column = "COUNT(1)";
var where = ResolveWhere();
var group = ResolveGroup();
if (group.Length > 0)
{
column = group.Remove(0, 10);
}
else if (_countExpression != null)
{
column = new SelectExpressionResovle(_countExpression).Resovle();
}
var sql = $"SELECT {column} FROM {table}{where}{group}";
if (group.Length > 0)
{
sql = $"SELECT COUNT(1) FROM ({sql}) as t";
return(sql);
}
return(sql);
}
protected string BuildSelectCommand(Expression expression)
{
var table = _viewName.ToString();
var column = new SelectExpressionResovle(_isSingleTable, expression).Resovle();
var where = BuildWhereExpression();
var group = BuildGroupExpression();
var having = BuildHavingExpression();
var orderBy = BuildOrderExpression();
string sql;
if (_context.DbContextType == DbContextType.SqlServer2008 || _context.DbContextType == DbContextType.SqlServer2012)
{
if (_lockname != string.Empty)
{
_lockname = $" WITH({_lockname})";
}
//第一页
if (_page.Index == 0)
{
sql = $"SELECT TOP {_page.Count} {column} FROM {table}{_lockname}{where}{group}{having}{orderBy}";
}
else if (_page.Index > 0) //大于一页
{
if (orderBy == string.Empty) //如果未指定排序
{
orderBy = " ORDER BY (SELECT 1)";
}
if (_context.DbContextType == DbContextType.SqlServer2008)
{
var rownumber = $"ROW_NUMBER() OVER ({orderBy}) AS RowNumber";
var offset = $"WHERE RowNumber > {_page.Index}";
sql = $"SELECT TOP {_page.Count} * FROM (SELECT {column},{rownumber} FROM {_lockname}{table}{where}{group}{having}) AS t {offset}";
}
else
{
var offset = $" OFFSET {_page.Index} ROWS FETCH NEXT {_page.Count} ROWS ONLY";
sql = $"SELECT {column} FROM {_lockname}{table}{where}{group}{having}{orderBy}{offset}";
}
}
else//不分页
{
sql = $"SELECT {column} FROM {_lockname}{table}{where}{group}{having}{orderBy}";
}
}
else
{
var offset = _page.Index > 0 || _page.Count > 0 ? $" LIMIT {_page.Index},{_page.Count}" : string.Empty;
sql = $"SELECT {column} FROM {table}{where}{group}{having}{orderBy}{offset}{_lockname}";
}
return(sql);
}
