存储过程分页以及实现.docx

public DataSet RunProcedure(string storedProcName, IDataParameter parameters, string tableName )using (SqlConnection connection = new SqlConnection(connectionString)DataSet dataSet = new DataSet();connection.Open();SqlDataAdapter sqlDA = new SqlDataAdapter();SqlCommand command = new SqlCommand( storedProcName, connection );command.CommandType = CommandType.StoredProcedure;foreach (SqlParameter parameter in parameters)command.Parameters.Add( parameter );sqlDA.SelectCommand = command;sqlDA.Fill( dataSet, tableName );connection.Close();return dataSet;一个高效分页存储过程(使用二分法,测试数据为200W以上)-建表create table tb_TestTable(id int identity(1,1) primary key not null,userName nvarchar(50) null,userPwd nvarchar(50) null,useremail nvarchar(200) null)-插入数据declare count intset count = 1while count0) +strCondition set strTmp=SqlSelect+ Counts=+SqlCounts+ FROM +tblName + where (10) +strCondition set strID = From + tblName + where (10) + strCondition end-取得查询结果总数量- exec sp_executesql strTmp,NCounts int out ,Counts out declare tmpCounts int if Counts = 0 set tmpCounts = 1 else set tmpCounts = Counts-取得分页总数 set pageCount=(tmpCounts+pageSize-1)/pageSize/*/*/*/*当前页大于总页数 取最后一页*/ if pagepageCount set page=pageCount/*-数据分页2分处理-*/ declare pageIndex int -总数/页大小 declare lastcount int -总数%页大小set pageIndex = tmpCounts/pageSize set lastcount = tmpCounts%pageSize if lastcount 0 set pageIndex = pageIndex + 1 else set lastcount = pagesize-/*显示分页 if strCondition is null or strCondition= -没有设置显示条件 begin if pageIndex2 or page=pageIndex / 2 + pageIndex % 2 -前半部分数据处理 begin if page=1 set strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName+ order by + fldSort + + strFSortType else begin if Sort=1 begin set strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName + where +ID + (select max(+ ID +) from (+ SqlSelect+ top + CAST(pageSize*(page-1) as Varchar(20) + + ID + from +tblName+ order by + fldSort + + strFSortType+) AS TBMinID) + order by + fldSort + + strFSortType end end end else begin set page = pageIndex-page+1 -后半部分数据处理 if page (select max(+ ID +) from( + SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + from +tblName+ order by + fldSort + + strSortType +) AS TBMaxID)+ order by + fldSort + + strSortType +) AS TempTB+ order by + fldSort + + strFSortType end else begin set strTmp=SqlSelect+ * from (+SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName + where +ID+ (select min(+ ID +) from( + SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + from +tblName+ order by + fldSort + + strSortType +) AS TBMaxID) + order by + fldSort + + strSortType+) AS TempTB+ order by + fldSort + + strFSortType end end end else -有查询条件 begin if pageIndex2 or page=pageIndex / 2 + pageIndex % 2 -前半部分数据处理 begin if page=1 set strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName + where 1=1 + strCondition + order by + fldSort + + strFSortType else if(Sort=1) begin set strTmp=SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName + where +ID + (select max(+ ID +) from (+ SqlSelect+ top + CAST(pageSize*(page-1) as Varchar(20) + + ID + from +tblName + where (1=1) + strCondition + order by + fldSort + + strFSortType+) AS TBMinID) + + strCondition + order by + fldSort + + strFSortType end end else begin set page = pageIndex-page+1 -后半部分数据处理 if page (select max(+ ID +) from( + SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + from +tblName + where (1=1) + strCondition + order by + fldSort + + strSortType+) AS TBMaxID) + + strCondition + order by + fldSort + + strSortType+) AS TempTB + order by + fldSort + + strFSortType else set strTmp=SqlSelect+ * from (+SqlSelect+ top + CAST(pageSize as VARCHAR(4)+ + fldName+ from +tblName + where +ID+ (select min(+ ID +) from( + SqlSelect+ top + CAST(pageSize*(page-2)+lastcount as Varchar(20) + + ID + from +tblName + where (1=1) + strCondition + order by + fldSort + + strSortType+) AS TBMaxID) + + strCondition + order by + fldSort + + strSortType+) AS TempTB + order by + fldSort + + strFSortType end end-返回查询结果- exec sp_executesql strTmp select datediff(ms,timediff,getdate() as 耗时 -print strTmp set nocount off goexec proc_paged_2part_selectMax tb_testTable,ID,userName,userPWD,userEmail,22,100000,ID,0,null,ID,01. 在项目中添加AspNetPager.dll引用。2.*.aspx代码： 3.*.aspx.cs代码：protected void Page_Load(object sender, EventArgs e) GridViewBind(); private void GridViewBind() SqlConnection con = new SqlConnection(server=8;database=XYGL;uid=hatest;pwd=hatest); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = P_newpager; int pageindex = 0; string strWhere = ; if (AspNetPager1.CurrentPageIndex 1) pageindex = 1; else pageindex = AspNetPager1.CurrentPageIndex; SqlParameter parameters = new SqlParameter(tblname,SqlDbType.VarChar,255), new SqlParameter(strGetFields,SqlDbType.VarChar,1000), new SqlParameter(fldName,SqlDbType.VarChar,255), new SqlParameter(PageSize,SqlDbType.Int,4), new SqlParameter(PageIndex,SqlDbType.Int,4), new SqlParameter(doCount,SqlDbType.Bit), new SqlParameter(OrderType,SqlDbType.Bit), new SqlParameter(strWhere,SqlDbType.VarChar,2000) ; parameters0.Value = usiKHKHDJ;/表或视图名 parameters1.Value = ID,JSID,KHID,KHDM,MC,DLRQ;/显示字段 parameters2.Value = ID;/以某字段排序 parameters3.Value = AspNetPager1.PageSize;/多少条为一页 parameters4.Value = pageindex;/索引页 parameters5.Value = 0;/默认就行了 parameters6.Value = 1;/这里的是降序，为升序 parameters7.Value = strWhere;/查询条件组合 foreach (SqlParameter p in parameters) cmd.Parameters.Add(p); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); /动态设置用户自定义文本内容 AspNetPager1.RecordCount = int.Parse(ds.Tables1.Rows00.ToString(); AspNetPager1.CustomInfoHTML = 共 + AspNetPager1.RecordCount.ToString() + 条,; AspNetPager1.CustomInfoHTML += + AspNetPager1.PageCount.ToString() + 页,; AspNetPager1.CustomInfoHTML += 第 + AspNetPager1.CurrentPageIndex.ToString() + 页; GridView1.DataSource = ds.Tables0.DefaultView; GridView1.DataBind(); protected void AspNetPager1_PageChanged(object sender, EventArgs e) GridViewBind(); 4.存储过程：CREATE PROCEDURE dbo.P_newpagertblname VARCHAR(255), - 表名strGetFields nvarchar(1000) = *, - 需要返回的列fldName varchar(255)=, - 排序的字段名PageSize int = 10, - 页尺寸PageIndex int = 1, - 页码doCount bit = 0, - 返回, 非0 值则返回记录总数OrderType bit = 0, - 设置排序类型, 非0 值则降序strWhere varchar(1500) = - 查询条件(注意: 不要加where)ASdeclare strSQL varchar(5000) - 主语句declare strTmp varchar(110) - 临时变量declare strOrder varchar(400) - 排序类型if doCount != 0begin if strWhere != set strSQL = select count(*) as Total from + tblName + where 1=1 + strWhere else set strSQL = select count(*) as Total from + tblName + end -以上代码的意思是如果doCount传递过来的不是，就执行总数统计。以下的所有代码都是doCount为的情况：elsebegin if OrderType != 0-降序 begin set strTmp = (select max set strOrder = order by + fldName + asc end if PageIndex = 1 begin if strWhere != set strSQL = select top + str(PageSize) + + strGetFields + from + tblName + where 1=1 + strWhere + + strOrder else set strSQL = select top + str(PageSize) + + strGetFields + from + tblName + + strOrder-如果是第一页就执行以上代码，这样会加快执行速度 end else begin-以下代码赋予了strSQL以真正执行的SQL代码 set strSQL = select top + str(PageSize) + + strGetFields + from + tblName + where + fldName + + strTmp + (+ fldName + ) from (select top + str(PageIndex-1)*PageSize) + + fldName + from + tblName + + strOrder + ) as tblTmp) + strOrder if strWhere != set strSQL = select top + str(PageSize) + +strGetFields+ from + tblName + where + fldName + + strTmp + ( + fldName + ) from (select top + str(PageIndex-1)*PageSize) + + fldName + from + tblName + where 1=1 + strWhere + + strOrder + ) as tblTmp) and 1=1 + strWhere + + strOrder end if strWhere != -得到记录的总行数 set strSQL =strSQL+ ; select count(*) as Total from + tblName + where 1=1 + strWhere else set strSQL =strSQL+ ; select count(*) as Total from + tblName + endexec (strSQL)RETURNCREATE PROCEDURE PageCuttblName varchar(255), - 表名strGetFields varchar(1000) = *, - 需要返回的列 fldName varchar(255)=, - 排序的字段名 PageSize int = 10, - 页尺寸 PageIndex int = 1, - 页码 OrderType bit = 0, - 设置排序类型, 非 0 值则降序 strWhere varchar(1500) = ,- 查询条件 (注意: 不要加 where) Counts int out - 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用AS set nocount ondeclare strSQL varchar(5000) - 主语句 declare CountSQL nVarchar(4000)declare strTmp varchar(110) - 临时变量 declare strOrder varchar(400) - 排序类型if OrderType != 0 begin set strTmp = (select max set strOrder = order by + fldName + asc endif PageIndex = 1 begin if strWhere != set strSQL = select top + str(PageSize) +strGetFields+ from + tblName + where + strWhere + + strOrderelse set strSQL = select top + str(PageSize) +strGetFields+ from + tblName + + strOrder -如果是第一页就执行以上代码，这样会加快执行速度 endelse begin -以下代码赋予了strSQL以真正执行的SQL代码 set strSQL = select top + str(PageSize) +strGetFields+ from + tblName + where + fldName + + strTmp + (+ fldName + ) from (select top + str(PageIndex-1)*PageSize) + + fldName + from + tblName + + strOrder + ) as tblTmp)+ strOrder if strWhere != set strSQL = select top + str(PageSize) + +strGetFields+ from + tblName + where + fldName + + strTmp + ( + fldName + ) from (select top + str(PageIndex-1)*PageSize) + + fldName + from + tblName + where + strWhere + strOrder + ) as tblTmp) and + strWhere + + strOrder endif strWhere != set CountSQL=select Counts=count(*) from + tblName + where + strWhere +elseset CountSQL=select Counts=count(*) from + tblName + exec sp_executesql CountSQL, NCounts int out ,Counts outexec ( strSQL)set nocount offGO+后台调用 protected void LinkButton_Click(ob