Server Paging with jqxGrid, ASP.NET and SQL

• Open Visual Studio.
• Create a new ASP .NET Web Application.
• Click the "Data" menu item in the Visual Studio's Menu.
• Add a new DataSource. In the sample, we will use the Northwind.mdf and will bind the Grid to the Customers table.
  
  
  
  
  
  
  
  
• Add the connectionStrings in the Web.config.
  They should be similar to the code below:

    <connectionStrings>
      <add name="NORTHWNDConnectionString" connectionString="Data Source=JOHN-PC\SQL8;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True"
        providerName="System.Data.SqlClient" />
    </connectionStrings>
  

  
  *Note: To find your connection string, open the Server Explorer, click on the NORTHWIND.MDF under the Data Connections node, and the Connection String will be visible in the Properties Window.
• In the Solution Explorer, click Site.Master and in the head section, add references to the jQuery Framework, jQWidgets Framework(jqx-all.js) and the Theme Files(jqx.base.css and any other theme file like jqx.classic.css).
• Double-click on the Default.aspx file.
  Add the following initialization code for jqxGrid. The Grid in the code is initialized in a DIV tag with id="jqxgrid".

      <script type="text/javascript">
          $(document).ready(function () {
              source = {
                  datatype: "xml",
                  datafields: [
                  { name: 'CompanyName' },
                  { name: 'ContactName' },
                  { name: 'ContactTitle' },
                  { name: 'City' },
                  { name: 'Country' },
                  { name: 'Address' }
                  ],
                  formatdata: function (data) {
                      return { pagenum: data.pagenum, pagesize: data.pagesize }
                  },
                  record: 'Table',
                  url: 'Default.aspx/GetCustomers'
              };
              var dataAdapter = new $.jqx.dataAdapter(source,
                  { contentType: 'application/json; charset=utf-8',
                      loadError: function (jqXHR, status, error) {
                          alert(error);
                      },
                      downloadComplete: function () {
                          // update the totalrecords count.
                          $.ajax({
                              url: 'Default.aspx/GetTotalRowsCount',
                              contentType: 'application/json; charset=utf-8',
                              async: false,
                              success: function (data) {
                                  source.totalrecords = data.d;
                              }
                          });
                      }
                  }
              );
              $("#jqxgrid").jqxGrid({
                  source: dataAdapter,
                  pageable: true,
                  autoheight: true,
                  virtualmode: true,
                  rendergridrows: function (args) {
                      return args.data;
                  },
                  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>
  

  
  
• Open the Default.aspx.cs file. Add the following code in it:

        [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["NORTHWNDConnectionString"].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;
                      }
                  }
              }
          }
          [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["NORTHWNDConnectionString"].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;
          }
  

  
  The "GetCustomers" method returns the data to be displayed in the Grid.
• As a result, you should see: