internal string CreatePagedSelect(string selectFields, string tableName, bool withNolock, string primaryKey, string condition, SqlParameterCollection parameters, DeclareVariableCollection beforeExecuteDeclare, string sortField, bool isDesc, int pageNumber, int pageSize, int offset, bool returnTotalRecords, int?totalRecords)
{
if (string.IsNullOrEmpty(selectFields))
{
throw new ArgumentNullException("selectFields");
}
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("tableName");
}
if (pageSize < 1)
{
throw new ArgumentException("每页显示的记录数不能为0", "pageSize");
}
string tableNameWithNolock;
if (string.IsNullOrEmpty(sortField))
{
if (string.IsNullOrEmpty(primaryKey))
{
throw new ArgumentNullException("必须指定sortField和primaryKey其中的一个", "sortField");
}
else
{
sortField = primaryKey;
}
}
if (primaryKey != string.Empty && string.Compare(primaryKey, sortField, true) == 0)
{
primaryKey = string.Empty;
}
if (withNolock)
{
tableNameWithNolock = tableName + " WITH (NOLOCK)";
}
else
{
tableNameWithNolock = tableName;
}
string desc = isDesc ? " DESC" : " ASC";
string primaryKeyDesc;
if (string.IsNullOrEmpty(primaryKey))
{
primaryKeyDesc = string.Empty;
}
else
{
primaryKeyDesc = isDesc ? string.Concat(",", primaryKey, " DESC") : string.Concat(",", primaryKey, " ASC");
}
if (pageNumber < 1)
{
pageNumber = 1;
}
//是否知道总记录数
bool knowTotalRecords = (totalRecords != null && totalRecords.Value > -1);
bool hasWhere = string.IsNullOrEmpty(condition) == false;
string whereString = hasWhere ? " WHERE " + condition : " ";
//是否在sql中已经声明了TotalRecords变量
bool declareTotalRecords = false;
StringBuffer sqlBuilder = new StringBuffer();
//在已知总记录数的情况下，以下情况直接返回0条数据 -- passed
if (
(
knowTotalRecords
&&
(
//已知没有数据
totalRecords.Value == 0
||
//页码已经超过最后一页
(
pageNumber > 1
&&
(pageSize * (pageNumber - 1)) >= (totalRecords.Value + offset)
)
)
)
||
//Offset已经大到本页面已经不需要取任何数据
(
offset > 0
&&
(pageSize * pageNumber) <= offset
)
)
{
sqlBuilder += string.Concat(@"SELECT TOP 0 ", selectFields, " FROM ", tableName, " WITH (NOLOCK);");
}
//取第一页，使用最简单高效的方式：top查询 -- passed
else if (offset / pageSize == pageNumber - 1)
{
#region TOP方式取第一页数据
//sql server 2005 及更高版本，top参数化
if (SqlHelper.SqlVersion > 8)
{
sqlBuilder += string.Concat("SELECT TOP (@_Top) ", selectFields, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, desc, primaryKeyDesc, ";");
SqlParameter topParam = new SqlParameter("@_Top", SqlDbType.Int);
topParam.Value = pageSize - offset % pageSize;
parameters.Add(topParam);
}
//sql server 2000 ，top没有优化
else
{
sqlBuilder += string.Concat("SELECT TOP ", (pageSize - offset % pageSize).ToString(), " ", selectFields, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, desc, primaryKeyDesc, ";");
}
#endregion
}
//最后一页，直接top查询，并颠倒结果 -- passed
else if (
knowTotalRecords
&&
(pageSize * pageNumber) >= (totalRecords.Value + offset)
)
{
#region TOP方式取最后一页数据
string tempDesc = isDesc ? " ASC" : " DESC";
string tempPrimaryKeyDesc;
if (string.IsNullOrEmpty(primaryKey))
{
tempPrimaryKeyDesc = string.Empty;
}
else
{
tempPrimaryKeyDesc = isDesc ? string.Concat(",", primaryKey, " ASC") : string.Concat(",", primaryKey, " DESC");
}
int top = (totalRecords.Value + offset) % pageSize;
if (top == 0)
{
top = pageSize - offset % pageSize;
}
//sql server 2005 及更高版本，top参数化
if (SqlHelper.SqlVersion > 8)
{
sqlBuilder += string.Concat(@"
SELECT * FROM (
SELECT TOP (@_Top) ", selectFields, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc, tempPrimaryKeyDesc, @"
) AS t1 ORDER BY ", sortField, desc, primaryKeyDesc, ";");
SqlParameter topParam = new SqlParameter("@_Top", SqlDbType.Int);
topParam.Value = top;
parameters.Add(topParam);
}
//sql server 2000 ，top没有优化
else
{
sqlBuilder += string.Concat(@"
SELECT * FROM (
SELECT TOP ", top.ToString(), " ", selectFields, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc, tempPrimaryKeyDesc, @"
) AS t1 ORDER BY ", sortField, desc, primaryKeyDesc, ";");
}
#endregion
}
//没有指定主键，使用MAX/MIN方式来分页，SortField必须是唯一字段 -- doing
else if (string.IsNullOrEmpty(primaryKey))
{
#region MIN/MAX分页模式
//如果是sql server 2005及更高版本，可以使用 top @参数 来优化查询，提高查询计划的复用程度，改善性能和内存占用
if (SqlHelper.SqlVersion > 8)
{
#region sql server 2005以上版本的处理
//如果已知总记录数，则可以马上推算出到底是否需要逆向查询以提高性能
//(逆向查询指：如果要查询的页数超过总页数的一般，则倒过来查询数据。例如：最后一页的数据应该使用 top 直接查询)
if (totalRecords != null && totalRecords.Value > -1)
{
#region 杂逻辑...
string minOrMax, tempDesc, op;
int topMax = pageSize * (pageNumber - 1);
if (topMax - offset > totalRecords.Value / 2)
{
minOrMax = isDesc ? "MAX" : "MIN";
op = isDesc ? "<=" : ">=";
tempDesc = isDesc ? " ASC" : " DESC";
topMax = totalRecords.Value - topMax + offset;
}
else
{
minOrMax = isDesc ? "MIN" : "MAX";
op = isDesc ? "<" : ">";
tempDesc = isDesc ? " DESC" : " ASC";
topMax -= offset;
}
sqlBuilder += string.Concat(@"
SELECT TOP (@_Size) ", selectFields, @" FROM ", tableNameWithNolock, whereString, (hasWhere ? " AND " : " WHERE "), sortField, op, @"
(SELECT ", minOrMax, "(", sortField, @") FROM (SELECT TOP (@_TopMax) ", sortField, @" FROM ", tableNameWithNolock, whereString, @" ORDER BY ", sortField, tempDesc, @") AS t1)
ORDER BY ", sortField, desc, @"
");
SqlParameter pageSizeParam = new SqlParameter("@_Size", SqlDbType.Int);
pageSizeParam.Value = pageSize;
parameters.Add(pageSizeParam);
SqlParameter topMaxParam = new SqlParameter("@_TopMax", SqlDbType.Int);
topMaxParam.Value = topMax;
parameters.Add(topMaxParam);
#endregion
}
//不知道总记录数，则需要在sql内部计算到底是否需要逆向查询来提高性能
else
{
#region 更复杂逻辑...
declareTotalRecords = true;
string tempDesc = isDesc ? " ASC" : " DESC";
sqlBuilder += @"
DECLARE @_Total int;
DECLARE @_TopMax int;
";
if (string.IsNullOrEmpty(TotalRecordsVariable) == false)
{
sqlBuilder += "SELECT @_Total = ";
sqlBuilder += TotalRecordsVariable;
sqlBuilder += ";";
}
else
{
sqlBuilder += "SELECT @_Total = COUNT(*) FROM ";
sqlBuilder += tableName;
if (WithNolock)
{
sqlBuilder += " WITH (NOLOCK) ";
}
sqlBuilder += whereString;
sqlBuilder += ";";
}
sqlBuilder += string.Concat(@"
SET @_TopMax = @_Size * (@_Number - 1);
--out page
IF (
(@_Total = 0)
OR
(@_Number > 1 AND @_TopMax >= (@_Total + @_Offset))
)
SELECT TOP 0 ", selectFields, " FROM ", tableName, @" WITH (NOLOCK);
--last page
ELSE IF (@_Size * @_Number) >= (@_Total + @_Offset) BEGIN
SET @_TopMax = (@_Total + @_Offset) % @_Size;
IF @_TopMax = 0
SET @_TopMax = @_Size - (@_Offset % @_Size);
SELECT * FROM (
SELECT TOP (@_TopMax) ", selectFields, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc, @"
) AS t1 ORDER BY ", sortField, desc, @";
END
");
//查询页数超过10页的时候才自动优化，否则提高不了太多性能，却反而使sql语句变得更长
if (pageNumber - (offset / pageSize) > 10)
{
sqlBuilder += string.Concat(@"
ELSE IF (@_TopMax - @_Offset) > (@_Total / 2) BEGIN
SET @_TopMax = @_Total - @_TopMax + @_Offset;
SELECT TOP (@_Size) ", selectFields, " FROM ", tableNameWithNolock, whereString, (hasWhere ? " AND " : " WHERE "), sortField, (isDesc ? "<" : ">"), @"=
(SELECT ", (isDesc ? "MAX" : "MIN"), "(", sortField, ") FROM (SELECT TOP (@_TopMax) ", sortField, @" FROM ", tableNameWithNolock, whereString, @" ORDER BY ", sortField, tempDesc, @") AS t1)
ORDER BY ", sortField, desc, @";
END
");
}
sqlBuilder += string.Concat(@"
ELSE BEGIN
SET @_TopMax = @_TopMax - @_Offset;
SELECT TOP (@_Size) ", selectFields, " FROM ", tableNameWithNolock, whereString, (hasWhere ? " AND " : " WHERE "), sortField, (isDesc ? "<" : ">"), @"
(SELECT ", (isDesc ? "MIN" : "MAX"), "(", sortField, ") FROM (SELECT TOP (@_TopMax) ", sortField, @" FROM ", tableNameWithNolock, whereString, @" ORDER BY ", sortField, desc, @") AS t1)
ORDER BY ", sortField, desc, @";
END
");
SqlParameter pageSizeParam = new SqlParameter("@_Size", SqlDbType.Int);
pageSizeParam.Value = pageSize;
parameters.Add(pageSizeParam);
SqlParameter pageNumberParam = new SqlParameter("@_Number", SqlDbType.Int);
pageNumberParam.Value = pageNumber;
parameters.Add(pageNumberParam);
SqlParameter offsetParam = new SqlParameter("@_Offset", SqlDbType.Int);
offsetParam.Value = offset;
parameters.Add(offsetParam);
#endregion
}
#endregion
}
else
{
#region sql server 2000版本的处理
//如果已知总记录数，则可以马上推算出到底是否需要逆向查询以提高性能
//(逆向查询指：如果要查询的页数超过总页数的一般，则倒过来查询数据。例如：最后一页的数据应该使用 top 直接查询)
if (totalRecords != null && totalRecords.Value > -1)
{
#region 杂逻辑...
string minOrMax, tempDesc, op;
int topMax = pageSize * (pageNumber - 1);
if (topMax - offset > totalRecords.Value / 2)
{
minOrMax = isDesc ? "MAX" : "MIN";
op = isDesc ? "<=" : ">=";
tempDesc = isDesc ? " ASC" : " DESC";
topMax = totalRecords.Value - topMax + offset;
}
else
{
minOrMax = isDesc ? "MIN" : "MAX";
op = isDesc ? "<" : ">";
tempDesc = isDesc ? " DESC" : " ASC";
topMax -= offset;
}
sqlBuilder += string.Concat(@"
SELECT TOP ", pageSize.ToString(), " ", selectFields, @" FROM ", tableNameWithNolock, whereString, (hasWhere ? " AND " : " WHERE "), sortField, op, @"
(SELECT ", minOrMax, "(", sortField, @") FROM (SELECT TOP ", topMax.ToString(), " ", sortField, @" FROM ", tableNameWithNolock, whereString, @" ORDER BY ", sortField, tempDesc, @") AS t1)
ORDER BY ", sortField, desc, @"
");
#endregion
}
//不知道总记录数，则需要在sql内部计算到底是否需要逆向查询来提高性能
else
{
#region 更复杂逻辑...
int topMax = pageSize * (pageNumber - 1);
declareTotalRecords = true;
string tempDesc = isDesc ? " ASC" : " DESC";
string paramString = buildParamStringForExecuteSql(beforeExecuteDeclare, condition, parameters);
sqlBuilder += @"
DECLARE @_Total int;
DECLARE @_TopMax int;
DECLARE @_Sql nvarchar(4000);
";
if (string.IsNullOrEmpty(TotalRecordsVariable) == false)
{
sqlBuilder += "SELECT @_Total = ";
sqlBuilder += TotalRecordsVariable;
sqlBuilder += ";";
}
else
{
sqlBuilder += "SELECT @_Total = COUNT(*) FROM ";
sqlBuilder += tableName;
if (WithNolock)
{
sqlBuilder += " WITH (NOLOCK) ";
}
sqlBuilder += whereString;
sqlBuilder += ";";
}
sqlBuilder += string.Concat(@"
SET @_TopMax = @_Size * (@_Number - 1);
--out page
IF (
(@_Total = 0)
OR
(@_Number > 1 AND @_TopMax >= (@_Total + @_Offset))
)
SELECT TOP 0 ", selectFields, " FROM ", tableName, @" WITH (NOLOCK);
--last page
ELSE IF (@_Size * @_Number) >= (@_Total + @_Offset) BEGIN
SET @_TopMax = (@_Total + @_Offset) % @_Size;
IF @_TopMax = 0
SET @_TopMax = @_Size - (@_Offset % @_Size);
SET @_Sql = N'SELECT * FROM (
SELECT TOP ' + CAST(@_TopMax AS varchar(9)) + N' ", selectFields, " FROM ", tableNameWithNolock, whereString.Replace("'", "''"), " ORDER BY ", sortField, tempDesc, @"
) AS t1 ORDER BY ", sortField, desc, @"';
EXEC sp_executesql @_Sql", paramString, @";
END
");
//查询页数超过10页的时候才自动优化，否则提高不了太多性能，却反而使sql语句变得更长
if (pageNumber - (offset / pageSize) > 10)
{
sqlBuilder += string.Concat(@"
ELSE IF (@_TopMax - @_Offset) > (@_Total / 2) BEGIN
SET @_TopMax = @_Total - @_TopMax + @_Offset;
SET @_Sql = N'SELECT TOP ", pageSize.ToString(), " ", selectFields, " FROM ", tableNameWithNolock, whereString.Replace("'", "''"), (hasWhere ? " AND " : " WHERE "), sortField, (isDesc ? "<" : ">"), @"=
(SELECT ", (isDesc ? "MAX" : "MIN"), "(", sortField, ") FROM (SELECT TOP ' + CAST(@_TopMax AS varchar(9)) + N' ", sortField, @" FROM ", tableNameWithNolock, whereString.Replace("'", "''"), @" ORDER BY ", sortField, tempDesc, @") AS t1)
ORDER BY ", sortField, desc, @"';
EXEC sp_executesql @_Sql", paramString, @";
END
");
}
sqlBuilder += string.Concat(@"
ELSE BEGIN
--SET @_TopMax = @_TopMax - @_Offset;
SELECT TOP ", pageSize.ToString(), " ", selectFields, " FROM ", tableNameWithNolock, whereString, (hasWhere ? " AND " : " WHERE "), sortField, (isDesc ? "<" : ">"), @"
(SELECT ", (isDesc ? "MIN" : "MAX"), "(", sortField, ") FROM (SELECT TOP ", (topMax - offset).ToString(), " ", sortField, @" FROM ", tableNameWithNolock, whereString, @" ORDER BY ", sortField, desc, @") AS t1)
ORDER BY ", sortField, desc, @";
END
");
SqlParameter pageSizeParam = new SqlParameter("@_Size", SqlDbType.Int);
pageSizeParam.Value = pageSize;
parameters.Add(pageSizeParam);
SqlParameter pageNumberParam = new SqlParameter("@_Number", SqlDbType.Int);
pageNumberParam.Value = pageNumber;
parameters.Add(pageNumberParam);
SqlParameter offsetParam = new SqlParameter("@_Offset", SqlDbType.Int);
offsetParam.Value = offset;
parameters.Add(offsetParam);
#endregion
}
#endregion
}
#endregion
}
//指定了主键，使用全TOP方式来分页，并且可以处理SortField并不是唯一字段的情况
else
{
//if (offset != 0)
// throw new NotSupportedException("指定了主键的分页方式不支持偏移分页(Offset必需为0)");
#region TOP分页模式
//如果是sql server 2005及更高版本，可以使用 top @参数 来优化查询，提高查询计划的复用程度，改善性能和内存占用
if (SqlHelper.SqlVersion > 8)
{
#region sql server 2005以上版本的处理
//如果已知总记录数，则可以马上推算出到底是否需要逆向查询以提高性能
//(逆向查询指：如果要查询的页数超过总页数的一般，则倒过来查询数据。例如：最后一页的数据应该使用 top 直接查询)
if (totalRecords != null && totalRecords.Value > -1)
{
#region 杂逻辑...
string tempDesc1, tempDesc2;
int topMax = pageSize * pageNumber;
if (topMax - offset > totalRecords.Value / 2)
{
tempDesc1 = isDesc ? " ASC" : " DESC";
tempDesc2 = isDesc ? " DESC" : " ASC";
topMax = totalRecords.Value - topMax + pageSize + offset;
}
else
{
tempDesc1 = isDesc ? " DESC" : " ASC";
tempDesc2 = isDesc ? " ASC" : " DESC";
topMax -= offset;
}
sqlBuilder += string.Concat(@"
SELECT ", selectFields, " FROM ", tableNameWithNolock, " WHERE ", primaryKey, @" IN (
SELECT TOP (@_Size) ", primaryKey, @" FROM (
SELECT TOP (@_TopMax) ", primaryKey, ",", sortField, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc1, ",", primaryKey, tempDesc1, @"
) AS t1 ORDER BY ", sortField, tempDesc2, ",", primaryKey, tempDesc2, @"
) ORDER BY ", sortField, desc, ",", primaryKey, desc, @";
");
SqlParameter pageSizeParam = new SqlParameter("@_Size", SqlDbType.Int);
pageSizeParam.Value = pageSize;
parameters.Add(pageSizeParam);
SqlParameter topMaxParam = new SqlParameter("@_TopMax", SqlDbType.Int);
topMaxParam.Value = topMax;
parameters.Add(topMaxParam);
#endregion
}
//不知道总记录数，则需要在sql内部计算到底是否需要逆向查询来提高性能
else
{
#region 更复杂逻辑...
declareTotalRecords = true;
string tempDesc = isDesc ? " ASC" : " DESC";
sqlBuilder += @"
DECLARE @_Total int;
DECLARE @_TopMax int;
";
if (string.IsNullOrEmpty(TotalRecordsVariable) == false)
{
sqlBuilder += "SELECT @_Total = ";
sqlBuilder += TotalRecordsVariable;
sqlBuilder += ";";
}
else
{
sqlBuilder += "SELECT @_Total = COUNT(*) FROM ";
sqlBuilder += tableName;
if (WithNolock)
{
sqlBuilder += " WITH (NOLOCK) ";
}
sqlBuilder += whereString;
sqlBuilder += ";";
}
sqlBuilder += string.Concat(@"
SET @_TopMax = @_Size * @_Number;
--out page
IF (
(@_Total = 0)
OR
(@_Number > 1 AND (@_Size * (@_Number - 1)) >= (@_Total + @_Offset))
)
SELECT TOP 0 ", selectFields, " FROM ", tableName, @" WITH (NOLOCK);
--last page
ELSE IF @_TopMax >= (@_Total + @_Offset) BEGIN
SET @_TopMax = (@_Total + @_Offset) % @_Size;
IF @_TopMax = 0
SET @_TopMax = @_Size - (@_Offset % @_Size);
SELECT * FROM (
SELECT TOP (@_TopMax) ", selectFields, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc, ",", primaryKey, tempDesc, @"
) AS t1 ORDER BY ", sortField, desc, ",", primaryKey, desc, @";
END
");
//查询页数超过10页的时候才自动优化，否则提高不了太多性能，却反而使sql语句变得更长
if (pageNumber - (offset / pageSize) > 10)
{
sqlBuilder += string.Concat(@"
ELSE IF (@_TopMax - @_Offset) > (@_Total / 2) BEGIN
SET @_TopMax = @_Total - @_TopMax + @_Size + @_Offset;
SELECT ", selectFields, " FROM ", tableNameWithNolock, " WHERE ", primaryKey, @" IN (
SELECT TOP (@_Size) " , primaryKey, @" FROM (
SELECT TOP (@_TopMax) " , primaryKey, ",", sortField, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc, ",", primaryKey, tempDesc, @"
) AS t1 ORDER BY " , sortField, desc, ",", primaryKey, desc, @"
) ORDER BY ", sortField, desc, ",", primaryKey, desc, @";
END
");
}
sqlBuilder += string.Concat(@"
ELSE BEGIN
SET @_TopMax = @_TopMax - @_Offset;
SELECT ", selectFields, " FROM ", tableNameWithNolock, " WHERE ", primaryKey, @" IN (
SELECT TOP (@_Size) " , primaryKey, @" FROM (
SELECT TOP (@_TopMax) " , primaryKey, ",", sortField, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, desc, ",", primaryKey, desc, @"
) AS t1 ORDER BY " , sortField, tempDesc, ",", primaryKey, tempDesc, @"
) ORDER BY ", sortField, desc, ",", primaryKey, desc, @";
END
");
SqlParameter pageSizeParam = new SqlParameter("@_Size", SqlDbType.Int);
pageSizeParam.Value = pageSize;
parameters.Add(pageSizeParam);
SqlParameter pageNumberParam = new SqlParameter("@_Number", SqlDbType.Int);
pageNumberParam.Value = pageNumber;
parameters.Add(pageNumberParam);
SqlParameter offsetParam = new SqlParameter("@_Offset", SqlDbType.Int);
offsetParam.Value = offset;
parameters.Add(offsetParam);
#endregion
}
#endregion
}
else
{
#region sql server 2000的处理
//如果已知总记录数，则可以马上推算出到底是否需要逆向查询以提高性能
//(逆向查询指：如果要查询的页数超过总页数的一般，则倒过来查询数据。例如：最后一页的数据应该使用 top 直接查询)
if (totalRecords != null && totalRecords.Value > -1)
{
#region 杂逻辑...
string tempDesc1, tempDesc2;
int topMax = pageSize * pageNumber;
if (topMax - offset > totalRecords.Value / 2)
{
tempDesc1 = isDesc ? " ASC" : " DESC";
tempDesc2 = isDesc ? " DESC" : " ASC";
topMax = totalRecords.Value - topMax + pageSize + offset;
}
else
{
tempDesc1 = isDesc ? " DESC" : " ASC";
tempDesc2 = isDesc ? " ASC" : " DESC";
topMax -= offset;
}
sqlBuilder += string.Concat(@"
SELECT ", selectFields, " FROM ", tableNameWithNolock, " WHERE ", primaryKey, @" IN (
SELECT TOP ", pageSize.ToString(), " ", primaryKey, @" FROM (
SELECT TOP ", topMax.ToString(), " ", primaryKey, ",", sortField, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, tempDesc1, ",", primaryKey, tempDesc1, @"
) AS t1 ORDER BY ", sortField, tempDesc2, ",", primaryKey, tempDesc2, @"
) ORDER BY ", sortField, desc, ",", primaryKey, desc, @";
");
#endregion
}
//不知道总记录数，则需要在sql内部计算到底是否需要逆向查询来提高性能
else
{
#region 更复杂逻辑...
declareTotalRecords = true;
int topMax = pageSize * pageNumber;
string tempDesc = isDesc ? " ASC" : " DESC";
string paramString = buildParamStringForExecuteSql(beforeExecuteDeclare, condition, parameters);
sqlBuilder += @"
DECLARE @_Total int;
DECLARE @_TopMax int;
DECLARE @_Sql nvarchar(4000);
";
if (string.IsNullOrEmpty(TotalRecordsVariable) == false)
{
sqlBuilder += "SELECT @_Total = ";
sqlBuilder += TotalRecordsVariable;
sqlBuilder += ";";
}
else
{
sqlBuilder += "SELECT @_Total = COUNT(*) FROM ";
sqlBuilder += tableName;
if (WithNolock)
{
sqlBuilder += " WITH (NOLOCK) ";
}
sqlBuilder += whereString;
sqlBuilder += ";";
}
sqlBuilder += string.Concat(@"
SET @_TopMax = @_Size * @_Number;
--out page
IF (
(@_Total = 0)
OR
(@_Number > 1 AND (@_Size * (@_Number - 1)) >= (@_Total + @_Offset))
)
SELECT TOP 0 ", selectFields, " FROM ", tableName, @" WITH (NOLOCK);
--last page
ELSE IF @_TopMax >= (@_Total + @_Offset) BEGIN
SET @_TopMax = (@_Total + @_Offset) % @_Size;
IF @_TopMax = 0
SET @_TopMax = @_Size - (@_Offset % @_Size);
SET @_Sql = N'SELECT * FROM (
SELECT TOP ' + CAST(@_TopMax AS varchar(9)) + N' ", selectFields, " FROM ", tableNameWithNolock, whereString.Replace("'", "''"), " ORDER BY ", sortField, tempDesc, ",", primaryKey, tempDesc, @"
) AS t1 ORDER BY ", sortField, desc, ",", primaryKey, desc, @"';
EXEC sp_executesql @_Sql", paramString, @";
END
");
//查询页数超过10页的时候才自动优化，否则提高不了太多性能，却反而使sql语句变得更长
if (pageNumber - (offset / pageSize) > 10)
{
sqlBuilder += string.Concat(@"
ELSE IF (@_TopMax - @_Offset) > (@_Total / 2) BEGIN
SET @_TopMax = @_Total - @_TopMax + @_Size + @_Offset;
SET @_Sql = N'SELECT ", selectFields, " FROM ", tableNameWithNolock, " WHERE ", primaryKey, @" IN (
SELECT TOP " , pageSize.ToString(), " ", primaryKey, @" FROM (
SELECT TOP ' + CAST(@_TopMax AS varchar(9)) + N' " , primaryKey, ",", sortField, " FROM ", tableNameWithNolock, whereString.Replace("'", "''"), " ORDER BY ", sortField, tempDesc, ",", primaryKey, tempDesc, @"
) AS t1 ORDER BY " , sortField, desc, ",", primaryKey, desc, @"
) ORDER BY ", sortField, desc, ",", primaryKey, desc, @"';
EXEC sp_executesql @_Sql", paramString, @";
END
");
}
sqlBuilder += string.Concat(@"
ELSE BEGIN
--SET @_TopMax = @_TopMax - @_Offset;
SELECT ", selectFields, " FROM ", tableNameWithNolock, " WHERE ", primaryKey, @" IN (
SELECT TOP " , pageSize.ToString(), " ", primaryKey, @" FROM (
SELECT TOP " , (topMax - offset).ToString(), " ", primaryKey, ",", sortField, " FROM ", tableNameWithNolock, whereString, " ORDER BY ", sortField, desc, ",", primaryKey, desc, @"
) AS t1 ORDER BY " , sortField, tempDesc, ",", primaryKey, tempDesc, @"
) ORDER BY ", sortField, desc, ",", primaryKey, desc, @";
END
");
SqlParameter pageSizeParam = new SqlParameter("@_Size", SqlDbType.Int);
pageSizeParam.Value = pageSize;
parameters.Add(pageSizeParam);
SqlParameter pageNumberParam = new SqlParameter("@_Number", SqlDbType.Int);
pageNumberParam.Value = pageNumber;
parameters.Add(pageNumberParam);
SqlParameter offsetParam = new SqlParameter("@_Offset", SqlDbType.Int);
offsetParam.Value = offset;
parameters.Add(offsetParam);
#endregion
}
#endregion
}
#endregion
}
//如果需要返回总记录数，返回。如果已经知道总记录数，此处无需再取
if (returnTotalRecords)
{
if (string.IsNullOrEmpty(this.TotalRecordsVariable) == false)
{
sqlBuilder += "\r\nSELECT ";
sqlBuilder += this.TotalRecordsVariable;
sqlBuilder += ";";
}
else if (knowTotalRecords || declareTotalRecords)
{
sqlBuilder += "\r\nSELECT @_Total;";
if (knowTotalRecords)
{
SqlParameter totalRecordsParam = new SqlParameter("@_Total", SqlDbType.Int);
totalRecordsParam.Value = totalRecords.Value;
parameters.Add(totalRecordsParam);
}
}
else
{
sqlBuilder += "SELECT COUNT(*) FROM ";
sqlBuilder += tableName;
if (WithNolock)
{
sqlBuilder += " WITH (NOLOCK) ";
}
sqlBuilder += whereString;
sqlBuilder += ";";
}
}
return(sqlBuilder.ToString());
}
/// <summary>
/// 生成sp_executesql专用的参数字符串
/// </summary>
/// <param name="beforeExecuteDeclare"></param>
/// <param name="condition"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private string buildParamStringForExecuteSql(DeclareVariableCollection beforeExecuteDeclare, string condition, SqlParameterCollection parameters)
{
if (string.IsNullOrEmpty(condition))// || parameters == null || parameters.Count == 0)
{
return(string.Empty);
}
if (
(beforeExecuteDeclare == null || beforeExecuteDeclare.Count == 0)
&&
(parameters == null || parameters.Count == 0)
)
{
return(string.Empty);
}
StringBuilder paramDeclare = new StringBuilder();
StringBuilder paramValues = new StringBuilder();
if (parameters != null && parameters.Count > 0)
{
#region 根据参数列表生成sp_executesql专用的参数字符串
foreach (SqlParameter param in parameters)
{
if (condition.IndexOf(param.ParameterName) == -1)
{
continue;
}
paramDeclare.Append(",");
DeclareVariable.BuildDeclareVariableSql(paramDeclare, param.ParameterName, param.SqlDbType, param.Size);
paramValues.Append(",");
paramValues.Append(param.ParameterName);
paramValues.Append("=");
paramValues.Append(param.ParameterName);
}
#endregion
}
if (beforeExecuteDeclare != null && beforeExecuteDeclare.Count > 0)
{
#region 根据BeforeExecute的变量声明列表生成sp_executesql专用的参数字符串
foreach (DeclareVariable declare in beforeExecuteDeclare)
{
if (condition.IndexOf(declare.Name) == -1)
{
continue;
}
paramDeclare.Append(",");
declare.BuildDeclareVariableSql(paramDeclare);
paramValues.Append(",");
paramValues.Append(declare.Name);
paramValues.Append("=");
paramValues.Append(declare.Name);
}
#endregion
}
if (paramDeclare.Length > 0)
{
paramDeclare.Insert(1, "N'");
paramDeclare.Append("'");
paramDeclare.Append(paramValues.ToString());
}
return(paramDeclare.ToString());
}
