locked
How to generate parametrized insert statement for details with master master work but details not ? RRS feed

  • Question

  • User696604810 posted

    problem

    when get data from json file for master it work but details not work ?

    i work on web application asp.net core 2.1 i get data as json then concatenate them for master and details and there are another layer will execute it .

    i success generate insert statement on master

    but details cannot do

    so that i need to add details with master 

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

    Expected Result is 3 statement insert :

    // generated success

    INSERT INTO master_table(id, branch_id, name, address, phone) VALUES(@id, @branch_id, @name, @address, @phone);

    // generated problem

    insert into detail1_table(id,branch_id,LineNumber,ItemCode,Quantity,Price,Total) values (@id,@branch_id,@LineNumber,@ItemCode,@Quantity,@Price,@Total)


    // generated problem


    insert into detail1_table(id,branch_id,LineNumber,ItemCode,Quantity,Price,Total) values (@id,@branch_id,@LineNumber,@ItemCode,@Quantity,@Price,@Total)



    What I have tried:


    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["master"];
                string connectionstring = "Server=sdfff-PC\\SQL2014;Database=sqlm;User Id=sa;Password=abc123;"; //change connection string
                using (SqlConnection connection = new SqlConnection(connectionstring))
                {
                    using (SqlCommand command = new SqlCommand(JsonHelper.GetInsertStatement(m), connection))
                    {
                        connection.Open();
                        List<SqlParameter> lsp = JsonHelper.GetSqlParams(jo["master"]);
                        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);
            }
    

    Wednesday, August 14, 2019 10:49 PM

Answers

  • User711641945 posted

    Hi ahmebarbary,

    You could foreach the loop and get the details array like below:

    public static List<string> GetInsertStatmentText(string JsonData)
            {
                string Insert = "";
                JObject jo = JObject.Parse(JsonData);
                JToken m = jo["master"];
                JToken n = jo["details"];
                string connectionstring = "Server=sdfff-PC\\SQL2014;Database=sqlm;User Id=sa;Password=abc123;"; //change connection string
                var list = new List<string>();
                using (SqlConnection connection = new SqlConnection(connectionstring))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(JsonHelper.GetInsertStatement(m), connection))
                    {
                        List<SqlParameter> lsp = JsonHelper.GetSqlParams(jo["master"]);
                        foreach (SqlParameter sqp in lsp)
                            command.Parameters.Add(sqp);
                        Insert = command.CommandText;
                        list.Add(Insert);
                    }
                    foreach (var item in n)
                    {
                        using (SqlCommand command = new SqlCommand(JsonHelper.GetInsertStatement(item), connection))
                        {                      
                                List<SqlParameter> lsp = JsonHelper.GetSqlParams(item);
                                foreach (SqlParameter sqp in lsp)
                                    command.Parameters.Add(sqp);
                                Insert = command.CommandText;
                            
                            list.Add(Insert);
                        }
                    }
                }
                return list;
            }
    //program.cs
    public static void Main(string[] args) { string JsonData = File.ReadAllText("D:\\2.json"); List<string> insertStatment = JsonHelper.GetInsertStatmentText(JsonData); }

    Results:

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 15, 2019 4:37 AM