主頁 > 知識(shí)庫 > 通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法

通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法

熱門標(biāo)簽:地圖標(biāo)注什么軟件好用 外呼系統(tǒng)線路經(jīng)常出問題嗎 安陽手機(jī)自動(dòng)外呼系統(tǒng)原理是什么 外呼系統(tǒng)怎樣才能不封號(hào) 如何辦理400客服電話 地圖標(biāo)注百度競價(jià) 神行者百貨商場地圖標(biāo)注 地圖標(biāo)注專員入駐 西藏地圖標(biāo)注改進(jìn)點(diǎn)

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

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

存儲(chǔ)過程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 ,       --指定當(dāng)前為第幾頁
 @TotalPage int output,      --返回總頁數(shù) 
 @totalRecord int output    --計(jì)算總記錄數(shù)       --返回總記錄數(shù)
 )
as
begin  Begin Tran --開始事務(wù)  
  
  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--計(jì)算總記錄數(shù) 
        
  
  --計(jì)算總頁數(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   --處理開始點(diǎn)和結(jié)束點(diǎn)
  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

前臺(tái)頁面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>

后臺(tái)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
}

以上這篇通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法就是小編分享給大家的全部內(nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。

您可能感興趣的文章:
  • asp.net中如何調(diào)用sql存儲(chǔ)過程實(shí)現(xiàn)分頁
  • ASP.NET存儲(chǔ)過程實(shí)現(xiàn)分頁效果(三層架構(gòu))
  • SQL Server 分頁查詢通用存儲(chǔ)過程(只做分頁查詢用)

標(biāo)簽:阜陽 AXB 貴港 張掖 雞西 衡水 萍鄉(xiāng) 酒泉

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法》,本文關(guān)鍵詞  通用,SQL,存儲(chǔ),過程,分頁,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法》相關(guān)的同類信息!
  • 本頁收集關(guān)于通用SQL存儲(chǔ)過程分頁以及asp.net后臺(tái)調(diào)用的方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章