技术文章 > Sql server存储过程和C#分页类简化你的代码

Sql server存储过程和C#分页类简化你的代码

2018-07-20 08:47

文档管理软件,文档管理系统,知识管理系统,档案管理系统的技术资料:
Sqlserver存储过程和C#分页类简化你的代码!
在最近的项目中,由于要用到自定义分页的功能,本人就在网上找了个存储过程。结合C#写了个分页类。由于本人第一次写文章。写得不好,大家不要扔鸡蛋。。
下面是存储过程(sqlserver2000下通过)

--最通用的分页存储过程
-- 获取指定页的数据
[code]
sql
CREATE PROCEDURE Pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = "*", -- 需要返回的列
@fldName varchar(255)="", -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = "" -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @doCount != 0
begin
if @strWhere !=""
set @strSQL = "select count(*) as Total from ["+ @tblName +"] where "+ @strWhere
else
set @strSQL = "select count(*) as Total from ["+ @tblName +"]"
end
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都
--是@doCount为0的情况
else
begin

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)
GO
[/code]

下面是C#的代码

using System.Data ;
using System.Data.SqlClient ;
using Microsoft.ApplicationBlocks.Data ;
using System.Web ;
using System.Web.UI ;
namespace RssLayer.PageHelper
{
/**////
/// 分页类PagerHelper 的摘要说明。
///

public class PagerHelper
{
private string connectionString;



public PagerHelper(string tblname,string sortname,bool docount,string connectionString)
{
this.tblName = tblname;
this.fldName = sortname ;
this.connectionString = connectionString ;
this.docount = docount;
}

public PagerHelper(string tblname,bool docount,
string strGetFields, string fldName,int pagesize,
int pageindex,bool ordertype,string strwhere,string connectionString
)
{
this.tblName = tblname ;
this.docount = docount ;
this.strGetFields = strGetFields ;
this.fldName = fldName;
this.pagesize = pagesize ;
this.pageindex = pageindex;
this.ordertype = ordertype ;
this.strwhere = strwhere ;
this.connectionString = connectionString ;

}


/**////
/// 得到记录集的构造函数
///

///
///
///
public PagerHelper(string tblname,string strwhere,string connectionString)
{
this.tblName = tblname;
this.strwhere = strwhere ;
this.docount = true;
this.connectionString = connectionString ;
}

private string tblName;
public string TblName
{
get{return tblName;}
set{tblName =value;}
}

private string strGetFields="*";
public string StrGetFields
{
get{return strGetFields ;}
set{strGetFields =value;}
}

private string fldName=string.Empty;
public string FldName
{
get{return fldName ;}
set{fldName =value;}
}



private int pagesize =10;
public int PageSize
{
get{return pagesize ;}
set{pagesize =value;}
}

private int pageindex =1;
public int PageIndex
{
get{return pageindex ;}
set{pageindex =value;}
}


private bool docount=false;
public bool DoCount
{
get{return docount ;}
set{docount =value;}
}

private bool ordertype=false;
public bool OrderType
{
get{return ordertype ;}
set{ordertype =value;}
}

private string strwhere=string.Empty ;
public string StrWhere
{
get{return strwhere ;}
set{strwhere =value;}
}






public IDataReader GetDataReader()
{

if(this.docount)
{
throw new ArgumentException("要返回记录集,DoCount属性一定为false");
}



// System.Web.HttpContext.Current.Response.Write(pageindex);

return SqlHelper.ExecuteReader(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}

public DataSet GetDataSet()
{
if(this.docount)
{
throw new ArgumentException("要返回记录集,DoCount属性一定为false");
}

return SqlHelper.ExecuteDataset(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}


public int GetCount()
{
if(!this.docount)
{
throw new ArgumentException("要返回总数统计,DoCount属性一定为true");
}



return (int)SqlHelper.ExecuteScalar(connectionString,CommandType.StoredProcedure,"Pagination",
new SqlParameter("@tblName",this.tblName),
new SqlParameter("@strGetFields",this.strGetFields),
new SqlParameter("@fldName",this.fldName),
new SqlParameter("@PageSize",this.pagesize),
new SqlParameter("@PageIndex",this.pageindex),
new SqlParameter("@doCount",this.docount),
new SqlParameter("@OrderType",this.ordertype),
new SqlParameter("@strWhere",this.strwhere)
);
}

}




}

如何调用???
假如我已经建立了2个类。一个是FavList数据库实体类,一个FavListCollection集合类。FavListCollection存储了FavList实体类的集合。
我可以这样写一个方法。
/**////
/// 返回FavList集合,使用存储过程自定义分页。
///

/// 数据库FavList的字段,用户ID
/// 查找的条件
/// 排序,true表示Desc,false表示asc
/// 排序的字段,只能是一个字段
/// 每页的记录数
/// 到第几页的参数,由1开始。1表示第一页,以此类推。
/// 总记录数。
///
public override FavListCollection GetFavListsByUser(int userid, string strwhere,
bool ordertype, string fldname, int pagesize,
int pageindex,out int recordcount
)
{
recordcount = 0;
PagerHelper helper = new PagerHelper("Vfavlist",strwhere,ConnectionString); //VFavList是View
recordcount = helper.GetCount();

PagerHelper helper2 = new PagerHelper("Vfavlist",false," * ",fldname,
pagesize,pageindex,ordertype,strwhere,ConnectionString);

IDataReader dr = helper2.GetDataReader();

FavListCollection list = new FavListCollection();

while(dr.Read())
{
list.Add(PopulateFavList(dr));
}

dr.Close();

return list;
}
DataGrid调用就不用说了吧。。
关于该分页的Bug和局限性
Bug:当排序那个字段内容相同的时候(例如:按时间来排序,而时间是一样的话。后面的记录会显示不出来。本人测试过)
局限性:排序只能一个字段,不能超过一个