（1）存儲過程建立

USE [NewPlat] GO

/****** Object:? StoredProcedure [dbo].[usp_PagingLarge]??? Script Date: 07/11/2013 08:27:44 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

Create PROCEDURE [dbo].[usp_PagingLarge]?

@TableNames VARCHAR(200),???? --表名，可以是多個表，但不能用別名

@PrimaryKey VARCHAR(100),???? --主鍵，可以為空，但@Order為空時該值不能為空?

@Fields???? VARCHAR(4000),???????? --要取出的字段，可以是多個表的字段，可以為空，為空表示select *?

@PageSize INT,???????????? --每頁記錄數?

@CurrentPage INT,???????? --當前頁，0表示第1頁?

@Filter VARCHAR(4000) = '',???? --條件，可以為空，不用填 where?

@Group VARCHAR(200) = '',???? --分組依據，可以為空，不用填 group by?

@Order VARCHAR(200) = '',??? --排序，可以為空，為空默認按主鍵升序排列，不用填 order by?

@RecordCount int OUTPUT???????????? --總記錄數,自己增加（總記錄數）

?AS?

BEGIN? ????

DECLARE @SortColumn VARCHAR(200)? ??

DECLARE @Operator CHAR(2)? ????

DECLARE @SortTable VARCHAR(200)? ????

DECLARE @SortName VARCHAR(200)? ????

IF @Fields = ''? ????????

SET @Fields = '*'? ????

IF @Filter = ''? ????????

SET @Filter = 'Where 1=1'? ????

ELSE? ????????

SET @Filter = 'Where ' +?? @Filter? ????

IF @Group <>''? ????????

SET @Group = 'GROUP BY ' + @Group? ? ????

IF @Order <> ''? ????

BEGIN? ????????

DECLARE @pos1 INT, @pos2 INT? ????????

SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')? ????????

IF CHARINDEX(' DESC', @Order) > 0? ?????

IF CHARINDEX(' ASC', @Order) > 0? ????????????

BEGIN? ????????????????

IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)? ????????????????????

SET @Operator = '<='? ????????????????

ELSE? ????????????????????

SET @Operator = '>='? ????????????

END? ???????????

? ELSE? ???????????????

? SET @Operator = '<='? ????????

ELSE? ????????????

SET @Operator = '>='? ????????

SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')? ????????

SET @pos1 = CHARINDEX(',', @SortColumn)????????

? IF @pos1 > 0? ????????????

SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)? ????????

SET @pos2 = CHARINDEX('.', @SortColumn)? ????????

IF @pos2 > 0? ????????

BEGIN? ????????????

SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)? ????????????

IF @pos1 > 0?? ????????????????

SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)? ????????????

ELSE? ????????????????

SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)? ????????

END? ????????

ELSE? ????????

BEGIN? ????????????

SET @SortTable = @TableNames? ????????????

SET @SortName = @SortColumn? ????????

END? ????

END? ????

ELSE? ????

BEGIN? ????????

SET @SortColumn = @PrimaryKey? ????????

SET @SortTable = @TableNames? ????????

SET @SortName = @SortColumn? ????????

SET @Order = @SortColumn? ????????

SET @Operator = '>='? ????

END? ? ????

DECLARE @type varchar(50)? ????

DECLARE @prec int? ????

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 @TopRows INT? ????

SET @TopRows = @PageSize * @CurrentPage + 1? ????

print @TopRows?

print @Operator? ????

EXEC('? ???????? DECLARE @SortColumnBegin ' + @type + '? ???????? SET ROWCOUNT ' + @TopRows + '? ???????? Select @SortColumnBegin=' + @SortColumn + ' FROM?? ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' orDER BY ' + @Order + '? ???????? SET ROWCOUNT ' + @PageSize + '? ???????? Select ' + @Fields + ' FROM?? ' + @TableNames + ' ' + @Filter?? + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' orDER BY ' + @Order + '????? ???? ')????? ?????

IF @RecordCount IS NULL?

BEGIN? ??? DECLARE @sql nvarchar(4000)? ???

SET @sql=N'SELECT @RecordCount=COUNT(*)'? ??????? +N' FROM '+@TableNames ? ??????? +N' '+@Filter ? ???

EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT?

END ? ?

END?

GO

?

（2）實現多表分頁的函數（c#代碼）

?public static DataTable ExecMultiPageList(string tableName, string iDName, string Fields, int pageSize, int currentPage, string Filter, string Group, string Order, List<SqlParameter> list, out int rowCount)
??????? {
??????????? rowCount = 0;
??????????? SqlConnection connection = new SqlConnection(connectionString);
??????????? SqlParameter[] parameters = {??
??????????????????? new SqlParameter("@TableNames",SqlDbType.VarChar,200),?
??????????????????? new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),?
??????????????????? new SqlParameter("@Fields",SqlDbType.VarChar,200),?
??????????????????? new SqlParameter("@PageSize",SqlDbType.Int,4),?
??????????????????? new SqlParameter("@CurrentPage",SqlDbType.Int,4),?
??????????????????? new SqlParameter("@Filter",SqlDbType.VarChar,200),?
??????????????????? new SqlParameter("@Group",SqlDbType.VarChar,200),?
??????????????????? new SqlParameter("@Order",SqlDbType.VarChar,200),?
??????????????????? new SqlParameter("@RecordCount",SqlDbType.Int,4)?
??????????????? };//參數列表?
??????????? parameters[0].Value = tableName;
??????????? parameters[1].Value = iDName;
??????????? parameters[2].Value = Fields;
??????????? parameters[3].Value = pageSize;
??????????? parameters[4].Value = currentPage;
??????????? parameters[5].Value = Filter;
??????????? parameters[6].Value = Group;
??????????? parameters[7].Value = Order;//參數對應值?
??????????? parameters[8].Value = rowCount;
??????????? parameters[8].Direction = ParameterDirection.Output;
??????????? SqlCommand cmd = new SqlCommand();
??????????? cmd.Connection = connection;
??????????? cmd.CommandText = "usp_PagingLarge";//存儲過程名?
??????????? cmd.CommandType = CommandType.StoredProcedure;//類型?
??????????? cmd.Parameters.AddRange(parameters);
??????????? SqlDataAdapter da = new SqlDataAdapter(cmd);
??????????? DataTable dt = new DataTable();
??????????? da.Fill(dt);
??????????? connection.Close();
??????????? rowCount = Convert.ToInt32(parameters[8].Value);//輸出?
??????????? return dt;
??????? }

?

（3）調用多表分頁的方法

??????? public string GetFenye(out int recordcount, NameValueCollection form,string loginid,int flag) ??????? { ????

??????? QueryModel queryMdodel = QueryModel.getQueryModel(form);

???????? string tablename = "Email_MailSender left join Email_MailInfo on Email_MailSender.mailid=Email_MailInfo.Mailid "; ??????

? ????? string iDName = "Email_MailSender.mailsenderid"; ???????????

?????? ?string Fields = "Email_MailSender.*,Email_MailInfo.mailtopic,Email_MailInfo.sendtime"; ???????????

????????int PageSize = queryMdodel.rows;????????????????????????????? ???????????

?????? ?int PageIndex = queryMdodel.page == 0 ? 0 : queryMdodel.page - 1; ???????????

?????? ?string Filter = "Email_MailSender.senderid = '" + loginid + "' and Email_MailSender.sendstatus ="+flag; ???????????

??????? string group = ""; ???????????

?????? ?string order = "Email_MailSender.mailsenderid asc"; ???????????

???????DataTable dt = DbHelperSQL.ExecMultiPageList(tablename, iDName, Fields, PageSize, PageIndex, Filter, group, order,queryMdodel.listPar ,out recordcount); ???????????

????? ?string strjson = Newtonsoft.Json.JsonConvert.SerializeObject(dt); ??????????? return strjson; ??

????? }

?

?

sqlserver 存儲過程學習筆記（二） 在項目中的應用<多表分頁>