ASP.NET 分頁預存程序 及 調用，asp.net預存程序

ASP.NET 分頁預存程序 及 調用，asp.net預存程序

廢話不多說，只說代碼，及過程

1.建立預存程序

<span style="font-size:18px;">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 ' + @where         exec(@sql)end</span>

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);        }    }}

<strong><span style="font-size:24px;">5.</span><span style="font-size:18px; font-family: Arial, Helvetica, sans-serif;">GetDivPager拼接字串</span></strong>

<span style="font-family: Arial, Helvetica, sans-serif;"></span>

<span style="font-family: Arial, Helvetica, sans-serif;">queryString       如果需要在URL加參數，比如：&charset=utf-8</span>

<span style="font-family: Arial, Helvetica, sans-serif;">ds                      從預存程序的取得資料集</span>

<span style="font-family: Arial, Helvetica, sans-serif;">dt                       將列表資料返回</span>

<span style="font-family: Arial, Helvetica, sans-serif;">傳回值               HTML標籤   </span>

<span style="font-family:Arial, Helvetica, sans-serif;"></span><span style="font-family: Arial, Helvetica, sans-serif;"></span><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("  <p class=\"fl\">總記錄：<span id=\"sum\">{0}</span>", TotalCount);                    sp.AppendFormat("  頁碼：<em><b id=\"current\">{0}</b>/<span id=\"count\">{1}</span></em> ", PageIndex, rowCount);                    sp.AppendFormat("  每頁：<span id=\"eachPage\">{0}</span></p> ", 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.頁面顯示

   

 <span style="white-space:pre"></span><table width="100%">                <tr>                    <th>                        序號                    </th>                    <th>                        使用者名稱                    </th>                    <th>                        密碼                    </th>                      <th>                        建立時間                    </th>                 </tr>                <%if (list != null && list.Rows.Count > 0)                  {                      int abc = 1;                      foreach (System.Data.DataRow item in list.Rows)                      {%>                <tr>                    <td><input type="checkbox"  name="checkbox2" value="<%=item["ID"] %>" /></td>                    <td><%=abc++ %></td>                    <td><%=item["UserName"]%></td>                    <td><%=item["PASSWORD"]%></td>                    <td><%=Convert.ToDateTime(item["CreateTime"]).ToString("yyyy-MM-dd hh:mm:ss") %></td>                </tr>                <%}        }%>            </table>             <!--分頁 -->            <div runat="server" id="runPageDiv" class="page"></div>

OK！在此大功告成！去瀏覽你的頁面吧。。。