none
How to make function return sqlparamters and full insert statement with sqlparamters ? RRS feed

  • Question

  • Problem

    How to make function return sqlparamters and full insert statement with sqlparamters ?

    meaning i need to return two things 

    full statement insert

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

    and sql paramters

    @id, @branch_id, @name, @address, @phone
    static void Main(string[] args)
            {
                
    
                
                string   JsonData = File.ReadAllText("D:\\1.json");
                JObject jo = JObject.Parse(JsonData);
                JToken m = jo["master"];
                string connectionstring = "Server=AHMEDSALAH-PC\\SQL2014;Database=Atum;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);
                      
                    }
                }
    

    command return full statement 

    lsp return SQL parameters

    how to return both this is actually my question

    i need code under main function put in another function return two things above

    full statement insert and SQL parameters .

    so that how to write function using code under main and return two thing

    1- full statement insert
    
    INSERT INTO master_table(id, branch_id, name, address, phone) VALUES(@id, @branch_id, @name, @address, @phone);
    2- and sql paramters
    
    @id, @branch_id, @name, @address, @phone

    details of code above

    using Newtonsoft.Json.Linq;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    
    namespace ReadJsonApp
    {
        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;
            }
    
        }
    
    }
    
    code work perfect but remaining to write code under main to return two things above

    Wednesday, August 14, 2019 2:28 PM

Answers

  • Hi engahmedbarbary,

    Thank you for posting here.

    It seems that I have answered your question in this thread, you could have a look.

    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 1:29 AM
    Moderator

All replies

  • When you need to return several things from one method, there are two ways to do it. One is to use out parameters. Another is to return an object of a class that encapsulates all the information that you want to return. In your particular case, you don't even need to define the class, since it already exists: It is called SqlCommand. Just code your procedure to return a SqlCommand, and put the insert statement in the CommandText property and the parameters in the Parameters collection.
    Wednesday, August 14, 2019 3:46 PM
    Moderator
  • thank you for reply

    i make as you told me 

    but i get error out paramter must be assigned before control leave current method ;

    public static string GetInsertStatmentText(string JsonData,out  sqp)
            {
                string Insert = "";
                //string JsonData = File.ReadAllText("D:\\1.json");
                JObject jo = JObject.Parse(JsonData);
                JToken m = jo["master"];
                string connectionstring = "Server=AHMEDSALAH-PC\\SQL2014;Database=Atum;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;
    
            }

    Wednesday, August 14, 2019 6:04 PM
  • can you please show me how to solve error of out parameter above
    Wednesday, August 14, 2019 7:44 PM
  • 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)



    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:32 PM
  • Hi engahmedbarbary,

    Thank you for posting here.

    It seems that I have answered your question in this thread, you could have a look.

    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 1:29 AM
    Moderator