I'm attempting to convert JSON sent from a web browser client to the server so I can send the data as parameters in the form of XML to an SQL database. I'm struggling to do this as I have objects inside of objects and I'm not sure how to convert them dynamically into a structured XML format. Below are examples of the JSON I am using, the possible XML formats I'm trying to turn it into (or anything close to them), and the code that I am using.
JSON:
[
{"value":50,"name":"desired_gross_margin","type":"int"},
{"value":50,"name":"desired_adjusted_gross_margin","type":"int"},
{"value":0,"name":"target_electricity_tariff_unit_charge","type":"decimal"},
{"value":0,"name":"target_electricity_tariff_standing_charge","type":"decimal"},
{"value":0,"name":"target_gas_tariff_unit_charge","type":"decimal"},
{"value":0,"name":"target_gas_tariff_standing_charge","type":"decimal"},
{"value":"10/10/2016","name":"planned_go_live_date","type":"DateTime"},
{"value":"0","name":"assumed_fuel_ratio","type":"int"},
{"value":{
"year_one":"Cold",
"year_two":"Average",
"year_three":"Warm"
},
"name":"weather_variable","type":"string"}
]
Possible XML outputs:
1:
<Filters>
<CustomerParameters>
<CustomParameter name="desired_gross_margin" type="int" value="50"/>
<CustomParameter name="desired_adjusted_gross_margin" type="int" value="50"/>
<CustomParameter name="target_electricity_tariff_unit_charge" type="decimal" value="0"/>
<CustomParameter name="target_electricity_tariff_standing_charge" type="decimal" value="0"/>
<CustomParameter name="target_gas_tariff_unit_charge" type="decimal" value="0"/>
<CustomParameter name="target_gas_tariff_standing_charge" type="decimal" value="0"/>
<CustomParameter name="planned_go_live_date" type="DateTime" value="10/10/2016"/>
<CustomParameter name="assumed_fuel_ratio" type="int" value="0"/>
<CustomParamaters name="weather_variables">
<CustomParameter name="year_one" type="string" value="Cold"/>
<CustomParameter name="year_two" type="string" value="Average"/>
<CustomParameter name="year_three" type="string" value="Cold"/>
</CustomParameters>
</CustomParameters>
</Filters>
2:
<?xml version="1.0" encoding="UTF-8" ?>
<0>
<value>50</value>
<name>desired_gross_margin</name>
<type>int</type>
</0>
<1>
<value>50</value>
<name>desired_adjusted_gross_margin</name>
<type>int</type>
</1>
<2>
<value>0</value>
<name>target_electricity_tariff_unit_charge</name>
<type>decimal</type>
</2>
<3>
<value>0</value>
<name>target_electricity_tariff_standing_charge</name>
<type>decimal</type>
</3>
<4>
<value>0</value>
<name>target_gas_tariff_unit_charge</name>
<type>decimal</type>
</4>
<5>
<value>0</value>
<name>target_gas_tariff_standing_charge</name>
<type>decimal</type>
</5>
<6>
<value>10/10/2016</value>
<name>planned_go_live_date</name>
<type>DateTime</type>
</6>
<7>
<value>0</value>
<name>assumed_fuel_ratio</name>
<type>int</type>
</7>
<8>
<value>
<year_one>Cold</year_one>
<year_two>Average</year_two>
<year_three>Warm</year_three>
</value>
<name>weather_variable</name>
<type>string</type>
</8>
</xml>
C# code:
ForecastController.cs:
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web.Http;
using System.Web.Script.Serialization;
using System.Xml;
namespace ForecastServices.Controllers
{
public class ForecastController : ApiController
{
[HttpPost]
public List<Data> GetData(HttpRequestMessage request)
{
string connection_string = ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
string sql = "DataHub.get_GrossMarginModel";
string json = request.Content.ReadAsStringAsync().Result;
//var filters2 = new JavaScriptSerializer().Deserialize<dynamic>(json); //this works but I can't turn it into XML! :(
List<Filter> filters = new JavaScriptSerializer().Deserialize<List<Filter>>(json);
string xml = Filter.getFilterListXML(filters);
List<Data> data = new List<Data>();
using(SqlConnection connection = new SqlConnection(connection_string))
{
connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("filter_xml", ""));
cmd.CommandType = CommandType.StoredProcedure;
var adapter = new SqlDataAdapter(cmd);
var set = new DataSet();
cmd.ExecuteNonQuery();
adapter.Fill(set);
if (set.Tables.Count > 0)
{
foreach (DataRow tableRow in set.Tables[0].Rows)
{
data.Add(new Data()
{
name = tableRow.ItemArray[0].ToString(),
year_one = (int)tableRow.ItemArray[1],
year_two = (int)tableRow.ItemArray[2],
year_three = (int)tableRow.ItemArray[3],
});
}
}
connection.Close();
}
return data;
}
}
}
Filter.cs:
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
namespace ForecastServices.Domain
{
public class Filter
{
public string value { get; set; }
public string name { get; set; }
public string type { get; set; }
public string getXML()
{
return string.Format("<CustomParameter name=\"{0}\" type=\"{1}\" value=\"{2}\"/>", name, type, value);
}
public static string getFilterListXML(ICollection<Filter> filters)
{
StringBuilder XMLString = new StringBuilder();
XMLString.Append("<Filters><CustomerParameters>");
foreach (Filter f in filters)
{
XMLString.Append(f.getXML());
}
XMLString.Append("</CustomParameters></Filters>");
return XMLString.ToString();
}
}
}
