Return nested JSON data from Generic Handler in ASP.Net using C# and VB.Net

Amitabha

SOLVED CLOSED ACTIVE ANSWERED
User: Amitabha
Posted: on Dec 20, 2021 02:53 AM
Forum: Data Controls
Answer: 1
Views: 2767
Sample Code: Download

Dear Sir

How can i create in my page

"statuscode": "200",
"message": "success",
"data": [ ],
"count": 1

{
    "statuscode": "200",
    "message": "success",
    "data": [
        [
            {
                "Id": 82514,
                "FromBranchCode": "BLR",
                "ChallanNo": "1058395",
                "Lrdate": "/Date(1627583400000)/",
                "RoyaltyPassNo": null,
                "VehicleNo": "",
                "TruckNo": "MH04KF6442",
                "Total": 3090.00,
                "ToBranchCode": "VPI",
                "ToBranchName": "VAPI",
                "Station": "SARIGAM",
                "Package": 8,
                "Freight": 2112.00,
                "CompanyName": "",
                "CustFullName": "",
                "BranchName": "BANGALORE",
                "ConsignorName": "VARNA COATS PRIVATE LIMITED-BLR",
                "ConsigneeName": "AHURA MAZDA MFG COMPANY PVT LTD-VPI",
                "RecieveDate": "05/08/2021",
                "DeliveryDate": "",
                "AuditedDate": "06/08/2021",
                "Status": "Out For Delivery",
                "AdminName": "VAPI OFFICE ",
                "GrossWeight": 352.000,
                "CNType": "PAID",
                "GodownName": "",
                "PODFileName": "202108061704071058395.pdf",
                "UserType": "1",
                "DelPersonName": "",
                "MrNo": "",
                "IsClosingEntry": 0,
                "BookingTerms": "",
                "CrossingVehicle": "",
                "PartyInvoiceNo": "12476-21,",
                "ChallanEntries": [
                    {
                        "BranchCode": "BLR",
                        "ChallanNo": "598",
                        "BranchName": "NEELMANGLA",
                        "ChallaDate": "/Date(1627669800000)/",
                        "Station": "BHIWANDI",
                        "ChlType": "0",
                        "Rate": 0.00,
                        "TotalWeight": 7002.000,
                        "VehNo": "GJ01FT8007",
                        "Frieght": 0.00,
                        "BrokerName": "INTER INDIA ROADWAYS PVT LTD",
                        "Total": 0.00,
                        "Balance": 0,
                        "TransitDate": "/Date(1627669800000)/",
                        "TotalPkgs": 147,
                        "CarrierType": "",
                        "ReceiptDate": "/Date(1628068800000)/",
                        "UnloadingDate": "/Date(1628068800000)/",
                        "Chl_DriverName": "GYANRAKASH",
                        "DriverMobileNo": "",
                        "Chl_Id": 50743
                    },
                    {
                        "BranchCode": "MUM",
                        "ChallanNo": "1465",
                        "BranchName": "MUMBAI",
                        "ChallaDate": "/Date(1628015400000)/",
                        "Station": "VAPI",
                        "ChlType": "0",
                        "Rate": 0.00,
                        "TotalWeight": 352.000,
                        "VehNo": "MH04KF6442",
                        "Frieght": 0.00,
                        "BrokerName": "EDISAFE LOGISTICS PVT LTD",
                        "Total": 0.00,
                        "Balance": 0,
                        "TransitDate": "/Date(1628015400000)/",
                        "TotalPkgs": 8,
                        "CarrierType": "",
                        "ReceiptDate": "/Date(1628141760000)/",
                        "UnloadingDate": "/Date(1628141760000)/",
                        "Chl_DriverName": "RAHUL",
                        "DriverMobileNo": "",
                        "Chl_Id": 51061
                    }
                ],
                "MachineQty": 0.00,
                "StationAddress": "\"Shop No.29 Varun Complex Opp.Reliance Fresh,Nr Industrial Boiler Gidc Vapi-396195       \r\n\r\n\r\n\r\n",
                "FromStation": "PEENYA",
                "PrivateMarkSingle": "",
                "LRCarrierType": "",
                "TrackingDetail": null,
                "Dscc_ApprovalByText": "",
                "ModeOfTransportation": "",
                "DriverDeliveryEntry": [
                    {
                        "Dri_BranchCode": "VPI",
                        "Dri_SrNo": "298",
                        "Dri_BranchName": "VAPI",
                        "Dri_Date": "/Date(1628101800000)/",
                        "Dri_Station": "silvassa , sarigam",
                        "Dri_CarrType": "0",
                        "Dri_Rate": 1600.00,
                        "Dri_TotalWeight": 1002.00,
                        "Dri_VehNo": "GJ15AT5484",
                        "Dri_Frieght": 1600.00,
                        "Dri_OwnerName": "ISLAM MOHAMMAD",
                        "Dri_Total": 1600,
                        "Dri_Balance": 1584.00,
                        "Dri_TransitDate": "/Date(1628101800000)/",
                        "Dri_TotalPkgs": 13,
                        "Dri_CarrierType": ""
                    }
                ]
            }
        ]
    ],
    "count": 1
}

