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: