To dynamically retrieve and format the data in the JSON structure you provided from an SQL database, follow these steps:
1. Design the Database Tables
- Master Tables: Store static details like TaxSch, SupTyp, etc.
- Transaction Tables: Store dynamic details like DocDtls, SellerDtls, BuyerDtls, etc.
- ItemList Table: Store item-wise details.
2. SQL Query to Fetch Data
Use SQL queries with FOR JSON to construct JSON objects. Here’s a basic outline:
SELECT
'1.1' AS Version,
(
SELECT 'GST' AS TaxSch,
'B2B' AS SupTyp,
'N' AS RegRev,
'N' AS IgstOnIntra
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS TranDtls,
(
SELECT 'INV' AS Typ,
'23-24/DEM/154' AS No,
'12/08/2024' AS Dt
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS DocDtls,
(
SELECT '29AADCG4992P1ZP' AS Gstin,
'GSTZEN DEMO PRIVATE LIMITED' AS LglNm,
'Manyata Tech Park' AS Addr1,
'BANGALORE' AS Loc,
560077 AS Pin,
'29' AS Stcd
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS SellerDtls,
(
SELECT '06AAMCS8709B1ZA' AS Gstin,
'Quality Products Private Limited' AS LglNm,
'06' AS Pos,
'133, Mahatma Gandhi Road' AS Addr1,
'HARYANA' AS Loc,
121009 AS Pin,
'06' AS Stcd
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS BuyerDtls,
(
SELECT 'Maharashtra Storage' AS Nm,
'133, Mahatma Gandhi Road' AS Addr1,
'Bhiwandi' AS Loc,
400001 AS Pin,
'27' AS Stcd
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS DispDtls,
(
SELECT 'URP' AS Gstin,
'Quality Products Construction Site' AS LglNm,
'Anna Salai' AS Addr1,
'Chennai' AS Loc,
600001 AS Pin,
'33' AS Stcd
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS ShipDtls,
(
SELECT
ROW_NUMBER() OVER(ORDER BY ItemNo) - 1 AS ItemNo,
'1' AS SlNo,
'N' AS IsServc,
'Computer Hardware - Keyboard and Mouse' AS PrdDesc,
'320504' AS HsnCd,
25 AS Qty,
0 AS FreeQty,
'PCS' AS Unit,
200 AS UnitPrice,
5000 AS TotAmt,
0 AS Discount,
0 AS PreTaxVal,
5000 AS AssAmt,
18 AS GstRt,
900 AS IgstAmt,
0 AS CgstAmt,
0 AS SgstAmt,
0 AS CesRt,
0 AS CesAmt,
0 AS CesNonAdvlAmt,
0 AS StateCesRt,
0 AS StateCesAmt,
0 AS StateCesNonAdvlAmt,
0 AS OthChrg,
5900 AS TotItemVal
FROM ItemsTable
FOR JSON PATH
) AS ItemList,
(
SELECT 5000 AS AssVal,
0 AS CgstVal,
0 AS SgstVal,
900 AS IgstVal,
0 AS CesVal,
0 AS StCesVal,
0 AS Discount,
0 AS OthChrg,
0 AS RndOffAmt,
5900 AS TotInvVal
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS ValDtls
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
3. Execution in Your Application
- ADO.NET: Use ADO.NET to execute the query in your ASP.NET application and retrieve the JSON.
- RestSharp API: Use RestSharp or any other API framework to send this JSON data as part of an API call.
4. Testing and Deployment
- Test the JSON Output: Ensure the JSON output structure is correct and matches the expected format.
- Handle Multiple Records: If there are multiple records, ensure that your query and application logic correctly handle arrays of records.
This approach dynamically builds the JSON structure directly from your database, making it flexible for different records and fields.