一个支持多表,多字段排序,支持别名的分页,大家看看,随便问一个进程/锁的问题
我在.net中使用单键模式,使用同一个数据库操作对象操作数据库,而且连接都有正常关闭,可是在SQL SERVER的企业管理器中发现进程/锁很多。 不知道这是为什么?
分页如下:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create proc dbo.Up_SysGetObjectPageList
--分页存储过程,在多表排序时,排序的第一个字段的排序方向由 变量 @OrderType 来确定。
@TableName nvarchar(255) , -- 表名,可以以 " hmeThread t,hmePost p " 的格式,甚至可以包括子查询
@GetFields nvarchar(1000) = '*', -- 需要返回的列 ,返回的列要注意不能有歧义,否则可能出错
@OrderFieldName nvarchar(255)='', -- 排序的字段名 "p.post_ID desc ,p.createdDate asc "
@strWhere nvarchar(1500) = '', -- 查询条件 (注意: 不要加 where) ,格式为 " and a = b "
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType int = 0, -- 设置排序类型, 非 0 值则降序
@IsCount bit = 0 -- 设置是否统计,如果为真,则不返回数据集合,而是返回中选记录总数,反之则只返回数据集合
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp nvarchar(110) -- 临时变量
declare @strOrder nvarchar(400) -- 排序类型
declare @intPosTmp int --临时位置
declare @strFirstOrderFullName nvarchar(50) -- 排序字段的完整名称,有带别名,如t.thread_ID
declare @strFirstOrderName nvarchar(50) -- 排序字段的字段名称,不带别名,如 thread_ID
declare @strSubjoinOrder nvarchar(200) -- 附加的排序
set @intPosTmp = charindex(',',@OrderFieldName)
set @strSubjoinOrder =''
if @intPosTmp >0
begin
set @strFirstOrderFullName = SUBSTRING(@OrderFieldName,1,@intPosTmp-1)
set @strSubjoinOrder = SUBSTRING(@OrderFieldName,@intPosTmp,len(@OrderFieldName) - @intPosTmp+1)
end
else
set @strFirstOrderFullName = @OrderFieldName
set @strFirstOrderFullName = lower(@strFirstOrderFullName)
set @strFirstOrderFullName = replace(@strFirstOrderFullName,' asc','')
set @strFirstOrderFullName = replace(@strFirstOrderFullName,' desc','')
set @intPosTmp = charindex('.',@strFirstOrderFullName)
if @intPosTmp>0
set @strFirstOrderName = SUBSTRING(@strFirstOrderFullName,@intPosTmp+1,len(@strFirstOrderFullName)-@intPosTmp)
else
set @strFirstOrderName = @strFirstOrderFullName
begin
if @strWhere <> ''
set @strWhere = ' 1=1 '+@strWhere
else
set @strWhere = ' 1=1 '
if @OrderType != 0
begin
set @strTmp = '<(select min'
if @OrderFieldName<>''
set @strOrder = ' order by ' + @strFirstOrderFullName +' desc' + @strSubjoinOrder
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
if @OrderFieldName<>''
set @strOrder = ' order by ' + @strFirstOrderFullName + @strSubjoinOrder
end
if @PageIndex = 1
set @strSQL = 'select top ' + ltrim(str(@PageSize)) +' ' + @GetFields +' from ' + @TableName + ' where ' + @strWhere + @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
if @strWhere != ''
set @strSQL = 'select top ' + ltrim(str(@PageSize)) +' ' +@GetFields +' from '
+ @TableName + ' where '+ @strWhere + ' and ' + @strFirstOrderFullName + @strTmp + '('
+ @strFirstOrderName + ') from (select top ' + ltrim(str((@PageIndex-1)*@PageSize)) + ' '
+ @strFirstOrderFullName + ' from ' + @TableName + ' where ' + @strWhere
+ @strOrder + ') as tblTmp) ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +@GetFields +' from '
+ @TableName + ' where ' + @strFirstOrderFullName + @strTmp + '('+ @strFirstOrderName + ') from (select top ' + str((@PageIndex)*@PageSize) + @strFirstOrderFullName + ' from ' + @TableName + @strOrder + ') as tblTmp)'+ @strOrder
end
end
--select @strSQL
exec (@strSQL)
if @IsCount=1
exec('select count(*) from ' + @TableName + ' where ' + @strWhere )
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO