目前用到的两个分页存储过程代码

第一个,取得数据总行数 复制代码 代码如下: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [sq8reyoung].[fenye_num] ( @TableNames NVARCHAR(200), @Filter nvarchar(200)) AS IF @Filter = '' SET @Filter = ' WHERE 1

第一个,取得数据总行数 复制代码 代码如下: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [sq8reyoung].[fenye_num] ( @TableNames NVARCHAR(200), @Filter nvarchar(200)) AS IF @Filter = '' SET @Filter = ' WHERE 1=1' ELSE SET @Filter = ' WHERE ' + @Filter EXECUTE('select count(*) from '+@TableNames+' '+@Filter) 第二个取得分页数据 复制代码 代码如下: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [sq8reyoung].[fenye] @TableNames VARCHAR(200), --表名，可以是多个表，但不能用别名 @PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空 @Fields VARCHAR(200), --要取出的字段，可以是多个表的字段，可以为空，为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页，0表示第1页 @Filter VARCHAR(200) = '', --条件，可以为空，不用填 where @Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by @Order VARCHAR(200) = '' --排序，可以为空，为空默认按主键升序排列，不用填 order by AS BEGIN DECLARE @SortColumn VARCHAR(200) DECLARE @Operator CHAR(2) DECLARE @SortTable VARCHAR(200) DECLARE @SortName VARCHAR(200) IF @Fields = '' SET @Fields = '*' IF @Filter = '' SET @Filter = 'WHERE 1=1' ELSE SET @Filter = 'WHERE ' + @Filter IF @Group <>'' SET @Group = 'GROUP BY ' + @Group IF @Order <> '' BEGIN DECLARE @pos1 INT, @pos2 INT SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC') IF CHARINDEX(' DESC', @Order) > 0 IF CHARINDEX(' ASC', @Order) > 0 BEGIN IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) SET @Operator = '<=' ELSE SET @Operator = '>=' END ELSE SET @Operator = '<=' ELSE SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') SET @pos1 = CHARINDEX(',', @SortColumn) IF @pos1 > 0 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) SET @pos2 = CHARINDEX('.', @SortColumn) IF @pos2 > 0 BEGIN SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) ELSE SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) END ELSE BEGIN SET @SortTable = @TableNames SET @SortName = @SortColumn END END ELSE BEGIN SET @SortColumn = @PrimaryKey SET @SortTable = @TableNames SET @SortName = @SortColumn SET @Order = @SortColumn SET @Operator = '>=' END DECLARE @type varchar(50) DECLARE @prec int SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 PRINT @type DECLARE @sql NVARCHAR(4000) SET @Sql = 'DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + Cast(@TopRows as VARCHAR(10))+ ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + CAST(@PageSize AS VARCHAR(10)) + ' SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ISNULL(@Group,'') + ' ORDER BY ' + @Order + '' -- Print(@sql) Exec(@sql) END 以及实现此方法的数据操作类 复制代码 代码如下: using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient ; using System.Data; using System.Configuration; using Wuqi.Webdiyer; using Models; namespace DAL { public class DBHelper { public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["RyMedicalConnectionString"].ConnectionString; public static SqlDataReader GetReader(string safeSql) { SqlConnection conn = new SqlConnection(CONN_STRING); SqlCommand cmd = new SqlCommand(safeSql, conn); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); reader.Close(); return reader; } public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlConnection conn = new SqlConnection(CONN_STRING); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); reader.Close(); conn.Close(); return reader; } public static DataTable GetDataSet(string safeSql) { SqlConnection conn = new SqlConnection(CONN_STRING); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, conn); conn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); conn.Close(); return ds.Tables[0]; } public static DataTable GetDataSet(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) { SqlConnection conn = new SqlConnection(CONN_STRING); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(cmdText, conn); conn.Open(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); conn.Close(); return ds.Tables[0]; } public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(CONN_STRING); conn.Open(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); rdr.Close(); conn.Close(); return rdr; } public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(CONN_STRING)) { conn.Open(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); conn.Close(); return val; } } public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } public static void ExecuteNonQuery(string sql) { SqlConnection conn = new SqlConnection(CONN_STRING); SqlCommand cmd = new SqlCommand(sql,conn); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } /// <summary> /// 传入SQL语句,返回int /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExcuteCommand(string sql) { SqlConnection conn = new SqlConnection(CONN_STRING); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); int result = cmd.ExecuteNonQuery(); conn.Close(); return result; } /// <summary> /// /// </summary> /// <param name="Name">需要分页的表明</param> /// <param name="pk">主键名</param> /// <param name="fields">需要取出的字段,留空则为*</param> /// <param name="pagesize">每页的记录数</param> /// <param name="CurrentPage">当前页</param> /// <param name="Filter">条件，可以为空，不用填 where</param> /// <param name="Group">分组依据，可以为空，不用填 group by</param> /// <param name="order">排序，可以为空，为空默认按主键升序排列，不用填 order by</param> /// <returns></returns> public static DataTable Pagedlist(string Name, string pk, string fields, int pagesize, int CurrentPage, string Filter, string Group, string order) { CurrentPage = CurrentPage - 1; DataTable dt = GetDataSet(CommandType.StoredProcedure, "fenye", new SqlParameter("@TableNames", Name), new SqlParameter("@PrimaryKey", pk), new SqlParameter("@Fields", fields), new SqlParameter("@PageSize", pagesize), new SqlParameter("@CurrentPage", CurrentPage), new SqlParameter("@Filter", Filter), new SqlParameter("@Group", Group), new SqlParameter("@Order", order) ); return dt; } public static int fenye_num(string Name, string Filter) { return (int)ExecuteScalar(CommandType.StoredProcedure, "fenye_num", new SqlParameter("@TableNames", Name), new SqlParameter("@Filter", Filter)); } /// <summary> /// /// </summary> /// <param name="Name">需要分页的表明</param> /// <param name="pk">主键名</param> /// <param name="fields">需要取出的字段,留空则为*</param> /// <param name="pagesize">每页的记录数</param> /// <param name="CurrentPage">当前页</param> /// <param name="Filter">条件，可以为空，不用填 where</param> /// <param name="Group">分组依据，可以为空，不用填 group by</param> /// <param name="order">排序，可以为空，为空默认按主键升序排列，不用填 order by</param> /// <param name="objanp">传递aspnetpager控件</param> /// <returns></returns> public static DataTable Paged(string Name, string pk, string fields, int pagesize, int CurrentPage, string Filter, string Group, string order, AspNetPager objanp) { CurrentPage = CurrentPage - 1; DataTable dt = GetDataSet(CommandType.StoredProcedure, "fenye", new SqlParameter("@TableNames", Name), new SqlParameter("@PrimaryKey", pk), new SqlParameter("@Fields", fields), new SqlParameter("@PageSize", pagesize), new SqlParameter("@CurrentPage", CurrentPage), new SqlParameter("@Filter", Filter), new SqlParameter("@Group", Group), new SqlParameter("@Order", order) ); objanp.RecordCount = fenye_num(Name, Filter); return dt; } } } 以及页面调用方式 复制代码 代码如下: using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using Insus.NET; using DAL; using System.Data.SqlClient; public partial class news_newlist : System.Web.UI.Page { int nid; int totalOrders; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Request.QueryString["typeid"] != null) { nid = Convert.ToInt16(Request.QueryString["typeid"].ToString()); binddata(1); } else { Response.Redirect("~/default.aspx"); } } } private void binddata(int page) { DataTable dt = DBHelper.Paged("M_NewInfoAll", "New_Id", "", AspNetPager1.PageSize, page, "New_TypeId=" + nid.ToString() + "", "", "New_PubDate desc", AspNetPager1); this.Repeater1.DataSource = dt; this.Repeater1.DataBind(); DataRow dr = dt.Rows[0]; this.Label1.Text = dr["New_TypeName"].ToString(); //this.Literal1.Text = dr["new_typeName"].ToString(); Page.Title = Label1.Text.Trim() + " - 新农合医药网"; } protected void AspNetPager1_PageChanged(object sender, EventArgs e) { if (Request.QueryString["page"] != null) { binddata(Convert.ToInt32(Request.QueryString["page"].ToString())); } } } 如此分页即可实现(下图),在任何项目中只需要COPY2个存储过程一个数据操作类,或者喜欢将数据类做成DLL也可以,在页面调用时传入参数只需一行代码即可.