练习 jquery+Ajax+Json 绑定数据

先看数据库结构和数据

html 代码

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm4.aspx.cs" Inherits="jquerytest.test1.WebForm4" %>

<!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.4.1-vsdoc.js" type="text/javascript"></script>
    

    <script type="text/javascript"> 
        $(document).ready(function () { 
            $.ajax({ 
                type: "get", 
                dataType: "Json", 
                url: "../Ashx/Handler1.ashx", 
                start: function () { alert("开始获取数据了") }, 
                complete: function () { alert("获取完了") }, 
                success: function (data) { 
                    var t = eval(data); //强制转换一下json字符串，生成json对象 
                    $.each(t, function (i, n) { 
                        var row = $("#template").clone(); //克隆模板，创建一个新数据行 
                        for (attribute in n) { 
                            row.find("#" + attribute).html(n[attribute]); //循环json对象的属性，并赋值到数据行中对应的列，此处列的id就是相应的属性名称 
                        } 
                        row.appendTo($("#testTable")); 
                    }); 
                } 
            });
        });

</script>

    
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table id="testTable" border="1"> 
            <th style="width:30%">编号</th><th style="width:30%">标题</th><th style="width:30%">内容</th> 
            <!--数据模板--> <!--其中每一列的id就是对应记录中的列名-->
             <tr id="template">
             <td id="Id" style="width:30%"></td>
             <td id="title" style="width:30%"></td>
             <td id="intro" style="width:30%"></td>
             </tr> <!--数据模板-->
        </table>
    </div>
    </form>
</body>
</html>

JavaScriptSerializer 类

System.Web.Script.Serialization.JavaScriptSerializer

命名空间： System.Web.Script.Serialization
程序集： System.Web.Extensions（在 System.Web.Extensions.dll 中）

参考微软解说：http://msdn.microsoft.com/zh-cn/library/system.web.script.serialization.javascriptserializer.aspx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Web;
using System.Web.Script.Serialization;
using System.Data; 


namespace tanyong.DB.JsonHelper
{
    /// <summary> 
    /// JSON帮助类 
    /// </summary> 
    public class JSONHelper
    {
        /// <summary> 
        /// 对象转JSON 
        /// </summary> 
        /// <param name="obj">对象</param> 
        /// <returns>JSON格式的字符串</returns> 
        public static string ObjectToJSON(object obj)
        {
            JavaScriptSerializer jss = new JavaScriptSerializer();
            try
            {
                return jss.Serialize(obj);
            }
            catch (Exception ex)
            {

                throw new Exception("JSONHelper.ObjectToJSON(): " + ex.Message);
            }
        }

        /// <summary> 
        /// 数据表转键值对集合 
        /// 把DataTable转成 List集合, 存每一行 
        /// 集合中放的是键值对字典,存每一列 
        /// </summary> 
        /// <param name="dt">数据表</param> 
        /// <returns>哈希表数组</returns> 
        public static List<Dictionary<string, object>> DataTableToList(DataTable dt)
        {
            List<Dictionary<string, object>> list
                 = new List<Dictionary<string, object>>();

            foreach (DataRow dr in dt.Rows)
            {
                Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                {
                    dic.Add(dc.ColumnName, dr[dc.ColumnName]);
                }
                list.Add(dic);
            }
            return list;
        }

        /// <summary> 
        /// 数据集转键值对数组字典 
        /// </summary> 
        /// <param name="dataSet">数据集</param> 
        /// <returns>键值对数组字典</returns> 
        public static Dictionary<string, List<Dictionary<string, object>>> DataSetToDic(DataSet ds)
        {
            Dictionary<string, List<Dictionary<string, object>>> result = new Dictionary<string, List<Dictionary<string, object>>>();

            foreach (DataTable dt in ds.Tables)
                result.Add(dt.TableName, DataTableToList(dt));

            return result;
        }

