create PROCEDURE getDataRecordCountByPage
(
@Tables varchar(1000),
@PK varchar(100),
@SortField varchar(200) = NULL,
@Sort varchar(4) = 'desc',
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@RecordCount int output
)
AS
/*Find the @PK type 查找主键类型*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*Set filter & group variables. 设置 过滤条件和分组条件*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
select @strFilter = ' WHERE ' + @Filter + ' '
select @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
select @strSimpleFilter = ''
select @strFilter = ''
END
exec @RecordCount = getRecordCount @Tables,@strFilter
/*Default Sorting 设置排序*/
IF @Sort IS NULL OR @Sort = ''
select @Sort = @PK
select @strSortColumn = @SortField
/*Set sorting variables. 设置排序变量*/
IF charindex('desc',@Sort)>0
if @Sort = 'desc'
select @operator = '<='
ELSE
select @operator = '>='
select @SortTable = @Tables
select @SortName = @strSortColumn
/*获取地段类型名称，字段类型精度
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.usertype=t.usertype
WHERE o.name = @SortTable AND c.name = @SortName
*/
select @type = 'int'
select @prec = 10
/* 默认第1页*/
IF @PageNumber < 1
select @PageNumber = 1
/*设置单页显示数量*/
select @strPageSize = convert(varchar(50),@PageSize)
select @strStartRow = convert(varchar(50),((@PageNumber - 1)*@PageSize + 1) )
IF @Group IS NOT NULL AND @Group != ''
select @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
select @strGroup = ''
/*Execute dynamic query 执行动态sql语句*/
declare @strSql varchar(4000)
select @strSql = "DECLARE @SortColumn " + @type + " set ROWCOUNT " + @strStartRow + " SELECT @SortColumn=" + @strSortColumn + " FROM " + @Tables + " " + @strFilter + " " +@strGroup + " ORDER BY "+ @SortField +" " + @Sort + " set ROWCOUNT " + @strPageSize + " SELECT " + @Fields + " FROM " + @Tables + " WHERE " + @strSortColumn + @operator + " @SortColumn " + @strSimpleFilter + " " + @strGroup + " ORDER BY " + @SortField +" " + @Sort + " set RowCount 0 "
EXEC(@strSql)
print @strSql
GO
发一个数据访问层的源码
需积分: 10 160 浏览量
2008-08-26
14:40:49
上传
评论
收藏 369KB RAR 举报
数据库访问层源码.rar （29个子文件） 
DB bin Debug 
DB.pdb 106KB MySql.Data.dll 144KB DB.dll 44KB DBOperatorFactory.cs 3KB CommonLib.cs 220B obj Debug DB.pdb 106KB DB.dll 44KB
DB.csproj.FileList.txt 140B DBOperator.cs 9KB Properties AssemblyInfo.cs 1KB DB.csproj.user 168B DB.suo 179KB CommonException ParamValueNotMatchException.cs 357B vssver2.scc 149B DB.sln 892B SqlDBOperator.cs 23KB Procedure sybasePageProcedureRecourdcount.txt 3KB sqlserverPageProcedure.txt 3KB getRecordCount.txt 273B sybasePageProcedure.txt 2KB OdbcDBOperator.cs 25KB MySqlDBOperator.cs 23KB OracleDBOperator.cs 22KB OleDBOperator.cs 20KB dll MySql .NET 1.0 MySql.Data.dll 144KB .NET 2.0 MySql.Data.dll 140KB .NET 1.1 MySql.Data.dll 144KB Mono 1.0 MySql.Data.dll 131KB DB.csproj 4KB
评论0