存储过程：
至于页面的下一页、上一页跳到哪一页、这里就不多说了，只是加一减一罢了。有什么更好的建议，请联系本人。大家共同进步。 create proc Test
@PageIndex INT,--@PageIndex从计数,0为第一页 @PageSize INT, --页面大小 @RecordCount INT OUT, --总记录数 @PageCount INT OUT--页数asSELECT @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 TWHERE 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,@PageCountgo存储过程的调用：-----已更新---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();