jQuery Datatable using webservice

In this article I will explore how to prepare datatable using jQuery and webservice. Webservice will return data as JSON which will be binded to jQuery Datatable.

Let's see how we can do this:

Step 1:  Download jQuery 1.4.2 and jQuery Datatable

Step 2: Add jquery-1.4.2.min.js and jquery.dataTables.min.js in the page

<script src="jquery-1.4.2.min.js" type="text/javascript"></script><script src="jquery.dataTables.min.js" type="text/javascript"></script>

Step 3: Add the below style in the page.

<style type="text/css">

.myGrid{
      width: 100%;
      margin: 0px 0 0px 0;      border: solid 1px #525252;
      border-collapse: collapse;
      width: 600px;
}

.myGrid td{
      padding: 2px;
      border: solid 1px #c1c1c1;
      color: Black;
      font-family: Arial,Helvetica,sans-serif;
      font-size: 0.9em;
      width:40px;
}

.myGrid th{
      color: #fff;
      background: url(images/grid_header.png) repeat-x top;
      font-family: Arial,Helvetica,sans-serif;
      font-size: 0.9em;
}

</style>

Step 4: Add below html content inside body tag

<table id="grid" class="myGrid">
      <thead>
            <tr>
                  <th>                        By
                  </th>
                  <th>
                        Recipie Name
                  </th>
                  <th>
                        Preparation Time
                  </th>
                  <th>
                        Cooking Time
                  </th>
            </tr>
      </thead>
      <tbody>
            <tr>
                  <td colspan="5">
                        Loading....
                  </td>
            </tr>
      </tbody></table><div id="errorMessage"></div>

Step 5: Add below javascript in the page.

<script language="javascript" type="text/javascript">
      $(document).ready(function() {
            function renderTable(result) {
                  var dtData = [];
                  $.each(result, function() {
                        dtData.push([
                              this.by,
                              this.Recipiename,
                              this.preparationtime,
                              this.cookingtime
                        ]);
            });
            $('#grid').dataTable({  //grid is the id of the table
                  'aaData': dtData,
                  'bPaginate': false,
                  'bInfo': false,
                  'bFilter': false
            });
      }

      $.ajax({
            type: "GET",
            url: "JsonWebService.asmx/GetRecipie",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function(response) {
                  renderTable(response.d);
            },
            failure: function(errMsg) {
                  $('#errorMessage').text(errMsg);  //errorMessage is id of the div
            }
      });});
</script>

Step 6: Create an asmx file and create below class.

public class Recipie{
      public string by;
      public string Recipiename;
      public string preparationtime;
      public string cookingtime;
}

Step 7: Add below name space in the asmx.cs file

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

Step 8: Create below webmethod in the asmx.cs file to get data from database and return List to jQuery

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
public List<Recipie> GetRecipie()
{
      string strQuery = "SELECT * FROM Recipie";
      DataTable dtRecipie = null;
      Recipie objRecipie;
      SqlConnection con = GetConnection("Data Source=(local);Initial Catalog=DataTable;Integrated Security=SSPI");
      using(con)
      {
            con.Open();
            using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strQuery, con))
            {
                  dtRecipie = new DataTable();
                  sqlAdapter.Fill(dtRecipie);
            }
      }
      List<Recipie> drlist = new List<Recipie>();
      foreach (DataRow row in dtRecipie.Rows)
      {
            objRecipie = new Recipie();
            objRecipie.by = row["by"].ToString();
            objRecipie.Recipiename = row["Recipiename"].ToString();
            objRecipie.preparationtime = row["preparationtime"].ToString();
            objRecipie.cookingtime = row["cookingtime"].ToString();
            drlist.Add(objRecipie);
      }
      return drlist;
}

Step 9 : Add below method to get SqlConnection

private SqlConnection GetConnection(string m_conString)
{
      SqlConnection con = new SqlConnection(m_conString);
      return con;
}

This ends the article of creating datatable using jquery and webservice.

Reference:

http://www.dotnetspeaks.com/DisplayArticle.aspx?ID=67