Get all rows data wile paging in jQuery DataTables in ASP.Net

Hi Rockstar8,

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

<form id="form1" runat="server">
    <div style="width: 500px">
        <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="CustomerID" HeaderText="Customer Id" />
                <asp:BoundField DataField="ContactName" HeaderText="Name" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="Country" HeaderText="Country" />
            </Columns>
        </asp:GridView>
        <br />
        <br />
        <input id="btnSave" type="button" name="name" value="Save" />
    </div>
</form>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css" rel="stylesheet"
    type="text/css" />
<script type="text/javascript">
    $(function () {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomers",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $("[id*=gvCustomers]").DataTable({
                    bLengthChange: true,
                    lengthMenu: [[5, 10, -1], [5, 10, "All"]],
                    bFilter: true,
                    bSort: true,
                    bPaginate: true,
                    data: response.d,
                    columns: [
                        { 'data': 'CustomerID' },
                        { 'data': 'ContactName' },
                        { 'data': 'City' },
                        { 'data': 'Country' }
                    ]
                });
            },
            failure: function (response) {
                alert(response.d);
            },
            error: function (response) {
                alert(response.d);
            }
        });
        $('[id*=btnSave]').on('click', function () {
            var totalList = [];
            $('#gvCustomers').DataTable().rows().iterator('row', function (context, index) {
                var row = $(this.row(index).node());
                var valList = {};
                valList.id = row.find("td").eq(0).html();
                valList.name = row.find("td").eq(1).html();
                valList.city = row.find("td").eq(2).html();
                valList.country = row.find("td").eq(3).html();
                totalList.push(valList);
            });
            alert(JSON.stringify(totalList));
        });
    });
</script>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dummy = new DataTable();
        dummy.Columns.Add("CustomerID");
        dummy.Columns.Add("ContactName");
        dummy.Columns.Add("City");
        dummy.Columns.Add("Country");
        dummy.Rows.Add();
        gvCustomers.DataSource = dummy;
        gvCustomers.DataBind();

        //Required for jQuery DataTables to work.
        gvCustomers.UseAccessibleHeader = true;
        gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader;
    }
}

[WebMethod]
public static List<Customer> GetCustomers()
{
    List<Customer> customers = new List<Customer>();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT TOP 8 CustomerID, ContactName, City, Country FROM Customers", con))
        {
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customers.Add(new Customer
                    {
                        CustomerID = sdr["CustomerID"].ToString(),
                        ContactName = sdr["ContactName"].ToString(),
                        City = sdr["City"].ToString(),
                        Country = sdr["Country"].ToString()
                    });
                }
            }
            con.Close();
        }
    }

    return customers;
}

public class Customer
{
    public string CustomerID { get; set; }
    public string ContactName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dummy As DataTable = New DataTable()
        dummy.Columns.Add("CustomerID")
        dummy.Columns.Add("ContactName")
        dummy.Columns.Add("City")
        dummy.Columns.Add("Country")
        dummy.Rows.Add()
        gvCustomers.DataSource = dummy
        gvCustomers.DataBind()

        'Required for jQuery DataTables to work.
        gvCustomers.UseAccessibleHeader = True
        gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader
    End If
End Sub

<WebMethod>
Public Shared Function GetCustomers() As List(Of Customer)
    Dim customers As List(Of Customer) = New List(Of Customer)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT TOP 8 CustomerID, ContactName, City, Country FROM Customers", con)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    customers.Add(New Customer With {
                        .CustomerID = sdr("CustomerID").ToString(),
                        .ContactName = sdr("ContactName").ToString(),
                        .City = sdr("City").ToString(),
                        .Country = sdr("Country").ToString()
                    })
                End While
            End Using
            con.Close()
        End Using
    End Using
    Return customers
End Function

Public Class Customer
    Public Property CustomerID As String
    Public Property ContactName As String
    Public Property City As String
    Public Property Country As String
End Class

Screenshot