Wednesday, November 26, 2014

Datatable Plugin implement Asp Net

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table.
DataTable jquery is very effective plugin for tabular data such as gridview in asp.net. In this example we have learn how to implement the datatable jquery plugin in asp.net. So follow the example
Database Stuff:  so I am created the table tmt_user in oracle
CREATE TABLE TMT_USER
(
  PK_USER_ID                  NUMBER,
  PBX_ID                      NUMBER,
  PASSWORD                    VARCHAR2(128 BYTE),

  USER_NAME                   VARCHAR2(500 BYTE),
  DESIGNATION                 VARCHAR2(500 BYTE),
  DOJ                         DATE,
  REPORTING_HEAD_ID           NUMBER,
  LAST_ACTIVITY_DATE          DATE,
  LAST_LOGIN_DATE             DATE,
  LAST_PASSWORD_CHANGED_DATE  DATE,
  IS_LOCKED_OUT               VARCHAR2(100 BYTE),
  LAST_LOCKED_OUT_DATE        DATE,
  ACTIVE_STATUS               NUMBER,
  SORT_ORDER                  NUMBER,
  CREATED_BY                  NUMBER,
  CREATED_DATE                DATE,
  MODIFIED_BY                 NUMBER,
  MODIFIED_DATE               DATE,
  IS_LOGIN                    NUMBER            DEFAULT 0,
  FK_CIRCLE_ID                NUMBER,
  FK_FORM_ID                  VARCHAR2(30 BYTE)
)
Insert records
Insert into WPD.TMT_USER
   (PK_USER_ID, PBX_ID, USER_NAME, DESIGNATION, DOJ, ACTIVE_STATUS, CREATED_BY, CREATED_DATE, PASSWORD, IS_LOGIN, FK_CIRCLE_ID, FK_FORM_ID)
