locked
How to create Json according to below Schema RRS feed

  • Question

  • User1979860870 posted

    Hi

     
      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

  • User-474980206 posted

    You can use vs2019 special paste of json as class  or 

      https://json2csharp.com

    Sunday, February 7, 2021 5:05 PM
  • 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
  • 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
  • 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
  • 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
  • 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