Blogs

In this post we will illustrate how to use the jqxGrid widget in paging mode in ASP .NET application. The records will be loaded on demand from a Northwind Database when the user changes the current page.

1. Create a new ASP .NET Web Application.
2. Click the Data item in Visual Studio 2010.
3. Add a new DataSource. In the sample, we will use the Northwind.mdf and will bind the Grid to the Customers table.
4. Add the JQWidgets JavaScript files to the Scripts folder.
5. Add the jQWidgets CSS files and images to the Styles folder.
6. Open the Default.aspx.cs file.
7. Create a new SqlConnection, SqlDataAdapter and DataSet. Use the NorthwindConnectionString(this is the default name) connection string for the connection. You can see the connection string in the Web.config, too. Fill the DataSet with data and return the XML result.

The code below is added to the Default.aspx.cs file and returns data records from the Customers table depending on the Grid’s current page number and page size.

[WebMethod]
[ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Xml)]
public static string GetCustomers(int pagenum, int pagesize)
{
    string query = "SELECT * FROM ( "
        + "  SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerID) as row FROM Customers "
        + " ) a WHERE row > " + pagenum * pagesize + " and row <= " + (pagenum + 1) * pagesize;
    SqlCommand cmd = new SqlCommand(query);
    
    // Populate the DataSet.
    DataSet data = GetData(cmd);
    // return the Customers table as XML.
    System.IO.StringWriter writer = new System.IO.StringWriter();
    data.Tables[0].WriteXml(writer, XmlWriteMode.WriteSchema, false);
    return writer.ToString(); 
}
private static DataSet GetData(SqlCommand cmd)
{
    string strConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].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;
            }
        }
    }
}

8. Create an additional WebMethod which will run a simple query for getting the Rows Count in the Customers Table.

[WebMethod]
[ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
public static int GetTotalRowsCount()
{
    string rowsNumberQuery = "SELECT Count(*) FROM Customers";
    SqlCommand countCmd = new SqlCommand(rowsNumberQuery);
    int count = GetRowsCount(countCmd);
    return count;
}
private static int GetRowsCount(SqlCommand cmd)
{
    string strConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    cmd.Connection = con;
    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    reader.Read();
    int rows = (int)reader[0];
    con.Close();
    return rows;
}

9. Open the default.aspx file and add the code below:

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    <link href="Styles/jqx.base.css" rel="stylesheet" type="text/css" />
    <link href="Styles/jqx.classic.css" rel="stylesheet" type="text/css" />
    <script src="Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
    <script src="Scripts/jqxcore.js" type="text/javascript"></script>
    <script src="Scripts/jqxbuttons.js" type="text/javascript"></script>
    <script src="Scripts/jqxdata.js" type="text/javascript"></script>
    <script src="Scripts/jqxgrid.js" type="text/javascript"></script>
    <script src="Scripts/jqxgrid.pager.js" type="text/javascript"></script>
    <script src="Scripts/jqxlistbox.js" type="text/javascript"></script>
    <script src="Scripts/jqxdropdownlist.js" type="text/javascript"></script>
    <script src="Scripts/jqxgrid.selection.js" type="text/javascript"></script>
    <script src="Scripts/jqxmenu.js" type="text/javascript"></script>
    <script src="Scripts/jqxscrollbar.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            //Getting the source data with ajax GET request
            source = {
                datatype: "xml",
                datafields: [
                { name: 'CompanyName' },
                { name: 'ContactName' },
                { name: 'ContactTitle' },
                { name: 'City' },
                { name: 'Country' },
                { name: 'Address' }
                ],
                async: false,
                formatdata: function(data)
                {
                    return { pagenum: data.pagenum, pagesize: data.pagesize }          
                },
                record: 'Table',
                url: 'Default.aspx/GetCustomers'
            };
            $.ajax({
                url: 'Default.aspx/GetTotalRowsCount',
                contentType: 'application/json; charset=utf-8',
                async: false,
                success: function (data) {
                    source.totalrecords = data.d;
                }
            });
            var dataAdapter = new $.jqx.dataAdapter(source,
                { contentType: 'application/json; charset=utf-8' }
            );
            $("#jqxgrid").jqxGrid({
                source: dataAdapter,
                theme: 'classic',
                pageable: true,
                autoheight: true,
                virtualmode: true,
                rendergridrows: function () {
                    return dataAdapter.records;
                },
                columns: [
                    { text: 'Company Name', dataField: 'CompanyName', width: 250 },
                    { text: 'Contact Name', dataField: 'ContactName', width: 150 },
                    { text: 'Contact Title', dataField: 'ContactTitle', width: 180 },
                    { text: 'Address', dataField: 'Address', width: 180 },
                    { text: 'City', dataField: 'City', width: 80 },
                    { text: 'Country', dataField: 'Country', width: 100 }
                ]
            });
        });
    </script>
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <div id="jqxgrid"></div>
</asp:Content>

The source object initialization in the above code includes a definition of the Data Fields, the data type(we get the records in XML format), the url to the GetCustomers method, the path to the data table in the returned XML and a formatdata function which specifies the parameters passed to the GetCustomers method. We also run another query for getting the Rows Count in the Customers table and set the result to the source object’s totalrecords property. The Grid is initialized in virtual mode, so it does not keep any records locally and displays only the records returned from the GetCustomers method. The method is called every time when the user navigates to a new page or selects a new page’s size.
Here’s the result: