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

Question

1.00/5 (1 vote)

See more:

problem

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

{
   "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)

not generated

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);
        }

Posted 14-Aug-19 12:15pm

ahmed_sa

Updated 15-Aug-19 7:59am

v3

Add a Solution

Comments

Richard Deeming 15-Aug-19 13:41pm

There is absolutely nothing in your code which even attempts to look at the details array from the JSON file. You build a command for the master object, and then stop.

Why would you expect that code to generate insert commands for the records in the details array?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-08-14T12:57:00

Best thing for you to do is going to rethink the architecture of this.

If this was my project, I would have:
1_. Multiple classes; each one mirroring a database table
1a. Each of these classes would have it's own respective CRUD methods

2. Use a standard JSON library (such as Newtonsoft) to serialize and deserialize JSON to the proper classes

3. Create a static class for database access.

Your sample code would then just take the received JSON, deserialize it to respective classes, and then call the respective classes Create routine.

Update 1: adding a rough class to match the "MasterTable"

C#

public class Master {
    public int id { get; set; }
    public int branch_id { get; set; }
    public string name { get; set; }
    public string address { get; set; }
    public string phone { get; set; }

    public bool Save(Master mt) {
        bool Success = false;
        string connection = "your connection string retrieval method";
        string query = "INSERT master_table (id, branch_id, name, address, phone) VALUES (@id, @branch_id, @name, @address, @phone)";

        using (SqlConnection conn = new SqlConnection(connection)) {
            using (SqlCommand cmd = new SqlCommand(query, conn)) {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@id", mt.id);
                cmd.Parameters.AddWithValue("@branch_id", mt.branch_id);
                cmd.Parameters.AddWithValue("@name", mt.name);
                cmd.Parameters.AddWithValue("@address", mt.address);
                cmd.Parameters.AddWithValue("@phone", mt.phone);

                try {
                    conn.Open();
                    Success = (cmd.ExecuteNonQuery() == 1);
                }
                catch (Exception ex) { /* error handling routine */ }

                finally { conn.Close(); }
            }
        }
        return Success;
    }
}

And to populate this, you would simply de-serialize your JSON into the object. This is using the System.Web.Script.Serialization class and it is not for your particular JSON string.

C#

JavaScriptSerializer jss = new JavaScriptSerializer();
Master master = jss.Deserialize<Master>(JsonString);

And after that you could call the save method
bool saved = master.Save(master);

Patrice T · Answer 2 · 2019-08-14T14:22:00

C#

return string.Format("INSERT INTO {0}({1}) VALUES({2});",
    mastertoken["table"],
    GetFieldParameterNames(mastertoken),
    GetFieldParameterNames(mastertoken, false));

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Richard Deeming · Answer 3 · 2019-08-15T08:00:00

Your GetInsertStatmentText method is useless. You can't execute the string that's returned, because you need the parameters as well.

Create a method to generate and return a SqlCommand object to insert a single item from your JSON file:

C#

public static SqlCommand CreateInsertCommand(SqlTransaction transaction, JToken token)
{
    var result = new SqlCommand();
    result.Connection = transaction.Connection;
    result.Transaction = transaction;
    
    result.CommandText = GetInsertStatement(token);
    result.CommandType = CommandType.Text;
    
    foreach (SqlParameter p in GetSqlParams(token))
    {
        result.Parameters.Add(p);
    }
    
    return result;
}

Then generate and execute the insert commands for the tokens you want to process. You'll want to do this within a transaction - either all tokens are inserted, or none of them are.

C#

static void InsertDataFromJson(JObject jsonData)
{
    using (SqlConnection connection = new SqlConnection("..."))
    {
        connection.Open();
        
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            using (SqlCommand command = CreateInsertCommand(transaction, jsonData["master"]))
            {
                command.ExecuteNonQuery();
            }
            
            JArray detailsList = (JArray)jsonData["details"];
            foreach (JToken details in detailsList)
            {
                using (SqlCommand command = CreateInsertCommand(transaction, details))
                {
                    command.ExecuteNonQuery();
                }
            }
            
            transaction.Commit();
        }
    }
}

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

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0