none
How to convert json to create delete statement from header table and footer? RRS feed

  • Question

  • problem

    How to convert json to create delete statement ?

    i have json string as following

    {
       "master" : {
           table: "",
           fields : {},
           keys: {}
           
       },
       "details" : [
           {
               table: "",
               fields : {},
               keys: {}
              
           }
       ]
    }


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

    here on json i need to make function csharp as class library convert json to delete query from two tables

    table header

    and

    table footer

    Result of code 
    
    delete from master_table where id=1 and branch_id=1
    
    delete from detail1_table where id=1 and branch_id=1 and Linenumber=1


    so that how to convert json string above to sql delete query ?

    but really the main goal is to create dynamic sql string query

    by depending  keys and fields on two tables header and footer meaning

    if i change data above then it will do another query with fields or keys changed

    scripts table

    CREATE TABLE [dbo].[detail1_table](
    	[id] [int] NOT NULL,
    	[branch_id] [int] NOT NULL,
    	[LineNumber] [int] NOT NULL,
    	[ItemCode] [nvarchar](50) NULL,
    	[Quantity] [int] NULL,
    	[Price] [decimal](18, 2) NULL,
    	[Total] [decimal](18, 2) NULL,
     CONSTRAINT [PK_detail1_table] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC,
    	[branch_id] ASC,
    	[LineNumber] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    /****** Object:  Table [dbo].[master_table]    Script Date: 11-08-2019 07:42:49 ص ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[master_table](
    	[id] [int] NOT NULL,
    	[branch_id] [int] NOT NULL,
    	[name] [nvarchar](50) NULL,
    	[address] [nvarchar](50) NULL,
    	[phone] [nvarchar](50) NULL,
     CONSTRAINT [PK_master_table] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC,
    	[branch_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    INSERT [dbo].[detail1_table] ([id], [branch_id], [LineNumber], [ItemCode], [Quantity], [Price], [Total]) VALUES (1, 1, 1, N'item-500', 10, CAST(50.00 AS Decimal(18, 2)), CAST(500.00 AS Decimal(18, 2)))
    INSERT [dbo].[detail1_table] ([id], [branch_id], [LineNumber], [ItemCode], [Quantity], [Price], [Total]) VALUES (1, 1, 2, N'item-500', 5, CAST(50.00 AS Decimal(18, 2)), CAST(250.00 AS Decimal(18, 2)))
    INSERT [dbo].[master_table] ([id], [branch_id], [name], [address], [phone]) VALUES (1, 1, N'michel plater', N'francw', N'1222222')
    INSERT [dbo].[master_table] ([id], [branch_id], [name], [address], [phone]) VALUES (2, 1, N'sergio ram', N'german', N'1221111')
    How to generate delete sql query to two tables ?
     




    • Edited by engahmedbarbary Sunday, August 11, 2019 6:01 AM write more details
    Sunday, August 11, 2019 5:18 AM

Answers

  • keys + fields on json file represent all fields on table

    are there are any way to loop through table and delete data dynamically based on data on json

    I need to apply that only on table master as above

    Monday, August 12, 2019 10:36 PM

All replies

  • can any one help me

    i need to create function return jobject and result of return is two statment

    Result of code 
    
    delete from master_table where id=1 and branch_id=1
    
    delete from detail1_table where id=1 and branch_id=1 and Linenumber=1

    Monday, August 12, 2019 6:58 AM
  • Hi engahmedbarbary,

    Thank you for posting here.

    For your question, you want to convert json to create delete sql statement.

    Since your json string you provided has some errors, I modified it and write a simple but successful code.

    Correct Json:

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

    C# code:

     class Program
        {
            static void Main(string[] args)
            {
                string text = File.ReadAllText("D:\\1.json");
                Example ex= JsonConvert.DeserializeObject<Example>(text);
                string table = ex.master.table;
                int id = ex.master.keys.id;
                int branch_id = ex.master.keys.branch_id;
                string sql1 = string.Format("delete from table {0} where id={1} and branch_id={2}",table,id,branch_id);
                Console.WriteLine(sql1);
    
            }
        }
        public class Example
        {
            public Master master { get; set; }
        }
    
        public class Master
            {
                public string table { get; set; }
                public Fields fields { get; set; }
                public Keys keys { get; set; }
            }
    
    
    
        public class Keys
        {
            public int id { get; set; }
            public int branch_id { get; set; }
    
        }
    
        public class Fields
        {
            public string name { get; set; }
            public string address { get; set; }
            public string phone { get; set; }
    
    
        }

    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.

    Monday, August 12, 2019 8:21 AM
    Moderator
  • thank you for reply

    this is actually what i need but this is not dynamic or flexible

    meaning fields my be will not name,address,phone

    my be another fields depend on structure of table

    fields may be birthrate or join date or no name or phone exist

    i dont make that for one table i need it flexible can done to any table .

    same thing according to keys 

    some tables my have keys serial or _id or any thing

    i need any thing treat flexible with keys .

    Monday, August 12, 2019 10:00 AM
  • in above jsonfile

    table , fields ,key ,master ,details is fixed keywords 

    but table content may be changed .

    key content may be changed .

    fields may be changed .

    Monday, August 12, 2019 1:34 PM
  • If you don't know what fields will be inside the 'table' and 'key' items, then you can't use DeserializeObject.  That requires having a fully declared .NET object to fill in.

    You may be forced to use the lower-level JsonTextReader class, which parses the JSON and returns a list of field names and field values.  It's more work, but that's what you'll need to have this level of flexibility.


    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    Monday, August 12, 2019 9:33 PM
  • keys + fields on json file represent all fields on table

    are there are any way to loop through table and delete data dynamically based on data on json

    I need to apply that only on table master as above

    Monday, August 12, 2019 10:36 PM