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