Tuesday, December 11, 2012

jquery DataTable PlugIn in ASP.Net using C# or jQuery DataTables and ASP.NET Integration

jquery DataTable PlugIn in ASP.Net using C# or jQuery DataTables and ASP.NET Integration





 Updated.... can find download link below

download the jquery datatable plugin from www.datatables.net

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Table5.aspx.cs" Inherits="DT_Pagination.Table5" %>
 
<!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 href="media_ColVis/css/ColVisAlt.css" rel="stylesheet" type="text/css" />--%>
    <link href="media_ColVis/css/ColVis.css" rel="stylesheet" type="text/css" />
    <link href="media/css/TableTools.css" rel="stylesheet" type="text/css" />
    <link href="media/css/TableTools_JUI.css" rel="stylesheet" type="text/css" />
    <link href="Scripts/css/themes/overcast/jquery.ui.theme.css" rel="stylesheet" type="text/css" />
    <link href="Scripts/css/themes/overcast/jquery-ui.css" rel="stylesheet" type="text/css" />
    <link href="Scripts/css/jquery.dataTables_themeroller.css" rel="stylesheet" type="text/css" />
 
 
    <script src="Scripts/js/jquery.js" type="text/javascript"></script>
    <script src="Scripts/js/jquery.dataTables.min.js" type="text/javascript"></script>
    <script src="media/js/ZeroClipboard.js" type="text/javascript"></script>
    <%--<script src="media/js/TableTools.min.js" type="text/javascript"></script>--%>
    <script src="media/js/TableTools.js" type="text/javascript"></script>
    <script src="Scripts/js/jquery.dataTables.columnFilter.js" type="text/javascript"></script>
    <script src="Scripts/js/jquery-ui-1.9.2.custom.min.js" type="text/javascript"></script>
    <script src="Scripts/js/FixedHeader.js" type="text/javascript"></script>
    <script src="media_ColVis/js/ColVis.js" type="text/javascript"></script>
 
 
    <style type="text/css">
        .ui-datepicker-calendar tr.ui-datepicker-calendar td.ui-datepicker-calendar td a.ui-datepicker-calendar th
        {
            font-sizeinherit;
        }
        div.ui-datepicker
        {
            font-size10px;
        }
        .ui-datepicker-title span
        {
            font-size10px;
        }
        
        .my-style-class input[type=text]
        {
            colorgreen;
        }
    </style>
    <script type="text/javascript">
        var oTable;
        $(document).ready(function () {
            $.datepicker.regional[""].dateFormat = 'dd/mm/yy';
            $.datepicker.setDefaults($.datepicker.regional['']);
            
 
            TableTools.DEFAULTS.aButtons = [
       "copy""csv""xls""pdf""print",
       {
           "sExtends""collection",
           "sButtonText""Save",
           "aButtons": [
                         "csv",
                         "xls",
                         //"pdf",
                         {
                            "sExtends""pdf",
                            //"sPdfOrientation": "landscape",
                            "sPdfMessage""Your custom message would go here."
                          },
                          "print"
                          ]
       }]
 
            //TableTools.DEFAULTS.aButtons = [ "copy", "csv", "xls",  "pdf" ]; 
 
 
            /*          Main Functionality       */
 
            $('#tblOscarNominees').dataTable({
                //"oLanguage": { "sSearch": "Search the nominees:" },
                "aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
                "iDisplayLength": 10,
                "aaSorting": [[0, "asc"]],
                "bJQueryUI"true,
                "bAutoWidth"false,
                "bProcessing"true,
                "sDom"'<"top"i><"title">lt<"bottom"pf>',
                "sPaginationType""full_numbers",
 
                //Scrolling--------------
                //"sScrollY": "250px",
                //"sScrollX": "100%",                
                //"sScrollXInner": "100%",
                //"bScrollCollapse": true,
 
                // ----  Print_Export_Copy  ----                
                "sDom"'T<"clear"><"H"lfr>t<"F"ip>',
                //"sDom": '<"top"iflp<"clear">>rt<"bottom"iflp<"clear">>',                            
 
                // ----- Column Visiblity ------                
                //"sDom": '<"H"Cfr>t<"F"ip>',
                "oColVis":
                {
                    //"sDom": 'C<"clear">lfrtip',
                    "activate""mouseover",                    
              //"bJQueryUI": true
                },
 
 
 
                //--- Dynamic Language---------
                "oLanguage": {
                    "sZeroRecords""There are no Records that match your search critera",
                    "sLengthMenu""Display _MENU_ records per page&nbsp;&nbsp;",
                    "sInfo""Displaying _START_ to _END_ of _TOTAL_ records",
                    "sInfoEmpty""Showing 0 to 0 of 0 records",
                    "sInfoFiltered""(filtered from _MAX_ total records)",
                    "sSearch""Search all columns:"
                },
 
                /* Column Sorting And Searching */
                //                "aoColumns": [
                //        { "bSearchable": false }, //Disable search on this column 1
                //        {"bSortable": false }, //Disable sorting on this column 2               
                //        {"asSorting": ["asc"] }, //Allow only "asc" sorting on column 2
                //        null,
                //        { "sSortDataType": "dom-text", "sType": "numeric" },
                //        { "iDataSort": 4 }, //Use column 4 to perform sorting
                //        null,
                //        null
                //        ],
 
 
                /*  Column Visibilities */
                //    "aoColumns": [
                //                /* Sno */{"bSearchable": false, "bVisible": false},
                //       /* Engine */   null,
                //       /* Browser */  null,
                //       /* Platform */ { "bSearchable": false, "bVisible":    false },
                //       /* Version */  { "bSearchable": false, "bVisible":    false },
                //                /* Grade */     null,
                //                            /* Share */    null,
                //                            /* Date */    null
                //      ],
 
                "oSearch": {
                    "sSearch""",
                    "bRegex"false,
                    "bSmart"true
                },
 
                //------------------------Total in footer                
                "fnFooterCallback"function TotalCalc(nRow, aaData, iStart, iEnd, aiDisplay) {
                    /* Calculate the total market share for all browsers in this table (ie inc. outside the pagination) */
                    var iTotalMarket = 0;
                    for (var i = 0; i < aaData.length; i++) {
                        //alert('Length : ' + aaData.length + ', Row No : ' + i + ', Share : ' + aaData[i][6]);
                        iTotalMarket += parseInt(aaData[i][6]);
                    }
 
                    /* Calculate the market share for browsers on this page */
                    var iPageMarket = 0;
                    for (var i = iStart; i < iEnd; i++) {
                        iPageMarket += parseInt(aaData[aiDisplay[i]][6]);
                        //alert('Length : ' + iStart + ', Row No : ' + i + ', Share : ' + aaData[aiDisplay[i]][6] + 'Total : ' + iPageMarket);
                    }
 
                    /* Modify the footer row to match what we want */
                    var nCells = nRow.getElementsByTagName('th');
                    nCells[1].innerHTML = parseInt(iPageMarket * 100) / 100 +
    '% (' + parseInt(iTotalMarket * 100) / 100 + '% Total)';
                } // End of Footer Footer
            });
 
            // ------- Header Buttons -----------
            $('<a id="btnDelete" style="padding: 0px; display:none;" class="ui-button ui-widget ui-state-default ui-corner-all'
            + 'ui-button-text-only" href="javascript:void(0)"><span style="font-size: small; padding: 2px 5px;"'
            + 'class="ui-button-text"> Delete selected Row</span></a>&nbsp;&nbsp;<button id="refresh">Refresh</button>').appendTo('div.dataTables_length');
            //$('<button id="refresh">Refresh</button>').appendTo('div.dataTables_length'); //ReFresh Button
 
 
 
            $("table#tblOscarNominees").dataTable().columnFilter(
                {
                    //sPlaceHolder: "foot:before",
                    "aoColumns": [
                                    null//{ "type": "number-range" },
                                    {"type""text", width: "50px" },
                                    { "type""select" },
                                    { "type""text" }, //null, //{ "type": "date-range", width: "50px" },
                                    {"type""number-range", width: "50px" },
                                    { "type""select" },
                                    { "type""select" },
                                    { "type""date-range"}   //{ "type": "date-range", width: "50px" },
                                    ]
                });
 
            // -------------  Fixed Header   -------------
            oTable = $('#tblOscarNominees').dataTable();
            new FixedHeader(oTable);
 
            //$('#tblOscarNominees div.title').text("This is a table title");
 
            /* Add a click handler to the rows - this could be used as a callback */
            $("#tblOscarNominees tbody tr").click(function (e) {
                if ($(this).hasClass('row_selected')) {
                    $(this).removeClass('row_selected');
                    $('#btnDelete').hide();
                }
                else {
                    oTable.$('tr.row_selected').removeClass('row_selected');
                    $(this).addClass('row_selected');
                    $('#btnDelete').show();
                }
            });
 
            /* Add a click handler for the delete row */
            $('#btnDelete').click(function () {
                var anSelected = fnGetSelected(oTable);
                if (anSelected.length !== 0) {
                    /* Nedd Ajax Call To perform in serverSide*/
                    if (confirm('Are you sure you wish to delete this row?')) {
                        /* do the delete */
                        oTable.fnDeleteRow(anSelected[0]);
                    }
                    else {
                        $("#tblOscarNominees tbody tr").removeClass('row_selected');
                        $('#btnDelete').hide();
                    }
                }
            });
 
            $.fn.dataTableExt.oStdClasses["filterColumn"] = "my-style-class";
 
            $('#tblOscarNominees tbody td').click(function () {
                /* Get the position of the current data from the node */
                var aPos = oTable.fnGetPosition(this);
                var aData = oTable.fnGetData(aPos[0]);
                //alert(aData[0]);
            });
 
            /* Init the table */
            oTable = $('#tblOscarNominees').dataTable();
        });
 
        function fnGetSelected(oTableLocal) {
            return oTableLocal.$('tr.row_selected');
        }
        //$("div.tools").html('Organize by: <select id="booking_status"><option value="">All Bookings</option><option value="confirmed">Upcoming</option><option value="arrived">Arrived</option><option value="rejected">Rejected</option></select>');
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div style="width1250pxpadding5px;">
        <div class="Shadow">
            <asp:Repeater ID="rptOscarNominees" runat="server">
                <HeaderTemplate>
                    <table id="tblOscarNominees" cellpadding="0" cellspacing="0" border="0" class="display">
                        <thead>
                            <tr>
                                <th>
                                    S.No
                                </th>
                                <th>
                                    Rendering Engine
                                </th>
                                <th>
                                    Browser
                                </th>
                                <th>
                                    PlotForm
                                </th>
                                <th>
                                    Engine Version
                                </th>
                                <th>
                                    CSS Grade
                                </th>
                                <th>
                                    Market Share(%)
                                </th>
                                <th>
                                    Released
                                </th>
                            </tr>
                        </thead>
                        <tbody class="tbody">
                </HeaderTemplate>
                <ItemTemplate>
                    <tr>
                        <td>
                            <%# Eval("id"%>
                        </td>
                        <td>
                            <%# Eval("engine"%>
                        </td>
                        <td>
                            <%# Eval("browser"%>
                        </td>
                        <td>
                            <%# Eval("platform"%>
                        </td>
                        <td>
                            <%# Eval("version"%>
                        </td>
                        <td>
                            <%# Eval("grade"%>
                        </td>
                        <td>
                            <%# Eval("marketshare"%>
                        </td>
                        <td>
                            <%# Eval("RDate")%>
                        </td>
                    </tr>
                </ItemTemplate>
                <FooterTemplate>
                    </tbody>
                    <tfoot>
                        <tr>
                            <th style="text-alignright" colspan="5">
                                Total:
                            </th>
                            <th colspan="2" align="left">
                            </th>
                            <th colspan="1" align="right">
                            </th>
                        </tr>
                        <tr>
                            <th>
                                S.No
                            </th>
                            <th>
                                Rendering Engine
                            </th>
                            <th>
                                Browser
                            </th>
                            <th>
                                PlotForm
                            </th>
                            <th>
                                Engine Version
                            </th>
                            <th>
                                CSS Grade
                            </th>
                            <th>
                                Market Share
                            </th>
                            <th>
                                Release Date
                            </th>
                        </tr>
                    </tfoot>
                    </table>
                </FooterTemplate>
            </asp:Repeater>
        </div>
    </div>
    </form>
