存儲(chǔ)過程分頁以及實(shí)現(xiàn).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;一個(gè)高效分頁存儲(chǔ)過程(使用二分法,測試數(shù)據(jù)為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)-插入數(shù)據(jù)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-取得查詢結(jié)果總數(shù)量- exec sp_executesql strTmp,NCounts int out ,Counts out declare tmpCounts int if Counts = 0 set tmpCounts = 1 else set tmpCounts = Counts-取得分頁總數(shù) set pageCount=(tmpCounts+pageSize-1)/pageSize/*/*/*/*當(dāng)前頁大于總頁數(shù) 取最后一頁*/ if pagepageCount set page=pageCount/*-數(shù)據(jù)分頁2分處理-*/ declare pageIndex int -總數(shù)/頁大小 declare lastcount int -總數(shù)%頁大小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= -沒有設(shè)置顯示條件 begin if pageIndex2 or page=pageIndex / 2 + pageIndex % 2 -前半部分?jǐn)?shù)據(jù)處理 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 -后半部分?jǐn)?shù)據(jù)處理 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 -前半部分?jǐn)?shù)據(jù)處理 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 -后半部分?jǐn)?shù)據(jù)處理 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-返回查詢結(jié)果- exec sp_executesql strTmp select datediff(ms,timediff,getdate() as 耗時(shí) -print strTmp set nocount off goexec proc_paged_2part_selectMax tb_testTable,ID,userName,userPWD,userEmail,22,100000,ID,0,null,ID,01. 在項(xiàng)目中添加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;/默認(rèn)就行了 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); /動(dòng)態(tài)設(shè)置用戶自定義文本內(nèi)容 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.存儲(chǔ)過程：CREATE PROCEDURE dbo.P_newpagertblname VARCHAR(255), - 表名strGetFields nvarchar(1000) = *, - 需要返回的列fldName varchar(255)=, - 排序的字段名PageSize int = 10, - 頁尺寸PageIndex int = 1, - 頁碼doCount bit = 0, - 返回, 非0 值則返回記錄總數(shù)OrderType bit = 0, - 設(shè)置排序類型, 非0 值則降序strWhere varchar(1500) = - 查詢條件(注意: 不要加where)ASdeclare strSQL varchar(5000) - 主語句declare strTmp varchar(110) - 臨時(shí)變量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傳遞過來的不是，就執(zhí)行總數(shù)統(tǒng)計(jì)。以下的所有代碼都是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-如果是第一頁就執(zhí)行以上代碼，這樣會(huì)加快執(zhí)行速度 end else begin-以下代碼賦予了strSQL以真正執(zhí)行的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 != -得到記錄的總行數(shù) 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, - 設(shè)置排序類型, 非 0 值則降序 strWhere varchar(1500) = ,- 查詢條件 (注意: 不要加 where) Counts int out - 返回記錄總數(shù), 非 0 值則返回 這里作運(yùn)行后返回的記錄總數(shù)分頁用AS set nocount ondeclare strSQL varchar(5000) - 主語句 declare CountSQL nVarchar(4000)declare strTmp varchar(110) - 臨時(shí)變量 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 -如果是第一頁就執(zhí)行以上代碼，這樣會(huì)加快執(zhí)行速度 endelse begin -以下代碼賦予了strSQL以真正執(zhí)行的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+后臺調(diào)用 protected void LinkButton_Click(ob