MS Sql Server分页存储过程以及C#调用

前面把Oracle的分页存储过程写了，这里也贴出MS SQL Server的分页存储过程，不过这个存储过程的灵活性没有Oracle那个强，大家如果有好的建议或者方法，记得留言哦

闲话不扯了，贴代码：

1、存储过程：

Create or procedure AspNetPage
@tblName varchar(1000), -- 表名
@SelectFieldName varchar(4000), -- 要显示的字段名(不要加select)
@strWhere varchar(4000), -- 查询条件(注意: 不要加 where)
@OrderFieldName varchar(255), -- 排序索引字段名
@PageSize int , -- 页大小
@PageIndex int = 1, -- 页码
@iRowCount int output, -- 返回记录总数
@OrderType bit = 0 -- 设置排序类型, 非 0 值则降序

AS
declare @strSQL varchar(4000) -- 主语句
declare @strTmp varchar(4000) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strRowCount nvarchar(4000) -- 用于查询记录总数的语句
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @tblName + @strTmp + ' ' + @strOrder
end
exec(@strSQL)
if @strWhere!=''
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName
end
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out

2、C#调用：

/// <summary>
/// 分页数据
/// </summary>
/// <param name="TableName">表明</param>
/// <param name="RetureFields">返回字段</param>
/// <param name="strWhere">条件</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="CurPage">当前页数</param>
/// <param name="RowCount">总记录数</param>
/// <param name="sortField">排序字段</param>
/// <returns></returns>
public static DataTable GetPageList(string TableName, string RetureFields, string strWhere, int PageSize, int CurPage, out int RowCount, string sortField)
{
SqlCommand cmd = new SqlCommand("AspNetPage");//存储过程名称
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tblName", TableName);//表名称
cmd.Parameters.AddWithValue("@OrderFieldName", sortField);//排序索引字段名
cmd.Parameters.AddWithValue("@PageIndex", CurPage);//当前第几页，页码
cmd.Parameters.AddWithValue("@PageSize", PageSize);//每页显示的数据条数
cmd.Parameters.AddWithValue("@SelectFieldName", RetureFields);//要显示的字段名(不要加Select)
cmd.Parameters.AddWithValue("@OrderType", 1);//设置排序类型，非0值则降序
cmd.Parameters.AddWithValue("@strWhere", strWhere);//查询条件，不要加where
cmd.Parameters.Add(new SqlParameter("@iRowCount", SqlDbType.Int));
cmd.Parameters["@iRowCount"].Direction = ParameterDirection.Output;
DataTable dt = RunProcedureCmd(cmd);
RowCount = Convert.ToInt32(cmd.Parameters["@iRowCount"].Value.ToString());//返回的总页数
return dt;
}

/// <summary>
/// 执行存储过程，返回DataTable
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public static DataTable RunProcedureCmd(SqlCommand cmd)
{
DataTable result = new DataTable();
SqlConnection conn = new SqlConnection(ConnectionString);//你自己的链接字符串
try
{
if ((conn.State == ConnectionState.Closed))
{
conn.Open();
}
cmd.Connection = conn;
WriteLog(cmd.CommandText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(result);
da.Dispose();
conn.Close();
conn.Dispose();

return result;
}
catch (Exception ex)
{
conn.Close();
conn.Dispose();
throw ex;
}
}

OK搞定，勿喷!