CREATE PROCEDURE AspNetPager
(
@TableName nvarchar(50), -- 表名
@ReturnFields nvarchar(2000) = '*', -- 需要返回的列
@PageSize int = 10, -- 每页记录数
@PageIndex int = 1, -- 当前页码
@Where nvarchar(2000) = '', -- 查询条件
@Orderfld nvarchar(2000), -- 排序字段名 最好为唯一主键
@OrderType int = 1 -- 排序类型 1:降序 其它为升序
)
AS
DECLARE @TotalRecord int
DECLARE @TotalPage int
DECLARE @CurrentPageSize int
DECLARE @TotalRecordForPageIndex int
DECLARE @OrderBy nvarchar(255)
DECLARE @CutOrderBy nvarchar(255)
if @OrderType = 1
BEGIN
set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
END
else
BEGIN
set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc '
set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '
END
-- 记录总数
declare @countSql nvarchar(4000)
set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord out
SET @TotalPage=(@TotalRecord-1)/@PageSize+1
SET @CurrentPageSize=@PageSize
IF(@TotalPage=@PageIndex)
BEGIN
SET @CurrentPageSize=@TotalRecord%@PageSize
IF(@CurrentPageSize=0)
SET @CurrentPageSize=@PageSize
END
-- 返回记录
set @TotalRecordForPageIndex=@PageIndex*@PageSize
exec('SELECT * FROM
(SELECT TOP '+@CurrentPageSize+' * FROM
(SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+'
FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2
'+@CutOrderBy+') TB3
'+@OrderBy)
-- 返回总页数和总记录数
SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
public static DataTable GetPagerData(AspNetPager PagerControl,string Sql,string OrderField,OrderType Type)
{
DataTable dt = new DataTable();
int RecordCount = 0;
int TempIndex = 0;
int TempEndIndex = 0;
string TableName = String.Empty;
string Where = String.Empty;
string Cells = String.Empty;
Sql = Sql.ToLower();
using(SqlConnection conn=GetSqlConnection())
{
try
{
TempIndex = Sql.IndexOf("select") + 6;
TempEndIndex = Sql.IndexOf("from");
Cells = Sql.Substring(TempIndex, TempEndIndex - TempIndex);
TempIndex = Sql.IndexOf("where");
if (TempIndex != -1)
{
TableName = Sql.Substring(TempEndIndex + 4, TempIndex - (TempEndIndex + 4)).Trim();
Where = Sql.Substring(TempIndex);
}
else
{
TableName = Sql.Substring(TempEndIndex + 4).Trim();
}
SqlCommand cmd = new SqlCommand("AspNetPager", conn);
cmd.CommandType=CommandType.StoredProcedure;
// 设置参数
cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 500).Value = TableName;
cmd.Parameters.Add("@ReturnFields", SqlDbType.NVarChar, 500).Value = "*";
cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = Where;
cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PagerControl.CurrentPageIndex;
cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PagerControl.PageSize;
cmd.Parameters.Add("@Orderfld", SqlDbType.NVarChar, 200).Value = OrderField;
cmd.Parameters.Add("@OrderType", SqlDbType.Int).Value = Type;
// 执行
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//dt.Load(dr);
for (int i = 0; i < dr.FieldCount; i++)
{
dt.Columns.Add(dr.GetName(i));
}
while (dr.Read())
{
DataRow Row = dt.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
Row[i] = dr[i].ToString();
}
dt.Rows.Add(Row);
}
// 取记录总数 及页数
if (dr.NextResult())
{
if (dr.Read())
{
RecordCount = Convert.ToInt32(dr["RecordCount"]);
}
}
PagerControl.RecordCount = RecordCount;
cmd.Dispose();
}
catch (Exception)
{
throw;
}
}
return dt;
}