Remove From My Forums

How to create Json according to below Schema

Question

User1979860870 posted

 
  I have 2 tables . I want to convert data into Json format according to the below Schema. What should be the Models. Since DispDetls are blank in Database it should be Null.
I am using MVC
{

"Version": "1.1",

"TranDtls": { -

"TaxSch": "GST",

"SupTyp": "B2B",

"RegRev": "Y",

"EcmGstin": null,

"IgstOnIntra": "N"

},

"DocDtls": {

"Typ": "INV",

"No": "DOC/001",

"Dt": "18/08/2020"

},

"SellerDtls": {

"Gstin": "37ARZPT4384Q1MT",

"LglNm": "NIC company pvt ltd",

"TrdNm": "NIC Industries",

"Addr1": "5th block, kuvempu layout",

"Addr2": "kuvempu layout",

"Loc": "GANDHINAGAR",

"Pin": 518001,

"Stcd": "37",

"Ph": "9000000000",

"Em": "abc@gmail.com"

},

"BuyerDtls": {

"Gstin": "29AWGPV7107B1Z1",

"LglNm": "XYZ company pvt ltd",

"TrdNm": "XYZ Industries",

"Pos": "12",

"Addr1": "7th block, kuvempu layout",

"Addr2": "kuvempu layout",

"Loc": "GANDHINAGAR",

"Pin": 562160,

"Stcd": "29",

"Ph": "91111111111",

"Em": "xyz@yahoo.com"

},

"DispDtls": {

"Nm": "ABC company pvt ltd",

"Addr1": "7th block, kuvempu layout",

"Addr2": "kuvempu layout",

"Loc": "Banagalore",

"Pin": 562160,

"Stcd": "29"

},

"ItemList": [

{

"SlNo": "1",

"PrdDesc": "Rice",

"IsServc": "N",

"HsnCd": "1001",

"Barcde": "123456",

"Qty": 100.345,

"FreeQty": 10,

"Unit": "BAG",

"UnitPrice": 99.545,

"TotAmt": 9988.84,

"Discount": 10,

"PreTaxVal": 1,

"AssAmt": 9978.84,

"GstRt": 12.0,

"IgstAmt": 1197.46,

"CgstAmt": 0,

"SgstAmt": 0,

"CesRt": 5,

"CesAmt": 498.94,

"CesNonAdvlAmt": 10,

"StateCesRt": 12,

"StateCesAmt": 1197.46,

"StateCesNonAdvlAmt": 5,

"OthChrg": 10,

"TotItemVal": 12897.7,

"OrdLineRef": "3256",

"OrgCntry": "AG",

"PrdSlNo": "12345",

"BchDtls": {

"Nm": "123456",

"ExpDt": "01/08/2020",

"WrDt": "01/09/2020"

},

"AttribDtls": [

{

"Nm": "Rice",

"Val": "10000"

}

]

}

],

}

 --------------------------------------------------------------------------------------------
 
