CREATE PROCEDURE [dbo].[RecordFromPage] @SelectList VARCHAR(max), @TableSource VARCHAR(100), @SearchCondition VARCHAR(max), @OrderExpression VARCHAR(1000), @PageIndex INT = 1, @PageSize INT = 10, @TotalCount int output AS BEGIN IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ‘‘ BEGIN SET @SelectList = ‘*‘ END PRINT @SelectList SET @SearchCondition = ISNULL(@SearchCondition,‘‘) SET @SearchCondition = LTRIM(RTRIM(@SearchCondition)) IF @SearchCondition <> ‘‘ BEGIN IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> ‘WHERE‘ BEGIN SET @SearchCondition = ‘WHERE ‘ + @SearchCondition END END PRINT @SearchCondition SET @OrderExpression = ISNULL(@OrderExpression,‘‘) SET @OrderExpression = LTRIM(RTRIM(@OrderExpression)) IF @OrderExpression <> ‘‘ BEGIN IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> ‘WHERE‘ BEGIN SET @OrderExpression = ‘ORDER BY ‘ + @OrderExpression END END PRINT @OrderExpression IF @PageIndex IS NULL OR @PageIndex < 1 BEGIN SET @PageIndex = 1 END PRINT @PageIndex IF @PageSize IS NULL OR @PageSize < 1 BEGIN SET @PageSize = 10 END PRINT @PageSize DECLARE @Total int DECLARE @Sql nVarchar(max) SET @Sql=(N‘SELECT @Total=Count(*) FROM ‘ + @TableSource +‘ ‘+ @SearchCondition) print @sql Exec sp_executesql @Sql, N‘@Total Int Out‘,@Total Out set @TotalCount=@Total DECLARE @SqlQuery VARCHAR(max) SET @SqlQuery=‘SELECT ‘+@SelectList+‘,RowNumber FROM (SELECT ‘ + @SelectList + ‘,ROW_NUMBER() OVER( ‘+ @OrderExpression +‘) AS RowNumber FROM ‘+@TableSource+‘ ‘+ @SearchCondition +‘) AS RowNumberTableSource WHERE RowNumber BETWEEN ‘ + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR) + ‘ AND ‘ + CAST((@PageIndex * @PageSize) AS VARCHAR) -- ORDER BY ‘ + @OrderExpression PRINT @SqlQuery SET NOCOUNT ON EXECUTE(@SqlQuery) SET NOCOUNT OFF END
/// <summary>
/// 查询接口
/// </summary>
public interface IQuery
{
string Column { get; set; }
//列名
string TableName { get; set; }
//表名
string OrderBy { get; set; }
//查询的"OrderBy"语句，不包含OrderBy，例子：ID DESC
string BulidQuery();
//查询的"where"语句
string BulidSelect(string where, string tableName = "");
//查语句
}
/// <summary> /// 分页实现简单查询 /// </summary> public class CommonPageSql { /// <summary> /// 分页获取数据列表 适用于SQL2005和SQL2008 /// </summary> /// <param>数据库连接字符串</param> /// <param>页索引 从0开始</param> /// <param>每页记录数</param> /// <param>查询接口</param> /// <param>输出参数</param> /// <returns>DataRead数据集</returns> public static IDataReader GetDataReaderByPager(string con, int pageindex, int pagesize, IQuery query, out SqlParameter p) { string cmd = "RecordFromPage"; SqlParameter[] para = SqlHelperParameterCache.GetSpParameterSet(con, cmd); para[0].Value = query.Column; para[1].Value = query.TableName; para[2].Value = query.BulidQuery(); para[3].Value = query.OrderBy; para[4].Value = pageindex; para[5].Value = pagesize; para[6].Direction = ParameterDirection.Output; //SqlParameter[] para = new SqlParameter[7]; //para[0] = new SqlParameter("@SelectList", query.Column); //para[1] = new SqlParameter("@TableSource", query.TableName); //para[2] = new SqlParameter("@SearchCondition", query.BulidQuery()); //para[3] = new SqlParameter("@OrderExpression", query.OrderBy); //para[4] = new SqlParameter("@pageindex", pageindex); //para[5] = new SqlParameter("@pagesize", pagesize); //para[6] = new SqlParameter("@TotalCount", SqlDbType.Int); //para[6].Direction = ParameterDirection.Output; IDataReader reader = SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, cmd, para); p = para[6]; return reader; } /// <summary> /// 分页获取数据列表 适用于SQL2005和SQL2008 /// </summary> /// <param>数据库连接字符串</param> /// <param>页索引 从0开始</param> /// <param>每页记录数</param> /// <param>查询接口</param> /// <param>输出参数</param> /// <returns>DataTable数据集</returns> public static DataTable GetDataByPager(string con, int pageindex, int pagesize, IQuery query, out SqlParameter p) { string cmd = "RecordFromPage"; SqlParameter[] para = SqlHelperParameterCache.GetSpParameterSet(con, cmd); para[0].Value = query.Column; para[1].Value = query.TableName; para[2].Value = query.BulidQuery(); para[3].Value = query.OrderBy; para[4].Value = pageindex; para[5].Value = pagesize; para[6].Direction = ParameterDirection.Output; DataTable datatable = SqlHelper.ExecuteDataset(con,CommandType.StoredProcedure,cmd,para).Tables[0]; p = para[6]; return datatable; } }