Return multiple tables in xml format from WebMethod in ASP.Net

Last Reply one month ago By kalpesh

Posted one month ago

HI i want to store two tables in Xml File  its possible ?

i want  to return two different tables to  xml  

like 1-select (ChqTo+1) ChqTo1, * from tblchqbooks

2-1-select  *  from books

       function loaddata() {         
           // LocationCheck();
            //DateCheck();
           
            $.ajax({
                type: "POST",
                url: "ChqInfo.aspx/GetData",
                data: "{Fromdate:'" + Fromdate + "',Todate:'" + Todate + "',Location:'" + Location + "',Finid:'" + mgFinID + "'}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess1,
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });

            return false;
          
        }
        function OnSuccess1(response) {
           
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            var customers = xml.find("Table");
            
            var row = $("[id*=GridView1] tr:last-child").clone(true);
            $("[id*=GridView1] tr").not($("[id*=GridView1] tr:first-child")).remove();
            $.each(customers, function () {
                var customer = $(this);
                var chqfrom, chqto;
                chqfrom = $(this).find("ChqFrom").text();
                chqto = $(this).find("ChqTo1").text();
             
                for (count = chqfrom; count < chqto; count++) {
                    $("td", row).eq(1).html("ChqNo#"+count);
                    $("[id*=GridView1]").append(row);
                    row = row.clone(true);
                }
                   
            });
        }

 

    [WebMethod]
    public static string GetData(String Fromdate, String Todate, string Location, string Finid)
    {

        DataLogic dl = new DataLogic();
        DataSet Ds = new DataSet();
        String Qry = @"select (ChqTo+1) ChqTo1, * from tblchqbooks";
        //  + " Where C.FINID='" + Finid + "' and C.locid like '" + Location + "'AND CONVERT(varchar(11),ChqDate,111) BETWEEN '" + Fromdate + "' AND '" + Todate + "' ";
        dl.LoadTxt1(Ds, Qry);
        if (Ds.Tables[0].Rows.Count == 0)
        {
            Ds.Tables[0].Rows.Add();
        }


        return Ds.GetXml();
    }

 

You are viewing reply posted by: kalpesh one month ago.
Posted one month ago

Refer below code for your reference and implement it as per your code logic.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetCustomers",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });
        });

        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);

            // From First Table Customers.          
            var customers = xml.find("Table");
            var row = $("[id*=gvCustomers] tr:last-child").clone(true);
            $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
            $.each(customers, function () {
                $("td", row).eq(0).html($(this).find("CustomerID").text());
                $("td", row).eq(1).html($(this).find("ContactName").text());
                $("td", row).eq(2).html($(this).find("City").text());
                $("[id*=gvCustomers]").append(row);
                row = $("[id*=gvCustomers] tr:last-child").clone(true);
            });

            // From Second Table Employees.
            var employees = xml.find("Table1");

            var row = $("[id*=gvEmployees] tr:last-child").clone(true);
            $("[id*=gvEmployees] tr").not($("[id*=gvEmployees] tr:first-child")).remove();
            $.each(employees, function () {
                $("td", row).eq(0).html($(this).find("EmployeeId").text());
                $("td", row).eq(1).html($(this).find("FirstName").text());
                $("[id*=gvEmployees]").append(row);
                row = $("[id*=gvEmployees] tr:last-child").clone(true);
            });
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" Caption="<b>Customers</b>">
        <Columns>
            <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" />
            <asp:BoundField DataField="ContactName" HeaderText="CustomerID" />
            <asp:BoundField DataField="City" HeaderText="City" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false" Caption="<b>Employees</b>">
        <Columns>
            <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" />
            <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
        </Columns>
    </asp:GridView>
    </form>
</body>
</html>

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("CustomerID");
    dummy.Columns.Add("ContactName");
    dummy.Columns.Add("City");
    dummy.Rows.Add();
    gvCustomers.DataSource = dummy;
    gvCustomers.DataBind();

    DataTable dummy1 = new DataTable();
    dummy1.Columns.Add("EmployeeId");
    dummy1.Columns.Add("FirstName");
    dummy1.Rows.Add();
    gvEmployees.DataSource = dummy1;
    gvEmployees.DataBind();
}

[WebMethod]
public static string GetCustomers()
{
    string query = "SELECT top 5 CustomerID, ContactName, City FROM Customers GO SELECT top 5 EmployeeId,FirstName FROM Employees";
    SqlCommand cmd = new SqlCommand(query);
    return GetData(cmd).GetXml();
}

private static DataSet GetData(SqlCommand cmd)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds);
                return ds;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.BindDummyRow()
    End If
End Sub

Private Sub BindDummyRow()
    Dim dummy As DataTable = New DataTable()
    dummy.Columns.Add("CustomerID")
    dummy.Columns.Add("ContactName")
    dummy.Columns.Add("City")
    dummy.Rows.Add()
    gvCustomers.DataSource = dummy
    gvCustomers.DataBind()
    Dim dummy1 As DataTable = New DataTable()
    dummy1.Columns.Add("EmployeeId")
    dummy1.Columns.Add("FirstName")
    dummy1.Rows.Add()
    gvEmployees.DataSource = dummy1
    gvEmployees.DataBind()
End Sub

<WebMethod()>
Public Shared Function GetCustomers() As String
    Dim query As String = "SELECT top 5 CustomerID, ContactName, City FROM Customers GO SELECT top 5 EmployeeId,FirstName FROM Employees"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Return GetData(cmd).GetXml()
End Function

Private Shared Function GetData(ByVal cmd As SqlCommand) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        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

Screenshot

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