-
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
}
}
]
}
-
August 14th, 2019, 09:01 PM
#2
Re: cannot convert details array from json file to generate insert statement and when
From a json point of view, the way you access "table" in master is different from how you access table in "details" because details is an array of items.
-
August 15th, 2019, 04:51 AM
#3
Re: cannot convert details array from json file to generate insert statement and when
can you help me make insert statement for details also with master
-
August 16th, 2019, 04:43 AM
#4
Re: cannot convert details array from json file to generate insert statement and when
Originally Posted by ahmed.salah
can you help me make insert statement for details also with master
You need to get the data out of json before you can insert it.
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
|