        /// <summary> 
        /// 数据表转JSON 
        /// </summary> 
        /// <param name="dataTable">数据表</param> 
        /// <returns>JSON字符串</returns> 
        public static string DataTableToJSON(DataTable dt)
        {
            return ObjectToJSON(DataTableToList(dt));
        }

        /// <summary> 
        /// JSON文本转对象,泛型方法 
        /// </summary> 
        /// <typeparam name="T">类型</typeparam> 
        /// <param name="jsonText">JSON文本</param> 
        /// <returns>指定类型的对象</returns> 
        public static T JSONToObject<T>(string jsonText)
        {
            JavaScriptSerializer jss = new JavaScriptSerializer();
            try
            {
                return jss.Deserialize<T>(jsonText);
            }
            catch (Exception ex)
            {
                throw new Exception("JSONHelper.JSONToObject(): " + ex.Message);
            }
        }

        /// <summary> 
        /// 将JSON文本转换为数据表数据 
        /// </summary> 
        /// <param name="jsonText">JSON文本</param> 
        /// <returns>数据表字典</returns> 
        public static Dictionary<string, List<Dictionary<string, object>>> TablesDataFromJSON(string jsonText)
        {
            return JSONToObject<Dictionary<string, List<Dictionary<string, object>>>>(jsonText);
        }

        /// <summary> 
        /// 将JSON文本转换成数据行 
        /// </summary> 
        /// <param name="jsonText">JSON文本</param> 
        /// <returns>数据行的字典</returns> 
        public static Dictionary<string, object> DataRowFromJSON(string jsonText)
        {
            return JSONToObject<Dictionary<string, object>>(jsonText);
        } 

    }
}

建一个一般处理程序

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using tanyong.DB.JsonHelper;
using System.Data;
using System.Data.SqlClient;

namespace jquerytest.Ashx
{
    /// <summary>
    /// $codebehindclassname$ 的摘要说明
    /// </summary>
    public class Handler1 : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string sql = "select Id,title,intro from books";
            DataTable table = SqlHelper.ExecuteDataTable(SqlHelper.connstr, CommandType.Text, sql,null);
            context.Response.Write(JSONHelper.DataTableToJSON(table)); 

        }

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

SqlHelper

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;

namespace tanyong.DB.JsonHelper
{

    public class SqlHelper
    {

