Sunday, December 16, 2012

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

Previously i had been shown how to use JQuery DAtaTable for repeater and now i will show how to use it for GridView in asp.net


















<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridTable.aspx.cs"  
Inherits="DT_Pagination.GridTable" %>
 
<!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 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/smoothness/jquery-ui.css" rel="stylesheet" type="text/css" />
    <link href="Scripts/css/themes/smoothness/jquery.ui.theme.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       */
 
            $('#GridView1').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",
                "bRetrieve"true,
 
                //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)",
                    "sEmptyTable"'No Rows to Display.....!',
                    "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('td');
                    nCells[0].innerHTML = 'Total : '+parseInt(iPageMarket * 100) / 100 +
    '% (' + parseInt(iTotalMarket * 100) / 100 + '% Grand 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#GridView1").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 = $('#GridView1').dataTable();
//            new FixedHeader(oTable);
 
            //$('#GridView1 div.title').text("This is a table title");
 
            /* Add a click handler to the rows - this could be used as a callback */
            $("#GridView1 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 {
                        $("#GridView1 tbody tr").removeClass('row_selected');
                        $('#btnDelete').hide();
                    }
                }
            });
 
            $.fn.dataTableExt.oStdClasses["filterColumn"] = "my-style-class";
 
            $('#GridView1 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 = $('#GridView1').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 class="Shadow">
        <asp:GridView ID="GridView1" runat="server" OnPreRender="GridView1_PreRender" 
             ShowFooter="true" AutoGenerateColumns="false">
            <Columns>                
                <asp:TemplateField HeaderText="S.No">
                    <ItemTemplate>
                        <%# Eval("id"%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Engine">
                    <ItemTemplate>
                        <%# Eval("engine")%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Browser">
                    <ItemTemplate>
                        <%# Eval("browser")%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Platform">
                    <ItemTemplate>
                        <%# Eval("platform")%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Version">
                    <ItemTemplate>
                        <%# Eval("version")%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Grade">
                    <ItemTemplate>
                        <%# Eval("grade")%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Market Share">
                    <ItemTemplate>
                        <%# Eval("marketshare")%>
                    </ItemTemplate>
                </asp:TemplateField>                
                <asp:TemplateField HeaderText="Date">
                    <ItemTemplate>
                        <%# Eval("RDate")%>
                    </ItemTemplate>                   
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </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 GridTable : 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");
 
            GridView1.DataSource = dataset;
            GridView1.DataBind();
        }
 
        protected void GridView1_PreRender(object sender, EventArgs e)
        {
            GridView1.UseAccessibleHeader = false;
            GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
            GridView1.FooterRow.TableSection = TableRowSection.TableFooter;
            int CellCount = GridView1.FooterRow.Cells.Count;
            GridView1.FooterRow.Cells.Clear();
            GridView1.FooterRow.Cells.Add(new TableCell());
            GridView1.FooterRow.Cells[0].ColumnSpan = CellCount - 1;
            GridView1.FooterRow.Cells[0].HorizontalAlign = HorizontalAlign.Right;
            GridView1.FooterRow.Cells.Add(new TableCell());
 
            TableFooterRow tfr = new TableFooterRow();
            for (int i = 0; i < CellCount; i++)
            {
                tfr.Cells.Add(new TableCell());
                //tfr.Cells[i].i
                //tfr.Cells[i].ColumnSpan = CellCount;
                //tfr.Cells[0].Text = "Footer 2";
            }
            GridView1.FooterRow.Controls[1].Controls.Add(tfr);
        }
    }
}
 
Download from HERE 

6 comments:

  1. I downloaded your example and only selects the row on the first page, in the other does not work. What to do?

    ReplyDelete
  2. I downloaded your example and only selects the row on the first page, in the other does not work. What to do?

    ReplyDelete
  3. Thanks a lot for this wonderful post

    ReplyDelete
  4. Thanks so much for that post,
    But can you please provide one example how to use it in child page and master page.
    Thanks a lot in advance for your help.

    ReplyDelete
    Replies
    1. use the script in the child page it will work.

      Delete
  5. can you give one example how to use this in child and master page please..

    ReplyDelete