繁體中文当前位置: 首页 > 图文教程 > 网络编程 > ASP.NET > asp.net安全、实用、简单的大容量存储过程分页
- ASP.NET
- FreeTextBox(版本3.1.6)在ASP.Net 2.0中使用方法
- .NET 常用功能和代码小结
- 在 .NET Framework 2.0 中未处理的异常导致基于 ASP.NET 的应用程序意外退出
- asp.net IList查询数据后格式化数据再绑定控件
- asp.net sql存储过程
- asp.net 简单实现禁用或启用页面中的某一类型的控件
- asp.net(c#)获取内容第一张图片地址的函数
- The remote procedure call failed and did not execute的解决办法
- ASP.NET 在线文件管理
- asp.net 读取并修改config文件实现代码
- ASP.NET Cookie 操作实现
- asp.net Silverlight中的模式窗体
- Silverlight中动态获取Web Service地址
- asp.net Silverlight应用程序中获取载体aspx页面参数
- asp.net 水晶报表隔行换色实现方法
- asp.net 获取Gridview隐藏列的值
- 手动把asp.net的类生成dll文件的方法
- asp.net 使用ObjectDataSource控件在ASP.NET中实现Ajax真分页
- 动态指定任意类型的ObjectDataSource对象的查询参数
- asp.net Md5的用法小结
ASP.NET 中的 asp.net安全、实用、简单的大容量存储过程分页
出处:互联网 整理: 软晨网（RuanChen.com） 发布: 2009-10-11 浏览: 179 ::
收藏到网摘: n/a
昨晚研究到2点多，对网络上主流的分页存储过程大体看了一遍，但对安全以及如何使用很多文章都没有过多的提及，而我要在这些文章的基础上总结出一个比较实用的分页存储过程，方便大家在以后的项目中使用。 基本上我下面要讲述的侧重点是如何使用，因为其实分页存储过程网上很多，如果你懒得找，那么可以直接使用下面这个我经过测试，并通过修改，网上评价都比较高的分页存储过程。
这个分页主要优点如下：
1、大容量下的数据分页，我的测试数据是520W。
2、我结合aspnetpager控件，使得使用起来更加方便。
3、为了结构清晰，实用3层。
4、安全，你就放心的用吧。SQL注入的问题在这里你可以放心了。网上有文章说只要存储过程是用sql拼接的就存在sql注入的问题，并且直接在sql查询分析器中测试了注入的情况。其实是不对的，采用存储过程和参数化的提交语句并没有sql注入的问题。因为它进数据库的时候会有替换的过程。
准备工作：
1、直接使用一个DB库，数据访问层基类，用它返回一个dataset对象，使用的时候我们只需要类似下面的语句就可以返回一个dataset对象。
sosuo8.DBUtility.DbHelperSQL.RunProcedure("pagination",parameter,"userinfo");
pagination是我在网上找的存储过程，我进行了修改的，主要是添加输出总记录数。这里总记录数也特意说一下，一般我们都是使用类似下面的语句：
复制代码 代码如下:
开始测试：
在BLL层（业务逻辑层），DAL层（数据访问层），DB(访问层基类)，WEB（网站），关于3层结构这里就不介绍了，大家可以看看我前面写过的文章《.net三层结构初探》 。
先添加存储过程：
--阿会楠根据网络上的代码进行了修改，版权归原作者所有2009-4-5
--修改输出总记录数
create PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@OrderType bit, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500), -- 查询条件 (注意: 不要加 where)
@rowCount int output --查询到的记录数
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strCountTmp nvarchar(100) --记录数
declare @timediff datetime --耗时
begin
select @timediff = Getdate()
if @strWhere !=''
set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + '] where '+@strWhere
else
set @strCountTmp = 'select @rowCount = rowCount_tmp from tmp where (table_tmp = ''' + @tblName +''')'
exec sp_executesql @strCountTmp,N'@rowCount int out',@rowCount out --输出总记录数
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
select datediff(ms,@timediff,GetDate()) as runtime
不放心自己测试下吧。
USE [data_Test]
GO
DECLARE @return_value int,
@rowCount int
EXEC @return_value = [dbo].[pagination]
@tblName = 'userinfo',
@strGetFields = N'id',
@fldName = N'id',
@PageSize = 10,
@PageIndex = 300,
@OrderType = 0,
@strWhere = null,
@rowCount = @rowCount OUTPUT
SELECT @rowCount as N'@rowCount'
SELECT 'Return Value' = @return_value
GO
最主要的DAL层代码：
public DataSet GetList(int PageIndex, string strWhere,ref int rowCount)
{
SqlParameter[] parameter = sosuo8.DBUtility.DbHelperSQL.pagePara();
parameter[0].Value = "userinfo";
parameter[1].Value = "id,userName,userWebName,createDate";
parameter[2].Value = "id";
parameter[3].Value = 10;
parameter[4].Value = PageIndex;
parameter[5].Value = 0;
parameter[6].Value = strWhere;
parameter[7].Direction = ParameterDirection.Output;//声明为输出类型
DataSet ds = sosuo8.DBUtility.DbHelperSQL.RunProcedure("pagination",parameter,"userinfo");
rowCount = Convert.ToInt32(parameter[7].Value);
return ds;
}
default.aspx.cs代码
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
bind();
}
}
private void bind()//绑定数据
{
this.anPager.PageSize = 10;//每页记录数
this.anPager.AlwaysShow = true;//是否一直显示分页
int rowCount = 0;//初始化记录数为0
string wherestr = string.Empty;//搜索关键字，这部分后面我在讲讲优化
sosuo8.BLL.bll_test bll = new sosuo8.BLL.bll_test();
this.rpt.DataSource = bll.GetList(this.anPager.CurrentPageIndex,wherestr,ref rowCount);//rowCount在经过这个函数后，返回的是重新赋值的记录总数
this.anPager.RecordCount = rowCount;
this.rpt.DataBind();
}
protected void anPager_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.anPager.CurrentPageIndex = e.NewPageIndex;
bind();
}
}
里面涉及到aspnetpager的使用，如果你还不会使用这个控件，可以自己看看有关教程。最终的界面如下：
令我十分高兴的是，在处理520W的记录时，它速度还在可以接受的范围内，不会出现超时的现象。而作为优化，可以在界面层中尽量少读数据。可以加上以page为参数页面输出缓存：
<%@ OutputCache Duration="360" VaryByParam="page" %>
你也许并没有用过里面的一些控件，但是知道原理和方法我相信你也可以自由搭配你需要的东西，下面我仅上传部分比较重要的代码，对于需要的控件我也放在里面，至于建表测试那些大家慢慢研究吧！当前的存储过程只能针对一个字段排序，后面有时间我会修改成多字段排序
打包下载 /upload/tech/20091011/20091011144129_f0adc8838f4bdedde4ec2cfad0515589.rar
开始测试：
在BLL层（业务逻辑层），DAL层（数据访问层），DB(访问层基类)，WEB（网站），关于3层结构这里就不介绍了，大家可以看看我前面写过的文章《.net三层结构初探》 。
先添加存储过程：
复制代码 代码如下:
--阿会楠根据网络上的代码进行了修改，版权归原作者所有2009-4-5
--修改输出总记录数
create PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@OrderType bit, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500), -- 查询条件 (注意: 不要加 where)
@rowCount int output --查询到的记录数
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strCountTmp nvarchar(100) --记录数
declare @timediff datetime --耗时
begin
select @timediff = Getdate()
if @strWhere !=''
set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + '] where '+@strWhere
else
set @strCountTmp = 'select @rowCount = rowCount_tmp from tmp where (table_tmp = ''' + @tblName +''')'
exec sp_executesql @strCountTmp,N'@rowCount int out',@rowCount out --输出总记录数
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
select datediff(ms,@timediff,GetDate()) as runtime
不放心自己测试下吧。
复制代码 代码如下:
USE [data_Test]
GO
DECLARE @return_value int,
@rowCount int
EXEC @return_value = [dbo].[pagination]
@tblName = 'userinfo',
@strGetFields = N'id',
@fldName = N'id',
@PageSize = 10,
@PageIndex = 300,
@OrderType = 0,
@strWhere = null,
@rowCount = @rowCount OUTPUT
SELECT @rowCount as N'@rowCount'
SELECT 'Return Value' = @return_value
GO
最主要的DAL层代码：
复制代码 代码如下:
public DataSet GetList(int PageIndex, string strWhere,ref int rowCount)
{
SqlParameter[] parameter = sosuo8.DBUtility.DbHelperSQL.pagePara();
parameter[0].Value = "userinfo";
parameter[1].Value = "id,userName,userWebName,createDate";
parameter[2].Value = "id";
parameter[3].Value = 10;
parameter[4].Value = PageIndex;
parameter[5].Value = 0;
parameter[6].Value = strWhere;
parameter[7].Direction = ParameterDirection.Output;//声明为输出类型
DataSet ds = sosuo8.DBUtility.DbHelperSQL.RunProcedure("pagination",parameter,"userinfo");
rowCount = Convert.ToInt32(parameter[7].Value);
return ds;
}
default.aspx.cs代码
复制代码 代码如下:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
bind();
}
}
private void bind()//绑定数据
{
this.anPager.PageSize = 10;//每页记录数
this.anPager.AlwaysShow = true;//是否一直显示分页
int rowCount = 0;//初始化记录数为0
string wherestr = string.Empty;//搜索关键字，这部分后面我在讲讲优化
sosuo8.BLL.bll_test bll = new sosuo8.BLL.bll_test();
this.rpt.DataSource = bll.GetList(this.anPager.CurrentPageIndex,wherestr,ref rowCount);//rowCount在经过这个函数后，返回的是重新赋值的记录总数
this.anPager.RecordCount = rowCount;
this.rpt.DataBind();
}
protected void anPager_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.anPager.CurrentPageIndex = e.NewPageIndex;
bind();
}
}
里面涉及到aspnetpager的使用，如果你还不会使用这个控件，可以自己看看有关教程。最终的界面如下：
令我十分高兴的是，在处理520W的记录时，它速度还在可以接受的范围内，不会出现超时的现象。而作为优化，可以在界面层中尽量少读数据。可以加上以page为参数页面输出缓存：
复制代码 代码如下:
<%@ OutputCache Duration="360" VaryByParam="page" %>
你也许并没有用过里面的一些控件，但是知道原理和方法我相信你也可以自由搭配你需要的东西，下面我仅上传部分比较重要的代码，对于需要的控件我也放在里面，至于建表测试那些大家慢慢研究吧！当前的存储过程只能针对一个字段排序，后面有时间我会修改成多字段排序
打包下载 /upload/tech/20091011/20091011144129_f0adc8838f4bdedde4ec2cfad0515589.rar
评论 (0) All
