Saturday, January 29, 2011

Jquery DataTables in asp.net with CRUD

I am going to show how we can use jquery datatables in asp.net webforms.
Jquery DataTables
More details
First we need to download the required files for jquery datatables form their website.
I have 2 cases:
Case1:
when bServerSide = false and jquery datatable gets the whole data
in this case, I need to fetch the whole data and populate the HTML table with proper format (THEAD, TBODY, ...).

Person.cs
using System;
using System.Collections.Generic;

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }

    public static ICollection<Person> GetPersons()
    {
        IList<Person> list = new List<Person>();
        for (int i = 0; i < 63; i++)
        {
            list.Add(new Person() { Id = i, Name = "name" + i });
        }
        return list;
    }
}

test1.aspx
<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="test1.aspx.cs" Inherits="jqueryDataTable_test1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <link rel="stylesheet" href="media/css/demo_table.css"type="text/css" />
    <script type="text/javascript" src="media/js/jquery.js"></script>
    <script type="text/javascript" src="media/js/jquery.dataTables.js"></script>
<script language="javascript" type="text/javascript">
    $(document).ready(function () {
        $('#tbl').dataTable({
            "sScrollY": "150px",
            //'sPaginationType': 'full_numbers',
            //'iDisplayLength': 5,
            "bPaginate": true,
            "bProcessing": true,
            "bServerSide": false
        });
    });
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div style="width:500px">
        <asp:Repeater ID="repeater" runat="server">
            <HeaderTemplate>
                <table id="tbl" cellpadding="1" cellspacing="0" 
                    border="0" class="display">
                  <thead>
                    <tr>
                        <th>Id</th>
                        <th>Name</th>
                    </tr>
                  </thead>
                <tbody>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                  <td><%# Eval("Id") %></td>
                  <td><%# Eval("Name") %></td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                    </tbody>
                </table>
            </FooterTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>

test1.aspx.cs
public partial class jqueryDataTable_test1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        repeater.DataSource = Person.GetPersons();
        repeater.DataBind();
    }
}


