Description:

DataTables is a powerful jQuery plugin for creating table listings and adding interactions to them. It provides searching, sorting and pagination without any configuration.  jQuery DataTables support both client side processing and as well as server side processing.

  

Let’s see an example server side data processing Using DataTables

 

Frist create a webform page “detatable.aspx” and add in header tag

<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>

 

Second create a html table in your webform page  “detatable.aspx”

       <table id="example" class="display" cellspacing="0">
                <thead>
                    <tr>
                        <th>Account Type</th>
                        <th>Nature</th>
                    </tr>
                </thead>
                <tbody>
                </tbody>
            </table>

Now, write script inside “Scripts” tag following code

<script type="text/javascript">
 $(function () {
$('#example').DataTable(
 {
    "columnDefs": [
      { "width": "300px", "targets": [0] }
    ],
    "language":
      {
          "processing": "Proceccing"
      },
    "processing": true,
    "serverSide": true,
    "ajax":
      {
          "url": "Datatable.aspx/GetData",
          "contentType": "application/json",
          "type": "GET",
          "dataType": "JSON",
          "data": function (d) {
              return d;
          },
          "dataSrc": function (json) {
              json.draw = json.d.draw;
              json.recordsTotal =json.d.recordsTotal;
              json.recordsFiltered =json.d.recordsFiltered;
              json.data = json.d.data;
              var return_data = json;
              return return_data.data;
          }
      },
    "columns": [        
          {"data": "AccountType"},
          {"data": "Nature"}        
    ]
});
});
</script>


Now create two new models inside “datatable.aspx.cs” "Model", name it "Account." and add following

properties in them.

public class Account
{
    public string AccountType { get; set; }
    public string Nature { get; set; }
 
}

Now create two new models inside “datatable.aspx.cs” "Model", name it " DataTables " and add following

properties in them.

public class DataTables
{
    public int draw { get; set; }
    public int recordsTotal { get; set; }
    public int recordsFiltered { get; set; }
    public List<Account> data { get; set; }
}

Now, In "datatable.aspx.cs" file add following function to load data from database.

  private static List<Account> LoadData()
    {
        List<Account> Data = new List<Account>();
       
            SqlConnection cn = new SqlConnection("");
            string connetionString = null;
            SqlConnection sqlCnn;
            SqlCommand sqlCmd;
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            string sql = null;
            connetionString = "Password=sa@123;Persist Security Info=True;User                               ID=sa;Initial Catalog=dellfinch;Data Source=(local)";
 
            sql = "select accountGroupName as AccountType,Nature from tbl_AccountGroup";
          
            sqlCnn = new SqlConnection(connetionString);
            try
            {
                sqlCnn.Open();
                sqlCmd = new SqlCommand(sql, sqlCnn);
                adapter.SelectCommand = sqlCmd;
                adapter.Fill(ds);
                adapter.Dispose();
                sqlCmd.Dispose();
                sqlCnn.Close();
                string str = Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[0]);
                JavaScriptSerializer json_serializer = new JavaScriptSerializer();
                Data= JsonConvert.DeserializeObject<List<Account>>(str.ToString());
 
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
     
        return Data;
    }


Now, In "datatable.aspx.cs" file add following function to short data from server side.

private static List<Account> SortByColumnWithOrder(string order,string orderDir,List<Account> data)
   {
       //Initialization.   
       List<Account> lst = new List<Account>();
       try
       {
           //Sorting   
           switch (order)
           {
               case "0":
             //Setting.    
lst= orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ?
            data.OrderByDescending(p=> p.AccountType).ToList()                                                        
            :data.OrderBy(p=>p.AccountType).ToList();
                   break;
               case "1":
                   // Setting.   
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase)?
      data.OrderByDescending(p => p.Nature).ToList()
      : data.OrderBy(p => p.Nature).ToList();
                   break;
               default:
                   // Setting.   
lst= orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ?
      data.OrderByDescending(p =>p.AccountType).ToList()
     :data.OrderBy(p => p.AccountType).ToList();
                   break;
           }
       }
       catch (Exception ex)
       {
           //info.   
           Console.Write(ex);
       }
       //info.   
       return lst;
   }   
 

Now, in "datatable.aspx.cs" file let's create "GetData" method as follow

   [WebMethod]
   [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
   public static object GetData()
    {
        //Initialization.   
        DataTables result = new DataTables();
        try
        {
            //Initialization.   
            string search = HttpContext.Current.Request.Params["search[value]"];
            string draw = HttpContext.Current.Request.Params["draw"];
            string order = HttpContext.Current.Request.Params["order[0][column]"];
            string orderDir = HttpContext.Current.Request.Params["order[0][dir]"];
            int PageNo = Convert.ToInt32(HttpContext.Current.Request.Params["start"]);
            int pageSize = Convert.ToInt32(HttpContext.Current.Request.Params["length"]);
            //Loading.   
            List<Account> data = Datatable.LoadData();
            // Total record count.   
            int totalRecords = data.Count;
            //Verification.   
            if (!string.IsNullOrEmpty(search) &&
              !string.IsNullOrWhiteSpace(search))
            {
                //Apply search   
                data = data.Where(p =>
                           p.AccountType.ToLower().Contains(search.ToLower()) ||
                            p.Nature.ToString().ToLower().Contains(search.ToLower())
                            ).ToList();
            }
            //Sorting.   
            data = Datatable.SortByColumnWithOrder(order,orderDir, data);
            //Filter record count.   
            int recFilter = data.Count;
            // Applypagination.   
            data =data.Skip(PageNo).Take(pageSize).ToList();
            //Loading drop down lists.   
            result.draw = Convert.ToInt32(draw);
            result.recordsTotal = totalRecords;
            result.recordsFiltered = recFilter;
            result.data = data;
        }
        catch (Exception ex)
        {
            //Info   
            Console.Write(ex);
        }
        //Return info.   
        return result;
    } 

Here how the results will look,

jquery datatable server side example in asp.net



comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like