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

[engahmedbarbary]

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 ?

Continue reading...