How to create function return statement insert into table v

ahmed elbarbary

7y1kLearn iOS Programming

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 :

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

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty7y ago
Try this.
private void ReturnSqlQry(string jsonString)
{
    JavaScriptSerializer objJSS = new JavaScriptSerializer();
    var dDynamic = objJSS.Deserialize(jsonString);

    var tableName = dDynamic["master"]["table"];
    var fieldsName = dDynamic["master"]["fields"];
    var keysName = dDynamic["master"]["keys"];

    string fields = "", fieldValues = "";
    int fieldCnt = 0;
    foreach (var item in fieldsName)
    {
        fieldCnt++;
        if (fieldCnt == fieldsName.Count)
        {
            fields += item.Key;
            fieldValues += "@" + item.Key;
        }
        else
        {
            fields += item.Key + ",";
            fieldValues += "@" + item.Key + ",";
        }
    }
    string keys = "", keyValues = "";
    foreach (var item in keysName)
    {
        keys += item.Key + ",";
        keyValues += "@" + item.Key + ",";
    }
    string sqlQry = "INSERT INTO " + tableName + " (" + keys + fields + ") VALUES (" + keyValues + fieldValues + ")";
    SqlConnection sqlCon = new SqlConnection("Data Source=.; Initial Catalog=DemoTest; Integrated Security=SSPI");
    sqlCon.Open();
    SqlCommand sqlCmd = new SqlCommand(sqlQry, sqlCon);
    foreach (var item in keysName)
    {
        sqlCmd.Parameters.Add("@" + item.Key, item.Value);
    }
    foreach (var item in fieldsName)
    {
        sqlCmd.Parameters.Add("@" + item.Key, item.Value);
    }
    sqlCmd.ExecuteNonQuery();
}
0
Sonu Gupta7y ago
Hi,

Visit the following link -
https://stackoverflow.com/questions/9319532/return-value-from-sql-server-insert-command-using-c-sharp
0
ahmed elbarbary7y ago
thank you for reply
this is correct and what i need actually
but can we modify code as paramterized query tom prevent sql injection
can you modify function above if possible as paramterized query
0
Amit Mohanty7y ago
Hey try this
private string ReturnSqlQry(string jsonString)
{
    JavaScriptSerializer objJSS = new JavaScriptSerializer();
    var dDynamic = objJSS.Deserialize(jsonString);

    var tableName = dDynamic["master"]["table"];
    var fieldsName = dDynamic["master"]["fields"];
    var keysName = dDynamic["master"]["keys"];

    string fields = "", fieldValues = "";
    int fieldCnt = 0;
    foreach (var item in fieldsName)
    {
        fieldCnt++;
        if (fieldCnt == fieldsName.Count)
        {
            fields += item.Key;
            fieldValues += "'" + item.Value + "'";
        }
        else
        {
            fields += item.Key + ",";
            fieldValues += "'" + item.Value + "'" + ",";
        }
    }
    string keys = "", keyValues = "";
    foreach (var item in keysName)
    {
        keys += item.Key + ",";
        keyValues += item.Value + ",";
    }
    string sqlQry = "INSERT INTO " + tableName + " (" + keys + fields + ") VALUES (" + keyValues + fieldValues + ")";
    return sqlQry;
}
0