文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>获取取SQL语句,生成包括多个查询的Json数据

获取取SQL语句,生成包括多个查询的Json数据

时间:2010-09-09  来源:DotNet过客

根据action 参数从 _sql.xml 中选取相应的SQL语句,再替换上URL中所给的参数,去查询数据库,生成包括多个查询的Json数据

粘贴部分代码看各位大侠有没有这方面的需求,我仅以此抛砖引玉。[为什么没有直接上传附件的功能?看不明白需要完整项目文件的请回复]

 

假如链接是:http://XXX/SqlToJson/GetJson.ashx?action=salon&sid=10

 

_sql.xml配置如下:

 

代码
 1 <?xml version="1.0" encoding="utf-8" ?>
2 <actions>
3 <salon>
4 <salondetail>
5 <![CDATA[
6 select 'page.php?action=price&sid='+convert(varchar,id) as pricelist,introduction,annoucement,mainpicture,showstaff,showstyle,showprice from stores where id={sid}
7 ]]>
8 </salondetail>
9 <pic>
10 select path as src from images where store_id={sid} order by seq
11 </pic>
12 <regionlist>
13 select region_name as text,id as value from regions where id={sid} order by region_name
14 </regionlist>
15 </salon>
16 <salon_reg>
17 <regionlist>
18 select region_name as text,id as value from regions order by region_name
19 </regionlist>
20 </salon_reg>
21 </actions>

 

GetJson.ashx:(生成的Json格式的数据是纯文本所以用此文件[一般处理程序])

 

代码
 1     protected string action = "";
2 public void ProcessRequest (HttpContext context) {
3 action = context.Request.QueryString["action"];//获取action参数值
4 DataSet ds = new DataSet();
5 DataSet objDataSet = new DataSet();
6 objDataSet.ReadXml(context.Server.MapPath("_sql.xml"));//读取_sql.xml
7 DataTable objdt = objDataSet.Tables[action];//action参数值对应的节点作为table
8 string strsql = "";
9 for (int i = 0; i < objdt.Rows.Count; i++)
10 {
11 for (int j = 0; j < objdt.Columns.Count; j++)
12 {
13 DataTable dt = new DataTable();
14 strsql = objdt.Rows[i][j].ToString();
15 for (int a = 1; a < context.Request.QueryString.Count; a++)//?action=salon&a=1&b=2&c=3 获取除action后面的多个参数
16 {
17 strsql = strsql.Replace("{" + context.Request.QueryString.GetKey(a) + "}", context.Request.QueryString[a]);
18 }
19 SQLHelper.ExecuteSql(strsql, out dt);//SQLHelper操作数据库的类
20 DataTable dt1 = dt.Copy();
21 dt1.TableName = objdt.Columns[j].ToString().Trim();
22 ds.Tables.Add(dt1);
23 }
24 }
25 context.Response.Write(JsonHelper.ToJson(ds));//JsonHelper生成json格式的类
26 }

 

 

JsonHelper.cs:

 

代码
 /// <summary>
/// 将ds转换成json
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
public static string ToJson(DataSet ds)
{
ArrayList jDs
= new ArrayList();
foreach (DataTable mDT in ds.Tables)
{
Dictionary
<string, object> jDT = new Dictionary<string, object>();
ArrayList jRows
= new ArrayList();
foreach (DataRow mRow in mDT.Rows)
{
Dictionary
<string, string> jRow = new Dictionary<string, string>();
foreach (DataColumn col in mDT.Columns)
{
jRow.Add(col.ColumnName, mRow[col].ToString());
}
jRows.Add(jRow);
}
jDT.Add(mDT.TableName, jRows);
jDs.Add(jDT);
}
StringBuilder json
= new StringBuilder();
JavaScriptSerializer jsSerializer
= new JavaScriptSerializer();
jsSerializer.Serialize(jDs, json);
return json.ToString();
}

 

 

SQLHelper.cs:

 

代码
 //数据库连接字符串
public static readonly string Con = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
/// <summary>
/// 执行SQL语句,使用SqlDataAdapter获取记录集,并将数据放到System.Data.DataTable 中
/// </summary>
/// <param name="SqlString">SQL语句的名字</param>
/// <param name="dataTable">输出的System.Data.DataTable 数据放在dataSet中 </param>
public static void ExecuteSql(string SqlString, out System.Data.DataTable dataTable)
{
DataSet dataSet
= new DataSet();
dataTable
= new System.Data.DataTable();
try
{
SqlConnection con
= new SqlConnection(Con);
con.Open();
SqlCommand cmd
= new SqlCommand(SqlString, con);
SqlDataAdapter dataAdapter
= new SqlDataAdapter(cmd);
dataAdapter.Fill(dataSet);
if (dataSet.Tables.Count > 0)
dataTable
= dataSet.Tables[0];
dataAdapter.Dispose();
cmd.Dispose();
con.Dispose();
}
catch
{
}
}

 

运行GetJson.ashx结果:

 

[{"salondetail":[{"pricelist":"page.php?action=price&sid=39","introduction":"xxxxxCelebrity Hair Stylist, Jonal Chong Sets Foot in Orchard Central.Aristocracy, hair and beauty embraces a familiar name","annoucement":"Suspendisse quis dolor id felis lobortis consectetur ut sit amet dui. Phasellus vitae orci dolor, a congue est. Donec posuere fermentum pulvinar. Nunc non eleifend libero. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Vivamus molestie aliquam libero, sed commodo lectus mattis et. Nulla velit ante, aliquet ut commodo in, venenatis non sapien.","mainpicture":"100604030343.jpg","showstaff":"1","showstyle":"0","showprice":"0"}]},{"pic":[{"src":"100704024259.jpg"},{"src":"100704024359.jpg"},{"src":"100704094411.jpg"}]},{"regionlist":[{"text":"Changi","value":"39"}]}]

 

实际数据:



salondetail
pricelist introduction annoucement mainpicture showstaff showstyle showprice
page.php?action=price&sid=39 xxxxxCelebrity Hair Stylist, Jonal Chong Sets Foot in Orchard Central.Aristocracy, hair and beauty embraces a familiar name Suspendisse quis dolor id felis lobortis consectetur ut sit amet dui. Phasellus vitae orci dolor, a congue est. Donec posuere fermentum pulvinar. Nunc non eleifend libero. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Vivamus molestie aliquam libero, sed commodo lectus mattis et. Nulla velit ante, aliquet ut commodo in, venenatis non sapien. 100604030343.jpg 1 0 0
pic
src
100704024259.jpg
100704024359.jpg
100704094411.jpg
regionlist
text value
Changi 39
相关阅读 更多 +
排行榜 更多 +
辰域智控app

辰域智控app

系统工具 下载
网医联盟app

网医联盟app

运动健身 下载
汇丰汇选App

汇丰汇选App

金融理财 下载