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

根据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"}]}]

实际数据：

pricelist	introduction	annoucement	mainpicture	showstaff	showstyle	showprice
salondetail
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