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
          }
      }
  ]
}

---

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.

can you help me make insert statement for details also with master

Quote:

---

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.