最近写了一篇课程论文关于地图的,于是就用到了百度地图API来进行开发。遇到一个问题就是如何把从数据库查询的结果以对象的形式回显到客户端。
通过查阅了解到了JSON对象,同时xml也可以实现,但是Json相对来说更简单些。Json 比xml更小、更简单、更快解析。
Json 语法
{"employees": [{ "firstName":"Bill" , "lastName":"Gates" },{ "firstName":"George" , "lastName":"Bush" },{ "firstName":"Thomas" , "lastName":"Carter" }]}
以大括号表示对象{} 上面显示的是employees对象
同时这是一个数据对象包含3个具体的人员对象。数组用中括号[]
JSON 文本格式在语法上与创建 JavaScript 对象的代码相同。
由于这种相似性,无需解析器,JavaScript 程序能够使用内建的 eval() 函数,用 JSON 数据来生成原生的 JavaScript 对象。(来自w3cschool)
所以在ASP.net 代码中我们只需要把查询的结果拼接成json字符串,然后返回到浏览器,利用函数eval进行解析,就可以像引用对象一样处理数据了。
我这里用的是ashx一般处理程序来作为后台处理代码。
JsonHelper.cs 类是把查询的结果以json字符串的形式返回
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;
using System.Text;
using MySql.Data.MySqlClient ;//引用mysql命名空间
using System.Runtime.Serialization.Json;
using System.Web.Script.Serialization;
using System.IO;
using System.ServiceModel.Web;
namespace WebGIS
{
public class JsonHelper
{
/// 获取JSON字符串
/// 值
/// 数据表名
public static string GetJSON(MySqlDataReader drValue, string strTableName)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("{");
sb.AppendLine(" "+ strTableName + ":[");
try
{
while (drValue.Read())
{
sb.Append(" {");
for (int i = 0; i < drValue.FieldCount; i++)
{
sb.AppendFormat("{0}:'{1}',", drValue.GetName(i), drValue.GetValue(i));
}
sb.Remove(sb.ToString().LastIndexOf(','), 1);
sb.AppendLine("},");
}
sb.Remove(sb.ToString().LastIndexOf(','), 1);
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
drValue.Close();
}
sb.AppendLine(" ]");
//sb.AppendLine(" }");
sb.AppendLine(" }");
return sb.ToString();
}
}
下面是对Json字符串的解析,采用AJAX前台发出命令后台进行处理
using System;
using System.Collections;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
namespace WebGIS
{
/// <summary>
/// $codebehindclassname$ 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class HandlerJson : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string type = context.Request["type"].ToString ();
if (type=="json")
{
CommClass .tablename = context.Request["tablename"].ToString();//表名
string queryField = context.Request["queryfield"].ToString();//查询图层
string querymethod = context.Request["querymethod"].ToString();//选择查询方式1代表按图层查询 2表示所有图层
string txtname = context.Request["txtname"].ToString();//查询的名称
if (querymethod == "1")
{
if (txtname=="")
{
CommClass.sConditon = string.Format("classname='{0}'", queryField);
}
else
{
CommClass.sConditon = string.Format("classname='{0}'and name LIKE '%{1}%'", queryField, txtname);
}
}
if (querymethod =="2")
{
if (txtname=="")
{
CommClass.sConditon = "";
}
else
{
CommClass.sConditon = string.Format("name LIKE '%{0}%'", txtname);
}
}
/*
* CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryByPageList`(
IN pageindex int,
IN columns varchar(1000),
IN table_name varchar(100),
IN scondition varchar(1000),
IN pagesize int ,
IN pk_field varchar(100)
)
*
*/
MySqlParameter [] param=new MySqlParameter [6];
param[0] = new MySqlParameter("?pageindex", MySqlDbType.Int32 );
param[1] = new MySqlParameter("?columns", MySqlDbType.VarChar, 1000);
param[2] = new MySqlParameter("?table_name", MySqlDbType.VarChar, 100);
param[3] = new MySqlParameter("?scondition", MySqlDbType.VarChar, 1000);
param[4] = new MySqlParameter("?pagesize", MySqlDbType.Int32 );
param[5] = new MySqlParameter("?pk_field", MySqlDbType.VarChar ,100 );
param[0].Value = 1;
param[1].Value = "*";
param[2].Value = CommClass.tablename;
param[3].Value = CommClass.sConditon;
param[4].Value = 10;
param[5].Value = "id";
MySqlDataReader dr = MySQLHelper.getDataReaderByPro("QueryByPageList", CommandType.StoredProcedure, param);
//转换为Json字符串
string strJson = JsonHelper.GetJSON(dr, "building");
//返回到客户端
context.Response.Write(strJson);
}
if (type =="pagecount")
{
/*
* CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPageCount`(
IN tablename varchar(100),
IN scondition varchar(1000),
out pagecount int
)
*
*/
MySqlParameter[] param = new MySqlParameter[3];
param[0] = new MySqlParameter("?tablename", MySqlDbType.VarChar ,100);
param[1] = new MySqlParameter("?scondition", MySqlDbType.VarChar, 1000);
param[2] = new MySqlParameter("?pagecount", MySqlDbType.Int32 );
param[0].Value = CommClass .tablename;
param[1].Value = CommClass .sConditon ;
param[2].Direction = ParameterDirection.Output;
MySqlDataReader dr = MySQLHelper.getDataReaderByPro("GetPageCount", CommandType.StoredProcedure, param);
int temp = Convert.ToInt32(param[2].Value);
if (temp%10==0)
{
context.Response.Write((temp / 10).ToString ());
}
else
{
context.Response.Write((temp / 10 + 1).ToString());
}
}
if (type =="querybypagenum")
{
int pagecount = Convert.ToInt32(context.Request["pagenum"].ToString());
MySqlParameter[] param = new MySqlParameter[6];
param[0] = new MySqlParameter("?pageindex", MySqlDbType.Int32);
param[1] = new MySqlParameter("?columns", MySqlDbType.VarChar, 1000);
param[2] = new MySqlParameter("?table_name", MySqlDbType.VarChar, 100);
param[3] = new MySqlParameter("?scondition", MySqlDbType.VarChar, 1000);
param[4] = new MySqlParameter("?pagesize", MySqlDbType.Int32);
param[5] = new MySqlParameter("?pk_field", MySqlDbType.VarChar, 100);
param[0].Value = pagecount;
param[1].Value = "*";
param[2].Value = CommClass .tablename;
param[3].Value = CommClass.sConditon;
param[4].Value = 10;
param[5].Value = "id";
MySqlDataReader dr = MySQLHelper.getDataReaderByPro("QueryByPageList", CommandType.StoredProcedure, param);
//转换为Json字符串
string strJson = JsonHelper.GetJSON(dr, "building");
//返回到客户端
context.Response.Write(strJson);
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
前台发出请求的Jquery代码
//查询数据
function queryData(table) {
var txtname = $("#txtName").val();
var querymethod = $(":radio:checked").val();
var tablename = table; //要查询的表名
$.ajax({
type: "Post",
url: "../HandlerJson.ashx",
cache: false,
data: { type: "json", tablename: tablename, queryfield: queryfield, querymethod: querymethod, txtname: txtname },
dataType: "text",
success: function(msg) {
obj = eval("(" + msg + ")");
//清空结果
$("#divResult ul li:gt(0)").remove();
//添加图标
AddMark(obj.building);
//显示页码
displayPageNum();
}
});
}