How to create function return statement insert into table values from json file ?

Question

0.00/5 (No votes)

See more:

Problem

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 problem how to make function return insert statement as bellow :

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 :

HTML

{
   "master" : {
       "table": "master_table",
       "fields": {
           "name" : "bar",
           "address" : "fleet street",
           "phone" : "555"
       },
       "keys":{
           "id" : 1,
           "branch_id" : 1 
       }
       
   }
}

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:

C#

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

Posted 12-Aug-19 17:21pm

ahmed_sa

Updated 12-Aug-19 23:51pm

DaveAuld

v3

Add a Solution

1 solution

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)

Maciej Los · Answer 1 · 2019-08-12T23:51:00

Seems, you are using Json.NET[^] library...

So, you have to do something like this:

C#

JObject jo = JObject.Parse(json);
JToken m = jo["master"];

StringBuilder sb = new StringBuilder();
string s = string.Format("INSERT INTO {0} ", m["table"]);
sb.Append(s);
sb.Append("(");
foreach(JProperty jp in m["keys"])
    sb.Append(string.Format("{0},", jp.Name));
foreach(JProperty jp in m["fields"])
    sb.Append(string.Format("{0},", jp.Name));
sb.Append(@") Values(");
foreach(JToken jt in m["keys"])
    sb.Append(string.Format("{0},", jt.First));
foreach(JToken jt in m["fields"])
    sb.Append(string.Format("'{0}',", jt.First));
sb.Append(")");
s = sb.ToString();
s= s.Replace(",)", ")");
//[s] variable stores insert command text

Result:

SQL

INSERT INTO master_table (id,branch_id,name,address,phone) Values(1,1,'bar','fleet street','555')

Note: you should rather use parameterized queries[^] instead of concatenated strings to avoid SQL Injection[^] . So, your command should take such of form:

SQL

INSERT INTO table_name (<field_list>) VALUES (<parameters_list>)

Then, you have to add parameter into ParameterCollection.
See:
SqlParameterCollection Class (System.Data.SqlClient) | Microsoft Docs[^]
OleDbParameterCollection Class (System.Data.OleDb) | Microsoft Docs[^]

[EDIT #1]
Finally, i'd suggest to create helper class:

C#

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

}

Usage:

C#

JObject jo = JObject.Parse(json);
JToken m = jo["master"];
string connectionstring = "Server=myServerAddress;Database=myDataBase;User Id=myUsernamePassword=myPassword;"; //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);
        //command is ready to use ;)
    }
}

Final note: This is not an elegant solution, but an idea to improve.