存储过程代码: --drop procedure p_page --go create procedure p_page ( @Tables varchar(1000), --表名如testtable @PrimaryKey varchar(100),--表的主键,必须唯一性 @Sort varchar(200) = NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段) @CurrentPage int = 1,--当前页 @PageSize int = 10,---每页大小 @Fields varchar(1000) = '*',--显示的字段列表 @Filter varchar(1000) = NULL,--条件语句,不加where,如 f_id>3 @Group varchar(1000) = NULL,--分组字段 @TotalPage int output --返回总页数 ) WITH ENCRYPTION ---加密存储 AS SET NOCOUNT ON Declare @intResult Int Begin Tran DECLARE @sql nvarchar(4000) if @Filter is null or @Filter='' set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables else set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数 select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数 IF @Sort IS NULL or @Sort = '' SET @Sort = @PrimaryKey DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') SET @operator = '<=' END ELSE IF CHARINDEX('ASC', @Sort) > 0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'ASC', '') SET @operator = '>=' END ELSE BEGIN SET @strSortColumn = @SORT SET @operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE IF CHARINDEX(' ', LTRIM(@Tables)) > 0 BEGIN SET @SortTable = SUBSTRING(LTRIM(@Tables), 0, CHARINDEX(' ', LTRIM(@Tables))) SET @SortName = @strSortColumn END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype Where o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50) DECLARE @strStartRow varchar(50) DECLARE @strFilter varchar(1000) DECLARE @strSimpleFilter varchar(1000) DECLARE @strGroup varchar(1000) IF @CurrentPage < 1 SET @CurrentPage = 1 SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50)) IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' ' SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE SET @strGroup = '' set @sql = 'DECLARE @SortColumn ' + @type + ' SET ROWCOUNT ' + @strStartRow + ' Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' SET ROWCOUNT ' + @strPageSize + ' Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' ' exec(@sql) --print @sql ------------------------------------------------------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @intResult ---返回记录总数 End GO C#代码片段: public IList<BookRecordEntity> SelectAll(int currentPage, int pageSize, out int totalPage, out int totalSize, bool historyState, string searchType, string searchKeyword, string beginDate, string endDate) { StringBuilder builder = new StringBuilder(" 1=1 "); if (historyState) { builder.Append(" AND RoomBook.HistoryState = '1'"); } else { builder.Append(" AND RoomBook.HistoryState = '0'"); } switch (searchType) { case "Name": if (!string.IsNullOrEmpty(searchKeyword)) { builder.Append(" AND RoomBook.Name LIKE '%" + DBHelper.FilterBadChar(searchKeyword) + "%'"); } break; case "IDNumber": if (!string.IsNullOrEmpty(searchKeyword)) { builder.Append(" AND RoomBook.IDNumber LIKE '%" + DBHelper.FilterBadChar(searchKeyword) + "%'"); } break; case "Email": if (!string.IsNullOrEmpty(searchKeyword)) { builder.Append(" AND RoomBook.Email LIKE '%" + DBHelper.FilterBadChar(searchKeyword) + "%'"); } break; case "Phone": if (!string.IsNullOrEmpty(searchKeyword)) { builder.Append(" AND RoomBook.Phone LIKE '%" + DBHelper.FilterBadChar(searchKeyword) + "%'"); } break; case "Date": if (!string.IsNullOrEmpty(beginDate)) { if (string.IsNullOrEmpty(endDate)) { builder.Append(" AND [Begin] >='" + DataConverter.CDate(beginDate).ToShortDateString() + "'"); } else { builder.Append(" AND [Begin] >='" + DataConverter.CDate(beginDate).ToShortDateString() + "' AND [Begin] <'" + DataConverter.CDate(endDate).AddDays(1.0).ToShortDateString() + "'"); } break; } if (!string.IsNullOrEmpty(endDate)) { builder.Append(" AND [Begin] <'" + DataConverter.CDate(endDate).AddDays(1.0).ToShortDateString() + "'"); } break; } string tableString = "RoomBook LEFT JOIN (SELECT BookGuid, SUM(Amount) AS RoomAmount FROM BookRoomType GROUP BY BookGuid) AS b ON RoomBook.Guid = b.BookGuid "; builder.Append(" AND (RoomBook.State = 0 OR RoomBook.State = 1) "); Database database = DatabaseFactory.CreateDatabase(); IList<BookRecordEntity> list = new List<BookRecordEntity>(); DbCommand storedProcCommand = database.GetStoredProcCommand("p_page"); database.AddInParameter(storedProcCommand, "@Tables", DbType.String, tableString); // 表名如testtable database.AddInParameter(storedProcCommand, "@PrimaryKey", DbType.String, "ID"); // 表的主键,必须唯一性 database.AddInParameter(storedProcCommand, "@Sort", DbType.String, "ID ASC"); // 排序字段如f_Name asc或f_name desc(注意只能有一个排序字段) database.AddInParameter(storedProcCommand, "@CurrentPage", DbType.Int32, currentPage); // 当前页 database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, pageSize); // 每页大小 database.AddInParameter(storedProcCommand, "@Fields", DbType.String, "*"); // 显示的字段列表 database.AddInParameter(storedProcCommand, "@Filter", DbType.String, builder.ToString()); // 条件语句,不加where,如 f_id>3 database.AddInParameter(storedProcCommand, "@Group", DbType.String, ""); // 分组字段 database.AddOutParameter(storedProcCommand, "@TotalPage", DbType.Int32, 4); // 返回总页数 database.AddParameter(storedProcCommand, "@return", DbType.Int32, ParameterDirection.ReturnValue, "@return", DataRowVersion.Default, "1"); // 返回记录总数 using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand))) { while (reader.Read()) { BookRecordEntity entity = GetEntity(reader); entity.RoomQuantities = reader.GetInt32("RoomAmount"); list.Add(entity); } } totalPage = (int)database.GetParameterValue(storedProcCommand, "@TotalPage"); totalSize = (int)database.GetParameterValue(storedProcCommand, "@return"); return list; }