How to read data from Sql

Hi Ramco,

Here is the C# code snippet that fetches SQL data matching your JSON schema, including the nested itemList array, and converts it into a structured JSON output.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text.Json;
using System.Threading.Tasks;

public class Document
{
    public string supplyType { get; set; }
    public string subSupplyType { get; set; }
    public string docType { get; set; }
    public string docNo { get; set; }
    public string docDate { get; set; }
    public string fromGstin { get; set; }
    public int fromPincode { get; set; }
    public int fromStateCode { get; set; }
    public string toGstin { get; set; }
    public int toPincode { get; set; }
    public int toStateCode { get; set; }
    public string transDistance { get; set; }
    public int actToStateCode { get; set; }
    public int actFromStateCode { get; set; }
    public decimal totInvValue { get; set; }
    public int transactionType { get; set; }
    public List itemList { get; set; }
}

public class Item
{
    public string productName { get; set; }
    public string productDesc { get; set; }
    public int hsnCode { get; set; }
    public decimal quantity { get; set; }
    public string qtyUnit { get; set; }
    public decimal taxableAmount { get; set; }
    public decimal sgstRate { get; set; }
    public decimal cgstRate { get; set; }
    public decimal igstRate { get; set; }
    public decimal cessRate { get; set; }
    public decimal cessNonadvol { get; set; }
}

class Program
{
    static async Task Main(string[] args)
    {
        var documents = new List();

        string connString = "Data Source=YourServer;Initial Catalog=YourDB;Integrated Security=True;";
        using SqlConnection conn = new SqlConnection(connString);
        await conn.OpenAsync();

        // Get distinct documents
        string docQuery = "SELECT * FROM DocumentHeader";
        using SqlCommand cmd = new SqlCommand(docQuery, conn);
        using SqlDataReader reader = await cmd.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            var doc = new Document
            {
                supplyType = reader["supplyType"].ToString(),
                subSupplyType = reader["subSupplyType"].ToString(),
                docType = reader["docType"].ToString(),
                docNo = reader["docNo"].ToString(),
                docDate = Convert.ToDateTime(reader["docDate"]).ToString("dd/MM/yyyy"),
                fromGstin = reader["fromGstin"].ToString(),
                fromPincode = Convert.ToInt32(reader["fromPincode"]),
                fromStateCode = Convert.ToInt32(reader["fromStateCode"]),
                toGstin = reader["toGstin"].ToString(),
                toPincode = Convert.ToInt32(reader["toPincode"]),
                toStateCode = Convert.ToInt32(reader["toStateCode"]),
                transDistance = reader["transDistance"].ToString(),
                actToStateCode = Convert.ToInt32(reader["actToStateCode"]),
                actFromStateCode = Convert.ToInt32(reader["actFromStateCode"]),
                totInvValue = Convert.ToDecimal(reader["totInvValue"]),
                transactionType = Convert.ToInt32(reader["transactionType"]),
                itemList = new List()
            };

            documents.Add(doc);
        }
        await reader.CloseAsync();

        // For each document, get its item list
        foreach (var doc in documents)
        {
            string itemQuery = "SELECT * FROM DocumentItems WHERE docNo = @docNo";
            using SqlCommand itemCmd = new SqlCommand(itemQuery, conn);
            itemCmd.Parameters.AddWithValue("@docNo", doc.docNo);
            using SqlDataReader itemReader = await itemCmd.ExecuteReaderAsync();

            while (await itemReader.ReadAsync())
            {
                doc.itemList.Add(new Item
                {
                    productName = itemReader["productName"].ToString(),
                    productDesc = itemReader["productDesc"].ToString(),
                    hsnCode = Convert.ToInt32(itemReader["hsnCode"]),
                    quantity = Convert.ToDecimal(itemReader["quantity"]),
                    qtyUnit = itemReader["qtyUnit"].ToString(),
                    taxableAmount = Convert.ToDecimal(itemReader["taxableAmount"]),
                    sgstRate = Convert.ToDecimal(itemReader["sgstRate"]),
                    cgstRate = Convert.ToDecimal(itemReader["cgstRate"]),
                    igstRate = Convert.ToDecimal(itemReader["igstRate"]),
                    cessRate = Convert.ToDecimal(itemReader["cessRate"]),
                    cessNonadvol = Convert.ToDecimal(itemReader["cessNonadvol"])
                });
            }
            await itemReader.CloseAsync();
        }

        // Serialize the output to JSON
        var jsonOptions = new JsonSerializerOptions { WriteIndented = true };
        string json = JsonSerializer.Serialize(documents, jsonOptions);
        Console.WriteLine(json);
    }
}

Good Luck!

If you want to view and SQL files easily, then you can ue the SysInfo SQL File Viewer Tool. It is the best and reliable method to view and SQL Files. It opens SQL file tables, classes, triggers, columns, functions, keys, indexes, etc. It supports both healthy and corrupt MDF & NDF Files. Additionally includes several features like no file size limitation, a preview option, and allows users to view deleted records. It is compatible with all the versions of windows OS.

To read data from SQL based on the provided JSON schema, you need to map each field in your schema to corresponding columns in your SQL database tables. Here's a simplified guide on how you can approach this:

1. Connect to the Database: Begin by establishing a connection to your SQL database. You can use libraries like SQLAlchemy, psycopg2, or other ORM frameworks based on your SQL database type.

2. Write SQL Query: Craft a SQL query that selects the data you need while ensuring that the columns align with your JSON schema's properties. The SELECT statement should fetch data from the tables matching the schema fields.

3. Execute Query: Once your query is ready, execute it using your database connection. You'll receive a result set containing the data in the same structure as your SQL query's SELECT statement.

4. Retrieve and Format Data: Fetch the data from the result set and organize it according to your JSON schema structure. You may need to transform the data or create nested objects/arrays as required by your schema.

5. Convert to JSON: Finally, convert the structured data into a JSON format. You can use JSON serialization libraries in your programming language to achieve this. Ensure that the final JSON output adheres to the schema's constraints.

Here's a brief Python example using Psycopg2 library for PostgreSQL:

import psycopg2
import json

# Establish a connection to your PostgreSQL database
conn = psycopg2.connect("dbname=test user=postgres password=postgres")

# Open a cursor to perform database operations
cur = conn.cursor()

# Write your SQL query based on the provided schema
cur.execute("SELECT supplyType, subSupplyType, docType, ... FROM your_table")

# Fetch all rows from the result set
rows = cur.fetchall()

# Create a list to store the structured data
data_list = []

for row in rows:
    data_dict = {
        "supplyType": row[0],
        "subSupplyType": row[1],
        "docType": row[2],
        # Add more fields as per the schema
    }
    data_list.append(data_dict)

# Convert the data_list to JSON
json_data = json.dumps(data_list, indent=2)

# Print or save the JSON data as needed
print(json_data)

# Close communication with the database
cur.close()
conn.close()

By following these steps and customizing the code to fit your specific SQL database and programming language, you can effectively read data from SQL and format it according to the provided JSON schema.