USE [Test]
GO
/****** Object: Table [dbo].[Master0] Script Date: 07-02-2021 19:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Master0](
[TaxSch] [varchar](10) NULL,
[SubTyp] [varchar](10) NULL,
[RegRev] [varchar](1) NULL,
[EcmGstin] [varchar](15) NULL,
[IgstOnIntra] [varchar](1) NULL,
[Typ] [varchar](3) NULL,
[No] [varchar](16) NULL,
[Dt] [varchar](10) NULL,
[GSTIN] [varchar](15) NULL,
[LglNm] [varchar](100) NULL,
[TrdNm] [varchar](100) NULL,
[Addr1] [varchar](100) NULL,
[Addr2] [varchar](100) NULL,
[Loc] [varchar](50) NULL,
[Pin] [int] NULL,
[Stcd] [varchar](2) NULL,
[Ph] [varchar](12) NULL,
[Em] [varchar](100) NULL,
[BuyerGSTIN] [varchar](15) NULL,
[BuyerLglNm] [varchar](100) NULL,
[BuyerTrdNm] [varchar](100) NULL,
[BuyerPos] [varchar](2) NULL,
[BuyerAddr1] [varchar](100) NULL,
[BuyerAddr2] [varchar](100) NULL,
[BuyerLoc] [varchar](50) NULL,
[BuyerPin] [int] NULL,
[BuyerStcd] [varchar](2) NULL,
[BuyerPhone] [varchar](12) NULL,
[BuyerEmail] [varchar](100) NULL,
[DispatchNm] [varchar](100) NULL,
[DispatchAddr1] [varchar](100) NULL,
[DispatchAddr2] [varchar](100) NULL,
[DispatchLoc] [varchar](50) NULL,
[DispatchPin] [int] NULL,
[DispatchStcd] [varchar](2) NULL,
[ShipGSTIN] [varchar](15) NULL,
[ShipLglNm] [varchar](100) NULL,
[ShipTrdNm] [varchar](100) NULL,
[ShipAddr1] [varchar](100) NULL,
[ShipAddr2] [varchar](100) NULL,
[ShipLoc] [varchar](50) NULL,
[ShipPin] [int] NULL,
[ShipStcd] [varchar](2) NULL,
[AssVal] [decimal](18, 2) NULL,
[CgstVal] [decimal](18, 2) NULL,
[IgstVal] [decimal](18, 2) NULL,
[CesVal] [decimal](18, 2) NULL,
[StCesVal] [decimal](18, 2) NULL,
[Discount] [decimal](18, 2) NULL,
[OthChg] [decimal](18, 2) NULL,
[RndOfAmount] [decimal](18, 2) NULL,
[TotalInvVal] [decimal](18, 2) NULL,
[TotalInvValFc] [decimal](18, 2) NULL,
[PayeeName] [nvarchar](100) NULL,
[PayeeAccDet] [nvarchar](18) NULL,
[PayeeMode] [nvarchar](18) NULL,
[PayeeBranch] [nvarchar](11) NULL,
[PayeePayTerm] [nvarchar](100) NULL,
[PayeePaymentInstruction] [nvarchar](100) NULL,
[PayeeCreditTfr] [nvarchar](100) NULL,
[PayeeDirectDebit] [nvarchar](100) NULL,
[PayeeCreditDays] [nvarchar](100) NULL,
[PayeePaidAmt] [decimal](18, 2) NULL,
[PayeePaymentDue] [decimal](18, 2) NULL,
[RefRemarksNote] [nvarchar](100) NULL,
[InvStDt] [nvarchar](10) NULL,
[InvEndDt] [nvarchar](10) NULL,
[InvNo] [nvarchar](16) NULL,
[InvDt] [nvarchar](10) NULL,
[OtherRefNo] [nvarchar](20) NULL,
[AdviceNo] [nvarchar](20) NULL,
[ReceiptAdvicetDt] [nvarchar](10) NULL,
[BatchRef] [nvarchar](20) NULL,
[ContractRef] [nvarchar](20) NULL,
[OtherRef] [nvarchar](20) NULL,
[ProjRef] [nvarchar](20) NULL,
[RefNo] [nvarchar](16) NULL,
[RefDate] [nvarchar](10) NULL,
[DocumentUrl] [nvarchar](100) NULL,
[Docs] [nvarchar](1000) NULL,
[Info] [nvarchar](1000) NULL,
[ShipBillNo] [nvarchar](20) NULL,
[ShipBillDate] [nvarchar](10) NULL,
[Port] [nvarchar](10) NULL,
[RefundClaim] [nvarchar](1) NULL,
[ForCurrency] [nvarchar](16) NULL,
[CountryCode] [nvarchar](2) NULL,
[ExpDuty] [int] NULL,
[TransId] [nvarchar](15) NULL,
[TransName] [nvarchar](100) NULL,
[TransMode] [nvarchar](1) NULL,
[Distance] [int] NULL,
[TransDocNo] [nvarchar](15) NULL,
[TransDocDate] [nvarchar](10) NULL,
[VehicleNo] [nvarchar](20) NULL,
[VehType] [nvarchar](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Trans0] Script Date: 07-02-2021 19:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Trans0](
[No] [nvarchar](16) NULL,
[SlNo] [varchar](6) NULL,
[ProductDesc] [varchar](100) NULL,
[IsServ] [varchar](1) NULL,
[HsnCode] [varchar](8) NULL,
[BarCode] [varchar](30) NULL,
[Qty] [decimal](18, 2) NULL,
[FreeQty] [decimal](18, 2) NULL,
[Unit] [varchar](8) NULL,
[UnitPrice] [decimal](18, 2) NULL,
[TotAmount] [decimal](18, 2) NULL,
[Discount] [decimal](18, 2) NULL,
[PreTaxVal] [decimal](18, 2) NULL,
[AssAmt] [decimal](18, 2) NULL,
[GstRate] [decimal](18, 2) NULL,
[IgstAmt] [decimal](18, 2) NULL,
[CgstAmt] [decimal](18, 2) NULL,
[SgstAmt] [decimal](18, 2) NULL,
[CessRate] [decimal](18, 2) NULL,
[CessAmount] [decimal](18, 2) NULL,
[CessNonAdvolAmt] [decimal](18, 2) NULL,
[StateCessRate] [decimal](18, 2) NULL,
[StateCessNonAdvolAmt] [decimal](18, 2) NULL,
[OtherCharges] [decimal](18, 2) NULL,
[TotItemVal] [decimal](18, 2) NULL,
[OrdLineRef] [varchar](50) NULL,
[OriginCountry] [varchar](2) NULL,
[ProductSrNo] [varchar](20) NULL,
[BatchNo] [varchar](20) NULL,
[BatchExpDate] [varchar](10) NULL,
[BatchWarrantDate] [varchar](10) NULL,
[AttributeName] [varchar](100) NULL,
[AttributeVal] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Master0] ([TaxSch], [SubTyp], [RegRev], [EcmGstin], [IgstOnIntra], [Typ], [No], [Dt], [GSTIN], [LglNm], [TrdNm], [Addr1], [Addr2], [Loc], [Pin], [Stcd], [Ph], [Em], [BuyerGSTIN], [BuyerLglNm], [BuyerTrdNm], [BuyerPos], [BuyerAddr1], [BuyerAddr2], [BuyerLoc], [BuyerPin], [BuyerStcd], [BuyerPhone], [BuyerEmail], [DispatchNm], [DispatchAddr1], [DispatchAddr2], [DispatchLoc], [DispatchPin], [DispatchStcd], [ShipGSTIN], [ShipLglNm], [ShipTrdNm], [ShipAddr1], [ShipAddr2], [ShipLoc], [ShipPin], [ShipStcd], [AssVal], [CgstVal], [IgstVal], [CesVal], [StCesVal], [Discount], [OthChg], [RndOfAmount], [TotalInvVal], [TotalInvValFc], [PayeeName], [PayeeAccDet], [PayeeMode], [PayeeBranch], [PayeePayTerm], [PayeePaymentInstruction], [PayeeCreditTfr], [PayeeDirectDebit], [PayeeCreditDays], [PayeePaidAmt], [PayeePaymentDue], [RefRemarksNote], [InvStDt], [InvEndDt], [InvNo], [InvDt], [OtherRefNo], [AdviceNo], [ReceiptAdvicetDt], [BatchRef], [ContractRef], [OtherRef], [ProjRef], [RefNo], [RefDate], [DocumentUrl], [Docs], [Info], [ShipBillNo], [ShipBillDate], [Port], [RefundClaim], [ForCurrency], [CountryCode], [ExpDuty], [TransId], [TransName], [TransMode], [Distance], [TransDocNo], [TransDocDate], [VehicleNo], [VehType]) VALUES (N'GST', N'B2B', N'N', N'', N'N', N'INV', N'INV/00001', N'2020/08/06', N'24AAAAA1116A1AA', N'Traders', N'', N'675', N'', N'BNG', 506060, N'01', N'', N'', N'24AAAAA1116A1AA', N'Traders', N'', N'14', N'879', N'', N'Delhi', 160087, N'14', N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Master0] ([TaxSch], [SubTyp], [RegRev], [EcmGstin], [IgstOnIntra], [Typ], [No], [Dt], [GSTIN], [LglNm], [TrdNm], [Addr1], [Addr2], [Loc], [Pin], [Stcd], [Ph], [Em], [BuyerGSTIN], [BuyerLglNm], [BuyerTrdNm], [BuyerPos], [BuyerAddr1], [BuyerAddr2], [BuyerLoc], [BuyerPin], [BuyerStcd], [BuyerPhone], [BuyerEmail], [DispatchNm], [DispatchAddr1], [DispatchAddr2], [DispatchLoc], [DispatchPin], [DispatchStcd], [ShipGSTIN], [ShipLglNm], [ShipTrdNm], [ShipAddr1], [ShipAddr2], [ShipLoc], [ShipPin], [ShipStcd], [AssVal], [CgstVal], [IgstVal], [CesVal], [StCesVal], [Discount], [OthChg], [RndOfAmount], [TotalInvVal], [TotalInvValFc], [PayeeName], [PayeeAccDet], [PayeeMode], [PayeeBranch], [PayeePayTerm], [PayeePaymentInstruction], [PayeeCreditTfr], [PayeeDirectDebit], [PayeeCreditDays], [PayeePaidAmt], [PayeePaymentDue], [RefRemarksNote], [InvStDt], [InvEndDt], [InvNo], [InvDt], [OtherRefNo], [AdviceNo], [ReceiptAdvicetDt], [BatchRef], [ContractRef], [OtherRef], [ProjRef], [RefNo], [RefDate], [DocumentUrl], [Docs], [Info], [ShipBillNo], [ShipBillDate], [Port], [RefundClaim], [ForCurrency], [CountryCode], [ExpDuty], [TransId], [TransName], [TransMode], [Distance], [TransDocNo], [TransDocDate], [VehicleNo], [VehType]) VALUES (N'GST', N'B2B', N'N', N'', N'N', N'INV', N'INV/00002', N'2020/09/15', N'14AAAAA1116A1AA', N'Traders', N'', N'675', N'', N'BNG', 506060, N'14', N'', N'', N'17AAAAA1116A1AA', N'Traders', N'', N'17', N'879', N'', N'XYZ', 160087, N'17', N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00001', N'1', N'Pr01', N'N', N'1001', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')
INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00002', N'1', N'Pr02', N'N', N'1002', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')
INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00002', N'1', N'Pr03', N'N', N'1003', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')

Thanks

Sunday, February 7, 2021 2:37 PM

All replies

0

Sign in to vote

User-474980206 posted

You can use vs2019 special paste of json as class or

https://json2csharp.com

Sunday, February 7, 2021 5:05 PM
0

Sign in to vote

User1979860870 posted

Hi

That's ok. I have 2 tables . One is Master & other is for Item Details. From those tables i want that Json should be created according to the above Schema

Thanks

Monday, February 8, 2021 5:41 AM
0

Sign in to vote
User1686398519 posted

Hi jagjit saini,

Do you want to convert the object to JSON?

string output = JsonConvert.SerializeObject(test);

You can click this link to see more explanation.

Best Regards,

YihuiSun
Monday, February 8, 2021 6:57 AM
0

Sign in to vote

User1979860870 posted

Hi YihuiSun

I want to know how to pass values from Database to Object & then convert to Json

Thanks

Monday, February 8, 2021 8:36 AM
0

Sign in to vote

User1979860870 posted

Hi Bruce

I want to know how to pass values from Database table to Object & then convert to Json. I have already created Objects.

Thanks

Monday, February 8, 2021 8:37 AM
0

Sign in to vote

User-474980206 posted

Use the tool of your choice to create the class objects for the json schema. Then map the database rows to the new objects. Your sql will return the tables as a product, which you will need to remap to a hierarchy. You could also use Sqlserver json support to build the json string,

Monday, February 8, 2021 3:35 PM

Asked by:

How to create Json according to below Schema

Question

All replies