通用SQL存儲過程分頁以及asp.net后臺調(diào)用

作者：佚名 2015-08-19 14:18:56

創(chuàng)建表格并添加300萬數(shù)據(jù)

use Stored 
CREATE TABLE UserInfo(  --創(chuàng)建表 
id int  IDENTITY(1,1) PRIMARY KEY not null,--添加主鍵和標(biāo)識列 
UserName varchar(50)  
) 
  
declare @i int  --添加3百萬數(shù)據(jù)，大概4分鐘時間 
set @i=1 
while @i<3000000 
begin 
insert into UserInfo (UserName) values(@i) 
set @i=@i+1 
end

存儲過程T-SQL

USE [Stored] 
GO 
/****** Object:  StoredProcedure [dbo].[GetDataList]    Script Date: 08/17/2015 16:40:21 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER  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 ,             --指定當(dāng)前為第幾頁 
 @TotalPage int output,           --返回總頁數(shù)  
 @totalRecord int output       --計算總記錄數(shù)              --返回總記錄數(shù) 
 ) 
as 
begin    Begin Tran --開始事務(wù)    Declare @sql nvarchar(4000); 
     
    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--計算總記錄數(shù)   
                 
     
    --計算總頁數(shù) 
    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     
 
--處理頁數(shù)超出范圍情況 
    if @PageIndex<=0  
        Set @pageIndex = 1 
     
    if @pageIndex>@TotalPage 
        Set @pageIndex = @TotalPage     --處理開始點和結(jié)束點 
    Declare @StartRecord int 
    Declare @EndRecord int 
     
    set @StartRecord = (@pageIndex-1)*@PageSize + 1 
    set @EndRecord = @StartRecord + @pageSize - 1    --繼續(xù)合成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 ---返回記錄總數(shù) 
      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;//當(dāng)前頁碼 
    private int PageSize = 50;//每頁幾條記錄 
    private int TotalPage = 1;//總分頁數(shù) 
    private int TotalRecord = 0;//總記錄 
    private string OrderType = " desc";//排序方式 默認(rèn)正序 
    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); 
        } 
    } 
 
    //數(shù)據(jù)庫連接字符 
    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 獲取分頁的數(shù)據(jù) 
    /// <summary> 
    /// 獲取分頁的數(shù)據(jù) 
    /// </summary> 
    /// <param name="TblName">數(shù)據(jù)表名</param> 
    /// <param name="Fields">要讀取的字段</param> 
    /// <param name="OrderField">排序字段</param> 
    /// <param name="OrderType">排序方式</param> 
    /// <param name="SqlWhere">查詢條件</param> 
    /// <param name="PageSize">每頁顯示多少條數(shù)據(jù)</param> 
    /// <param name="pageIndex">當(dāng)前頁碼</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出來的參數(shù)值 
 
        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 
}

責(zé)任編輯：honglu 來源：博客園

SQL asp.net后臺調(diào)用