Sunday, October 3, 2010

Simplest way to use JQuery DataTable in ASP.net (C#)

Hi All,

Today i will be using JQuery dataTable in ASP.net c# code without any 'bProcessing' , 'bServerSide' and 'sAjaxSource'.

First of All what is Jquery Datatable, just Click and find out :).

After getting the basic structure of JQuery datatable you may find that all the server side examples on site is in PHP and the stuff done for ASP.net seems so difficult nor such newbies like me. so lets talk about a simpler solution.

First we create a simple HTML table. and the place where data will get populated just add an ASP Literal control. :)

<table class="datatable">
                        <thead>
                            <tr>
                                <th class="bSortable">
                                    <input type="checkbox" class="checkbox select-all" />
                                </th>
                                <th>
                                    First Name
                                </th>
                                <th>
                                    Middle Name
                                </th>
                                <th>
                                    Last Name
                                </th>
                               </tr>
                        </thead>
                        <tbody>
                            <asp:Literal runat="server" ID="ltData"></asp:Literal>
                        </tbody>
                    </table>


Now on the server side write a simple function to populate the literal with required data.

private void loadGrid()
    {
        StringBuilder sb = new StringBuilder();
        DataTable dt = MyData.GetAllUserData();
        if (dt != null)
            if (dt.Rows.Count > 0)
                foreach (DataRow dr in dt.Rows)
                {

                    sb.Append("<tr>");
                    sb.Append("<td>");
                    sb.Append("<input type='checkbox' class='checkbox' />");
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchFirstName"]));
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchMiddleName"]));
                    sb.Append("</td>");
                    sb.Append("<td>");
                    sb.Append((dr["vchLastName"]));
                    sb.Append("</td>");
                   
                    sb.Append("</tr>");

                }

        ltData.Text = sb.ToString();

    }

For initializing the data table you can refer to the site or use the following code and write it in document.ready() function of jQuery.

function LoadTable() {
 $('.datatable').dataTable({
  'bLengthChange': true,
  'bPaginate': true,
  'sPaginationType': 'full_numbers',
  'iDisplayLength': 5,
  'bInfo': false,
  'oLanguage': 
  {
   'sSearch': 'Search all columns:',
   'oPaginate': 
   {
    'sNext': '&gt;',
    'sLast': '&gt;&gt;',
    'sFirst': '&lt;&lt;',
    'sPrevious': '&lt;'
   }
  }
 });
}

At this point we are done with the basic grid view with just text data but what if we need to perform some functions like delete a particular row, Just don't worry about it create a webservice and write the deletion function in that service then use the following code to call it using jQuery.

function DeleteMe(string IdToBeDeleted){
var webMethod = 'DeleteRecord.asmx/DeleteUser'
var parameters = "{'ID':'" + IdToBeDeleted + "'}"

$.ajax({
        type: "POST",
        url: webMethod,
        data: parameters,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function(msg) {    
                alert('Success Deletion');
        },
        error: function(e){
                 alert('Failure Deletion');                         
        }
});

and for Datatable just create an other column and call this DeleteMe function with RowId as parameter to it and a nice deletion will occur.

Hope it helps.

Happy coding....

4 comments:

Salman ansari said...

thax a lot..but i want to perform sorting in my datatable..so can u tell me how it possible using your code

Unknown said...

if you are loading the complete Dataset at one time then "bsorting" : "true" would do he job but if you need to do custom sorting from Database with custom pagination then find my posts

http://usmanshabbir.blogspot.com/2010/12/extending-jquery-datatable-with-server.html

and this procedure of you are using mySQL at backend.

http://usmanshabbir.blogspot.com/2010/12/paging-sorting-searching-in-mysql.html

for SQL server you can find many procedures that can do custom sorting.

Hope it helps.

Johny Bravo said...

Hi, its a really good article.
You have shown how to delete.But what after I delete that row, I need to bind the table again from database.
How to hide the last deleted row?

Vara said...

Hi this may help some, this is having more options...

for Gridview
http://www.reddyinfosoft.blogspot.in/2012/12/jquery-datatable-plugin-in-aspnet-using_15.html

for repeater
http://www.reddyinfosoft.blogspot.in/2012/12/jquery-datatable-plugin-in-aspnet-using.html