Case2:
when bServerSide = true and jquery datatable gets the data from server side on demand and it has insert and update and delete features. and I am using $.ajax to call page methods and can be easily replaced with calling asmx or wcf methods or ashx handler.
test2.aspx
<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="test2.aspx.cs" Inherits="jqueryDataTable_test2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    
    <link rel="stylesheet" href="media/css/demo_table.css"type="text/css" />
    <script type="text/javascript" src="media/js/jquery.js"></script>
    <script type="text/javascript" src="media/js/jquery.dataTables.js"></script>
    <script type="text/javascript" src="media/js/json2.js"></script>

    <script type="text/javascript">

        //http: //www.sprymedia.co.uk/article/DataTables
        //http://www.datatables.net

        var oTable;
        var tableId = 'tbl';
        var ws_GetData = 'test2.aspx/GetPersonData';
        var ws_DeleteData = 'test2.aspx/DeletePerson';
        var ws_UpdateData = 'test2.aspx/UpdatePerson';
        var ws_AddData = 'test2.aspx/AddPerson';
        var isDeleteEnbaled = true;
        var isUpdateEnabled = true;
        var isAddEnabled = true;
        var isShowGrid = <%= isShowGrid %>;

        $(document).ready(function () {

            if(isShowGrid == "0")
            { 
                $('#' + tableId).css("display","none");
                return;
            }
            if(isAddEnabled)
            {
                $('#' + tableId + '_add').css("display","block");
                $('#' + tableId + '_add_btnAdd').click(function(){ AddMe(); });
            }

            oTable = $('#' + tableId).dataTable({
                //'sPaginationType': 'full_numbers',
                "sScrollY": "250px",
                'bPaginate': true,
                //'iDisplayLength': 10,
                'bProcessing': true,
                'bFilter': false,
                'bServerSide': true,
                "aoColumns": [null, null, { "bSortable": false }, { "bSortable": false}],
                'sAjaxSource': ws_GetData,
                "fnServerData": function (sSource, aoData, fnCallback) { GrabData(sSource, aoData, fnCallback); }
            });



            $('#' + tableId + ' tbody tr').live('click', function () {

                $(oTable.fnSettings().aoData).each(function () { $(this.nTr).removeClass('row_selected'); });

                $(this).addClass('row_selected');

                //var aPos = oTable.fnGetPosition(this);
                //var aData = oTable.fnGetData(this);
                //iId = aData[0];
            });

        });
        //-------------------------------------------------
        function GrabData(sSource, aoData, fnCallback) {
            //aoData.push({ "name": "more_data", "value": "my_value" });

            $.ajax({
                type: "GET",
                url: sSource,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                data: aoData,
                success: function (result) {
                    //var json = eval('(' + result.d + ')');
                    var myObject = JSON.parse(result.d);

                    if (isUpdateEnabled) {
                        for (var i = 0; i < myObject.aaData.length; i++) {
                            var str2 = "<input id='" + tableId + "_update_" + myObject.aaData[i][0] + "' type='button' value='update' onclick='UpdatePrepare(" + JSON.stringify(myObject.aaData[i]) + ");' />";
                            myObject.aaData[i].push(str2);
                        }

                    }

                    if (isDeleteEnbaled) {

                        for (var i = 0; i < myObject.aaData.length; i++) {
                            var str1 = "<input id='" + tableId + "_delete_" + myObject.aaData[i][0] + "' type='button' value='delete' onclick='DeleteMe(" + myObject.aaData[i][0] + ");' />";
                            myObject.aaData[i].push(str1);
                        }
                    }

                    fnCallback(myObject);
                },
                error: function (errMsg) {
                    alert(errMsg);
                }
            });

        }
        //--------------------------------------------------------------------------
        function DeleteMe(IdToBeDeleted) 
        {
            if (window.confirm('Are you sure to delete this row?') == false) return;
            //*************************************************
            var parameters = "{'id':'" + IdToBeDeleted + "'}";
            //*************************************************
            $.ajax({
                type: "POST",
                url: ws_DeleteData,
                data: parameters,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (msg) {
                    alert(msg.d);
                    oTable.fnDraw();
                },
                error: function (e) {
                    alert('Failure Deletion');
                }
            });
        }
        //---------------------------------------------------------------------------
        function UpdatePrepare(data) 
        {
            
                $('#' + tableId + '_update').css("display","block");
                //**************************************************
                $('#' + tableId + '_update_txtId').val(data[0]);
                $('#' + tableId + '_update_txtName').val(data[1]);
                //***************************************************
                $('#' + tableId + '_update_btnUpdate').unbind("click");
                $('#' + tableId + '_update_btnUpdate').click(function(){ UpdateMe(); });

                $('#' + tableId + '_update_btnCancel').click(function(){
                     $('#' + tableId + '_update').css("display","none");   
                  });
        }
        function UpdateMe() {
            
            if (window.confirm('Are you sure to update this?') == false) return;

            //********************************************************
            var person = new Object();
            person.Id = $('#' + tableId + '_update_txtId').val();
            person.name = $('#' + tableId + '_update_txtName').val();
             
             //var parameters = "{'id':'" + id + "', 'name':'" + name + "'}";
             var DTO = { 'person' : person };

             var parameters = JSON.stringify(DTO);
            //******************************************************
            $.ajax({
                type: "POST",
                url: ws_UpdateData,
                data:  parameters,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (msg) {
                    alert(msg.d);
                    $('#' + tableId + '_update').css("display","none");
                    oTable.fnDraw();
                },
                error: function (e) {
                    alert('Failure Update');
                }
            });
        }
        //------------------------------------------------------------------------------
        function AddMe() {

            if (window.confirm('Are you sure to add this?') == false) return;

            //************************************************************
            var name = $('#' + tableId + '_add_txtName').val();

            var parameters = "{'id':'0', 'name':'" + name + "'}";
            //***********************************************************

            $.ajax({
                type: "POST",
                url: ws_AddData,
                data: parameters,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (msg) {
                    alert(msg.d);
                    oTable.fnDraw();
                },
                error: function (e) {
                    alert('Failure Add');
                }
            });
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div style="width:500px">

         <table cellpadding="2" cellspacing="0" border="0" class="display" id="tbl_add"  style="width:250px;display:none"> 
            <tr>
                <td>Name</td>
                <td><input type="text" id="tbl_add_txtName" value="" /></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td><input type="button" id="tbl_add_btnAdd" value="Add" /></td>
            </tr>
         </table>
         <br />
         <table cellpadding="2" cellspacing="0" border="0" class="display" id="tbl"> 
            <thead> 
            <tr> 
                <th>ID</th> 
                <th>Name</th> 
                <th>&nbsp;</th>
                <th>&nbsp;</th>
            </tr> 
            </thead> 
            <tbody> 
            <tr> 
                <td colspan="2" class="dataTables_empty">Loading data from server</td> 
            </tr> 
            </tbody> 
        </table>
         <br />
         <table cellpadding="2" cellspacing="0" border="0" class="display" id="tbl_update" style="width:250px;display:none"> 
            <tr>
                <td>Id</td>
                <td><input type="text" id="tbl_update_txtId" value="0" 
                        style="border:solid 1px Black;width:100px;background-color:#D1D1D1" onfocus="this.blur();" /></td>
            </tr>
            <tr>
                <td>Name</td>
                <td><input type="text" id="tbl_update_txtName" value="" /></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td>
                    <input type="button" id="tbl_update_btnUpdate" value="Update" />&nbsp;
                    <input type="button" id="tbl_update_btnCancel" value="Cancel" />
                </td>
            </tr>
         </table>

    </div>
    </form>
</body>
</html>

test2.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Web.Script.Services;

[System.Web.Script.Services.ScriptService]
public partial class jqueryDataTable_test2 : System.Web.UI.Page
{
    protected string isShowGrid = "1";

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    [System.Web.Services.WebMethod()]
    [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)] 
    public static string GetPersonData()
    {
        System.Web.HttpContext context = System.Web.HttpContext.Current;

        context.Response.ContentType = "application/json";
        //--------------------------------------------------------
        // These parameters are sent by the plugin
        var iDisplayLength = (context.Request["iDisplayLength"] != null) ? int.Parse(context.Request["iDisplayLength"]) : 0;
        var iDisplayStart = (context.Request["iDisplayStart"] != null) ? int.Parse(context.Request["iDisplayStart"]) : 0;
        var iSortCol = (context.Request["iSortCol_0"] != null) ? int.Parse(context.Request["iSortCol_0"]) : 0;
        var iSortDir = context.Request["sSortDir_0"];
        var echo = (context.Request["sEcho"] != null) ? int.Parse(context.Request["sEcho"]) : 0;
        //---------------------------------------------------------

        //Fetch the data from a repository
        var persons = Person.GetPersons();

        //order function based on the iSortCol parameter
        Func<Person, object> order = p =>
        {
            if (iSortCol == 0)
            {
                return p.Id;
            }
            return p.Name;
        };

        //order direction based on the iSortDir parameter
        if ("desc" == iSortDir)
        {
            persons = persons.OrderByDescending(order).ToList<Person>();
        }
        else
        {
            persons = persons.OrderBy(order).ToList();
        }


        IEnumerable<Person> li = persons.Select(p => new Person() { Id = p.Id, Name = p.Name }).Skip(iDisplayStart).Take(iDisplayLength);

        List<List<string>> aaList = new List<List<string>>();
        foreach (Person p in li)
        {
            List<string> l = new List<string>();
            l.Add(p.Id.ToString());
            l.Add(p.Name);
            aaList.Add(l);
        }
        //--------------------------------------------------------
        // make an anonymous object for JSON serialization
        var formatedList = new
        {
            sEcho = echo,
            iTotalRecords = persons.Count(),
            iTotalDisplayRecords = persons.Count(),
            aaData = aaList
        };

        var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        var json = serializer.Serialize(formatedList);

        return json;
    }


    [WebMethod]
    public static string DeletePerson(string id)
    {
        return "delete done successfully. ";
    }

    [WebMethod]
    public static string AddPerson(string id ,string name)
    {
        return "add done successfully. ";
    }

    [WebMethod]
    public static string UpdatePerson(Person person)
    {
        return "update done successfully. ";
    }

}
Cheers!