Values
   (571, 240109, 'Ratna_Kotwani', 'Agent', TO_DATE('02/22/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 433, TO_DATE('03/05/2014 12:53:25', 'MM/DD/YYYY HH24:MI:SS'), 'Pass123', 0, 4, '1');
Insert into WPD.TMT_USER
   (PK_USER_ID, PBX_ID, USER_NAME, DESIGNATION, ACTIVE_STATUS, CREATED_BY, CREATED_DATE, PASSWORD, IS_LOGIN, FK_CIRCLE_ID, FK_FORM_ID)
Values
   (572, 240100, 'Santosh Koli', 'Agent', 1, 433, TO_DATE('03/05/2014 12:53:25', 'MM/DD/YYYY HH24:MI:SS'), 'Pass123', 0, 4, '1');
Insert into WPD.TMT_USER
   (PK_USER_ID, PBX_ID, USER_NAME, DESIGNATION, ACTIVE_STATUS, CREATED_BY, CREATED_DATE, PASSWORD, IS_LOGIN, FK_CIRCLE_ID, FK_FORM_ID)
Values
   (573, 240098, 'Shubham Rane', 'Agent', 1, 433, TO_DATE('03/05/2014 12:53:25', 'MM/DD/YYYY HH24:MI:SS'), 'Pass123', 0, 4, '1');
So here I have completed the database stuff. now we can go for asp.net application
ASPX Page: you need datatable and jquery scripting library .you can download the library “datatable.net”.  jquery and jquery ui theme download from jquery and jquery ui site
      <script src="/Scripts/jquery-1.9.1.min.js " type="text/javascript"></script>
  
    <script src="/Scripts/jquery.dataTables.js" type="text/javascript"></script>
    <link rel="stylesheet" href="/css/style.css" />
    <link href="/media/css/jquery-ui-1.9.2.custom.css" rel="stylesheet" type="text/css" />
    <link href="/media/css/jquery.dataTables_themeroller.css" rel="stylesheet" type="text/css" />
    <link href="/Content/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
    <script src="/Scripts/jquery-ui-1.10.3.min.js" type="text/javascript"></script>

<script type="text/javascript">

        $(function () {

            var oTable = $('#example').dataTable({
                "sPaginationType": "full_numbers",
                "bJQueryUI": true,
                "bProcessing": true,
                "bServerSide": true,
               "sAjaxSource": "datatable.ashx",
                "aoColumnDefs": [
        {

            "mRender": function (data, type, row) {
                return '<a class="Upd" href= "UserEdit.aspx?UserId=' + data + '" >Update</a> | <a onclick="resetPassword(' + data + ' )" class="ResetPwd" dat-val= "' + data + '" >Reset Password</a>';
            },
            "aTargets": [7], "bSortable": false
        },
        {
            "aTargets": [6], "bSortable": false
        }
                ]

            });
        }
        )

    </script>
<table cellpadding="0" class="dataTable" cellspacing="0" border="0" id="example">
            <thead>
                <tr>
                    <th width="8%">pbx ID
                    </th>
                    <th width="15%">Name
                    </th>
                    <th width="15%">Designation
                    </th>
                    <th width="15%">Date Of Joining
                    </th>
                    <th width="10%">Roles
                    </th>
                    <th width="10%">Status
                    </th>
                    <th width="15%">Created Date
                    </th>
                    <th>Action
                    </th>
                </tr>
            </thead>
           <tbody>
            </tbody>
        </table>

datatable.ashx : in datatable scripting we have seen set sAjaxSource =” datatable. Ashx” . here is the code this file
<%@ WebHandler Language="C#" Class="datatable" %>

using System;
using System.Web;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Text;

public class datatable : IHttpHandler, System.Web.SessionState.IRequiresSessionState
{

    public void ProcessRequest(HttpContext context) {
        context.Response.ContentType = "application/json";

        var conn = new OracleConnection("your connection string"//Utility.Conn);
        try
        {
          
            int echo = Int32.Parse(context.Request.Params["sEcho"]);
            int displayLength = Int32.Parse(context.Request.Params["iDisplayLength"]);
            int displayStart = Int32.Parse(context.Request.Params["iDisplayStart"]);
            string search = context.Request.Params["sSearch"];

            ///////////
            //SEARCH (filter)
            //- build the where clause
            ////////
            var sb = new StringBuilder();
            string whereClause = string.Empty;
            if (!String.IsNullOrEmpty(search))
            {
                sb.Append(" WHERE pbx_id LIKE '%");
                sb.Append(search.ToUpper());
                sb.Append("%' OR  upper(user_name) LIKE '%");
                sb.Append(search.ToUpper());
                sb.Append("%' OR upper(DESIGNATION) LIKE '%");
                sb.Append(search.ToUpper());
                sb.Append("%' OR DOJ LIKE '%");
                sb.Append(search);
                sb.Append("%'");
                whereClause = sb.ToString();
            }


            sb.Clear();
            //Check which column is to be sorted by in which direction
            for (int i = 0; i < 11; i++)
            {
                if (context.Request.Params["bSortable_" + i] == "true")
                {
                    sb.Append(context.Request.Params["iSortCol_" + i]);
                    sb.Append(" ");
                    sb.Append(context.Request.Params["sSortDir_" + i]);
                }
            }
            string orderByClause = sb.ToString();
            //Replace the number corresponding the column position by the corresponding name of the column in the database
            if (!String.IsNullOrEmpty(orderByClause))
            {
                orderByClause = orderByClause.Replace("0", ", pbx_id");
                orderByClause = orderByClause.Replace("1", ", user_name");
                orderByClause = orderByClause.Replace("2", ", DESIGNATION");
                orderByClause = orderByClause.Replace("3", ", DOJ");
                orderByClause = orderByClause.Replace("4", ", ACTIVE_STATUS");
                orderByClause = orderByClause.Replace("5", ", CREATED_DATE");
                //Eliminate the first comma of the variable "order"
                orderByClause = orderByClause.Remove(0, 1);
            }
            else
                orderByClause = "pbx_id pk_user_id desc";
            orderByClause = "ORDER BY " + orderByClause;

            /////////////
            //T-SQL query
            //- ROW_NUMBER() is used for db side pagination
            /////////////
            sb.Clear();
           
            string query = @"SELECT * FROM
                        (SELECT rownum  AS RowNumber,TotalDisplayRows,TotalRows,pbx_id,user_name,DESIGNATION,DOJ,user_role,ACTIVE_STATUS,CREATED_DATE,PK_USER_ID,CIRCLE_NAME FROM
                           (SELECT ( SELECT COUNT(*)FROM tmt_user U inner join tmt_circle C  on C.PK_CIRCLE_ID = U.FK_CIRCLE_ID  {1} )AS TotalDisplayRows,
                         ( SELECT COUNT(*)FROM tmt_user U inner join tmt_circle C  on C.PK_CIRCLE_ID = U.FK_CIRCLE_ID  where 1=1  )AS TotalRows,pbx_id,user_name,DESIGNATION,DOJ,
                            (select  listagg (MASTERROLE.ROLE_NAME , '-') WITHIN GROUP (ORDER BY MASTERROLE.ROLE_NAME) Role from  TMT_USER_IN_ROLE UserRole inner join TMT_USER_ROLE MasterRole on UserRole.FK_USER_ROLE_ID=MasterRole.PK_USER_ROLE_ID where UserRole.FK_USER_ID=PK_USER_ID) user_role,
                             ACTIVE_STATUS, CREATED_DATE,PK_USER_ID , (select circle_name from tmt_circle where pk_circle_id= fk_circle_id  ) CIRCLE_NAME FROM  tmt_user {1}  {0}  ) RawResults  where 1=1 ) Results
                           WHERE  RowNumber BETWEEN {2} AND {3}";


            ;


            query = String.Format(query, orderByClause, whereClause, displayStart + 1, displayStart + displayLength);
            //Get result rows from DB

            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            IDataReader rdrBrowsers = cmd.ExecuteReader();


            sb.Clear();
            int totalDisplayRecords = 0;
            int totalRecords = 0;
            while (rdrBrowsers.Read())
            {
                if (totalRecords == 0)
                    totalRecords = Int32.Parse(rdrBrowsers["TotalRows"].ToString());
                if (totalDisplayRecords == 0)
                    totalDisplayRecords = Int32.Parse(rdrBrowsers["TotalDisplayRows"].ToString());
                sb.Append("[");
                sb.Append("\"" + rdrBrowsers["pbx_id"] + "\",");
                sb.Append("\"" + rdrBrowsers["user_name"] + "\",");
                sb.Append("\"" + rdrBrowsers["DESIGNATION"] + "\",");

                if (!string.IsNullOrEmpty(rdrBrowsers["DOJ"].ToString()))
                    sb.Append("\"" + Convert.ToDateTime(rdrBrowsers["DOJ"].ToString()).ToString("MM/dd/yyyy") +
                              "\",");
                else
                    sb.Append("\"" + rdrBrowsers["DOJ"] + "\",");

                sb.Append("\"" + rdrBrowsers["user_role"] + "\",");
                sb.Append("\"" + rdrBrowsers["ACTIVE_STATUS"] + "\",");

                if (!string.IsNullOrEmpty(rdrBrowsers["CREATED_DATE"].ToString()))
                    sb.Append("\"" +
                              Convert.ToDateTime(rdrBrowsers["CREATED_DATE"].ToString()).ToString("MM/dd/yyyy") +
                              "\",");
                else
                    sb.Append("\"" + rdrBrowsers["CREATED_DATE"] + "\",");


                sb.Append("\"" + rdrBrowsers["PK_USER_ID"] + "\"");

                //

                sb.Append("],");
            }
            var outputJson = sb.ToString();
            if (outputJson.Length > 0)
                outputJson = outputJson.Remove(outputJson.Length - 1);
            sb.Clear();

            cmd.Dispose();
            sb.Append("{");
            sb.Append("\"sEcho\": ");
            sb.Append(echo);
            sb.Append(",");
            sb.Append("\"iTotalRecords\": ");
            sb.Append(totalRecords);
            sb.Append(",");
            sb.Append("\"iTotalDisplayRecords\": ");
            sb.Append(totalDisplayRecords);
            sb.Append(",");
            sb.Append("\"aaData\": [");
            sb.Append(outputJson);
            sb.Append("]}");
            outputJson = sb.ToString();


           context.Response.Clear();
           context.Response.ClearHeaders();
           context.Response.ClearContent();
           context.Response.Write(outputJson);
           context.Response.Flush();
           context.ApplicationInstance.CompleteRequest();
        }
        finally
        {

            conn.Close();
            conn.Dispose();

        }

       
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}
So check out that example .if you need help contact me email “anilsharma1983.blog@gmail.com”

No comments:

Post a Comment

http://blogsiteslist.com