sqlserver 存储过程分页(按多条件排序)
网络编程 2021-07-05 13:42www.168986.cn编程入门
sqlserver 存储过程分页,并支持条件排序,需要的朋友可以参考下。
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, userwele;
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);
}
}
//调用存储过程的函数
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.panyName,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());
}
.aspx页面代码
<table id="SXFSTable" style="width:100%;" class="table">
<tr><td><b>公司名称</b></td><td><b>公司网址</b></td><td><b>认领状态</b></td></tr>
<%for (int i = 0; i < dt.Rows.Count; i++)
{
%>
<tr>
<td><%= dt.Rows[i]["panyName"].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 style="margin-left:auto; margin-right:auto; width:70%; text-align:left; font-size:9pt;">
第 <%=PageCurrent %> 页 共 <%=RowsCount %> 条 共 <%=TotalPage%> 页
<% if (PageCurrent != 1)
{
%>
<a href="test.aspx">首 页</a>
<a href="test.aspx?page=<%=PageCurrent-1 %>">上一页</a>
<%
}
if (PageCurrent != TotalPage)
{
%>
<a href="test.aspx?page=<%=PageCurrent+1 %>">下一页</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),--主键列
@OrderField nvarchar(50),--排序字段,如果是多个字段,除一个字段外,后面都要加排序条件(asc/desc),不包含order by,一个排序字段不用加排序条件
@OrderType nvarchar(4),--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 @nRd 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(1) 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(@CurrentPage<1)
Set @CurrentPage=1
if(@PageSize<1)
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'+','+@IdField +' asc'
end
else
begin
set @OrderField=REPLACE(@OrderField,'desc','lai512343975')
set @OrderField=REPLACE(@OrderField,'asc','desc')
set @OrderField=REPLACE(@OrderField,'lai512343975','asc')
set @Sql='select ' + @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 '+ 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
代码如下:
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, userwele;
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);
}
}
//调用存储过程的函数
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.panyName,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());
}
.aspx页面代码
<table id="SXFSTable" style="width:100%;" class="table">
<tr><td><b>公司名称</b></td><td><b>公司网址</b></td><td><b>认领状态</b></td></tr>
<%for (int i = 0; i < dt.Rows.Count; i++)
{
%>
<tr>
<td><%= dt.Rows[i]["panyName"].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 style="margin-left:auto; margin-right:auto; width:70%; text-align:left; font-size:9pt;">
第 <%=PageCurrent %> 页 共 <%=RowsCount %> 条 共 <%=TotalPage%> 页
<% if (PageCurrent != 1)
{
%>
<a href="test.aspx">首 页</a>
<a href="test.aspx?page=<%=PageCurrent-1 %>">上一页</a>
<%
}
if (PageCurrent != TotalPage)
{
%>
<a href="test.aspx?page=<%=PageCurrent+1 %>">下一页</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),--主键列
@OrderField nvarchar(50),--排序字段,如果是多个字段,除一个字段外,后面都要加排序条件(asc/desc),不包含order by,一个排序字段不用加排序条件
@OrderType nvarchar(4),--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 @nRd 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(1) 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(@CurrentPage<1)
Set @CurrentPage=1
if(@PageSize<1)
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'+','+@IdField +' asc'
end
else
begin
set @OrderField=REPLACE(@OrderField,'desc','lai512343975')
set @OrderField=REPLACE(@OrderField,'asc','desc')
set @OrderField=REPLACE(@OrderField,'lai512343975','asc')
set @Sql='select ' + @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 '+ 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
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程:电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示:编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程