34,576
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('PageTest') IS NOT NULL
DROP PROC PageTest
GO
CREATE PROCEDURE [dbo].[PageTest]
@Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
@TIndex NVARCHAR(100), --主键,可以带表头 a.AID
@Column NVARCHAR(2000) = '*',--读取字段
@Sql NVARCHAR(3000) = '',--Where条件
@PageIndex INT = 1, --开始页码
@PageSize INT = 10, --页大小
@Sort NVARCHAR(200) = '' --排序字段
AS
--_PageTest 'HoursMIPK','id','*','',1,10,'id'
DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0
BEGIN
SET @strWhere = ' WHERE ' + @Sql + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END
IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)
BEGIN
IF(@Sort='')
SET @Sort = @TIndex + ' DESC '
ELSE
SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '
END
IF @PageIndex < 1
SET @PageIndex = 1
IF @PageIndex = 1 --第一页提高性能
BEGIN
SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ ' FROM ' + @Table + ' ' + @strWhere + ' ORDER BY '+ @Sort
END
ELSE
BEGIN
DECLARE @START_ID NVARCHAR(50)
DECLARE @END_ID NVARCHAR(50)
SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)
SET @strsql = ' SELECT '+@Column+ '
FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum,
'+@Column+ '
FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D
WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
END
EXEC(@strsql)
PRINT @strsql
SET @strsql = 'SELECT Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere
PRINT @strsql
EXEC(@strsql)
/// <summary>
/// 获取数据源
/// </summary>
/// <param name="conn"></param>
/// <param name="strProcedure"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
private DataTable GetData(string conn, string strProcedure, int pageIndex, int pageSize)
{
using (SqlConnection connection = new SqlConnection(conn))
{
SqlCommand command = new SqlCommand(strProcedure, connection);
command.CommandType = CommandType.StoredProcedure;//采用存储过程
//存储过程参数
command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = "HoursMIPK";
command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "ID";
command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = "*";
command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = " 1=1 ";
command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();
command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();
command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = " ID desc";
//打开连接
if (connection.State != ConnectionState.Open) { connection.Open(); }
try
{
//填充数据
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
//获取总记录数
RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
//返回数据集
return ds.Tables[0];
}
catch (SqlException err)
{
MessageBox.Show(err.Message);
return null; ;
}
finally
{
connection.Close();
}
}
}
这里有个我以前写的:
Use LvRui
Go
if object_ID('[rGiftByCondition]') is not null
Drop Procedure [rGiftByCondition]
Go
/****************************************************************************************************************************************************************
%%存储过程名:rGiftByCondition
%%输入参数:
%%输出参数:
%%功能:根据条件读取记录在表Gift
****************************************************************************************************************************************************************
%%编写:fredrickhu 2013-11-08
exec rGiftByCondition 30,1,3,'飞机',100000,100
****************************************************************************************************************************************************************/
CREATE PROCEDURE rGiftByCondition
(
@PageSize int=30,
@PageCount int=1,
@GiftCatagroyID SMALLINT=NULL,
@CName VARCHAR(50)=NULL , --关键字
@MaxPoint INT=NULL ,
@MinPoint INT=NULL ,
@RowCount INT=0 OUTPUT
)
AS
DECLARE @PageTotalCount INT
--SELECT @GiftCatagroyID=ISNULL(@GiftCatagroyID,0),@MaxPoint=ISNULL(@MaxPoint,0),@MinPoint=ISNULL(@MinPoint,0),@RowCount=ISNULL(@RowCount,0)
SELECT
@RowCount=COUNT(1)
FROM
Gift a
--LEFT JOIN GiftPhoto b ON a.ID=b.GiftID
INNER JOIN GiftCatagroy c ON a.GiftCatagroyID=c.ID
WHERE
[Effectiveness]=1
AND
(a.GiftCatagroyID=@GiftCatagroyID OR @GiftCatagroyID IS NULL)
AND
(a.CName LIKE '%'+@CName+'%' OR @CName IS NULL)
AND
(((a.Point>=@MinPoint AND a.Point<=@MaxPoint) OR (@MinPoint IS NULL AND @MaxPoint IS NULL)) OR (a.Point>=@MinPoint AND @MaxPoint IS NULL) OR (a.Point<=@MaxPoint AND @MinPoint IS NULL))
SELECT @PageTotalCount=CEILING(@RowCount*1.0/@PageSize),@PageCount=CASE WHEN @PageCount>=@PageTotalCount THEN @PageTotalCount ELSE @PageCount END
;WITH CTE AS
(
SELECT
a.[ID] ,
a.GiftCatagroyID,
a.GiftNr,
a.[CName] ,
a.[Point] ,
a.[Remark],
a.DefaultPhotoPath AS PhotoPath,
c.Code AS GiftCatagroyCode,
c.[CName] AS GiftCatagroyName,
ROW_NUMBER()OVER(ORDER BY a.[CreateDate] DESC) AS rows
FROM
Gift a
--LEFT JOIN GiftPhoto b ON a.ID=b.GiftID
INNER JOIN GiftCatagroy c ON a.GiftCatagroyID=c.ID
WHERE
a.[Effectiveness]=1
AND
(a.GiftCatagroyID=@GiftCatagroyID OR @GiftCatagroyID IS NULL)
AND
(a.CName LIKE '%'+@CName+'%' OR @CName IS NULL)
AND
(((a.Point>=@MinPoint AND a.Point<=@MaxPoint) OR (@MinPoint IS NULL AND @MaxPoint IS NULL)) OR (a.Point>=@MinPoint AND @MaxPoint IS NULL) OR (a.Point<=@MaxPoint AND @MinPoint IS NULL))
)
SELECT
[ID] ,
GiftNr,
[CName] ,
[Point] ,
[Remark],
PhotoPath,
GiftCatagroyCode,
GiftCatagroyName
FROM
CTE
WHERE
rows BETWEEN (@PageCount-1)*@PageSize+1 AND @PageCount*@PageSize
Go