根据Json将数据保存到Sql中

Question

I have json i need to insert data into sql server according to json using c# Code thorugh ado.net or something....
the sample json is :
{
"barcode": "Hasim Marfatiya",
"checkbox": true,
"date": "2022-02-01",
"datetime": "2022-02-01 11:58",
"email": "[e-mail]",
"lookup1": "Option 1",
"lookup": [
"Option 1",
"Option 2",
"Option 3",
"Option 4"
],
"number": 100,
"phone": "[phone]",
"pin": {
},
"radio": "option1",
"signature": "gridfs://registrationFiles/a2dd2bad-ca25-4909-b8b8-88160c004fbb",
"slider": 5,
"text": "Simple Text",
"textArea": "Simple Text Area",
"time": "12:18",
"location": "science city road, Sola, - 380060, Gujarat, India",
"file": "gridfs://registrationFiles/f5c5eb64-a4ac-4217-be72-aa02d8fcfe47",
"search": {
"id": "1",
"email": "[e-mail]",
"identificador": "11111112A",
"nomICognoms": "Valeriano Castaño",
"pais": "ES",
"contracte": "SDN123456H"
},
"timeDifference": 0,
"subform": [
{
"text": "Hasim",
"lookup": [
"Option 1",
"Option 2",
"Option 3",
"Option 4"
],
"searchV1": {
"id": "1",
"email": "[e-mail]",
"identificador": "11111111A",
"nomICognoms": "Valeriano Castaño",
"pais": "ES",
"servei": "SECAO"
},
"subformV2": [
{
"text": "Maharshi",
"lookup": [
"Option 2",
"Option 1"
],
"searchV2": {
"id": "2",
"email": "[e-mail]",
"identificador": "99999018D",
"nomICognoms": "Manuel Cantón Infante",
"pais": "ES",
"servei": "SECAP"
}
},
{
"text": "Vasudev",
"lookup": [
"Option 1",
"Option 2"
],
"searchV2": {
"id": "3",
"email": "[e-mail]",
"identificador": "46235239H",
"nomICognoms": "Roman de Blas",
"pais": "ES",
"servei": "SECAP"
}
}
]
},
{
"text": "Ravi",
"lookup": [
"Option 1",
"Option 2",
"Option 3",
"Option 4"
],
"searchV1": {
"id": "2",
"email": "[e-mail]",
"identificador": "99999018D",
"nomICognoms": "Manuel Cantón Infante",
"pais": "ES",
"servei": "SECAP"
},
"subformV2": [
{
"text": "Nimesh Patel",
"lookup": [
"Option 1",
"Option 2"
],
"searchV2": {
"id": "4",
"email": "[e-mail]",
"identificador": "99999999R",
"nomICognoms": "AAA Engineer",
"pais": "ES ",
"servei": "AAA"
}
}
]
}
]
}

所以我需要按照上面的json动态创建表结构，并将数据插入到表中以及中

例如：
表结构应为：

Table 1 => Widget_Test_Form => ParentTable
{
"RowId": "c7b079b9-561a-4292-88b0-5e66a1bc670b", //auto genrated Guid
"Barcode": "Hasim Marfatiya",
"Checkbox": true,
"Date": "2022-02-01",
"Datetime": "2022-02-01 11:58",
"Email": "[e-mail]",
"Lookup1": "Option 1",
"Lookup": "[rn "Option 1",rn "Option 2",rn "Option 3",rn "Option 4"rn]",
"Number": "100",
"Phone": "[phone]",
"Pin": "{rn "pins": []rn}",
"Radio": "option1",
"RDW": " ",
"Signature": "gridfs://registrationFiles/a2dd2bad-ca25-4909-b8b8-88160c004fbb",
"Slider": "5",
"Text": "Simple Text",
"TextArea": "Simple Text Area",
"Time": "12:18",
"Calculation": " ",
"Location": "science city road, Sola, - 380060, Gujarat, India",
"File": "gridfs://registrationFiles/f5c5eb64-a4ac-4217-be72-aa02d8fcfe47",
"TimeDifference": "0"
}
Table 2 => Widget_Test_Form_Search => SubTable of Widget_TestForm
{
"RowId": "ffe5e58a-5f49-4bca-898b-3aabc1c4d555",
"Widget_Test_FormRawId": "c7b079b9-561a-4292-88b0-5e66a1bc670b", //foriegn Key of (Widget_Test_Form)
"Email": "[e-mail]",
"Identificador": "11111112A",
"NomICognoms": "Valeriano Castaño",
"Pais": "ES",
"Contracte": "SDN123456H"
}

