asp.net+jQuery(jPaginate插件)+AJAX 分页

最新推荐文章于 2021-08-06 03:53:40 发布

great-sage

最新推荐文章于 2021-08-06 03:53:40 发布

阅读量6.2k

点赞数

分类专栏： C#/.net js/jQuery/html/css 文章标签： ajax jquery asp.net dataset class string

本文链接：https://blog.csdn.net/thinkscape/article/details/7547507

版权

C#/.net 同时被 2 个专栏收录

48 篇文章 0 订阅

订阅专栏

js/jQuery/html/css

41 篇文章 0 订阅

订阅专栏

最近在作一个证券模拟交易项目,数据显示采用了jQuery的ajax和jPaginate分页,其中还涉及到DataSet数据格式转换为Json格式.

涉及知识:

前端:
    Json:
        Newtonsoft.Json:DateSet与Json的数据转换
    jQuery:
        ajax异步请求,
        jPaginate分页插件,

数据库:
    oracle:分页sql语句写法

需要下载 
    Newtonsoft.Json组件
    jQuery
    jPaginate分页插件

注意:特别要注意ajax请求返回的Json的数据格式,如jquery和jEasyUI二者ajax请求返回的Json数据格式就不一样!!

时序:
aspx+jQuery(ajax,jPaginate分页)------异步请求----->>asmx(WebService[WebMother])------数据请求------->>数据请求Service---------->>数据层ADO(ADO.NET API封装类)------------->>DB
aspx页面显示<<------jQuery解析Json数据------asmx(WebService[WebMother])<<------DataSet转换Json(Newtonsoft.Json)(少量数据,可手动写Json)-----Service<<------返回DataSet-------DB

代码:

index.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="com.jjtg.web.Test.jPaginate.index" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>无标题页</title>
        <script src="jquery-1.7.1.min.js" type="text/javascript"> </script>
        <script src="jquery.paginate.js" type="text/javascript"> </script>
        <script src="jPaginate.js" type="text/javascript"> </script>
        <link href="css/style.css" rel="stylesheet" type="text/css" />
    </head>
    <body>
        <form id="form1" runat="server">
            
            <div class="demo"> 
                <h4>Demo ： asp.net+jQuery(jPaginate插件)+AJAX 分页</h4> 
                <div id="pagetxt" style="width: 98%;"> 
                    <img id="load_gif" src="loading_invs.gif" style="display: none;"/>
                </div> 
                <div id="demo" style="margin: 10px 0px 0px 0px; vertical-align: middle; width: 92%; height:30px;"></div> 
            </div> 
            <input id="Hidden1" type="hidden" />
        </form>
    </body>
</html>

jPaginate.asmx

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;

namespace com.jjtg.web.Test.jPaginate
{
    /// <summary>
    /// jPaginate 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
    [System.Web.Script.Services.ScriptService]
    public class jPaginate : System.Web.Services.WebService
    {

        /// <summary>
        /// 取出总页数
        /// </summary>
        /// <param name="pageSize">每页记录条数</param>
        /// <returns></returns>
        [WebMethod]
        public string getTotalPage(int pageSize)
        {
            jPaginateService mts = new jPaginateService();
            string sql = "select count(*) from invs_imt_trade";
            int pages = mts.getTotalPages(sql, pageSize);
            string str = "{\"pages\":" + pages + "}";
            return str;
        }

        /// <summary>
        /// 取出分页数据
        /// </summary>
        /// <param name="pageSize">每页记录条数</param>
        /// <param name="currentPage">当前是第几页</param>
        /// <returns></returns>
        [WebMethod]
        public string getPaginateData(int pageSize, int currentPage)
        {
            jPaginateService mits = new jPaginateService();

            string sql = "select uui.name invs_name, t.invs_id, t.id, t.stk_unicode, t.stk_code, " +
                         "psc.sec_name stk_name, decode(t.bs_mark, 1,'买',0,'卖') bs_mark,  " +
                         "t.bs_time, t.bs_price, t.bs_count, t.occur_amount,  " +
                         "decode(t.is_finished, 1,'已清仓',0,'持有') is_finished  " +
                         "from INVS_IMT_TRADE t " +
                         "left outer join user_uni_info uui on t.invs_id=uui.user_uniid left outer join jjdb.pub_sec_code@jjdb psc on t.stk_unicode=psc.sec_unicode  " +
                         "where 1=1 order by t.id desc ";
            string str_id = "id desc";
            DataSet ds = jPaginateService.getPaginateRows(sql, str_id, pageSize, currentPage);
            string imtPostStr = mits.imtTradeRecodeDataSet2Json(ds);

            return imtPostStr;
        }
    }
}

