ASP.NET SQL SERVER 三层架构 Ajax 分页

ASP.NET+三层架构+SqlServer Ajax无刷新分页

1.首先数据表如下（IT_ExamPaper）;

2.确定查询内容（比如查询：ExamName,AddTime,ExamTimelimit），那么这里本人通过存储过程来查。
存储如下：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[ExamPaper_GetPages]
 @pageIndex int,--下标
 @pageSize int,--大小
 @count int output--总数量
as
 declare @n int
 select  @count=COUNT(*) from IT_Exampaper
 select * from
 (select *,ROW_NUMBER() over(order by ExamId asc) as num from IT_Exampaper) as t
 where num between (@pageIndex-1)*@pageSize + 1 and @pageIndex*@pageSize
 order by ExamId asc

2.1然后为了测试存储是否正确可以调用执行一下
因为这里带了输出参数：@Count（输出总数量的！） 其中ROW_NUMBER()是一个sql函数用于排序生成流水号的，针对于ID加密什么的可以用，当然这里看个人喜好了！
declare @qq int 
exec [dbo].[ExamPaper_GetPages] 1, 10, @qq  out

3.那么就是调用存储过程了！
IExam(接口层):
代码： DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count);

Dal（数据访问层）：
/// <summary>
        /// 获得所有试卷再分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count)
        {
            SqlParameter[] val = {
                                  new SqlParameter("@pageIndex",SqlDbType.Int),
                                  new SqlParameter("@pageSize",SqlDbType.Int),
                                  new SqlParameter("@count",SqlDbType.Int)
                                   };
            //设置输入参数
            val[0].Value = pageIndex;
            val[1].Value = pageSize;
            val[2].Direction = ParameterDirection.Output;
            DataTable dt = DBResHelper.ExexProcQuery("ExamPaper_GetPages", val);
            int.TryParse(val[2].Value.ToString(), out count);
            return dt;
        }
ADO.NET:
这里有一个ADO.NET辅助方法ExexProcQuery（“存储过程名”，参数列表）
读取存储过程方法代码如下：
 /// <summary>
        /// 读取存贮过程
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="Pars"></param>
        /// <returns></returns>
        public static DataTable ExexProcQuery(string ProcName, SqlParameter[] Pars)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlDataAdapter sda = new SqlDataAdapter(ProcName, con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < Pars.Length; i++)
            {
                sda.SelectCommand.Parameters.Add(Pars[i]);
            }
            //这里写成
            DataTable dt = new DataTable();
            try
            {
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                //LogTool.WriteLog(ex.Message.ToString());
                return dt;
            }
            finally
            {
                con.Close();
            }
        }

BLL:业务逻辑层

        public DataTable GetaAllExamPaper(int pageIndex, int pageSize, out int count)
        {
            return ie.GetaAllExamPaper(pageIndex, pageSize, out count);
        }
       这里ie来自简单工厂模式中的公共副类对象


好了到了这一步如果没有其他问题，数据是取出来了，那么接下路就是在UI层通过Aajx进行处理了!
UI（Exampaper.Aspx）：

