ASP.NET 分页存储过程及调用

废话不多说，只说代码，，及过程

1.创建存储过程

create procedure [dbo].[sp_PagingTabs]@TableName nvarchar(200),/* 表名 */@FieldName nvarchar(500),/* 要查询的字段 */@where nvarchar(500),/* 查询的条件 */@OrderField nvarchar(500),/* 排序指定的字段 */@Order nvarchar(50),/* 排序只能是 asc desc */@PageIdORField nvarchar(50),/* 指定字段来分页 */@PageSize int ,/* 每页个数 */@PageIndex int/* 当前页码 */ asbegin/* 先清除字符串左右的空格 */set @TableName = LTRIM(rtrim(@TableName));set @FieldName = LTRIM(RTRIM(@FieldName));set @where = LTRIM(RTRIM(@where));set @Order = LTRIM(RTRIM(@Order));set @OrderField=LTRIM(RTRIM(@OrderField));set @PageIdORField =LTRIM(rtrim(@PageIdORField));/* 然后对非空传值进行判断 */if ISNULL(@TableName,'')='' return ;if ISNULL(@FieldName,'')='' set @FieldName='*'; if ISNULL(@Order,'')='' set @Order = 'desc';/* 查询分页的数据 */declare @sql nvarchar(1000) set @sql=' select top ' + cast(@PageSize as nvarchar(50)) + ' ' + @FieldName + ' from ' + @TableName + ' where 1=1' + @where + ' and ' + @PageIdORField + ' not in(select top ' + cast((@PageSize * (@PageIndex-1)) as nvarchar(50)) + @PageIdORField + ' from ' + @TableName + ' where 1=1 '+@where + ' order by ' + @OrderField + ' ' + @Order+') order by ' + @OrderField + ' ' + @Order;/* 总页数 */set @sql+=' select count(*) from ' + @TableName + ' where 1=1 ' + @whereexec(@sql)end

2.创建方法类

/// <summary>/// 分页存储过程/// </summary>/// <param name="TableName">表名</param>/// <param name="FieldName">字段名</param>/// <param name="wheres">where条件</param>/// <param name="order">只能是desc or asc</param>/// <param name="PageSize">每页条数</param>/// <param name="PageIndex">当前页码</param>/// <param name="TotalCount">总页码</param>/// <param name="PageIdORField">指定字段来分页</param>/// <param name="OrderField">排序指定的字段</param>public static DataSet LinkProce(string TableName, string FieldName, string wheres, string order,string PageIdORField,string OrderField, ref int PageSize,ref int PageIndex){using (SqlConnection conn = new SqlConnection(Conn.ConnString)){conn.Open();using (SqlCommand cmd = conn.CreateCommand()){cmd.CommandText = "sp_PagingTabs";cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@TableName", (TableName.Trim() == "User" ? "[User]" : TableName));cmd.Parameters.AddWithValue("@FieldName", FieldName);cmd.Parameters.AddWithValue("@where", wheres);cmd.Parameters.AddWithValue("@Order", order);cmd.Parameters.AddWithValue("@OrderField", OrderField);cmd.Parameters.AddWithValue("@PageIdORField", PageIdORField);cmd.Parameters.AddWithValue("@PageSize", PageSize);cmd.Parameters.AddWithValue("@PageIndex", PageIndex);SqlDataAdapter da = new SqlDataAdapter();da.SelectCommand = cmd;DataSet ds = new DataSet();try{da.Fill(ds);return ds;}catch{return null;}finally{ds.Dispose();conn.Close();}}}}

3.调用方法

public DataSet LinkServer(string TableName, string FieldName, string wheres, string order,string PageIdORField,string OrderField, ref int PageSize, ref int PageIndex){return SQLHelperMe.LinkProce(TableName, FieldName, wheres, order, PageIdORField, OrderField,ref PageSize,ref PageIndex);}

4.正式使用

namespace Web.WebForm1{public partial class WebForm1 : System.Web.UI.Page{private int PageIndex = 0;public DataTable list;public string DivPager = "";protected void Page_Load(object sender, EventArgs e){GetParams();showData();}private void GetParams(){if (!String.IsNullOrEmpty(Request["page"])){PageIndex = Convert.ToInt32(Request["Page"]);}else{ PageIndex = 1;}}private void showData(){list = new DataTable();int PageSize = 10;Pager pager = new Pager(PageIndex);DivPager = pager.GetDivPager("", pager.LinkServer(" dbo.ze_user ", "*",""," desc "," Id "," Id ",ref PageSize,ref PageIndex), out list);}}}5.GetDivPager拼接字符串queryString如果需要在URL加参数，比如：&charset=utf-8ds从存储过程的取得数据集dt将列表数据返回返回值HTML标签 <pre name="code" class="csharp">public string GetDivPager(string queryString, DataSet ds, out DataTable dt){StringBuilder sp = new StringBuilder();int PageSize = 10;if (ds != null && ds.Tables.Count > 0){dt = ds.Tables[0];int TotalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());int rowCount = (TotalCount % PageSize != 0) ? TotalCount / PageSize + 1 : TotalCount / PageSize;if (dt != null && dt.Rows.Count > 0){sp.AppendFormat(" 总记录：{0}", TotalCount);sp.AppendFormat(" 页码：{0}/{1} ", PageIndex, rowCount);sp.AppendFormat(" 每页：{0} ", PageSize);sp.AppendFormat(" <div class=\&;pagination fr\&;> ");sp.AppendFormat(" <a class=\&;disabled\&; href='{0}'>首页</a> ", "?page=1" + queryString);if (PageIndex > 1){sp.AppendFormat(" <a href='{0}'>< 上一页 </a>", "?page=" + (PageIndex – 1) + queryString);}int temp = 0;int loopc = rowCount > 10 ? 10 : rowCount;for (int i = 0; i < loopc; i++){temp = i + 1;if (PageIndex > 10) { temp = (PageIndex – 10) + i + 1; }sp.AppendFormat(" <a class=\&;{0}\&; href='{1}'>{2}</a>", PageIndex == temp ? "active" : "", "?page=" + temp + queryString, temp);}if (PageIndex != rowCount){sp.AppendFormat(" <a href='{0}'>下一页 ></a>", "?page=" + (PageIndex + 1) + queryString);}sp.AppendFormat(" <a href='{0}'>尾页</a>", "?page=" + rowCount + queryString);sp.AppendFormat(" </div>");}}else{dt = null;}return sp.ToString();}

6.页面显示

人生没有彩排，每天都是现场直播。

相关文章：

你感兴趣的文章：

标签云：