Table 3 => Widget_Test_Form_Subform => SubTable of Widget_TestForm
[
{
"RowId": "0da42b02-7f7e-4c01-b87d-de926787cb48",
"Widget_Test_FormRawId": "c7b079b9-561a-4292-88b0-5e66a1bc670b", //foriegn Key of (Widget_Test_Form)
"Text": "Hasim",
"Lookup": "Option 1,Option 2,Option 3,Option 4"
},
{
"RowId": "bfcd7cb6-65e1-4df8-9914-5635b6400ba2",
"Widget_Test_FormRawId": "c7b079b9-561a-4292-88b0-5e66a1bc670b", //foriegn Key of (Widget_Test_Form)
"Text": "Ravi",
"Lookup": "Option 1,Option 2,Option 3,Option 4"
}
]
Table 4 => Widget_Test_Form_Subform_SearchV1 => SubTable of Widget_Test_Form_Subform
[
{
"RowId": "e5032fdc-bc20-4f02-a523-22ca17d36baf",
"Widget_Test_Form_SubformRawId": "bfcd7cb6-65e1-4df8-9914-5635b6400ba2", //foriegn Key of (Widget_Test_Form_Subform)
"Email": "[e-mail]",
"Identificador": "99999018D",
"NomICognoms": "Manuel Cantón Infante",
"Pais": "ES",
"Servei": "SECAP"
},
{
"RowId": "1f17cb6a-6725-424e-984d-afda92681b9d",
"Widget_Test_Form_SubformRawId": "0da42b02-7f7e-4c01-b87d-de926787cb48", //foriegn Key of (Widget_Test_Form_Subform)
"Email": "[e-mail]",
"Identificador": "11111111A",
"NomICognoms": "Valeriano Castaño",
"Pais": "ES",
"Servei": "SECAO"
}
]
Table 5 => Widget_Test_Form_Subform_SubformV2 => SubTable of Widget_Test_Form_Subform
[
{
"RowId": "94565700-c4bf-4d9d-adf3-1a0409f213f4",
"Widget_Test_Form_SubformRawId": "0da42b02-7f7e-4c01-b87d-de926787cb48",
"Text": "Maharshi",
"Lookup": "Option 2,Option 1"
},
{
"RowId": "b972f55f-aac4-4b6e-8ade-634aad52a592",
"Widget_Test_Form_SubformRawId": "bfcd7cb6-65e1-4df8-9914-5635b6400ba2",
"Text": "Vasudev",
"Lookup": "Option 1,Option 2"
},
{
"RowId": "4f591f2d-4f94-4557-a8d7-738fda8868a1",
"Widget_Test_Form_SubformRawId": "bfcd7cb6-65e1-4df8-9914-5635b6400ba2",
"Text": "Nimesh Patel",
"Lookup": "Option 1,Option 2"
}
]
Table 6 => Widget_Test_Form_Subform_SubformV2_SearchV2 => sub table of Widget_Test_Form_Subform_SubformV2
[
{
"RowId": "2573fac9-2d7e-432c-a398-baa871025b13",
"Widget_Test_Form_Subform_SubformV2RawId": "b972f55f-aac4-4b6e-8ade-634aad52a592", //foriegn key of (Widget_Test_Form_Subform_SubformV2)
"Email": "[e-mail]",
"Identificador": "46235239H",
"NomICognoms": "Roman de Blas",
"Pais": "ES",
"Servei": "SECAP"
},
{
"RowId": "3d5cfd5a-5601-4cc4-9dc3-0dc5e79a9478",
"Widget_Test_Form_Subform_SubformV2RawId": "94565700-c4bf-4d9d-adf3-1a0409f213f4", //foriegn key of (Widget_Test_Form_Subform_SubformV2)
"Email": "[e-mail]",
"Identificador": "99999018D",
"NomICognoms": "Manuel Cantón Infante",
"Pais": "ES",
"Servei": "SECAP"
},
{
"RowId": "c2fb61bf-b78a-43ea-8168-a0091556dabd",
"Widget_Test_Form_Subform_SubformV2RawId": "4f591f2d-4f94-4557-a8d7-738fda8868a1", //foriegn key of (Widget_Test_Form_Subform_SubformV2)
"Email": "[e-mail]",
"Identificador": "99999999R",`enter code here`
"NomICognoms": "AAA Engineer",
"Pais": "ES ",
"Servei": "AAA"
}
]

1. 我需要按照json形式在Sql中动态地制作表
2. 项目必须作为数据输入到这些表中
3. 必须自动分配Foraign密钥

Answer 1

您可以使用OPENJSON提取Json字符串
例如

声明@txt_Tar为nvarchar(max(set@txt_Tar="｛"id"：1，"text"："test"｝，｛"id&"：2，"text&"："test2"｝">

set@txt_Tar='['+@txt_Tar+']'插入您的表格(第1栏，第2栏(选择a.id，a.[text]T1来自
OPENJSON(@txt_Tar(
WITH(id int，[text]nvarchar(max((