-
August 14th, 2019, 07:52 PM
#1
cannot convert details array from json file to generate insert statement and when rea
cannot convert details array from json file to generate insert statement and when reading from file to generate insert statement for details it give error
Accessed J Array values with invalid key value: "table". Int32 array index expected.'
for master_table my code work good without any problem but details array not work
Code:
public static class JsonHelper
{
public static string GetInsertStatement(JToken mastertoken)
{
return string.Format("INSERT INTO {0}({1}) VALUES({2});",
mastertoken["table"],
GetFieldParameterNames(mastertoken),
GetFieldParameterNames(mastertoken, false));
}
static string GetFieldParameterNames(JToken mastertoken, bool fieldOnly = true)
{
string p = fieldOnly ? string.Empty : "@";
return string.Concat(string.Join(", ", mastertoken["keys"].Cast<JProperty>().Select(jp => p + jp.Name)),
", ", string.Join(", ", mastertoken["fields"].Cast<JProperty>().Select(jp => p + jp.Name)));
}
public static List<SqlParameter> GetSqlParams(JToken mastertoken)
{
List<SqlParameter> para = new List<SqlParameter>();
foreach (JToken jt in mastertoken["keys"])
para.Add(new SqlParameter("@" + jt.ToObject<JProperty>().Name, jt.First));
foreach (JToken jt in mastertoken["fields"])
para.Add(new SqlParameter("@" + jt.ToObject<JProperty>().Name, jt.First));
return para;
}
public static string GetInsertStatmentText(string JsonData)
{
string Insert = "";
JObject jo = JObject.Parse(JsonData);
JToken m = jo["details"];
string connectionstring = "Server=sdfff-PC\\SQL2014;Database=sqlm;User Id=sa;Password=abc123;";
using (SqlConnection connection = new SqlConnection(connectionstring))
{
using (SqlCommand command = new SqlCommand(JsonHelper.GetInsertStatement(m), connection))
{
connection.Open();
List<SqlParameter> lsp = JsonHelper.GetSqlParams(jo["details"]);
foreach (SqlParameter sqp in lsp)
command.Parameters.Add(sqp);
Insert = command.CommandText;
}
}
return Insert;
}
program.cs
static void Main(string[] args)
{
string JsonData = File.ReadAllText("D:\\2.json");
string insertStatment = JsonHelper.GetInsertStatmentText(JsonData);
}
Expected Result
3 insert statement :
Expected Result is 3 statement insert :
/
Code:
/ generated success
INSERT INTO master_table(id, branch_id, name, address, phone) VALUES(@id, @branch_id, @name, @address, @phone);
// cannot generated because error on first thread
insert into detail1_table(id,branch_id,LineNumber,ItemCode,Quantity,Price,Total) values (@id,@branch_id,@LineNumber,@ItemCode,@Quantity,@Price,@Total)
// cannot generate
insert into detail1_table(id,branch_id,LineNumber,ItemCode,Quantity,Price,Total) values (@id,@branch_id,@LineNumber,@ItemCode,@Quantity,@Price,@Total)
only work for master and another for details not work
and when generate insert statement for details give me error Accessed J Array values with invalid key value: "table". Int32 array index expected.'
json file
Code:
{
"master" : {
"table" : "master_table",
"fields" : {
"name" : "bar",
"address" : "fleet street",
"phone" : "555"
},
"keys":{
"id" : 1,
"branch_id" : 1
}
},
"details" : [
{
"table": "detail1_table",
"keys":{
"id" : 1,
"branch_id" : 1 ,
"LineNumber" : 1
},
"fields" : {
"ItemCode" : "item-5050",
"Quantity" : 10 ,
"Price" : 50 ,
"Total" : 500
}
},
{
"table": "detail1_table",
"keys":{
"id" : 1,
"branch_id" : 1 ,
"LineNumber" : 2
},
"fields" : {
"ItemCode" : "item-9050",
"Quantity" : 5 ,
"Price" : 20 ,
"Total" : 100
}
}
]
}
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|