<div class="pracl_dalist">
        <div class="paper_tit">
            <div class="pt_left">  <a href="#">首页</a><em>&gt;</em><a href="#">课程列表</a></div>
             <div class="labnum">  一共：<asp:Label CssClass="lab" ID="labNum" runat="server" Text=""></asp:Label>条记录。</div>
        </div>
        <div class="examList_data clear" id="divtable">
              
        </div>
    </div>
    <div class="page_nav">
        <ul class="pages">
            <li id="page-first" class="pgNext"style=" cursor:pointer;">首页</li>
            <li id="page-pre" class="pgNext" style=" cursor:pointer;">上一页</li>
            <li id="page-next" class="pgNext" style=" cursor:pointer;">下一页</li>
            <li id="page-last" class="pgNext" style=" cursor:pointer;">末页</li>
            <li  class="pgNext" style=" width:150px;">转到第<input id="txtIndex" type="text" />页</li>
            <li class="pgNext go"  id="pageTo">确定</li>
        </ul>
    </div>

   然后再写Ajax：在此之前要求先把JQUERY包引入进来，网上有的下载！
   在写Ajax之前得先建一个一般处理程序（ExamPageCut.ashx）,建好之后那么就可以开始写Ajax了
    页面上代码如下：
 <script type="text/javascript" language="javascript">
       var pagesize = 10;
       var pageindex;
       var lastindex;
       $(function () {
           loaddata();
           aaa();
           $("#page-first").click(function () {
               if (pageindex == 1) {
                   alert('已经是第一页了!');
               }
               else {
                   pageindex = 1;
                   loaddata();
               }
           });
           $("#page-pre").click(function () {
               if (pageindex == 1) {
                   alert('这是第一页!');
               }
               else {
                   if (pageindex > 1) {
                       pageindex--;
                       loaddata();
                   }
               }
           });
           $("#page-next").click(function () {
               if (pageindex == lastindex) {
                   alert('已经是最后一页了!');
               }
               else {
                   if (pageindex < lastindex) {
                       pageindex++;
                       loaddata();
                   }
               }
           });

           $("#page-last").click(function () {
               if (pageindex == lastindex) {
                   alert('已经是最后一页了!');
               }
               else {
                   pageindex = lastindex;
                   loaddata();
               }
           })
           $("#pageTo").click(function (event) {
               var txtval = $("#txtIndex").val();
               pageindex = txtval;
               loaddata();
           });
       });

       //..
       function loaddata() {
           pageindex = !pageindex ? 1 : pageindex;

           $.ajax({
               url: "WebServeice/PageForExamPaper.ashx?action=GetExamForPage", //调用后台方法发送请求
               type: "get",
               contentType: "application/json",
               dataType: "json",
               data: { pageindex: pageindex, pagesize: pagesize },
               success: function (result) {
                   if (result) {
                       var count = result.PageCount;
                       if (lastindex) {
                           lastindex = Math.floor(count / pagesize);
                           if (count % pagesize > 0) {
                               lastindex += 1;
                           }
                       }
                       var data = result.data;
                       var biaoqian = "<table style='width:100%;'>";//动态添加数据，
                       biaoqian += "<tr style='text-align:center;'><td>课程名称</td><td>所属级别</td><td>添加时间</td></tr>";
                       $.each(data, function (name, obj) {

                           biaoqian += "<tr style='text-align:left;'>";
                           biaoqian += "<td>" + obj["Id"] + "</td>";//rowNumber中的Id，就是存储过程中的流水号！
                           biaoqian += "<td>" + obj["ExamName"] + "</td>";
                           biaoqian += "<td>" + obj["ExamTimeLimit"] + "</td>";
                           biaoqian += "<td>" + formatDateTime(obj["AddTime"]) + "</td>";
                           biaoqian += "</tr>";

                       });

                       biaoqian += "</table>";
                       $('#divtable').html(biaoqian);
                   }
               },
               error: function (err) {

                   //alert(err.status);
               }
           });
       }

       function aaa() {
           $.ajax({
               type: "get",
               contentType: "application/json",
               url: "./WebServeice/PageForExamPaper.ashx",
               //data: "{pagesize:" + pagesize + "}",
               data: {
                   action: "GetExamPageForClassPageindex",
                   pagesize: pagesize,
                   ts: new Date().getTime()
               },
               success: function (result) {
                   //alert(result);
                   lastindex = result;
               },
               error: function (err) {
                   alert(err.status);
               }
           });
       }
       function go(ipage) {
           loaddata();
       }
       //这个方法是转换时间格式，因为JSON取到的是时间戳，那么要转换为时间格式。
       function formatDateTime(dateTime, format) {
           var dtime = new Date(dateTime);
           var str = dtime.getFullYear().toString() + "-"
                          + (dtime.getMonth() + 1).toString() + "-"
                          + dtime.getDate().toString();
           switch (format) {
               case "yyyy-MM-dd HH:mm:ss":
                   str += " " + dtime.getHours().toString() + ":"
                                  + dtime.getMinutes().toString() + ":"
                                  + dtime.getSeconds().toString();
                   break;
           }
           return str;
       }
    </script> 
   这一块就是AJAX了
  

   处理程序中代码：
    public class PageForExamPaper : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string action = context.Request["action"];
            string result = string.Empty;
            if (!string.IsNullOrEmpty(action))
            {
                switch (action)
                {
                    case "GetExamPageForClassPageindex":
                        result = GetExamPageForClassPageindex(int.Parse(context.Request["pagesize"])).ToString();
                        break;
                    case "GetExamForPage":
                        result = GetExamForPage(int.Parse(context.Request["pageindex"]), int.Parse(context.Request["pagesize"]));
                        break;
                }

            }
            context.Response.Write(result);
            context.Response.End();
        }


        public string GetExamForPage(int pageindex, int pagesize)
        {
            int pageCount = 0;
            Content.CExam cce = new Content.CExam();
            DataTable dt = cce.GetaAllExamPaper(pageindex, pagesize, out pageCount);
            List<Modle.exampaperModle> list = new List<Modle.exampaperModle>();
            int id = 0;//获取存储过程中NUM
            string examname = "";
            string timelimit = "";
            DateTime Addtime;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                id = int.Parse(dt.Rows[i]["num"].ToString());//添加流水号从1开始递增
                examname = dt.Rows[i]["ExamName"].ToString();
                timelimit = dt.Rows[i]["ExamTimeLimit"].ToString();
                Addtime = Convert.ToDateTime(dt.Rows[i]["AddTime"]);
                Modle.exampaperModle ctl = new Modle.exampaperModle();
                {
                    ctl.Id = id;
                    ctl.ExamName = examname;
                    ctl.ExamTimeLimit = int.Parse(timelimit.ToString());
                    ctl.AddTime = Addtime;
                };
                list.Add(ctl);
            }
            var obj = new
            {
                PageCount = pageCount,
                data = list
            };
            Newtonsoft.Json.Converters.IsoDateTimeConverter timeFormat = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
            timeFormat.DateTimeFormat = "yyyy-MM-dd";
            string result = Newtonsoft.Json.JsonConvert.SerializeObject(obj);
            return result;
        }



        [WebMethod]
        public int GetExamPageForClassPageindex(int pagesize)
        {
            Content.CExam cce = new Content.CExam();
            int totalcount = cce.GetExamCount();//这个方法是获取一个总数量的方法，用来查看共有多少页，一共有多少条数据！
            if (totalcount % pagesize == 0)
            {
                return totalcount / pagesize;
            }
            else
            {
                return totalcount / pagesize + 1;
            }
        }


        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

   好了，这一个模块的功能就全部完成了，希望对大家有帮助，有什么好的指点或者意见，留言一起讨论吧，谢谢！