Page Coding

<%@ WebHandler Language="C#" Class="DWBTracking" %>

using System;
using System.Web;
using System.Web.Services;
using Oracle.DataAccess.Client;
using System.IO;
using System.Data;
using Newtonsoft.Json;

public class DWBTracking : IHttpHandler
{
    operation oper = new operation();
    OracleDataReader dr;
    DataSet ds;
    string str = "";
    string data;
    public void ProcessRequest(HttpContext context)
    {

        string LrNumber = context.Request.QueryString["LrNumber"];
        string json = this.GetCustomersJSON(LrNumber);
        if (data != null)
        {
            context.Response.ContentType = "text/json";
            context.Response.Write(json);

        }
        else
        {
            context.Response.TrySkipIisCustomErrors = true;
        }
    }

    private string GetCustomersJSON(string LrNumber)
    {
        try
        {
            str = "SELECT DISTINCT JSON_OBJECT " +
            "('BkgBranch' VALUE FROM_BRANCH_NAME ,'DWBNo' VALUE CNS_NO,'DWBDt' VALUE TO_CHAR(CNS_DATE,'dd-Mon-yyyy'),'ActWt' VALUE CNS.ACTUAL_WEIGHT,'ChgWt' VALUE CNS.CHARGED_WEIGHT, " +
            "'TotPkgs' VALUE TOT_PKGS,'DlyBrCd' VALUE BR.BRANCH_NAME,'GoodsDesc' VALUE GOODS_DESC, " +
            "'DoorDly' VALUE CASE WHEN DOOR_DLY='Y' THEN 'Yes' ELSE 'No' END,'CCAtch' VALUE CC_ATTACH,'DoorDlyNm' VALUE DOOR_DLY_TOWN_NAME, " +
            "'CnsrName' VALUE CONSIGNOR_NAME,'CnseName' VALUE CNEE_NAME,'Segment' VALUE " +
            "CASE WHEN CFM_FLAG='F' THEN 'FTL' WHEN CFM_FLAG='L' THEN 'LTL' WHEN CFM_FLAG='S' THEN 'Sundry' END ,'Packing' VALUE PACKING_METHOD,'BkgFrt' VALUE GRAND_TOTAL,'DlyPin' VALUE DLY_PIN_CODE, " +
            "'Status' VALUE CASE WHEN MR.POD_IMAGE_NAME IS NOT NULL THEN 'Delivered' WHEN DELIVERY_FLG='X' THEN 'Out for Delivery' ELSE 'In Transit' END ,'POD' VALUE MR.POD_IMAGE_DIR||MR.POD_IMAGE_NAME, " +
            "'ChallanEntries' VALUE (SELECT JSON_ARRAYAGG(JSON_OBJECT('ClnBrCd' VALUE BF.BRANCH_NAME,'ClnNo' VALUE CLN_CHALLAN_NO,'ClnDt' value TO_CHAR(CLNH.CHALLAN_DATE,'dd-Mon-yyyy'), 'ClnBrMust' value CF.BRANCH_NAME, 'clnvhl' VALUE LORRY_REGIS_NO , " +
            "'Arvd' VALUE TO_CHAR(ULN.ARRIVAL_DATE,'dd-Mon-yyyy') )ORDER BY TO_DATE(CLNH.CHALLAN_DATE,'DD/MM/RRRR') ASC ) " +
            "AS CLM FROM CT_CLNDTL CLND,CT_CLN CLNH,CT_CNS_PRINT CNS,CT_UNLDTL ULN,CM_BRANCH BF,CM_BRANCH CF WHERE " +
            "CLND.CLN_DIV_CODE=CLNH.DIV_CODE AND CLND.CLN_CHALLAN_NO=CLNH.CHALLAN_NO AND CLND.CLN_CHALLAN_DATE=CLNH.CHALLAN_DATE AND CLND.CLN_BRANCH_BRANCH_CODE=CLNH.BRANCH_BRANCH_CODE " +
            "AND CLND.CNS_BRANCH_BRANCH_CODE=CNS.BRANCH_BRANCH_CODE AND CLND.CNS_CNS_NO=CNS.CNS_NO AND CLND.CNS_CNS_DATE=CNS.CNS_DATE " +
            "AND CLNH.CHALLAN_NO=ULN.CLN_NO(+) AND CLNH.CHALLAN_DATE=ULN.CLN_DATE(+) AND CNS.CNS_NO=ULN.CNS_CNS_NO(+) AND CNS.BRANCH_BRANCH_CODE=ULN.CNS_BRANCH_BRANCH_CODE(+) AND CNS.CNS_DATE=ULN.CNS_CNS_DATE(+) " +
            "AND CLNH.BRANCH_BRANCH_CODE=BF.BRANCH_CODE AND CLNH.BRANCH_BRANCH_CODE_MUST=CF.BRANCH_CODE AND CLND.CNS_CNS_NO='" + LrNumber.ToUpper().Trim() + "' ))as data " +
            "FROM CT_CNS_PRINT CNS,CM_BRANCH BR,CT_LCLDTL LCL,CT_MR_POD MR WHERE CNS.BRANCH_BRANCH_CODE_MUST=BR.BRANCH_CODE AND CNS.BRANCH_BRANCH_CODE=LCL.CNS_BRANCH_BRANCH_CODE(+) " +
            "AND CNS.CNS_NO=LCL.CNS_CNS_NO(+) AND CNS.CNS_DATE=LCL.CNS_CNS_DATE(+) AND CNS.BRANCH_BRANCH_CODE=MR.CNS_BRANCH_BRANCH_CODE(+) " +
            "AND CNS.CNS_NO=MR.CNS_CNS_NO(+) AND CNS.CNS_DATE=MR.CNS_CNS_DATE(+) AND CNS_NO='" + LrNumber.ToUpper().Trim() + "' ";
            ds = oper.DTSET(str);
            if (ds.Tables[0].Rows.Count > 0)
            {
                data = ds.Tables[0].DefaultView[0].Row["DATA"].ToString();

            }
            else
            {
                data = "No Data available ";
            }
        }
        catch (Exception ex)
        {
            string s = ex.Message;
            s = s.Replace("\"", "");
            s = s.Replace("'", "");
            s = s.Replace("\r", "\\r");
            s = s.Replace("\n", "\\n");
            data = s.ToString();
        }

        return data;
    }


    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Dec 20, 2021 06:02 AM
Modified: on Dec 26, 2021 11:15 PM
Report

Hi Amitabha,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:Button Text="Generate JSON" runat="server" OnClick="GenerateJSON" />
<hr />
<asp:Label ID="lblJson" runat="server" />

Handler

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Web;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

public class Handler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        List<Customer> customers = new List<Customer>();

