none
How to create function return statement insert into table from json file ? RRS feed

  • Question

    • 300
    • 11.2Problem
    How to return statement insert into table values from json file ?
    I work on newton soft library and I try to insert data to table master_table
    but my pro
    1. insert into master_table(id,branch_id,name,address,phone) values (1,1,"bar","fleet street","555")  
    table ,keys,fields content flexible or dynamic .
    my jsonfile D:\\1.json as below :
    1. {  
    2. "master" : {  
    3. "table""master_table",  
    4. "fields": {  
    5. "name" : "bar",  
    6. "address" : "fleet street",  
    7. "phone" : "555"  
    8. },  
    9. "keys":{  
    10. "id" : 1,  
    11. "branch_id" : 1  
    12. }  
    13. }  
    14. }  
    I already make get keys and fields but cannot make concatenate
    insert statement as result .
    How to concatenate insert statement that have keys + fields

    as statement on first thread .

    what i have tried

     
    public static ExpandoObject ToExpando(string json)  
    {  
    if (string.IsNullOrEmpty(json))  
    return null;  
    return (ExpandoObject)ToExpandoObject(JToken.Parse(json));  
    }  
      
      
    private static object ToExpandoObject(JToken token)  
    {  
      
    switch (token.Type)  
    {  
    case JTokenType.Object:  
    var expando = new ExpandoObject();  
    var expandoDic = (IDictionary<string, object>)expando;  
    foreach (var prop in token.Children<JProperty>())  
    expandoDic.Add(prop.Name, ToExpandoObject(prop.Value));  
    return expando;  
    case JTokenType.Array:  
    return token.Select(ToExpandoObject).ToList();  
      
    default:  
    return ((JValue)token).Value;  
    }  
    }  
    static void Main(string[] args)  
    {  
    string JsonData = File.ReadAllText("D:\\1.json");  
    var ebj = SqlFactory.ToExpando (JsonData);  
    var name = (ebj as dynamic).master.table;  
    var fields = (ebj as dynamic).master.fields;  
    foreach (dynamic i in fields)  
    {  
    string key = i.Key;  
    object value = i.Value;  
    }  
    var keys = (ebj as dynamic).master.keys;  
      // i need after that create function return create insert statement 
    } 
    Tuesday, August 13, 2019 3:15 AM

Answers

  • Hi engahmedbarbary,

    Thanks for the feedback.

    I have modified my code, which could prevent SQL injection.

    class Program { public static ExpandoObject ToExpando(string json) { if (string.IsNullOrEmpty(json)) return null; return (ExpandoObject)ToExpandoObject(JToken.Parse(json)); } private static object ToExpandoObject(JToken token) { switch (token.Type) { case JTokenType.Object: var expando = new ExpandoObject(); var expandoDic = (IDictionary<string, object>)expando; foreach (var prop in token.Children<JProperty>()) expandoDic.Add(prop.Name, ToExpandoObject(prop.Value)); return expando; case JTokenType.Array: return token.Select(ToExpandoObject).ToList(); default: return ((JValue)token).Value; } } static void Main(string[] args) { string JsonData = File.ReadAllText("D:\\1.json"); var ebj = ToExpando(JsonData); var name = (ebj as dynamic).master.table; var fields = (ebj as dynamic).master.fields; string key1 = string.Empty; string value1 = string.Empty; List<SqlParameter> list = new List<SqlParameter>(); foreach (dynamic i in fields) { string key = i.Key; object value = i.Value; key1 = key1 + key + ","; value1 = value1 + "@" + key + ","; list.Add(new SqlParameter("@" + key, value)); } var keys = (ebj as dynamic).master.keys; foreach (dynamic i in keys) { string key = i.Key; object value = i.Value; key1 = key1 + key + ","; value1 = value1 + "@" + key + ","; list.Add(new SqlParameter("@" + key, value)); } key1 = key1.Remove(key1.LastIndexOf(',')); value1 = value1.Remove(value1.LastIndexOf(',')); string sql = string.Format("insert into {0}({1}) values({2})", name, key1, value1); SqlConnection connection = new SqlConnection(@"connectionstring"); connection.Open(); SqlCommand cmd = new SqlCommand(sql, connection); foreach (var item in list) { cmd.Parameters.Add(item); }

        cmd.ExecuteNonQuery(); } }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 5:06 AM
    Moderator
  • Hi 

    Thanks for the feedback.

    If you want to take array of details automatically, you could try the following code.

         static void Main(string[] args)
            {
                string JsonData = File.ReadAllText("D:\\1.json");
                var ebj = ToExpando(JsonData);
                string key1 = string.Empty;
                string value1 = string.Empty;
                dynamic name = null;
                string sql1 = string.Empty;
                var array = (ebj as dynamic).details;
                List<string> list = new List<string>();
                foreach (dynamic item in array)
                {
                    name = item.table;
                    foreach (dynamic i in item.fields)
                    {
                        string key = i.Key;
                        key1 = key1 + key + ",";
                        value1 = value1 + "@" + key + ",";
    
                    }
                    foreach (dynamic i in item.keys)
                    {
                        string key = i.Key;
                        key1 = key1 + key + ",";
                        value1 = value1 + "@" + key + ",";
                    }
                    key1 = key1.Remove(key1.LastIndexOf(','));
                    value1 = value1.Remove(value1.LastIndexOf(','));
                    sql1 = string.Format("insert into {0}({1}) values({2})", name, key1, value1);
                    list.Add(sql1);
                    value1 = string.Empty;
                    key1 = string.Empty;
                    Console.WriteLine(sql1);
                }
    
                Console.ReadKey();
    
    
    
            }

    Result:

    The result could be stored in a list.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 3:17 AM
    Moderator

