In code behind data_users.aspx
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
public partial class data_tables_data_users : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
data_users();
}
void data_users()
{
int e = Int32.Parse(Request.Params["sEcho"]);
int tampil_length = Int32.Parse(Request.Params["iDisplayLength"]);
int tampil_awal = Int32.Parse(Request.Params["iDisplayStart"]);
string cari = Request.Params["sSearch"];
StringBuilder sb = new StringBuilder();
string w = string.Empty;
if (!String.IsNullOrEmpty(cari))
{
sb.Append(" WHERE id LIKE '%");
sb.Append(cari);
sb.Append("%' OR username LIKE '%");
sb.Append(cari);
sb.Append("%' OR password LIKE '%");
sb.Append(cari);
sb.Append("%' OR email LIKE '%");
sb.Append(cari);
sb.Append("%' OR level LIKE '%");
sb.Append(cari);
sb.Append("%' OR namalengkap LIKE '%");
sb.Append(cari);
sb.Append("%'");
w = sb.ToString();
}
sb.Clear();
string urutan = string.Empty;
for (int i = 0; i < 11; i++)
{
if (Request.Params["bSortable_" + i] == "true")
{
sb.Append(Request.Params["iSortCol_" + i]);
sb.Append(" ");
sb.Append(Request.Params["sSortDir_" + i]);
}
}
urutan = sb.ToString();
if (!String.IsNullOrEmpty(urutan))
{
urutan = urutan.Replace("0", ", id");
urutan = urutan.Replace("1", ", username");
urutan = urutan.Replace("2", ", password");
urutan = urutan.Replace("3", ", email");
urutan = urutan.Replace("4", ", level");
urutan = urutan.Replace("5", ", namalengkap");
urutan = urutan.Remove(0, 1);
}
else
urutan = "id ASC";
urutan = "ORDER BY " + urutan;
sb.Clear();
string q = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT COUNT(*) FROM t_users {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM t_users) AS TotalRows,id,username,password,email,level,namalengkap FROM t_users {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND {3}";
q = String.Format(q, urutan, w, tampil_awal + 1, tampil_awal + tampil_length);
SqlConnection koneksi = new
SqlConnection(ConfigurationManager.ConnectionStrings["koneksi_material"].ConnectionString);
koneksi.Open();
SqlCommand cmd = koneksi.CreateCommand();
cmd.CommandText = q;
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
sb.Clear();
string output = string.Empty;
int total_data_tampil = 0;
int total_data = 0;
while (dr.Read())
{
if (total_data == 0)
total_data = Int32.Parse(dr["TotalRows"].ToString());
if (total_data_tampil == 0)
total_data_tampil = Int32.Parse(dr["TotalDisplayRows"].ToString());
sb.Append("[");
sb.Append("\"" + dr["id"] + "\",");
sb.Append("\"" + dr["username"] + "\",");
sb.Append("\"" + dr["password"] + "\",");
sb.Append("\"" + dr["email"] + "\",");
sb.Append("\"" + dr["level"] + "\",");
sb.Append("\"" + dr["namalengkap"] + "\"");
sb.Append("],");
}
output = sb.ToString();
output = output.Remove(output.Length - 1);
sb.Clear();
sb.Append("{");
sb.Append("\"sEcho\": ");
sb.Append(e);
sb.Append(",");
sb.Append("\"iTotalRecords\": ");
sb.Append(total_data);
sb.Append(",");
sb.Append("\"iTotalDisplayRecords\": ");
sb.Append(total_data_tampil);
sb.Append(",");
sb.Append("\"aaData\": [");
sb.Append(output);
sb.Append("]}");
output = sb.ToString();
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Write(output);
Response.Flush();
Response.End();
}
}In view_users.aspx (data tables syntax)
<%@ Page Title = "" Language="C#" MasterPageFile="~/media_admin.master" AutoEventWireup="true" CodeFile="View_Users.aspx.cs" Inherits="View_Users" %>
<asp:Content ID = "Content1" ContentPlaceHolderID="konten_tengah_admin" Runat="Server">
<form id = "form1" runat="server">
<div class='container_12' id='content-wrapper'>
<div id = 'main_content' >
< h2 class='grid_12'><asp:Button ID = "btnAdd" runat="server" Text="Add Users" PostBackUrl="~/Add_Users.aspx"/></h2>
<div class='clear'></div>
<div class='grid_12'>
<div class='box'>
<div class='header'>
<img src = 'img/icons/packs/fugue/16x16/shadeless/table-excel.png' width='16' height='16' alt=""/>
<h3>Users Management</h3>
</div>
<div class='content'>
<table id = 'table-users' class="table">
<thead>
<tr>
<th width = "100" > ID </ th >
< th width="100">Username</th>
<th width = "100" > Password </ th >
< th width="100">Email</th>
<th width = "100" > Level </ th >
< th width="100">Full Name</th>
<th width = "100" > Option </ th >
</ tr >
</ thead >
< tbody >
< tr class='gradeX'>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
</div>
<div class='clear'></div>
</div>
</div>
<div class='clean'></div>
</div>
</div>
</form>
</asp:Content>In javascript (client side)
$(document).ready(function ()
{
$('#table-users').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"bSortClasses": true,
"bStateSave": true,
"bPaginate": true,
"bDeferRender": true,
"sAjaxSource": "data_tables/data_users.aspx",
"aoColumns": [
null,
null,
null,
null,
null,
null,
{ "fnRender": function(oObj) {
var users_id = oObj.aData[0];
return '<a href=Edit_Users.aspx?id=' + users_id + ' title="Click for edit data"><img src="images/edit.png"></a> | <a href=Delete_Users.aspx?id=' + users_id + ' title="Click for delete data"><img src="images/delete.png"></a>';
}
}
]
});
});