多款高效sql存储过程分页代码(1/8)

cs页面调用代码: 复制代码 代码如下:
public int TotalPage = 0; public int PageCurrent = 1; public int
PageSize = 25; public int RowsCount = 0; string userid, username; public
DataTable dt = new DataTable(卡塔尔; public string path, userwelcome; public
string opt,cid; protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack) { if (Request.Params[“page”] == null ||
Request.Params[“page”].ToString().Equals(“”)) PageCurrent = 1; else
PageCurrent=int.Parse(Request.Params[“page”].ToString(卡塔尔卡塔尔;
this.getPage(out TotalPage, out RowsCount, PageSize, PageCurrentState of Qatar; } }
//调用存款和储蓄进程的函数 private void getPage(out int totalPage, out int
rowsCount, int pageSize, int currentPageState of Qatar { SqlParameter[] parameters
= { new SqlParameter(“@TotalPage”, SqlDbType.Int,4), new
SqlParameter(“@RowsCount”, SqlDbType.Int,4), new
SqlParameter(“@PageSize”, SqlDbType.Int,4), new
SqlParameter(“@CurrentPage”, SqlDbType.Int,4), new
SqlParameter(“@SelectFields”, SqlDbType.NVarChar,700), new
SqlParameter(“@IdField”,SqlDbType.NVarChar,50), new
SqlParameter(“@OrderField”, SqlDbType.NVarChar,200), new
SqlParameter(“@OrderType”, SqlDbType.NVarChar,2), new
SqlParameter(“@TableName”, SqlDbType.NVarChar,300), new
SqlParameter(“@strWhere”, SqlDbType.NVarChar,300), };
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = pageSize; parameters[3].Value = currentPage;
parameters[4].Value =
“a.RLId,a.companyName,a.webSite,a.isRL,a.ordernum,a.isrl,a.userid”;
parameters[5].Value = “a.RLId”; parameters[6].Value = ” a.isrl asc ,
a.orderNum “; parameters[7].Value = “1”; parameters[8].Value =
“qiYeRenling a”; parameters[9].Value = “1=1”;// DataSet ds =
Wm23Abc.DBUtility.DbHelperSQL.RunProcedure(“getRecordByPage”,
parameters, “dt”); dt = ds.Tables[0]; totalPage =
int.Parse(parameters[0].Value.ToString()); rowsCount =
int.Parse(parameters[1].Value.ToString(卡塔尔国State of Qatar; } .aspx页面代码: table
trtdb公司名称/b/tdtdb公司网站/b/tdtdb认领状态/b/td/tr %for (int i = 0; i
dt.Rows.Count; i++卡塔尔(قطر‎ { % tr td%= dt.Rows[i][“companyName”].ToString()
%排序值:%= dt.Rows[i][“ordernum”].ToString() %/td td%=
dt.Rows[i][“webSite”].ToString(卡塔尔(قطر‎ % 是或不是认领:%=dt.Rows
[i][“userid”].ToString () %/td td%=
dt.Rows[i][“isRL”].ToString().Equals(“0”) ? “a
href=\”javascript:;\”
onclick=\”renLing(event,'”+dt.Rows[i][“RLId”].ToString()+”‘);\”认领该商家/a”
: “font color=\”red\”该商厦已被收养/font”%/td /tr % } % /table /div
div 第 %=PageCurrent % 页 共 %=RowsCount % 条 共 %=TotalPage% 页 % if
(PageCurrent != 1) { % a href=”test.aspx”首 页/a a
href=”test.aspx?page=%=PageCurrent-1%20%”/a % } if (PageCurrent !=
TotalPage卡塔尔国 { % a href=”test.aspx?page=%=PageCurrent+1%20%”/a a
href=”test.aspx?page=%=TotalPage%”末 页/a % } % /div 存储进程代码:
复制代码 代码如下: CREATE proc
[dbo].[getRecordByPage] @TotalPage int output,–总页数 @RowsCount
int output,–总条数 @PageSize int,–每页多少多少 @CurrentPage
int,–当前页数 @SelectFields nvarchar(1000卡塔尔国,–select
语句不过不包罗select @IdField nvarchar(50卡塔尔(قطر‎,–主键列 @OrderFieldnvarchar(50卡塔尔国,–排序字段,假设是多个字段,除最终八个字段外,前面都要加排序条件,不分包order
by,最终二个排序字段不用加排序条件 @OrderType nvarchar(4State of Qatar,–1升序,0降序
@TableName nvarchar(200卡塔尔,–表名 @strWhere nvarchar(300卡塔尔(قطر‎–条件 As Begin
declare @RecordCount float declare @PageNum int –分页依靠数 Declare
@Compare nvarchar(50卡塔尔–相比较字段分别min只怕max Declare @Compare1
nvarchar(2卡塔尔 –大于号“” 也许小于号”“ Declare @OrderSql
nvarchar(10卡塔尔(قطر‎–排序字段 declare @Sql nvarchar(4000卡塔尔国 Declare @TemSql
nvarchar(1000卡塔尔 Declare @n帕Jerod int declare @afterRows int declare
@tempTableName nvarchar(10卡塔尔国 if(@OrderType=’1’卡塔尔(قطر‎ Begin set @OrderSql=’
asc’ End Else Begin set @OrderSql= ‘ desc’ End if(isnull(@strWhere,
”卡塔尔”卡塔尔国 Set @strWhere = @strWhere if(@strWhere=”卡塔尔 Set @strWhere=’ 1=1 ‘
Set @TemSql=’Select @RecordCount=Count(1State of Qatar from ‘+@TableName +’ where
‘+@strWhere exec sp_executesql @TemSql,N’@RecordCount float
output’,@RecordCount output Set @RowsCount=@RecordCount Set @TotalPage=
ceiling(@RecordCount/@PageSize) if(@CurrentPage@TotalPage) Set
@CurrentPage=@TotalPage if(@CurrentPage1) Set @CurrentPage=1
if(@PageSize1) Set @PageSize=1 print(@RecordCount) if(@CurrentPage=1)
Begin set Rowcount @PageSize set @Sql=’select ‘+ @SelectFields +’ from
‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’
‘+@OrderSql +’,’+@IdField +’ asc’ –print(@Sql) exec sp_executeSql @Sql
End else if(@CurrentPage=@TotalPage) begin set
@afterRows=@RowsCount-(@CurrentPage-1)*@PageSize set RowCount
@afterRows if(@OrderType=’1’卡塔尔(قطر‎ begin set
@OrderField=REPLACE(@OrderField,’asc’,’lai512343975’卡塔尔//这里用变量将asc和desc沟通,哈哈,太神了
set @OrderField=REPLACE(@OrderField,’desc’,’asc’卡塔尔 set
@OrderField=REPLACE(@OrderField,’lai512343975′,’desc’卡塔尔 set @Sql=’select
‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by
‘+@OrderField +’ desc’+’,’+@Id菲尔德 +’ asc’ end else begin set
@OrderField=REPLACE(@OrderField,’desc’,’lai512343975’State of Qatar set
@OrderField=REPLACE(@OrderField,’asc’,’desc’卡塔尔 set
@OrderField=REPLACE(@OrderField,’lai512343975′,’asc’卡塔尔(قطر‎ set @Sql=’select ‘

public int TotalPage = 0; public int PageCurrent = 1; public int
PageSize = 25; public int RowsCount = 0; string userid, username; public
DataTable dt = new DataTable(); public string path, userwelcome; public
string opt,cid; protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack) { if (Request.Params[page] == null ||
Request.Params[page].ToString().Equals()) PageCurrent = 1; else
PageCurrent=int.Parse(Request.Params[page].ToString());
this.getPage(out TotalPage, out RowsCount, PageSize, PageCurrent); } }

  • @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by
    ‘+@OrderField +’ asc ‘ +’,’+@IdField+ ‘ asc’ print(@Sql) end
    –print(@Sql) exec sp_executeSql @Sql end else Begin set
    @nRd=@PageSize* (@CurrentPage-1) print(@nRd) set RowCount @PageSize set
    @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘
    +@strWhere+’ and ‘+@IdField + ‘ not in (select top ‘+ cast(@nRd as
    nvarchar(10))+’ ‘+@IdField+’ from ‘+@TableName+’ where ‘+ @strWhere+’
    order by ‘+@OrderField +’ ‘+@OrderSql+’,’+@IdField +’ asc) ‘ + ‘ order
    by ‘+ @OrderField + ‘ ‘ +@OrderSql+’,’+@IdField +’ asc’ exec
    sp_executeSql @Sql –Print(@sql) End end GO

//调用存款和储蓄进程的函数

private void getPage(out int totalPage, out int rowsCount, int pageSize,
int currentPage) { SqlParameter[] parameters = { new
SqlParameter(@TotalPage, SqlDbType.Int,4), new SqlParameter(@RowsCount,
SqlDbType.Int,4), new SqlParameter(@PageSize, SqlDbType.Int,4), new
SqlParameter(@CurrentPage, SqlDbType.Int,4), new
SqlParameter(@SelectFields, SqlDbType.NVarChar,700), new
SqlParameter(@IdField,SqlDbType.NVarChar,50), new
SqlParameter(@OrderField, SqlDbType.NVarChar,200), new
SqlParameter(@OrderType, SqlDbType.NVarChar,2), new
SqlParameter(@TableName, SqlDbType.NVarChar,300), new
SqlParameter(@strWhere, SqlDbType.NVarChar,300), };
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = pageSize; parameters[3].Value = currentPage;
parameters[4].Value =
a.RLId,a.companyName,a.webSite,a.isRL,a.ordernum,a.isrl,a.userid;
parameters[5].Value = a.RLId;