SQL：分页存储过程(优化了部分语句,附C#调用接口)

网上分页存储过程的代码很多，这个是我一直在用的经典的三层架构里提取出来的，并做了部分语句的优化...

如有更好的欢迎讨论！

01	setANSI_NULLS ON

02	setQUOTED_IDENTIFIER ON

03

GO

04	------------------------------------

05	--用途：分页存储过程(对有主键的表效率极高)

06	--说明：（优化了部分语句）

07	------------------------------------

08

09	ALTERPROCEDURE[dbo].[TP_GetRecordByPage]

10	@tblName      varchar(255),       -- 表名

11	@fldName      varchar(255),       -- 主键字段名

12	@PageSize     int= 10,           -- 页尺寸

13	@PageIndex    int= 1,            -- 页码

14	@IsReCount    bit= 0,            -- 返回记录总数, 非 0 值则返回

15	@OrderType    bit= 0,            -- 设置排序类型, 非 0 值则降序

16	@strWhere     varchar(1000) = ''-- 查询条件 (注意: 不要加 where)

17

AS

18

19	declare@strSQL   varchar(6000)       -- 主语句

20	declare@strTmp   varchar(800)        -- 临时变量(查询条件过长时可能会出错，可修改100为1000)

21	declare@strOrder varchar(400)        -- 排序类型

22

23	if @OrderType != 0

24

begin

25	set@strTmp = '<(select min'

26	set@strOrder = ' order by ['+ @fldName +'] desc'

27

end

28

else

29

begin

30	set@strTmp = '>(select max'

31	set@strOrder = ' order by ['+ @fldName +'] asc'

32

end

33

34	set@strSQL = 'select top '+ str(@PageSize) + ' * from ['

35	+ @tblName + '] where ['+ @fldName + ']'+ @strTmp + '(['

36	+ @fldName + ']) from (select top '+ str((@PageIndex-1)*@PageSize) + ' ['

37	+ @fldName + '] from ['+ @tblName + ']'+ @strOrder + ') as tblTmp)'

38	+ @strOrder

39

40	if @strWhere != ''

41	set@strSQL = 'select top '+ str(@PageSize) + ' * from ['

42	+ @tblName + '] where ['+ @fldName + ']'+ @strTmp + '(['

43	+ @fldName + ']) from (select top '+ str((@PageIndex-1)*@PageSize) + ' ['

44	+ @fldName + '] from ['+ @tblName + '] where '+ @strWhere + ' '

45	+ @strOrder + ') as tblTmp) and '+ @strWhere + ' '+ @strOrder

46

47	if @PageIndex = 1

48

begin

49	set@strTmp =''

50	if @strWhere != ''

51	set@strTmp = ' where '+ @strWhere

52

53	set@strSQL = 'select top '+ str(@PageSize) + ' * from ['

54	+ @tblName + ']'+ @strTmp + ' '+ @strOrder

55

end

56

57	if @IsReCount != 0

58	set@strSQL = 'select count(*) as Total from ['+ @tblName + ']'+' where '+ @strWhere

59

60	exec(@strSQL)

以下是C#调用的接口方法

使用时注意一下命名空间改为你的，需要引用DBUtility操作类。

01	/// <summary>

02	/// 得到记录总数

03	/// </summary>

04	/// <param name="tableName">表名</param>

05	/// <returns></returns>

06	publicstaticintGetRecordCount(stringtableName)

07

{

08	returnGetRecordCount(tableName, "");

09

}

10	/// <summary>

11	/// 得到记录总数

12	/// </summary>

13	/// <param name="tableName">表名</param>

14	/// <param name="strWhere">筛选条件（可以不用加where）</param>

15	/// <returns></returns>

16	publicstaticintGetRecordCount(stringtableName, stringstrWhere)

17

{

18	stringstrsql = string.Empty;

19	if(string.IsNullOrEmpty(strWhere))

20	strsql = "select count(*) from "+ tableName;

21

else

22	strsql = "select count(*) from "+ tableName + " where "+ strWhere;

23	objectobj = TopkeeOA.DBUtility.DbHelperSQL.GetSingle(strsql);

24	if(obj == null)

25

{

26

return0;

27

}

28

else

29

{

30	returnint.Parse(obj.ToString());

31

}

32

}

33

34	/// <summary>

35	/// 分页获取数据列表（分页）

36	/// </summary>

37	/// <param name="tableName">表名</param>

38	/// <param name="PageSize">每页显示多少要记录</param>

39	/// <param name="PageIndex">当前页</param>

40	/// <param name="strWhere">筛选条件（可为空）</param>

41	/// <param name="FieldName">排序字段（一般为主键ID）</param>

42	/// <param name="OrderType">排序类型（0为降序，1为升序）</param>

43	/// <returns></returns>

44	publicstaticDataSet GetList(stringtableName, intPageSize, intPageIndex, stringstrWhere, stringFieldName, intOrderType)

45

{

46	SqlParameter[] parameters = {

47	newSqlParameter("@tblName", SqlDbType.VarChar, 255),

48	newSqlParameter("@fldName", SqlDbType.VarChar, 255),

49	newSqlParameter("@PageSize", SqlDbType.Int),

50	newSqlParameter("@PageIndex", SqlDbType.Int),

51	newSqlParameter("@IsReCount", SqlDbType.Bit),

52	newSqlParameter("@OrderType", SqlDbType.Bit),

53	newSqlParameter("@strWhere", SqlDbType.VarChar,1000),

54

};

55	parameters[0].Value = tableName;

56	parameters[1].Value = FieldName;

57	parameters[2].Value = PageSize;

58	parameters[3].Value = PageIndex;

59	parameters[4].Value = 0;

60	parameters[5].Value = OrderType;

61	parameters[6].Value = strWhere;

62	returnTopkeeOA.DBUtility.DbHelperSQL.RunProcedure("TP_GetRecordByPage", parameters, "ds");

63

}

第一次使用请把“ALTERPROCEDURE ”改为“CREATEPROCEDURE ”以便创建一个新的存储过程。

AspNetPager调用示例：

view sourceprint?

01	privatevoidDataBinder()

02

{

03	DataList1.DataSource = GetList("Test",AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, "", 0);

04	DataList1.DataBind();

05

}

06

07	protectedvoidAspNetPager1_PageChanged(objectsrc, EventArgs e)

08

{

09	DataBinder();

10

}