row_number over(order by id desc)语句进行存储过程高效分页,百度分页(1,2,3,4,5....)
来源：互联网 发布：17年淘宝虚拟店开不了 编辑：程序博客网 时间：2024/06/05 10:27
模仿百度分页示意效果图：
sql procdure code:
create proc [dbo].[MyPages](
@tableName varchar(155),
@pageIndex int,
@pageSize int,
@pageCurrent int output,
@pageNumber int output, --总页数
@pageCount int output, --总记录
@clickType varchar(15)=null,--点击按钮的类型
@keyId varchar(15),
@columnName varchar(255),
@strWhere varchar(1000)
)
as
declare @totalCount int
declare @strCount nvarchar(155)
declare @strSql nvarchar(1000)
set nocount on
set @strCount='SELECT @totalCount=COUNT(*) FROM '+ @tableName+' WHERE '+@strWhere
exec sp_executeSql @strCount,N'@totalCount int out',@totalCount=@pageCount out
if(@pageCount%@pageSize=0)
set @pageNumber=(@pageCount/@pageSize)
else
begin
set @pageNumber=(@pageCount/@pageSize)+1
end
--if(@strWhere='')begin set @strWhere='1=1' end
if(@clickType!='')begin
if(@clickType='pre')begin
set @pageIndex=(@pageIndex-1)
end
else if(@clickType='next')begin
set @pageIndex=(@pageIndex+1)
end
end
set @pageCurrent=@pageIndex
set @strSql='SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@keyId+' DESC) AS Row,'+@columnName+' FROM '+@tableName+' WHERE '+ @strWhere+' ) TT WHERE TT.Row BETWEEN '+str((@pageIndex-1)*@pageSize+1)+' AND '+str(@pageIndex*@pageSize)
exec (@strSql)
set nocount off
在设计这段存储过程的时候，让我深刻了解了两个知识点：
一：在声明变量的时候，一定要注意变量的类型
下面讲下声明变量类型时的错误:
1.sql存储过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'错误时，就要注意在declare xx声明变量的时候，它的类型（nvarchar(size)）一定要定义正确。
比如：
create procedure testProce(
@tableName varchar(25),
@count int output
)AS
declare @strSql varchar(255) -----改为declare @sttSql Nvarchar(255)就对了
declare @totalCount int
set @strSql='SELECT @totalCount=COUNT(*) FROM '+@tableName
execute (@strSql)
set @count=@totalCount
//调用存储过程
declare @count int
execute testProce N'BG_Bargain',@count out
消息 214，级别 16，状态 2，过程 sp_executesql，第 1 行
过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'。
一定要切记：declare @strSql Nvarchar(255) 要加一个N
二：execute命令和execute sp_executeSql的区别
2.在很久以前对于存储过程一直比较的陌生，今天要用到动态拼接sql语句，并且要输入、输出参数，远远用execute是很难做到的，这让我了解到sp_executeSql的用法
简单的讲下execute @sql 和execute sp_executeSq @Sqll的区别：
execute描述：
a.execute不提供接口这里的接口是指，它不能执行一个包含一个带变量符的批处理，如果执行带变量符的批处理，就会出现 必须声明标量变量 "@变量"等错误信息。
sp_executesql提供接口
sp_executesql命令比execute命令更灵活，因为它提供一个接口，该接口及支持输入参数也支持输出参数。这功能使你可以创建带参数的查询字符串，这样就可以比EXEC更好的重用执行计划,sp_executesql的构成与存储过程非常相似，不同之处在于你是动态构建代码。它的构成包括：代码快，参数声明部分，参数赋值部分。说了这么多，还是看看它的语法吧
execute sp_executesql
@stmt = <statement>,--类似存储过程主体
@params = <params>, --类似存储过程参数部分
<params assignment> --类似存储过程调用
@stmt参数是输入的动态批处理，它可以引入输入参数或输出参数，和存储过程的主体语句一样，只不过它是动态的，而存储过程是静态的，不过你也可以在存储过程中使用sp_executesql;
@params参数与定义输入/输出参数的存储过程头类似，实际上和存储过程头的语法完全一样；
@<params assignment> 与调用存储过程的EXEC部分类似。
从其中可以体会到，sp_executeSql命令比execute命令更加强大，更加灵活，可编译性更强。
三：采用C#调用sql中存储过程（得到输出参数的value）
SqlParameter[] param ={
new SqlParameter("@tableName",SqlDbType.VarChar,15),
new SqlParameter("@pageIndex",SqlDbType.Int,4),
new SqlParameter("@pageSize",SqlDbType.Int,4),
new SqlParameter("@pageCurrent",SqlDbType.Int,4),
new SqlParameter("@pageNumber",SqlDbType.Int,4),
new SqlParameter("@pageCount",SqlDbType.Int,4),
new SqlParameter("@clickType",SqlDbType.VarChar,15),
new SqlParameter("@keyId",SqlDbType.VarChar,15),
new SqlParameter("@strWhere",SqlDbType.VarChar,155)
};
param[0].Value=""; //为参数赋值
param[1].Value=""
.............................
param[4].Direction= ParameterDirection.Output; //定义该参数为输出参数
a.如何取得存储过程输出参数：
pageCount = Convert.ToInt32(scom.Parameters["@pageCount"].Value.ToString());
后台代码：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using My.TB_Model;
using My.TB_Dal;
public partial class MY_Main_rightContent : System.Web.UI.Page
{
public string htmlText = "";
protected void Page_Load(object sender, EventArgs e)
{
if (Session["User"] != null)
{
htmlText += BindPage(15, "XT_Bulletin", "NTID", "NT_Type=1", gvBulletin);
}
else
{
Response.Write("浏览器会话失效，请重新登录系统！！");
form1.Style["display"] = "none";
}
}
private string BindPage(int pSize, string tableName, string keyId, string strWhere, Control con)
{
int pageIndex = 1;
int pageCount = 1;
int pageSize = pSize;
////总页数
int pageYm = 1;
string clickType = "";
if (Request.QueryString["cm"] != null)
{
clickType = Request.QueryString["cm"].ToString();
}
if (Request.QueryString["pageIndex"] != null)
{
pageIndex = Convert.ToInt32(Request.QueryString["pageIndex"].ToString());
}
SqlParameter[] param ={
new SqlParameter("@tableName",SqlDbType.VarChar,155),
new SqlParameter("@pageIndex",SqlDbType.Int,4),
new SqlParameter("@pageSize",SqlDbType.Int,4),
new SqlParameter("@pageCurrent",SqlDbType.Int,4),
new SqlParameter("@pageNumber",SqlDbType.Int,4),
new SqlParameter("@pageCount",SqlDbType.Int,4),
new SqlParameter("@clickType",SqlDbType.VarChar,15),
new SqlParameter("@keyId",SqlDbType.VarChar,15),
new SqlParameter("@columnName",SqlDbType.VarChar,255),
new SqlParameter("@strWhere",SqlDbType.VarChar,1000)
};
param[0].Value = tableName;
param[1].Value = pageIndex;
param[2].Value = pageSize;
param[3].Direction = ParameterDirection.Output;
param[4].Direction = ParameterDirection.Output;
param[5].Direction = ParameterDirection.Output;
param[6].Value = clickType;
param[7].Value = keyId;
param[8].Value = "*";
param[9].Value = strWhere;
DBHelper db = new DBHelper();
DataSet ds = db.ExcuteProduct("MyPages", param, CommandType.StoredProcedure, out pageCount, out pageIndex, out pageYm);
if (ds.Tables[0].Rows.Count > 0)
{
((GridView)con).DataSource = ds;
((GridView)con).DataBind();
}
else
{
Command.ShowHeadMethod(ds, ((GridView)con));
}
int a = (pageIndex - 6) <= 0 ? 1 : (pageIndex - 6);
int b = (pageIndex + 6) >= pageYm ? pageYm : (pageIndex + 6);
htmlText += "<div id=\"pre\" class=\"page\"><a href=\"rightContent.aspx?pageIndex=" + pageIndex + "&cm=pre" + "\"><<</a></div><div class=\"page\">";
for (int i = a; i <= b; i++)
{
htmlText += "<a id=\"L" + i.ToString() + "\" href=\"rightContent.aspx?pageIndex=" + i.ToString() + "\">" + i.ToString() + "</a>";
}
htmlText += "</div><div id=\"next\" class=\"page\"><a href=\"rightContent.aspx?pageIndex=" + pageIndex + "&cm=next" + "\">>></a></div>";
htmlText += "<div class=\"page\"><a style=\"background-image:url(../Images/btn.png);background-repeat:repeat-x;color:white;\">总记录:<font style=\"color:red\">" + pageCount.ToString() + "</font>,页码: <font style=\"color:red\">" + pageIndex + "</font> /" + pageYm.ToString() + " 转到<input type=\"text\" id=\"txtNumber\" style=\"width:31px;height:12px; margin-top:-5px;text-align: center\" value=\"" + pageIndex.ToString() + "\" onfocus=\"this.value=''\" onkeyup=\"this.value=this.value.replace(/\\D/g,'')\" /> 页</a><a href=\"javascript:void(0)\" onclick=\"GoTo(" + pageYm + ",'../rightContent.aspx'" + ")\">Go</a></div>";
if (b != 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "xa", "pageActive(" + pageIndex + "," + pageYm + ")", true);
}
return htmlText;
}
protected void gvBulletin_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes["id"] =e.Row.RowIndex.ToString();
e.Row.Attributes.Add("onclick", "selectx(this,'" + e.Row.RowIndex.ToString() + "')");
//e.Row.Attributes.Add("ondblclick", "open5(800,550,'XT_Manager/xt_BullDetail.aspx',1,'gvBulletin',1)"); //实现双击事件
e.Row.Attributes.Add("ondblclick", "openPrint(800,550,'XT_Manager/xt_BullDetail.aspx','"+e.Row.Cells[1].Text+"')");
}
//实现自动编号
//if (e.Row.RowIndex != -1)
//{
// int id = e.Row.RowIndex + 1;
// e.Row.Cells[0].Text = id.ToString();
//}
}
protected void gvBulletin_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "titles")
{
ClientScript.RegisterStartupScript(this.GetType(), "bx", "<script>openPrint(800,550,'XT_Manager/xt_BullDetail.aspx','" + e.CommandArgument.ToString() + "')</script>");
}
}
}
前台代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="rightContent.aspx.cs" Inherits="MY_Main_rightContent" %>
<!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 id="Head1" runat="server">
<title>right内容区域</title>
<link type="text/css" rel="stylesheet" href="Style/MyPage.css" />
<script type="text/javascript" src="Js/zDrag.js"></script>
<script type="text/javascript" src="Js/zDialog.js"></script>
</head>
<body>
<form id="form1" runat="server">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td style="height: 30px; background: url(Images/tab_05.gif)">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td style="width: 12px; height: 30px">
<img src="Images/tab_03.gif" width="12" height="30" alt="" /></td>
<td>
<div style="float: left;">
<img src="Images/tb.gif" alt="" /></div>
<div style="float: left; line-height: 16px; width: 229px;">
您当前的位置：当前主页>>系统公告
</div>
</td>
<td style="width: 16px">
<img src="Images/tab_07.gif" width="16" height="30" alt="" /></td>
</tr>
</table>
</td>
</tr>
<tr>
<td valign="top">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td style="width: 8px; background: url(Images/tab_12.gif);">
</td>
<td>
<table border="0" cellpadding="0" cellspacing="0" style="width: 100%">
<tr>
<td colspan="2">
<div id="gvDiv" class="gvBorder" style="height:auto;">
<asp:GridView ID="gvBulletin" runat="server" AutoGenerateColumns="False" Width="100%" CssClass="gridview_m" OnRowDataBound="gvBulletin_RowDataBound" OnRowCommand="gvBulletin_RowCommand">
<Columns>
<asp:BoundField DataField="Row" HeaderText="序号">
<ItemStyle Width="40px" />
</asp:BoundField>
<asp:BoundField DataField="NTID" HeaderText="系统编号" >
<ItemStyle Width="90px" />
</asp:BoundField>
<asp:TemplateField HeaderText="公告主题">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("NT_Content") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<div style="padding-left:5px; text-align:left;">
<asp:LinkButton ID="lkContent" runat="server" CommandName="titles" CommandArgument='<%# Eval("NTID") %>' Text='<%# Eval("NT_Content") %>'></asp:LinkButton>
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="NT_Sender" HeaderText="发布人" />
<asp:TemplateField HeaderText="发布日期">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("NT_Time") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("NT_Time", "{0:yyyy-MM-dd}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<SelectedRowStyle Font-Bold="True" />
<HeaderStyle CssClass="gridview_head" Font-Bold="True" />
</asp:GridView>
</div>
</td>
</tr>
</table>
</td>
<td style="width: 8px; background: url(Images/tab_15.gif)">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="height: 35px; background: url(Images/tab_19.gif);">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td style="width: 12px; height: 35px">
<img src="Images/tab_18.gif" width="12" height="35" alt="" /></td>
<td style="padding-bottom:5px;">
<asp:TextBox ID="txtHidd" runat="server" style="display:none; width:1px"></asp:TextBox>
<%=htmlText %>
</td>
<td style="width: 16px">
<img src="Images/tab_20.gif" width="16" height="35" alt="" /></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>
- row_number over(order by id desc)语句进行存储过程高效分页,百度分页(1,2,3,4,5....)
- ROW_NUMBER() over(order by id)分页存储过程 代码直接可用
- SqlServer 分页 ROW_NUMBER() OVER(Order by * DESC ) AS RowNumber
- SQL 高效分页（百万条数据）ROW_NUMBER() OVER (ORDER BY id) | 分页
- SQL 高效分页（百万条数据）ROW_NUMBER() OVER (ORDER BY id) | 分页
- row_number() over (partition by idno order by id desc)
- row_number() over(order by col2 desc)用法
- ROW_NUMBER() over(order by Id)
- oracle 分析函数：ROW_NUMBER() OVER(PARTITION BY ID ORDER BY VERSION DESC)
- mysql 实现oracle里面row_number() OVER (PARTITION BY cid,author ORDER BY id DESC) 的方法
- Sql Server2005 的快速分页 ROW_NUMBER() OVER (ORDER BY ReportID)
- oracle分页技术之row_number() over( order by 列名)
- row_number() over(partition by a order by b desc) rn
- row_number() over(partition by a order by b desc) 用法
- mysql实现ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)
- ROW_NUMBER() over(order by ID) 的用法
- sqlserver 高效分页的存储过程 基于ROW_NUMBER()
- SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
- win8 开发，提示框
- EnableDocking
- cisco asa 功能
- java中所有运算符以及权限
- 在linux上创建nfs共享
- row_number over(order by id desc)语句进行存储过程高效分页,百度分页(1,2,3,4,5....)
- Android入门第九篇之AlertDialog .
- Vim的分屏显示
- 保护眼睛的重要食品
- Linux设备驱动程序：中断处理之顶半部和底半部
- Android入门第八篇之GridView(九宫图) .
- Android系统启动过程分析
- 如何使用log4j记录日志
- linux 常用时间函数及时间类型
