Return DataSet from ASP.Net Generic Handler (WebHandler) using C# and VB.Net

Hi Amitabha,

You cant return DataSet from WebHandler. You need to Serialize the DataSet to json string, then return the json string.

In code behind Deserialize the json string to DataSet. 

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

Handler

C#

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

using System;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.SessionState;
using System.Web.Script.Serialization;

public class HandlerCS : IHttpHandler, IRequiresSessionState
{
    public void ProcessRequest(HttpContext context)
    {
        string deliveryLoc = context.Request.QueryString["DeliveryLoc"];
        DataSet ds = GetData(context.Session["DivCode"].ToString(), deliveryLoc);
        string json = new JavaScriptSerializer().Serialize(ds);
        context.Response.ContentType = "text/json";
        context.Response.Write(json);
    }

    private DataSet GetData(string DivCode, string DeliveryLoc)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "SELECT CUST_CODE,CUST_NAME,ADDRESS,PINCODE,GSTIN,E_MAIL,MOBILE_NO " +
            "FROM CM_CUST " +
            "WHERE DIV_CODE='" + DivCode + "' AND BRANCH_BRANCH_CODE='" + DeliveryLoc + "' AND STATUS='Y' " +
            "UNION " +
            "SELECT CUST_CODE,CUST_NAME,ADDRESS,PIN_CODE,NVL(GSTIN_NO,'NA'),EMAIL_ID,MOBILE_NO " +
            "FROM CT_CM_CUST " +
            "WHERE DIV_CODE='" + DivCode + "' AND BRANCH_CODE='" + DeliveryLoc + "' ORDER BY 2";
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query);
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    }

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

VB.Net

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

Imports System
Imports System.Web
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.SessionState
Imports System.Web.Script.Serialization

Public Class Handler : Implements IHttpHandler, IRequiresSessionState

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim deliveryLoc As String = context.Request.QueryString("DeliveryLoc")
        Dim ds As DataSet = GetData(context.Session("DivCode").ToString(), deliveryLoc)
        Dim json As String = New JavaScriptSerializer().Serialize(ds)
        context.Response.ContentType = "text/json"
        context.Response.Write(json)
    End Sub

    Private Function GetData(ByVal DivCode As String, ByVal DeliveryLoc As String) As DataSet
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT CUST_CODE,CUST_NAME,ADDRESS,PINCODE,GSTIN,E_MAIL,MOBILE_NO " &
                "FROM CM_CUST " &
                "WHERE DIV_CODE='" & DivCode & "' AND BRANCH_BRANCH_CODE='" & DeliveryLoc & "' AND STATUS='Y' " &
                "UNION " & "SELECT CUST_CODE,CUST_NAME,ADDRESS,PIN_CODE,NVL(GSTIN_NO,'NA'),EMAIL_ID,MOBILE_NO " &
                "FROM CT_CM_CUST " &
                "WHERE DIV_CODE='" & DivCode & "' AND BRANCH_CODE='" & DeliveryLoc & "' ORDER BY 2"
        Using con As SqlConnection = New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using ds As DataSet = New DataSet()
                    sda.Fill(ds)
                    Return ds
                End Using
            End Using
        End Using
    End Function

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

End Class

HTML

<asp:DropDownList runat="server" ID="drpDeliveryLoc">
    <asp:ListItem Text="Mumbai" Value="Mumbai" />
    <asp:ListItem Text="Pune" Value="Pune" />
</asp:DropDownList>
<asp:Button Text="Submit" runat="server" OnClick="OnSubmit" />

Namespaces

C#

using System.Data;
using System.Net;
using System.Web.Script.Serialization;

VB.Net

Imports System.Data
Imports System.Net
Imports System.Web.Script.Serialization

Code

C#

protected void OnSubmit(object sender, EventArgs e)
{
    string handlerUrl = "http://localhost:6340/Handler.ashx?DeliveryLoc=" + drpDeliveryLoc.SelectedValue;
    string response = (new WebClient()).DownloadString(handlerUrl);
    DataSet ds = new JavaScriptSerializer().Deserialize<DataSet>(response);
    // Do rest of task.
}

VB.Net

Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
    Dim handlerUrl As String = "http://localhost:6340/Handler.ashx?DeliveryLoc=" & drpDeliveryLoc.SelectedValue
    Dim response As String = (New WebClient()).DownloadString(handlerUrl)
    Dim ds As DataSet = New JavaScriptSerializer().Deserialize(Of DataSet)(response)
    ' Do rest of task.
End Sub