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

$(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>

<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”

Vnext C# -Programming Blog

Pages

Wednesday, November 26, 2014

Datatable Plugin implement Asp Net

No comments:

Post a Comment

Afflate links