</body>
</html>

Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
 
namespace DT_Pagination
{
    public partial class Table5 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string strConnect = "server=.\\MYDATABASE; user id=sa; pwd=*****; database=aspdotnetDB;";
 
            DataSet dataset = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("select *,convert(varchar(10),released,103) as RDate from ajax", strConnect);
            da.Fill(dataset, "ajax");
 
            rptOscarNominees.DataSource = dataset;
            rptOscarNominees.DataBind();
        }
    }
}
 
Download from HERE 

4 comments:

  1. Hi,

    Could you please help me with adding the filter columns (on footer) on top of the table (above the header).

    ReplyDelete
    Replies
    1. Sorry for the inconvenience ...

      check out this link it may help U

      http://www.datatables.net/extras/thirdparty/ColumnFilterWidgets/DataTables/extras/ColumnFilterWidgets/

      and

      http://jquery-datatables-column-filter.googlecode.com/svn/trunk/dateRange.html

      Delete
  2. Vara,

    Excellent post. I followed it and you made my day.

    When I populate the repeater with more than 900, the page stops responding for minutes. Do I need to follow some other technique for large data sets?
    -Gyan

    ReplyDelete
    Replies
    1. Hi U need some other technique to increase the speed...
      I too had the same issue....

      I had seen many posts but none of them was help full

      Delete