10 comments:

David L said...

These are the most clear and complete examples I've seen for using DataTables with Asp.Net. Thanks a lot!

Jason Robertson said...

Ok you claim it will work with WCF I say it won't, prove me wrong

Saeed said...

thanks mate, check this out:
http://rantdriven.com/post/Using-Datatablesnet-JQuery-Plug-in-with-WCF-Services.aspx

Eran Smith said...

ASP.net and Java have been the most successful languages in the 4th generation languages that have evolved over the years.
http://secureteam.net/Obfuscator.aspx

mahakk01 said...

In this post data tables in ASP.Net described. Jquery is interesting topic. The code is simple so you can try to implement it. This post is very useful. I got idea how to implement jquery in my application. Thanks. Great work guys!
digital signature Adobe

Dagger said...

MAN! You are absolutely the best human alive. You have any idea how many days I wasted trying to get databales to read data from my SQL Server database? Too many! I wish I would have found this sooner. Worked so well I feel stupid now that the solution turned out to be something so simple. Great stuff.

Unknown said...

Man.... You are hero! thanks a lot for this nice, clean and wonderful simple written Demo with source code.

Morgan said...

You are the best. thanks

Unknown said...

You save my programming life.
Thank you very much to this article :).

Unknown said...

How old is the universe

Post a Comment