All replies

  • Hi engahmedbarbary,

    Thank you for posting here.

    For your question, you want to create function to return  sql statement.

    First, I want to mention that we should use the following statement to do some operations on database.

    insert into master_table(name,address,phone,id,branch_i) values('bar','fleet street','555','1','1')

    Please note after values, as usual, we need to use two single quotes(' ') to contains the value.

    Second, you could use the following code to get sql statement.

       class Program
        {
    
            public static ExpandoObject ToExpando(string json)
            {
                if (string.IsNullOrEmpty(json))
                    return null;
                return (ExpandoObject)ToExpandoObject(JToken.Parse(json));
            }
            private static object ToExpandoObject(JToken token)
            {
    
                switch (token.Type)
                {
                    case JTokenType.Object:
                        var expando = new ExpandoObject();
                        var expandoDic = (IDictionary<string, object>)expando;
                        foreach (var prop in token.Children<JProperty>())
                            expandoDic.Add(prop.Name, ToExpandoObject(prop.Value));
                        return expando;
                    case JTokenType.Array:
                        return token.Select(ToExpandoObject).ToList();
    
                    default:
                        return ((JValue)token).Value;
                }
            }
                static void Main(string[] args)
                {
                string JsonData = File.ReadAllText("D:\\1.json");
                var ebj = ToExpando(JsonData);
                var name = (ebj as dynamic).master.table;
                var fields = (ebj as dynamic).master.fields;
                string key1 = string.Empty;
                string value1 = string.Empty;
                foreach (dynamic i in fields)
                {
                   
                    string key = i.Key;
                    object value = i.Value;
                    key1 = key1 + key + ",";
                    value1 = value1 +"'" +value.ToString()+"'" + ",";
                }
                var keys = (ebj as dynamic).master.keys;
                foreach (dynamic i in keys)
                {
                    string key = i.Key;
                    object value = i.Value;
                    key1 = key1 + key + ",";
                    value1 = value1 + "'" + value.ToString() + "'" + ",";
                }
                key1=key1.Remove(key1.LastIndexOf(','));
                value1= value1.Remove(value1.LastIndexOf(','));
                string sql = string.Format("insert into {0}({1}) values({2})", name, key1, value1);
    
            }
        }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 13, 2019 5:59 AM
    Moderator
  • thank you for reply 

    this is actually what i need but are way of writing code above allow SQL injection or not .

    or writing code as above is secure .

    Tuesday, August 13, 2019 11:03 AM
  • Hello,

    In regards to SQL-Injection, that would be dependent on if you think there is a possibility of someone first wanting to hack your program or not.

    On a side note for preparing data for SQL w/o formal parameters I would use the following method of formatting data.

    See the section on "Building SQL queries"

    https://thomaslevesque.com/2015/02/24/customizing-string-interpolation-in-c-6/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, August 13, 2019 11:18 AM
    Moderator
  • please can you help me to write code under main function as paramterized query because 

    i need to prevent SQL injection .

    so that How to write code under main function as paramterized query

    Tuesday, August 13, 2019 11:30 AM
  • Hi engahmedbarbary,

    Thanks for the feedback.

    I have modified my code, which could prevent SQL injection.

    class Program { public static ExpandoObject ToExpando(string json) { if (string.IsNullOrEmpty(json)) return null; return (ExpandoObject)ToExpandoObject(JToken.Parse(json)); } private static object ToExpandoObject(JToken token) { switch (token.Type) { case JTokenType.Object: var expando = new ExpandoObject(); var expandoDic = (IDictionary<string, object>)expando; foreach (var prop in token.Children<JProperty>()) expandoDic.Add(prop.Name, ToExpandoObject(prop.Value)); return expando; case JTokenType.Array: return token.Select(ToExpandoObject).ToList(); default: return ((JValue)token).Value; } } static void Main(string[] args) { string JsonData = File.ReadAllText("D:\\1.json"); var ebj = ToExpando(JsonData); var name = (ebj as dynamic).master.table; var fields = (ebj as dynamic).master.fields; string key1 = string.Empty; string value1 = string.Empty; List<SqlParameter> list = new List<SqlParameter>(); foreach (dynamic i in fields) { string key = i.Key; object value = i.Value; key1 = key1 + key + ","; value1 = value1 + "@" + key + ","; list.Add(new SqlParameter("@" + key, value)); } var keys = (ebj as dynamic).master.keys; foreach (dynamic i in keys) { string key = i.Key; object value = i.Value; key1 = key1 + key + ","; value1 = value1 + "@" + key + ","; list.Add(new SqlParameter("@" + key, value)); } key1 = key1.Remove(key1.LastIndexOf(',')); value1 = value1.Remove(value1.LastIndexOf(',')); string sql = string.Format("insert into {0}({1}) values({2})", name, key1, value1); SqlConnection connection = new SqlConnection(@"connectionstring"); connection.Open(); SqlCommand cmd = new SqlCommand(sql, connection); foreach (var item in list) { cmd.Parameters.Add(item); }

        cmd.ExecuteNonQuery(); } }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 5:06 AM
    Moderator
  • thank you for reply

    this is post solved for master but details is array 

    so that how to generate master and details 

    meaning generate 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)

    my json file as below

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

     
    Thursday, August 15, 2019 7:30 AM
  • Hi engahmedbarbary,

    Thanks for the feedback.

    I have modified the code, you could have a look.

         static void Main(string[] args)
            {
                string JsonData = File.ReadAllText("D:\\1.json");
                var ebj = ToExpando(JsonData);
                var name = (ebj as dynamic).details[0].table;
                var keys = (ebj as dynamic).details[0].keys;
                var fields = (ebj as dynamic).details[0].fields;
                var name1 = (ebj as dynamic).details[1].table;
                var keys1 = (ebj as dynamic).details[1].keys;
                var fields1 = (ebj as dynamic).details[1].fields;
                string key1 = string.Empty;
                string value1 = string.Empty;
                foreach (dynamic i in fields)
                {
                    string key = i.Key;
                    object value = i.Value;
                    key1 = key1 + key + ",";
                    value1 = value1 + "@" + key + ",";
    
                }
                foreach (dynamic i in keys)
                {
                    string key = i.Key;
                    object value = i.Value;
                    key1 = key1 + key + ",";
                    value1 = value1 + "@" + key + ",";
                }
                key1 = key1.Remove(key1.LastIndexOf(','));
                value1 = value1.Remove(value1.LastIndexOf(','));
                string sql1 = string.Format("insert into {0}({1}) values({2})", name, key1, value1);
                //output:insert into detail1_table(ItemCode,Quantity,Price,Total,id,branch_id,LineNumber) values(@ItemCode,@Quantity,@Price,@Total,@id,@branch_id,@LineNumber)
                key1 = string.Empty;
                value1 = string.Empty;
                foreach (dynamic i in fields1)
                {
                    string key = i.Key;
                    object value = i.Value;
                    key1 = key1 + key + ",";
                    value1 = value1 + "@" + key + ",";
    
                }
                foreach (dynamic i in keys1)
                {
                    string key = i.Key;
                    object value = i.Value;
                    key1 = key1 + key + ",";
                    value1 = value1 + "@" + key + ",";
                }
                key1 = key1.Remove(key1.LastIndexOf(','));
                value1 = value1.Remove(value1.LastIndexOf(','));
                string sql2 = string.Format("insert into {0}({1}) values({2})", name, key1, value1);
                //output:insert into detail1_table(ItemCode,Quantity,Price,Total,id,branch_id,LineNumber) values(@ItemCode,@Quantity,@Price,@Total,@id,@branch_id,@LineNumber)
    
    
            }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 8:03 AM
    Moderator
  • thank you for reply

    How to modify code above to take array of details automatically without writing on code

    meaning i need to write loop for details


    Monday, August 19, 2019 1:27 PM
  • Hi 

    Thanks for the feedback.

    If you want to take array of details automatically, you could try the following code.

         static void Main(string[] args)
            {
                string JsonData = File.ReadAllText("D:\\1.json");
                var ebj = ToExpando(JsonData);
                string key1 = string.Empty;
                string value1 = string.Empty;
                dynamic name = null;
                string sql1 = string.Empty;
                var array = (ebj as dynamic).details;
                List<string> list = new List<string>();
                foreach (dynamic item in array)
                {
                    name = item.table;
                    foreach (dynamic i in item.fields)
                    {
                        string key = i.Key;
                        key1 = key1 + key + ",";
                        value1 = value1 + "@" + key + ",";
    
                    }
                    foreach (dynamic i in item.keys)
                    {
                        string key = i.Key;
                        key1 = key1 + key + ",";
                        value1 = value1 + "@" + key + ",";
                    }
                    key1 = key1.Remove(key1.LastIndexOf(','));
                    value1 = value1.Remove(value1.LastIndexOf(','));
                    sql1 = string.Format("insert into {0}({1}) values({2})", name, key1, value1);
                    list.Add(sql1);
                    value1 = string.Empty;
                    key1 = string.Empty;
                    Console.WriteLine(sql1);
                }
    
                Console.ReadKey();
    
    
    
            }

    Result:

    The result could be stored in a list.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 3:17 AM
    Moderator