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百萬(wàn)數(shù)據(jù)，大概4分鐘時(shí)間
set @i=1
while @i3000000
begin
insert into UserInfo (UserName) values(@i)
set @i=@i+1
end
create PROCEDURE [dbo].[GetDataList]
(
@TableName varchar(5000), --表名
@Fields varchar(5000) = '*', --字段名(全部字段為*)
@OrderField varchar(5000), --排序字段(必須!支持多字段)
@OrderType varchar(5000), --排序類型
@sqlWhere varchar(5000) = Null, --條件語(yǔ)句(不用加where)
@pageSize int, --每頁(yè)多少條記錄
@pageIndex int = 1 , --指定當(dāng)前為第幾頁(yè)
@TotalPage int output, --返回總頁(yè)數(shù)
@totalRecord int output --計(jì)算總記錄數(shù) --返回總記錄數(shù)
)
as
begin Begin Tran --開(kāi)始事務(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ì)算總頁(yè)數(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
--處理頁(yè)數(shù)超出范圍情況
if @PageIndex=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage --處理開(kāi)始點(diǎn)和結(jié)束點(diǎn)
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1 --繼續(xù)合成sql語(yǔ)句
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
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)前頁(yè)碼
private int PageSize = 50;//每頁(yè)幾條記錄
private int TotalPage = 1;//總分頁(yè)數(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ù)庫(kù)連接字符
public static string StrConn()
{
//return string.Format("{0}","server=.;database=Stored;user=sa;password=123456");
return ConfigurationSettings.AppSettings["ConnString"].ToString();
}
//Get方式獲得下一頁(yè)
private void GetParams()
{
if (!String.IsNullOrEmpty(Request["page"]))
{
PageIndex = Convert.ToInt32(Request["Page"]);
}
else
{
PageIndex = 1;
}
}
#region 獲得分頁(yè)字符
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(" 頁(yè)碼：em>b id=\"current\">{0}/b>/span id=\"count\">{1}/span>/em> ", PageIndex, rowCount);
sp.AppendFormat(" 每頁(yè)：span id=\"eachPage\">{0}/span>/p> ", PageSize);
sp.AppendFormat(" a href='{0}'>首頁(yè)/a> ", "?page=1" + queryString);
if (PageIndex > 1)
{
sp.AppendFormat(" a href='{0}'> 上一頁(yè) /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}'>下一頁(yè) >/a>", "?page=" + (PageIndex + 1) + queryString);
}
sp.AppendFormat(" a href='{0}'>尾頁(yè)/a>", "?page=" + rowCount + queryString);
}
else
{
ds = null;
}
return sp.ToString();
}
#endregion
#region 獲取分頁(yè)的數(shù)據(jù)
/// summary>
/// 獲取分頁(yè)的數(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">每頁(yè)顯示多少條數(shù)據(jù)/param>
/// param name="pageIndex">當(dāng)前頁(yè)碼/param>
/// param name="TotalPage">返回值，共有多少頁(yè)/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出來(lái)的參數(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ǔ)過(guò)程分頁(yè)以及asp.net后臺(tái)調(diào)用的方法就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