        DataTable dt = GetData("SELECT Top 3 CustomerID,ContactName,Address From Customers");
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            Customer customer = new Customer
            {
                CustomerID = Convert.ToString(dt.Rows[i]["CustomerId"]),
                ContactName = Convert.ToString(dt.Rows[i]["ContactName"]),
                Address = Convert.ToString(dt.Rows[i]["Address"]),
                Orders = GetOrders(Convert.ToString(dt.Rows[i]["CustomerId"]))
            };
            customers.Add(customer);
        }


        Data data = new Data();
        data.message = "success";
        data.statuscode = "200";
        data.data = customers;
        data.count = customers.Count;

        var json = new JavaScriptSerializer().Serialize(data);

        context.Response.ContentType = "text/plain";
        context.Response.Write(json);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    public List<Order> GetOrders(string customerId)
    {
        List<Order> orders = new List<Order>();
        DataTable dt = GetData(string.Format("SELECT Top 2 OrderID,CustomerID,ShipCity,Freight,OrderDate FROM Orders Where CustomerID ='{0}'", customerId));
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            orders.Add(new Order
            {
                OrderID = Convert.ToInt32(dt.Rows[i]["OrderID"]),
                ShipCity = Convert.ToString(dt.Rows[i]["ShipCity"]),
                Freight = Convert.ToDecimal(dt.Rows[i]["Freight"]),
                OrderDate = Convert.ToDateTime(dt.Rows[i]["OrderDate"]).ToShortDateString()
            });
        }
        return orders;
    }

    private DataTable GetData(string query)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        SqlCommand cmd = new SqlCommand(query);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);

                    return dt;
                }
            }
        }
    }

    public class Data
    {
        public string statuscode { get; set; }
        public string message { get; set; }
        public List<Customer> data { get; set; }
        public int count { get; set; }
    }

    public class Customer
    {
        public string CustomerID { get; set; }
        public string ContactName { get; set; }
        public string Address { get; set; }
        public List<Order> Orders { get; set; }
    }

    public class Order
    {
        public int OrderID { get; set; }
        public string ShipCity { get; set; }
        public decimal Freight { get; set; }
        public string OrderDate { get; set; }
    }
}

VB.Net

<%@ WebHandler Language="VB" Class="Handler" %>

Imports System
Imports System.Web
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization

