建立表格並添加300萬資料
use StoredCREATE TABLE UserInfo( --建立表id int IDENTITY(1,1) PRIMARY KEY not null,--添加主鍵和識別欄位UserName varchar(50) ) declare @i int --添加3百萬資料，大概4分鐘時間set @i=1while @i<3000000begininsert into UserInfo (UserName) values(@i)set @i=@i+1end
預存程序T-SQL
create PROCEDURE [dbo].[GetDataList]( @TableName varchar(5000), --表名 @Fields varchar(5000) = '*', --欄位名(全部欄位為*) @OrderField varchar(5000), --排序欄位(必須!支援多欄位) @OrderType varchar(5000), --排序類型 @sqlWhere varchar(5000) = Null, --條件陳述式(不用加where) @pageSize int, --每頁多少條記錄 @pageIndex int = 1 , --指定當前為第幾頁 @TotalPage int output, --返回總頁數 @totalRecord int output --計算總記錄數 --返回總記錄數 )asbegin Begin Tran --開始事務 Declare @sql nvarchar(500); if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'select @totalRecord = count(*) from ' + @TableName else set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--計算總記錄數 --計算總頁數 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField +' ' + @Ordertype+' ) as rowId,' + @Fields + ' from ' + @TableName else set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField +' ' + @Ordertype+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --處理頁數超出範圍情況 if @PageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --處理開始點和結束點 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --繼續合成sql語句 set @Sql = @Sql + ') as ' + @TableName + ' where rowid between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) --print @Sql Exec(@Sql) --------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord ---返回記錄總數 End end--exec GetDataList 'Userinfo','*','id','desc','',10,1,3,3000000
前台頁面Default2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title></title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> <asp:Label ID="lbl_page" runat="server" Text="Label"></asp:Label> </div> </form></body></html>
後台CS代碼Default2.aspx.cs
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Text;public partial class Default2 : System.Web.UI.Page{ private int PageIndex = 0;//當前頁碼 private int PageSize = 50;//每頁幾條記錄 private int TotalPage = 1;//總分頁數 private int TotalRecord = 0;//總記錄 private string OrderType = " desc";//排序方式 預設正序 protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { GetParams(); DataSet ds = PageData("UserInfo", "*", "id", OrderType, "", PageSize, PageIndex, out TotalPage, out TotalRecord); GridView1.DataSource = ds; GridView1.DataBind(); lbl_page.Text = GetDivPager("", ds); } } //資料庫連接字元 public static string StrConn() { //return string.Format("{0}","server=.;database=Stored;user=sa;password=123456"); return ConfigurationSettings.AppSettings["ConnString"].ToString(); } //Get方式獲得下一頁 private void GetParams() { if (!String.IsNullOrEmpty(Request["page"])) { PageIndex = Convert.ToInt32(Request["Page"]); } else { PageIndex = 1; } } #region 獲得分頁字元 public string GetDivPager(string queryString, DataSet ds) { StringBuilder sp = new StringBuilder(); int TotalCount = TotalRecord; int rowCount = TotalPage; if (ds != null) { sp.AppendFormat(" <p>總記錄：<span id=\"sum\">{0}</span>", TotalCount); sp.AppendFormat(" 頁碼：<em><b id=\"current\">{0}</b>/<span id=\"count\">{1}</span></em> ", PageIndex, rowCount); sp.AppendFormat(" 每頁：<span id=\"eachPage\">{0}</span></p> ", PageSize); sp.AppendFormat(" <a href='{0}'>首頁</a> ", "?page=1" + queryString); if (PageIndex > 1) { sp.AppendFormat(" <a href='{0}'>< 上一頁 </a>", "?page=" + (PageIndex - 1) + queryString); } int temp = 0; int loopc = rowCount > 10 ? 10 : rowCount; for (int i = 0; i < loopc; i++) { temp = i + 1; if (PageIndex > 10) { temp = (PageIndex - 10) + i + 1; } sp.AppendFormat(" <a class=\"{0}\" href='{1}'>{2}</a>", PageIndex == temp ? "active" : "", "?page=" + temp + queryString, temp); } if (PageIndex != rowCount) { sp.AppendFormat(" <a href='{0}'>下一頁 ></a>", "?page=" + (PageIndex + 1) + queryString); } sp.AppendFormat(" <a href='{0}'>尾頁</a>", "?page=" + rowCount + queryString); } else { ds = null; } return sp.ToString(); } #endregion #region 擷取分頁的資料 /// <summary> /// 擷取分頁的資料 /// </summary> /// <param name="TblName">資料表名</param> /// <param name="Fields">要讀取的欄位</param> /// <param name="OrderField">排序欄位</param> /// <param name="OrderType">排序方式</param> /// <param name="SqlWhere">查詢條件</param> /// <param name="PageSize">每頁顯示多少條資料</param> /// <param name="pageIndex">當前頁碼</param> /// <param name="TotalPage">傳回值，共有多少頁</param> /// <param name="TotalRecord">傳回值，總有多少條記錄</param> /// <returns></returns> public static DataSet PageData(string TblName, string Fields, string OrderField, string OrderType, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord) { SqlConnection conn = new SqlConnection(StrConn()); SqlCommand comm = new SqlCommand("GetDataList", conn); comm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar, 100)).Value = TblName; comm.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar, 1000)).Value = Fields; comm.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar, 1000)).Value = OrderField; comm.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.NVarChar, 1000)).Value = OrderType; comm.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.NVarChar, 1000)).Value = SqlWhere; comm.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = PageSize; comm.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int)).Value = pageIndex; comm.Parameters.Add(new SqlParameter("@TotalPage", SqlDbType.Int)); comm.Parameters["@TotalPage"].Direction = ParameterDirection.Output;//獲得out出來的參數值 comm.Parameters.Add(new SqlParameter("@totalRecord", SqlDbType.Int)); comm.Parameters["@totalRecord"].Direction = ParameterDirection.Output; comm.CommandType = CommandType.StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter(comm); DataSet ds = new DataSet(); dataAdapter.Fill(ds); TotalPage = (int)comm.Parameters["@TotalPage"].Value; TotalRecord = (int)comm.Parameters["@totalRecord"].Value; conn.Close(); conn.Dispose(); comm.Dispose(); return ds; } #endregion}
以上這篇通用SQL預存程序分頁以及asp.net後台調用的方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支援雲棲社區。
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