        public static readonly string connstr = "Data Source=.;Initial Catalog=testDB1;User ID=sa;Password=sasa;Connect Timeout=30";
        public static DataTable ExecuteDataTable(string connectionstring, CommandType cmdtype, string commandText, params SqlParameter[] cmdParms)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionstring))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                System.Data.SqlClient.SqlCommand command = new SqlCommand();
                command.CommandText = commandText;
                command.CommandType = cmdtype;
                command.Connection = con;
                command.CommandTimeout = 1000 * 60 * 10;
                if (cmdParms != null)
                {
                    command.Parameters.AddRange(cmdParms);
                }
                adapter.SelectCommand = command;
                adapter.Fill(ds, "tb");
            }
            return ds.Tables["tb"];

        }
        public static DataTable ExecuteDataTable(string connectionstring, CommandType cmdtype, string commandText, int timeout, params SqlParameter[] cmdParms)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionstring))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                System.Data.SqlClient.SqlCommand command = new SqlCommand();
                command.CommandText = commandText;
                command.CommandType = cmdtype;
                command.Connection = con;
                command.CommandTimeout = timeout;
                if (cmdParms != null)
                {
                    command.Parameters.AddRange(cmdParms);
                }
                adapter.SelectCommand = command;
                adapter.Fill(ds, "tb");
            }

            return ds.Tables["tb"];

        }
        public static DataTable ExecuteM_LogDataTable(string connectionstring, CommandType cmdtype, string commandText)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionstring))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                System.Data.SqlClient.SqlCommand command = new SqlCommand();
                command.CommandText = commandText;
                command.CommandType = cmdtype;
                command.Connection = con;
                adapter.SelectCommand = command;
                adapter.Fill(ds, "tb");
            }
            return ds.Tables["tb"];

        }
        public static int InsertAndReturnID(string connectionstring, CommandType cmdtype, string commandText, params SqlParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                SqlCommand myCommand = new SqlCommand();
                conn.Open();
                SqlTransaction myTrans = conn.BeginTransaction();
                myCommand.Transaction = myTrans;
                try
                {
                    PrepareCommand(myCommand, conn, null, cmdtype, commandText + ";select scope_identity();", cmdParms);
                    Object o = myCommand.ExecuteScalar();
                    myCommand.Parameters.Clear();
                    myTrans.Commit();
                    return Convert.ToInt32(o);
                }
                catch (Exception e)
                {
                    try
                    {
                        myTrans.Rollback();
                        return 0;
                    }
                    catch (SqlException ex)
                    {
                        return 0;
                    }
                }
                finally
                {
                    conn.Close();
                }

            }
        }
        public static int ExecuteUpdate(string connectionstring, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {
            using (SqlConnection con = new SqlConnection(connectionstring))
            {
                try
                {
                    SqlCommand command = new SqlCommand();
                    PrepareCommand(command, con, null, cmdtype, cmdText, cmdParms);
                    int i = command.ExecuteNonQuery();
                    command.Parameters.Clear();
                    return i;

                }
                catch
                {
                    return 0;
                }
                finally
                {
                    con.Close();
                }
            }

        }
        public static int ExecuteUpdateorInsertorDelete(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, con, null, cmdtype, cmdText, cmdParms);
            int i = command.ExecuteNonQuery();
            command.Parameters.Clear();
            con.Close();
            return i;
        }
        public static int ExecuteUpdateorInsertorDelete(SqlTransaction trans, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, trans.Connection, trans, cmdtype, cmdText, cmdParms);
            int i = command.ExecuteNonQuery();
            command.Parameters.Clear();
            trans.Connection.Close();
            return i;
        }
        public static Object ExecuteScalar(string connectionstring, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {
            using (SqlConnection con = new SqlConnection(connstr))
            {
                SqlCommand command = new SqlCommand();
                PrepareCommand(command, con, null, cmdtype, cmdText, cmdParms);
                Object o = command.ExecuteScalar();
                command.Parameters.Clear();
                return o;
            }

        }
        public static Object ExecuteScalar(SqlConnection con, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {

            SqlCommand command = new SqlCommand();
            PrepareCommand(command, con, null, cmdtype, cmdText, cmdParms);
            Object o = command.ExecuteScalar();
            command.Parameters.Clear();
            return o;


        }
        public static int ExecuteScalar(SqlTransaction trans, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand command = new SqlCommand();
            PrepareCommand(command, trans.Connection, trans, cmdtype, cmdText, cmdParms);
            int i = command.ExecuteNonQuery();
            command.Parameters.Clear();
            return i;
        }
        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;
            cmd.CommandType = cmdType;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            if (cmdParms != null)
            {
                foreach (SqlParameter sp in cmdParms)
                {
                    if (sp.Value == null)
                        sp.Value = DBNull.Value;
                }
                cmd.Parameters.AddRange(cmdParms);
            }
        }
        public static SqlDataReader ExecuteReader(string connectionstring, CommandType cmdtype, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionstring);
            try
            {
                PrepareCommand(cmd, conn, null, cmdtype, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }

        }
        public static DataSet ExecuteDataSet(string connectionstring, CommandType cmdtype, string commandText)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionstring))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                System.Data.SqlClient.SqlCommand command = new SqlCommand();
                command.CommandText = commandText;
                command.CommandType = cmdtype;
                command.Connection = con;
                adapter.SelectCommand = command;
                adapter.Fill(ds);
            }
            return ds;
            }

        #region 带参数的DataSet查询
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }
        #endregion        
    }
}

最后无刷新效果

原文链接：https://blog.csdn.net/suntanyong88/article/details/7532118

练习 jquery+Ajax+Json 绑定数据

标签云

近期文章

分类

练习 jquery+Ajax+Json 绑定数据

相关文章