SQL Server 百万级数据分页存储过程 分页
CREATE PROC [dbo].[Common_PageList]
(
@tab nvarchar(max),---数据表名
@strFld nvarchar(max), --要显示的字段
@strWhere varchar(max), --where条件
@PageIndex int, --当页页码
@PageSize int, --页码尺寸
@Sort VARCHAR(255), --排序字段及规则,不用加order by
@IsGetCount bit --是否得到记录总数,1为得到记录总数,0为不得到记录总数,返回记录集
)
AS
declare @strSql nvarchar(max)
set nocount on;
if(@IsGetCount = 1)
begin
set @strSql='SELECT COUNT(0) FROM ' + @tab + ' WHERE ' + @strWhere
end
else
begin
set @strSql=' SELECT * FROM (SELECT ROW_NUMBER()
OVER(ORDER BY ' + @Sort + ') AS rownum, ' + @strFld + ' FROM ' + @tab + ' where ' + @strWhere + ') AS Dwhere
WHERE rownum BETWEEN ' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar(20)) + ' and ' + cast((@PageIndex*@PageSize) as nvarchar(20))
end
exec (@strSql)
set nocount off;
select * from [dbo].[Teacher]
存储过程
调用方法; 执行方法;
exec Common_PageList 'Teacher','*','',1,10,null,0
C#调用方法;调用方式;
/// <summary>
/// 读取存贮过程
/// </summary>
/// <param name="ProcName"></param>
/// <param name="Pars"></param>
/// <returns></returns>
public static DataTable ExexProcQuery(string ProcName, SqlParameter[] Pars)
{
SqlConnection con = new SqlConnection(ConnectionString);
SqlDataAdapter sda = new SqlDataAdapter(ProcName, con);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Pars.Length; i++)
{
sda.SelectCommand.Parameters.Add(Pars[i]);
}
//这里写成
DataTable dt = new DataTable();
try
{
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
//LogTool.WriteLog(ex.Message.ToString());
return dt;
}
finally
{
con.Close();
}
}