CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2019
    Posts
    6

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

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    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.

  3. #3
    Join Date
    Aug 2019
    Posts
    6

    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

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: cannot convert details array from json file to generate insert statement and when

    Quote Originally Posted by ahmed.salah View Post
    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
  •  





Click Here to Expand Forum to Full Width

Featured