jPaginateService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
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 Newtonsoft.Json;
using Newtonsoft.Json.Converters;

namespace com.jjtg.web.Test.jPaginate
{
    public class jPaginateService
    {

        /// <summary>
        /// 取得总记录数
        /// </summary>
        /// <param name="sql">执行sql</param>
        /// <param name="pageSize">每页记录条数</param>
        /// <returns></returns>
        public int getTotalPages(string sql, int pageSize)
        {
            int pages = 0;

            int totalRec = 0;
            try
            {
                object obj = DbHelper.ExecuteScalar(DbHelper.ConnectionString, CommandType.Text, sql, null);
                if (obj != null)
                {
                    totalRec = Convert.ToInt32(obj);
                    pages = totalRec / pageSize;
                    if (totalRec % pageSize != 0)
                    {
                        pages += 1;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return pages;
        }


        /// <summary>
        /// 取得符合条件的数据
        /// 由于前端是ajax请求,所以未使用"页面模型"
        /// </summary>
        /// <param name="sql">获取数据的sql语句</param>
        /// <param name="str_id">用于OVER(ORDER BY " + str_id + ")的排序字段</param>
        /// <param name="pageSize">每页记录条数</param>
        /// <param name="currentPage">当前是第几页</param>
        /// <returns></returns>
        public static DataSet getPaginateRows(string sql, string str_id, int pageSize, int currentPage)
        {
            DataSet ds = null;
            if (string.IsNullOrEmpty(sql))
            {
                return ds;
            }

            StringBuilder sb_count = new StringBuilder();
            sb_count.Append("SELECT u.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + str_id + ") rn, rhf.* FROM (");
            sb_count.Append(sql);
            sb_count.Append(")");
            sb_count.Append(" rhf WHERE ROWNUM <= (:pCurrentPage * :pPageSize)) u WHERE rn >= ((:pCurrentPage-1) * :pPageSize)");

            OracleParameter[] paras = {
                new OracleParameter("pCurrentPage", currentPage),
                new OracleParameter("pPageSize", pageSize)          
            };

            try
            {
                string conn = DbHelper.ConnectionString;
                ds = DbHelper.ExecuteDataSet(conn, CommandType.Text, sb_count.ToString(), paras);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }


        /// <summary>
        /// 成交记录数据转换:DataSet转Json
        /// </summary>
        /// <param name="ds">数据源DataSet</param>
        /// <returns></returns>
        public string imtTradeRecodeDataSet2Json(DataSet ds)
        {
            string jsonStr = null;
            List<jPaginateModel> list = new List<jPaginateModel>();

            if (DbHelper.DataSetIsNull(ds))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    jPaginateModel iitMod = new jPaginateModel();
                    iitMod.Id = Convert.ToInt32(dr["id"].ToString());
                    iitMod.Stk_Unicode = Convert.ToInt32(dr["Stk_Unicode"].ToString());
                    iitMod.Stk_Code = dr["Stk_Code"].ToString();

                    iitMod.Stk_Name = dr["Stk_Name"].ToString();
                    iitMod.Bs_Time = Convert.ToDateTime(dr["Bs_Time"].ToString());
                    iitMod.Bs_Mark_Name = dr["Bs_Mark"].ToString();

                    iitMod.Invsadv_Id = Convert.ToInt32(dr["Invs_Id"].ToString());
                    iitMod.Invs_Name = dr["Invs_Name"].ToString();
                    iitMod.Bs_Price = Convert.ToDouble(dr["Bs_Price"].ToString());
                    iitMod.Bs_Count = Convert.ToInt32(dr["Bs_Count"].ToString());
                    iitMod.Occur_Amount = Convert.ToDouble(dr["Occur_Amount"].ToString());
                    iitMod.Is_Finished_Name = dr["Is_Finished"].ToString();

                    list.Add(iitMod);
                }

                //Newtonsoft.Json在转换日期的时候，会出现格式和时区(差8小时)差别
                //如果不作如下格式转换，会出现形如Date(1335247957000+0800)/的日期
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                //这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                jsonStr = JsonConvert.SerializeObject(list, Formatting.Indented, timeConverter);
                //jsonStr = "[{Stk_Code:'601398',Stk_name:'工商银行'},{Stk_Code:'500999',Stk_name:'农业银行'}]";
            }

            return jsonStr;
        }
    }
}

jPaginateModel.cs

using System;
using System.Data;
using System.Configuration;
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;

namespace com.jjtg.web.Test.jPaginate
{
    
    /// <summary>
    /// 投顾模拟交易
    /// csk
    /// </summary>
    [Serializable]
    public class jPaginateModel
    {
        /// <summary>
        /// ID
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// 股票统一编码
        /// </summary>
        public int Stk_Unicode { get; set; }

        /// <summary>
        /// 股票代码
        /// </summary>
        public string Stk_Code { get; set; }

        /// <summary>
        /// 股票名称
        /// </summary>
        public string Stk_Name { get; set; }

        /// <summary>
        /// 买卖标志(买1 | 卖 0)
        /// </summary>
        public int Bs_Mark { get; set; }

        /// <summary>
        /// 买卖标志名称
        /// </summary>
        public string Bs_Mark_Name { get; set; }

        /// <summary>
        /// 买卖时间
        /// </summary>
        public DateTime Bs_Time { get; set; }

        /// <summary>
        /// 买卖价格
        /// </summary>
        public Double Bs_Price { get; set; }

        /// <summary>
        /// 买卖数量
        /// </summary>
        public int Bs_Count { get; set; }

        /// <summary>
        /// 发生金额
        /// </summary>
        public Double Occur_Amount { get; set; }

        /// <summary>
        /// 手续费(%)
        /// </summary>
        public Double Cc { get; set; }

        /// <summary>
        /// 投顾ID
        /// </summary>
        public int Invsadv_Id { get; set; }

        /// <summary>
        /// 投顾名字
        /// </summary>
        public string Invs_Name { get; set; }

        /// <summary>
        /// 是否卖完(0否 | 1 是)
        /// </summary>
        public int Is_Finished { get; set; }

        /// <summary>
        /// 是否卖完名称
        /// </summary>
        public string Is_Finished_Name { get; set; }
    }

}
jPaginate.js/// <reference path="jquery-1.7.1.min.js" />
/// <reference path="jquery.paginate.js" />

$(document).ready(function() {

/*
公用方法
showPaginateData(data) 显示分页数据
getPaginateData(pSize, curPage) 取得分页数据
getTotalPage(pSize) 取得总页数
setPaginate() 分页页码显示
*/
	
	var begPage = 1; //开始页码
	var displayPage = 5; //显示页码数
	var pageSize = 5; //每面显示记录数

    //显示分页数据
	function showPaginateData(data) {

		$("#pagetxt").html("");
		var v_str = "<table id='stk_position_tab' class='pos_tab'>";
		v_str += "<thead><tr>";
		v_str += "<td class='pos_tab_td_header'>证券代码</td><td class='pos_tab_td_header'>证券名称</td>";
		v_str += "<td class='pos_tab_td_header'>买卖时间</td><td class='pos_tab_td_header'>业务类型</td>";
		v_str += "<td class='pos_tab_td_header'>投顾</td><td class='pos_tab_td_header'>成交价格</td>";
		v_str += "<td class='pos_tab_td_header'>成交数量</td><td class='pos_tab_td_header'>发生金额</td>";
		v_str += "<td class='pos_tab_td_header'>持仓</td><td class='pos_tab_td_header'>统一编码</td>";

		v_str += "</tr></thead><tbody>";

		try {
			$(data).each(function(i, item) {
				v_str += "<tr id='show_stk_tr_" + i + "'";

				//盈亏颜色变化，-为绿，+为红
				if (item.Bs_Mark_Name == "买") {
					v_str += " class='show_stk_tr_green'>";

				} else if (item.Bs_Mark_Name == "卖") {
					v_str += " class='show_stk_tr_red'>";
				}

				v_str += "<td id='s_code_td' >" + item.Stk_Code + "</td><td>" + item.Stk_Name + "</td>";
				v_str += "<td class='alignRight'>" + item.Bs_Time + "</td><td class='alignRight'>" + item.Bs_Mark_Name + "</td>";
				v_str += "<td class='alignRight'>" + item.Invs_Name + "</td><td class='alignRight'>" + item.Bs_Price + "</td>";
				v_str += "<td class='alignRight'>" + item.Bs_Count + "</td><td class='alignRight'>" + item.Occur_Amount + "</td>";
				v_str += "<td class='alignRight'>" + item.Is_Finished_Name + "</td><td class='alignRight'>" + item.Stk_Unicode + "</td>";
				v_str += "</tr>";

			});
			v_str += "</tbody></table>";
			$("#pagetxt").html(v_str);


		} catch(e) {
			alert(e);
			return;
		}
	}
    //showPaginateData(data):end

	//取得分页数据
	//pageSize:每页记录条数
	//currentPage:当前是第几页
	function getPaginateData(pSize, curPage) {
		var paras = "{pageSize:" + pSize + ",currentPage:" + curPage + "}";
		$.ajax({
			type: "POST",
			contentType: "application/json; charset=utf-8",
			url: "jPaginate.asmx/getPaginateData",
			data: paras,
			dataType: 'json',
			beforeSend: function() {
				$("#pagetxt").html("");
				$("#load_gif").attr('style', 'display:block');
			},

			success: function(json) {
				var data = eval('(' + json.d + ')');
				showPaginateData(data);
			},

			complete: function() {
				$("#load_gif").attr('style', 'display:none');
			},
			error: function(xhr) { //如果没有上面的捕获出错会执行这里的回调函数
				alert('页出错\n\r' + xhr.responseText);
			}
		});
	}
    //getPaginateData(pSize, curPage):end

	//取得总页数
	function getTotalPage(pSize) {
		var paras = "{pageSize:" + pSize + "}";
		$.ajax({
			type: "POST",
			contentType: "application/json; charset=utf-8",
			url: "jPaginate.asmx/getTotalPage",
			data: paras,
			dataType: 'json',
			beforeSend: function() {
				$("#load_gif").attr('style', 'display:block');
			},

			success: function(json) {
				var data = eval('(' + json.d + ')');
				$("#Hidden1").val(data.pages);

				
				//放置分页:
				//因为ajax的执行,是在所有js代码中非服务器请求执行完成后,才执行ajax代码,
				//所以为了取得总记录行数,必须要等到ajax执行完成.
				setPaginate();
				//

			},

			complete: function() {
				$("#load_gif").attr('style', 'display:none');
			},
			error: function(xhr) { //如果没有上面的捕获出错会执行这里的回调函数
				alert('页出错\n\r' + xhr.responseText);
			}
		});
	}
    //getTotalPage(pSize):end

	//分页页码显示 setPaginate()
	function setPaginate() {
		var pages = $("#Hidden1").val(); //总页面数

		$("#demo").paginate({
			count: pages,
			start: begPage,
			display: displayPage,
			border: true,
			border_color: '#BEF8B8',
			text_color: '#79B5E3',
			background_color: '#E3F2E1',
			border_hover_color: '#68BA64',
			text_hover_color: '#2573AF',
			background_hover_color: '#CAE6C6',
			images: false,
			mouse: 'press',
			onChange: function(currentPage) {
				//点击页码时,执行的函数(作ajax异步请求数据)
				//alert(currentPage);
				//取得分页数据
				getPaginateData(pageSize, currentPage);

			}
		});
	}
    //setPaginate():end

	///::正文开始::/
	//
	//pageSize:每页显示记录数
	getTotalPage(pageSize);
	//首次进入页面,取出第一页数据:currentPage=1
	getPaginateData(pageSize, 1);

});

DbHelper.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections;

namespace com.jjtg.web.Test.jPaginate
{

    /// <summary>
    /// A helper class used to execute queries against an Oracle database
    /// </summary>
    public abstract class DbHelper
    {
        /// <summary>
        /// 判断DataSet ds是否有数据，包括为空，没有表，没有行数据。如果返回true，表示有数据
        /// </summary>
        /// <param name="ds">待验证的DataSet</param>
        /// <returns></returns>
        public static bool DataSetIsNull(DataSet ds)
        {
            bool bl = false; //默认没有数据
            if (ds != null)
            {
                if (ds.Tables.Count != 0)
                {
                    if (ds.Tables[0].Rows.Count != 0)
                    {
                        bl = true;
                    }
                }
            }

            return bl;
        }
        /// <summary>
        /// Execute a select query that will return a result DataSet
        /// </summary>
        /// <param name="connString">Connection string</param>
         <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            //Create the command and connection and DataSet
            OracleCommand cmd = new OracleCommand();
            DataSet ds = new DataSet();

            using(OracleConnection conn = new OracleConnection(connectionString))
            {
                //Prepare the command to execute
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                OracleDataAdapter oda = new OracleDataAdapter(cmd);

                //Execute the query, stating that the connection should close when the resulting datareader has been fill
                oda.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
            
        }    
     }
}