存储分页代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】存储分页

作者:CSDN / 发布于2012/1/11/ 699

<div><span style="color: rgb(255,0,0)">存储分页</span>
CREATE PROCEDURE proc_Page 
@Table VARCHAR(100), --表名 
@Primarykey VARCHAR(100), --主键 
@Condition VARCHAR(500), --查询条件 
@PageNumber INT, --开始页数 
@PageSize INT, --每页大小 
@IsCount BIT --是否获得记录数，0为否 
AS 
DECLARE @SQL VARCHAR(8000) 
IF @IsCount != 0 
SET @SQL = 'SELECT Count(*) FROM ' + @Table + ' WHERE ' + @Condition 
ELSE 
BEGIN 
IF @PageNumber = 1 
SET @SQL = 'SELECT TOP ' + STR(@PageSize) + ' SerialNum AS 流水号,PatientName AS 病人姓名,CheckDate AS 检查日期 FROM ' + @Table + ' WHERE ' + @Condition 
ELSE 
SET @SQL = 'SELECT TOP ' + STR(@PageSize) + ' SerialNum AS 流水号,PatientName AS 病人姓名,CheckDate AS 检查日期 FROM ' + @Table + 
' WHERE ' + @Primarykey + ' NOT IN (SELECT TOP ' + STR(@PageSize*(@PageNumber - 1)) 
+ ' ' + @Primarykey + ' FROM ' + @Table + ' WHERE ' + @Condition + ') AND ' + @Condition 
END 
EXEC(@SQL) 
RETURN

<span style="color: rgb(255,0,0)">调用函数</span>
public static DataTable GetPatientInfo(string Condition, string StoreProcedure, int pageNumber, int PageSize, int isCount)
{
SqlParameter[] sqlParameter = 
{ 
new SqlParameter("@Table","CheckRecord"), 
new SqlParameter("@Primarykey","SerialNum"), 
new SqlParameter("@Condition",Condition), 

new SqlParameter("@PageNumber",pageNumber), 
new SqlParameter("@PageSize",PageSize), 
new SqlParameter("@IsCount",isCount) 
};
using (SqlConnection sqlConnection = new SqlConnection(WebConfig.ConnectionString))
{
try
{
sqlConnection.Open();
SqlCommand sqlcommand = new SqlCommand(StoreProcedure, sqlConnection);
sqlcommand.CommandType = CommandType.StoredProcedure;
sqlcommand.Parameters.AddRange(sqlParameter);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlcommand;
DataTable dt = new DataTable();
sqlDataAdapter.Fill(dt);
return dt;
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
}

试试其它关键字

　存储分页　

同语言下

可能有用的

CSDN贡献的其它代码(27)