Public Class Handler : Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim customers As List(Of Customer) = New List(Of Customer)()
        Dim dt As DataTable = GetData("SELECT Top 3 CustomerID,ContactName,Address From Customers")
        For i As Integer = 0 To dt.Rows.Count - 1 Step 1
            Dim customer As Customer = New Customer With {
                .CustomerID = Convert.ToString(dt.Rows(i)("CustomerId")),
                .ContactName = Convert.ToString(dt.Rows(i)("ContactName")),
                .Address = Convert.ToString(dt.Rows(i)("Address")),
                .Orders = GetOrders(Convert.ToString(dt.Rows(i)("CustomerId")))
            }
            customers.Add(customer)
        Next

        Dim data As Data = New Data()
        data.message = "success"
        data.statuscode = "200"
        data.data = customers
        data.count = customers.Count
        Dim json = New JavaScriptSerializer().Serialize(data)
        context.Response.ContentType = "text/plain"
        context.Response.Write(json)
    End Sub

    Public Function GetOrders(ByVal customerId As String) As List(Of Order)
        Dim orders As List(Of Order) = New List(Of Order)()
        Dim dt As DataTable = GetData(String.Format("SELECT Top 2 OrderID,CustomerID,ShipCity,Freight,OrderDate FROM Orders Where CustomerID ='{0}'", customerId))
        For i As Integer = 0 To dt.Rows.Count - 1
            orders.Add(New Order With {
                .OrderID = Convert.ToInt32(dt.Rows(i)("OrderID")),
                .ShipCity = Convert.ToString(dt.Rows(i)("ShipCity")),
                .Freight = Convert.ToDecimal(dt.Rows(i)("Freight")),
                .OrderDate = Convert.ToDateTime(dt.Rows(i)("OrderDate")).ToShortDateString()
            })
        Next

        Return orders
    End Function

    Private Function GetData(ByVal query As String) As DataTable
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim cmd As SqlCommand = New SqlCommand(query)
        Using con As SqlConnection = New SqlConnection(conString)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    Public Class Data
        Public Property statuscode As String
        Public Property message As String
        Public Property data As List(Of Customer)
        Public Property count As Integer
    End Class

    Public Class Customer
        Public Property CustomerID As String
        Public Property ContactName As String
        Public Property Address As String
        Public Property Orders As List(Of Order)
    End Class

    Public Class Order
        Public Property OrderID As Integer
        Public Property ShipCity As String
        Public Property Freight As Decimal
        Public Property OrderDate As String
    End Class

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
End Class

Code

protected void GenerateJSON(object sender, EventArgs e)
{
    string handlerUrl = "http://localhost:60628/Handler.ashx";
    lblJson.Text = new System.Net.WebClient().DownloadString(handlerUrl);
}

VB.Net

Protected Sub GenerateJSON(ByVal sender As Object, ByVal e As EventArgs)
    Dim handlerUrl As String = "http://localhost:60628/Handler.ashx"
    lblJson.Text = New Net.WebClient().DownloadString(handlerUrl)
End Sub

Generated JSON

{
  "statuscode": "200",
  "message": "success",
  "data": [
    {
      "CustomerID": "ALFKI",
      "ContactName": "Maria Anders",
      "Address": "Obere Str. 57",
      "Orders": [
        {
          "OrderID": 10643,
          "ShipCity": "Berlin",
          "Freight": 29.4600,
          "OrderDate": "13-10-1997"
        },
        {
          "OrderID": 10692,
          "ShipCity": "Berlin",
          "Freight": 61.0200,
          "OrderDate": "13-10-1997"
        }
      ]
    },
    {
      "CustomerID": "ANATR",
      "ContactName": "Ana Trujillo ",
      "Address": "Avda. de la ConstituciÃ³n 2222",
      "Orders": [
        {
          "OrderID": 10308,
          "ShipCity": "MÃ©xico D.F.",
          "Freight": 1.6100,
          "OrderDate": "28-11-1997"
        },
        {
          "OrderID": 10625,
          "ShipCity": "MÃ©xico D.F.",
          "Freight": 43.9000,
          "OrderDate": "28-11-1997"
        }
      ]
    },
    {
      "CustomerID": "ANTON",
      "ContactName": "Antonio Moreno ",
      "Address": "Mataderos 2312",
      "Orders": [
        {
          "OrderID": 10365,
          "ShipCity": "MÃ©xico D.F.",
          "Freight": 22.0000,
          "OrderDate": "28-11-1997"
        },
        {
          "OrderID": 10507,
          "ShipCity": "MÃ©xico D.F.",
          "Freight": 47.4500,
          "OrderDate": "28-11-1997"
        }
      ]
    }
  ],
  "count": 3
}

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Return nested JSON data from Generic Handler in ASP.Net using C# and VB.Net

Company

Explore

Follow us

Contact Us