SQL存储过程分页以及.NET调用
create proc Test
@PageIndex INT,--@PageIndex从计数,0为第一页
@PageSize INT, --页面大小
@RecordCount INT OUT, --总记录数
@PageCount INT OUT--页数
as
SELECT @RecordCount = COUNT(*) FROM A --获取记录数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) --计算页面数据
SELECT SerialNumber,Id,Names,Age FROM
(SELECT Id,Names,Age,ROW_NUMBER() OVER (ORDER BY Id ) AS SerialNumber FROM A ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
go
存储过程测试：
DECLARE @RecordCount int ,@PageCount int
exec Test 0,10, @RecordCount OUTPUT, @PageCount OUTPUT
select @RecordCount,@PageCount
go
存储过程的调用：
-----已更新---
DBHelper类中的方法：
/// <summary>
/// sql带返回参数的存储过程(分页)
/// </summary>
/// <param name="procName">存储过程名字</param>
/// <param name="outParameterName">输出参数的名字</param>
/// <param name="dic">输出参数得到的值</param>
/// <param name="pars">参数列表</param>
/// <returns>DataTable</returns>
public static DataTable GetFillData(string procName,string [] outParameterName, out Dictionary<string,object> dic,params SqlParameter[] pars)
{
Dictionary<string, object> dictionary = new Dictionary<string, object>();
DataSet ds = new DataSet();
using (cmd = new SqlCommand(procName, Connectionstrings))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(pars);
foreach (var item in outParameterName)
{
cmd.Parameters[item].Direction = ParameterDirection.Output;
}
using (adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(ds);
dic = dictionary;
foreach (var item in outParameterName)
{
dictionary.Add(item, cmd.Parameters[item].Value.ToString());
}
return ds.Tables[0];
}
}
}
DBHelper类可能大家的都不同，可以根据自己的DBHelper类修改此方法。
在这里我也把自己用的DBHelper写出来（注重版权，本人只是交流需要，无其他商业目的）
// ***********************************************************************
// Copyright (C) 2008-2009 じJF[CMS]ve
// All rights reserved
// guid1: 5315b4dc-168b-44a3-98c0-83524a8bfcce
// CLR Version: 2.0.50727.1433
// CreateUser： じJF[CMS]ve
// Email: jfcms_lzq@qq.com
// SiteWeb： http://www.jfstudio.net
// CreateDate: 11/08/2008 13:22:59
//
// ***********************************************************************
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace WebSite1.DAL
{
/// <summary>
/// DBHelper 的摘要说明
/// </summary>
public class DBHelper : IDisposable
{
//定义这个类要使用的全局变量
private static string constr;
private static SqlConnection conn;
private static SqlCommand cmd;
private static SqlDataReader dr;
private static SqlDataAdapter adapter;
/// <summary>
/// 数据库连接属性
/// </summary>
public static SqlConnection Connectionstrings
{
get
{
constr = ConfigurationManager.ConnectionStrings["SqlConnectionStrings"].ToString();
//上面这个必须添加引用System.configuartion
conn = new SqlConnection(constr);
//DotNet默认打开数据库连接池
conn.Open();
return conn;
}
}
// 先做几个处理 ,该类实现了IDisposable接口，自动调用非托管堆中释放资源，在由GC自动清理。
public void Dispose()
{
Close();
cmd.Dispose();
dr.Dispose();
conn.Dispose();
}
/// <summary>
/// 取消 Command 执行，并关闭 DataReader 对象和数据连接
/// </summary>
public void Close()
{
cmd.Cancel();
if (!dr.IsClosed)
dr.Close();
if (conn.State != ConnectionState.Closed)
conn.Close();
}
/// <summary>
/// 创建一个 SQL 参数，主要实现SqlParameter[] 参数列表
/// </summary>
/// <param name="parameterName">参数名</param>
/// <param name="dbType">类型</param>
/// <param name="value">值</param>
/// <returns>返回创建完成的参数</returns>
public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, object value)
{
SqlParameter result = new SqlParameter(parameterName, dbType);
//if(value!=null)
result.Value = value;
return result;
}
/// <summary>
/// 单向操作，主要用于（增加，删除，修改）,返回受影响的行数
/// </summary>
/// <param name="cmdTxt">安全的sql语句（string.format）</param>
/// <returns></returns>
public static int GetExcuteNonQuery(string cmdTxt)
{
return GetExcuteNonQuery(cmdTxt, null);
}
/// <summary>
/// 带参数化的 主要用于（增加，删除，修改）,返回受影响的行数
/// </summary>
/// <param name="cmdTxt">带参数列表的sql语句</param>
/// <param name="pars">要传入的参数列表</param>
/// <returns></returns>
public static int GetExcuteNonQuery(string cmdTxt, params SqlParameter[] pars)
{
using (cmd = new SqlCommand(cmdTxt, Connectionstrings))
{
if (pars != null)
cmd.Parameters.AddRange(pars);
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 对连接执行 Transact-SQL 语句或者存储过程并返回受影响的行数
/// </summary>
/// <param name="cmdText">SQL 语句或者存储过程名称</param>
/// <param name="cmdType">枚举存储过程或者sql查询文本</param>
/// <param name="pars">参数</param>
/// <returns>受影响的行数</returns>
public static int GetExcuteNonQuery(string cmdTxt, CommandType cmdtype, params SqlParameter[] pars)
{
using (cmd = new SqlCommand(cmdTxt, Connectionstrings))
{
cmd.CommandType = cmdtype;
cmd.Parameters.AddRange(pars);
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <typeparam name="T">返回的类型</typeparam>
/// <param name="cmdText">SQL 语句</param>
/// <param name="pars">参数列表</param>
/// <returns>结果集中第一行的第一列或空引用</returns>
public static T ExecuteScalar<T>(string cmdText, params SqlParameter[] pars)
{
using (SqlCommand cmd = new SqlCommand(cmdText, Connectionstrings))
{
if (pars != null)
cmd.Parameters.AddRange(pars);
T result = (T)cmd.ExecuteScalar();
conn.Close();
return result;
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <typeparam name="T">返回的类型</typeparam>
/// <param name="cmdType">枚举存储过程或者sql查询文本</param>
/// <param name="cmdText">SQL 语句或者存储过程名称</param>
/// <returns></returns>
public static T ExecuteScalar<T>(string cmdText, CommandType type, params SqlParameter[] pars)
{
using (SqlCommand cmd = new SqlCommand(cmdText, Connectionstrings))
{
cmd.CommandType = type;
if (pars != null)
cmd.Parameters.AddRange(pars);
T result = (T)cmd.ExecuteScalar();
conn.Close();
return result;
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="cmdText">sql语句</param>
/// <returns></returns>
public static T ExecuteScalar<T>(string cmdText)
{
return ExecuteScalar<T>(cmdText, null);
}
/// <summary>
/// 将 cmdText 发送到 System.Data.SqlClient.SqlCommand.Connection，并使用 System.Data.CommandBehavior 值之一生成一个 DataReader
/// </summary>
/// <param name="cmdTxt">安全的sql语句（string.format）</param>
/// <returns>一个 DataReader 对象</returns>
public static SqlDataReader GetDataReader(string cmdTxt)
{
return GetDataReader(cmdTxt, null);
}
/// <summary>
/// 将 cmdText 发送到 System.Data.SqlClient.SqlCommand.Connection，并使用 System.Data.CommandBehavior 值之一生成一个 DataReader
/// </summary>
/// <param name="cmdTxt">安全的sql语句（string.format）</param>
/// <param name="pars">参数</param>
/// <returns>一个 DataReader 对象</returns>
public static SqlDataReader GetDataReader(string cmdTxt, params SqlParameter[] pars)
{
using (cmd = new SqlCommand(cmdTxt, Connectionstrings))
{
if (pars != null)
cmd.Parameters.AddRange(pars);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
}
/// <summary>
/// 将 cmdText 发送到 System.Data.SqlClient.SqlCommand.Connection，并使用 System.Data.CommandBehavior 值之一生成一个 DataReader
/// </summary>
/// <param name="cmdTxt">安全的sql语句（string.format）或者存储过程名称</param>
/// <param name="pars">参数</param>
/// <param name="cmdType">枚举存储过程或者sql查询文本</param>
/// <returns>一个 DataReader 对象</returns>
public static SqlDataReader GetDataReader(string cmdTxt, CommandType type, params SqlParameter[] pars)
{
using (cmd = new SqlCommand(cmdTxt, Connectionstrings))
{
cmd.CommandType = type;
if (pars != null)
cmd.Parameters.AddRange(pars);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
}
/// <summary>
/// 做数据绑定显示作用，一般绑定的是数据查看控件
/// </summary>
/// <param name="cmdTxt">sql语句</param>
/// <param name="tableName">要绑定显示的具体表名</param>
/// <returns>返回一个数据表</returns>
public static DataTable GetFillData(string cmdTxt)
{
return GetFillData(cmdTxt, null);
}
/// <summary>
/// 做数据绑定显示作用，一般绑定的是数据查看控件
/// </summary>
/// <param name="cmdTxt">带参数的sql语句</param>
/// <param name="pars">参数列表</param>
/// <returns>返回是一个数据表</returns>
public static DataTable GetFillData(string cmdTxt, params SqlParameter[] pars)
{
DataSet ds = new DataSet();
using (cmd = new SqlCommand(cmdTxt, Connectionstrings))
{
if (pars != null)
cmd.Parameters.AddRange(pars);
using (adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
/// <summary>
/// 做数据绑定显示作用，一般绑定的是数据查看控件
/// </summary>
/// <param name="cmdTxt">带参数的sql语句</param>
/// <param name="cmdType">枚举存储过程或者sql查询文本</param>
/// <param name="pars">参数列表</param>
/// <returns>返回是一个数据表</returns>
public static DataTable GetFillData(string cmdTxt, CommandType type, params SqlParameter[] pars)
{
DataSet ds = new DataSet();
using (cmd = new SqlCommand(cmdTxt, Connectionstrings))
{
cmd.CommandType = type;
cmd.Parameters.AddRange(pars);
using (adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(ds);
return ds.Tables[0];
}
}
}
/// <summary>
/// 分页数据绑定显示
/// </summary>
/// <param name="cmdTxt">string.format格式化sql语句,格式如:"select top {0} * from books where typeid not in (select top {1} id from books order by typeid) order by typeid"总记录数 TotalRecordCount总记录数通过executescalar获取</param>
/// <param name="pageSize">设置的分页数大小,默认为10</param>
/// <param name="currentIndex">当前页的索引,通常是通过querystring获取.如:string currentIndex = Request.QueryString["id"] ?? "1"</param>
/// <returns>返回当前页的数据显示</returns>
public static DataTable GetFillData(string cmdTxt, int pageSize, int currentIndex)
{
DataTable dt = new DataTable();
using (adapter = new SqlDataAdapter(string.Format(cmdTxt, pageSize, pageSize * (currentIndex - 1)), Connectionstrings))
{
adapter.Fill(dt);
}
return dt;
}
/// <summary>
/// sql带返回参数的存储过程(分页)
/// </summary>
/// <param name="procName">存储过程名字</param>
/// <param name="outParameterName">输出参数的名字</param>
/// <param name="dic">输出参数得到的值</param>
/// <param name="pars">参数列表</param>
/// <returns>DataTable</returns>
public static DataTable GetFillData(string procName,string [] outParameterName, out Dictionary<string,object> dic,params SqlParameter[] pars)
{
Dictionary<string, object> dictionary = new Dictionary<string, object>();
DataSet ds = new DataSet();
using (cmd = new SqlCommand(procName, Connectionstrings))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(pars);
foreach (var item in outParameterName)
{
cmd.Parameters[item].Direction = ParameterDirection.Output;
}
using (adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(ds);
dic = dictionary;
foreach (var item in outParameterName)
{
dictionary.Add(item, cmd.Parameters[item].Value.ToString());
}
return ds.Tables[0];
}
}
}
}
}
现在我们看看表示层的调用：
//数据绑定
Dictionary<string, object> dictionary = new Dictionary<string, object>();
DataTable dt = new DataTable();
string[] outParameterName = { "@RecordCount", "@PageCount" };//要输出的参数放入数组中
dt = DBHelper.GetFillData("Test", outParameterName, out dictionary,
DBHelper.CreateParameter("@PageIndex", SqlDbType.Int, PageIndex),
DBHelper.CreateParameter("@PageSize", SqlDbType.Int, 10),
DBHelper.CreateParameter("@RecordCount", SqlDbType.Int, 100),
DBHelper.CreateParameter("@PageCount", SqlDbType.Int, 100));
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
//绑定当前页、总页数、总条数
this.lb_RecordCount.Text = dictionary["@RecordCount"].ToString();//从dictionary取到输出参数的值
this.lb_PageCount.Text = dictionary["@PageCount"].ToString();
this.lb_Page.Text = (PageIndex +1).ToString();
