Show Hide Rows of jQuery DataTables in Asp.net
(Hide/show rows of jQuery DataTables in Asp.net) – Khi hiển thị dữ liệu với những bảng có nhiều trường, do màn hình có hạn nên không thể hiển thị hết được thông tin của tất cả các trường. Có một cách thông thường để giải quyết vấn đề này đó là kích và xem chi tiết. Với cách này thì người lập trình sẽ phải tạo thêm 1 trang khác để hiển thị thông tin dữ liệu, vậy có cách nào để có thể hiển thị dữ liệu ngay trên màn hình của danh sách không? Bài viết dưới đây sẽ hướng dẫn các bạn cách để ẩn/hiện thông tin chi tiết 1 dòng dữ liệu.
- B1: Download CSDL Northwind tại đây và thực hiện công việc Restore Data.
End Namespace
- B3: Mở file Site.Master dạng HTML và bổ xung đoạn mã phía dưới trong thẻ Head
- B2: Tạo Project trong Microsoft Visual Studio 2010
Trong Visual Studio tạo 1 Class có tên: Utility và nhập đoạn Code phía dưới cho Class này.
C# Code
using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace ShowHideRowsofjQueryDataTables
{
public class SqlDataProvider
{
#region
"Membres Prives"
private string
_connectionString;
#endregion
#region
"Constructeurs"
public SqlDataProvider()
{
try
{
_connectionString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
}
catch
{
}
}
#endregion
#region
"Proprietes"
public string
ConnectionString
{
get { return
_connectionString; }
}
#endregion
#region
"Functions"
public DataTable
FillTable(string sql)
{
try
{
DataTable tb = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(sql,
_connectionString);
adap.Fill(tb);
return tb;
}
catch
{
return null;
}
}
#endregion
}
public class Customers
{
public string
CustomerID { get; set;
}
public string
CompanyName { get; set;
}
public string
ContactName { get; set;
}
public string
ContactTitle { get; set;
}
public string Address { get;
set; }
public string City { get; set; }
public string Country
{ get; set; }
public string
PostalCode { get; set;
}
}
}
VB.NET Code
Imports System.Data.SqlClient
Imports System.Data
Namespace ShowHideRowsofjQueryDataTables
Public Class SqlDataProvider
#Region "Membres
Prives"
Shared _IsError As Boolean = False
Private _connectionString As
String
#End Region
#Region "Constructeurs"
Public Sub New()
Try
_connectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
_IsError = False
Catch ex As Exception
_IsError = True
End Try
End Sub
#End Region
#Region "Proprietes"
Public ReadOnly Property ConnectionString() As
String
Get
Return _connectionString
End Get
End Property
#End Region
#Region "Functions"
Public Function
FillTable(ByVal sql As
String) As DataTable
Try
Dim tb As
New DataTable
Dim adap As
New SqlDataAdapter(sql,
_connectionString)
adap.Fill(tb)
Return tb
Catch ex As Exception
Return Nothing
End Try
End Function
#End Region
End Class
Public Class Customers
#Region "Membres
Prives"
Private _CustomerID As
String
Private _CompanyName As
String
Private _ContactName As
String
Private _ContactTitle As
String
Private _Address As String
Private _City As String
Private _Country As String
Private _PostalCode As
String
#End Region
#Region "Properties"
Public Property
CustomerID() As String
Get
Return _CustomerID
End Get
Set(ByVal value As String)
_CustomerID = value
End Set
End Property
Public Property
CompanyName() As String
Get
Return _CompanyName
End Get
Set(ByVal value As String)
_CompanyName = value
End Set
End Property
Public Property
ContactName() As String
Get
Return _ContactName
End Get
Set(ByVal value As String)
_ContactName = value
End Set
End Property
Public Property
ContactTitle() As String
Get
Return _ContactTitle
End Get
Set(ByVal value As String)
_ContactTitle = value
End Set
End Property
Public Property
Address() As String
Get
Return _Address
End Get
Set(ByVal value As String)
_Address = value
End Set
End Property
Public Property
City() As String
Get
Return _City
End Get
Set(ByVal value As String)
_City = value
End Set
End Property
Public Property
Country() As String
Get
Return _Country
End Get
Set(ByVal value As String)
_Country = value
End Set
End Property
Public Property
PostalCode() As String
Get
Return _PostalCode
End Get
Set(ByVal value As String)
_PostalCode = value
End Set
End Property
#End Region
End Class
<head id="Head1" runat="server">
<title>Scrolling and Bootstrap Tabs of DataTables in Asp.net</title>
<link href="Styles/Site.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" href="http://netdna.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
<script type="text/javascript" src="http://netdna.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/dataTables.bootstrap.min.css" />
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.9/js/dataTables.bootstrap.min.js"></script>
<asp:ContentPlaceHolder ID="HeadContent" runat="server">
</asp:ContentPlaceHolder>
</head>
- B4: Tạo file WebService.asmx
- B5: Viết Code cho file WebServices.aspx
C# Code
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web.Script.Serialization;
using System.Web.Services;
using System.Text;
namespace ShowHideRowsofjQueryDataTables
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
[WebMethod]
public void
ListCustomers(int iDisplayLength, int iDisplayStart, int
iSortCol_0,
string sSortDir_0, string
sSearch)
{
SqlDataProvider objSQL = new
SqlDataProvider();
int displayLength = iDisplayLength;
int displayStart = iDisplayStart;
int sortCol = iSortCol_0;
string sortDir = sSortDir_0;
string Keyword = sSearch;
string OrderByClause = "";
int totalDisplayRecords = 0;
int totalRecords = 0;
StringBuilder sb = new
StringBuilder();
string cs = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
List<Customers>
ListCustomers = new List<Customers>();
string whereClause = "";
if (!string.IsNullOrEmpty(Keyword))
{
sb.Append(" WHERE CustomerID LIKE
'%");
sb.Append(Keyword);
sb.Append("%' OR CompanyName LIKE
'%");
sb.Append(Keyword);
sb.Append("%' OR ContactName LIKE
'%");
sb.Append(Keyword);
sb.Append("%' OR ContactTitle LIKE
'%");
sb.Append(Keyword);
sb.Append("%' OR Address LIKE '%");
sb.Append(Keyword);
sb.Append("%'");
whereClause = sb.ToString();
}
sb.Clear();
for (int i = 0; i
<= 10; i++)
{
if (Context.Request.Params["bSortable_" + i] == "true")
{
sb.Append(Context.Request.Params["iSortCol_"
+ i]);
sb.Append(" ");
sb.Append(Context.Request.Params["sSortDir_"
+ i]);
}
}
OrderByClause = sb.ToString().Trim();
if (!string.IsNullOrEmpty(OrderByClause))
{
OrderByClause = OrderByClause.Replace("0",
", CustomerID");
OrderByClause = OrderByClause.Replace("1",
", CompanyName");
OrderByClause = OrderByClause.Replace("2",
", ContactName");
OrderByClause = OrderByClause.Replace("3",
", ContactTitle");
OrderByClause = OrderByClause.Replace("4",
", Address");
OrderByClause = OrderByClause.Remove(0, 1);
}
else
{
OrderByClause = "CustomerID ASC";
}
OrderByClause = "order by "
+ OrderByClause;
string sQuery = "SELECT
* FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT
COUNT(*) FROM Customers {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM
Customers) AS
TotalRecords,CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Country,PostalCode
FROM Customers {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND
{3}";
sQuery = String.Format(sQuery,
OrderByClause, whereClause, displayStart + 1, displayStart + displayLength);
DataTable objBind = objSQL.FillTable(sQuery);
sb.Clear();
if (objBind != null)
{
foreach (DataRow
row in objBind.Rows)
{
if
(totalRecords == 0)
{
totalRecords = Int32.Parse(row["TotalRecords"].ToString());
}
if
(totalDisplayRecords == 0)
{
totalDisplayRecords
= Int32.Parse(row["TotalDisplayRows"].ToString());
}
Customers
Customers = new Customers();
Customers.CustomerID =
row["CustomerID"].ToString();
Customers.CompanyName =
row["CompanyName"].ToString();
Customers.ContactName =
row["ContactName"].ToString();
Customers.ContactTitle =
row["ContactTitle"].ToString();
Customers.Address = row["Address"].ToString();
Customers.City = row["City"].ToString();
Customers.Country = row["Country"].ToString();
Customers.PostalCode =
row["PostalCode"].ToString();
ListCustomers.Add(Customers);
}
}
var result = new
{
iTotalRecords = totalRecords,
iTotalDisplayRecords = totalDisplayRecords,
aaData = ListCustomers
};
JavaScriptSerializer js = new
JavaScriptSerializer();
Context.Response.Write(js.Serialize(result));
}
}
}
VB.NET Code
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Services
Imports System.Text
Namespace ShowHideRowsofjQueryDataTables
<WebService([Namespace]:="http://tempuri.org/")>
_
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.ComponentModel.ToolboxItem(False)> _
<System.Web.Script.Services.ScriptService()>
_
Public Class WebService
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Sub
ListCustomers(ByVal iDisplayLength As Integer, ByVal iDisplayStart As
Integer, ByVal
iSortCol_0 As Integer,
ByVal sSortDir_0 As
String, ByVal
sSearch As String)
Dim objSQL As New SqlDataProvider()
Dim displayLength As Integer = iDisplayLength
Dim displayStart As Integer = iDisplayStart
Dim sortCol As Integer = iSortCol_0
Dim sortDir As String = sSortDir_0
Dim Keyword As String = sSearch
Dim OrderByClause As String = ""
Dim totalDisplayRecords As
Integer = 0
Dim totalRecords As Integer = 0
Dim sb As New StringBuilder()
Dim cs As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
Dim ListCustomers As New List(Of Customers)()
Dim whereClause As String = ""
If Not String.IsNullOrEmpty(Keyword) Then
sb.Append(" WHERE CustomerID LIKE
'%")
sb.Append(Keyword)
sb.Append("%' OR CompanyName LIKE
'%")
sb.Append(Keyword)
sb.Append("%' OR ContactName LIKE
'%")
sb.Append(Keyword)
sb.Append("%' OR ContactTitle LIKE
'%")
sb.Append(Keyword)
sb.Append("%' OR Address LIKE '%")
sb.Append(Keyword)
sb.Append("%'")
whereClause = sb.ToString()
End If
sb.Clear()
For i As Integer = 0 To 10
If Context.Request.Params("bSortable_" & i) = "true" Then
sb.Append(Context.Request.Params("iSortCol_"
& i))
sb.Append(" ")
sb.Append(Context.Request.Params("sSortDir_"
& i))
End If
Next
OrderByClause = sb.ToString.Trim
If Not String.IsNullOrEmpty(OrderByClause) Then
OrderByClause = OrderByClause.Replace("0",
", CustomerID")
OrderByClause = OrderByClause.Replace("1",
", CompanyName")
OrderByClause = OrderByClause.Replace("2",
", ContactName")
OrderByClause = OrderByClause.Replace("3",
", ContactTitle")
OrderByClause = OrderByClause.Replace("4",
", Address")
OrderByClause = OrderByClause.Remove(0, 1)
Else
OrderByClause = "CustomerID ASC"
End If
OrderByClause = "order by "
& OrderByClause
Dim sQuery As String = "SELECT *
FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT
COUNT(*) FROM Customers {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM
Customers) AS
TotalRecords,CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Country,PostalCode
FROM Customers {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND
{3}"
sQuery = [String].Format(sQuery,
OrderByClause, whereClause, displayStart + 1, displayStart + displayLength)
Dim objBind As DataTable = objSQL.FillTable(sQuery)
sb.Clear()
If Not objBind Is Nothing Then
For Each
row As DataRow
In objBind.Rows
If totalRecords = 0 Then
totalRecords = Int32.Parse(row("TotalRecords").ToString())
End If
If totalDisplayRecords = 0 Then
totalDisplayRecords = Int32.Parse(row("TotalDisplayRows").ToString())
End If
Dim Customers As
New Customers()
With Customers
.CustomerID = row("CustomerID").ToString()
.CompanyName = row("CompanyName").ToString()
.ContactName = row("ContactName").ToString()
.ContactTitle = row("ContactTitle").ToString()
.Address = row("Address").ToString()
.City = row("City").ToString()
.Country = row("Country").ToString()
.PostalCode = row("PostalCode").ToString()
End With
ListCustomers.Add(Customers)
Next
End If
Dim result = New With { _
Key .iTotalRecords = totalRecords, _
Key .iTotalDisplayRecords =
totalDisplayRecords, _
Key .aaData = ListCustomers _
}
Dim js As New JavaScriptSerializer()
Context.Response.Write(js.Serialize(result))
End Sub
End Class
End Namespace
- B6: Mở file site.css bổ xung đoạn mã sau
td.details-control {
background: url('Images/expand.jpg')
no-repeat center
center;
cursor: pointer;
width:5%;
}
tr.shown td.details-control
{
background: url('Images/collapse.jpg')
no-repeat center
center;
width:5%;
}
- B7: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ Page
Title="Show/Hide
Rows of jQuery DataTables in Asp.net" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ShowHideRowsofjQueryDataTables._Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<asp:ScriptManager ID="ScriptManager1"
runat="server">
</asp:ScriptManager>
<div class="panel
panel-default">
<div class="panel-heading">
<h3>Show/Hide Rows of jQuery DataTables in Asp.net</h3>
</div>
<div class="panel-body">
<table id="tblData"
class="table
table-striped table-bordered" cellpadding="0" cellspacing="0" width="100%">
<thead>
<tr>
<th></th>
<th align="center">CustomerID</th>
<th align="center">CompanyName</th>
<th align="center">ContactName</th>
<th align="center">ContactTitle</th>
<th align="center">Address</th>
</tr>
</thead>
</table>
</div>
</div>
<script type="text/javascript">
function format(d) {
return '<table
cellpadding="5" cellspacing="3" border="0"
style="padding-left:50px;width:50%;">' +
'<tr>' +
'<td style="width:20%;">City:
</td>' +
'<td>' + d.City + '</td>' +
'</tr>' +
'<tr>' +
'<td>Country: </td>' +
'<td>' + d.Country + '</td>' +
'</tr>' +
'<tr>' +
'<td>PostalCode: </td>' +
'<td>' + d.PostalCode + '</td>' +
'</tr>' +
'</table>';
}
function pageLoad() {
var table = $('#tblData').DataTable({
bServerSide: true,
sAjaxSource: 'WebService.asmx/ListCustomers',
sServerMethod: 'post',
"columns": [
{
"class": "details-control",
"orderable": false,
"data": null,
"defaultContent": ""
},
{ "data": "CustomerID" },
{ "data": "CompanyName" },
{ "data": "ContactName" },
{ "data": "ContactTitle" },
{ "data": "Address" }
],
"order": [[1, 'asc']]
});
$('#tblData tbody').on('click', 'td.details-control',
function () {
var tr = $(this).closest('tr');
var row = table.row(tr);
if (row.child.isShown()) {
row.child.hide();
tr.removeClass('shown');
}
else {
row.child(format(row.data())).show();
tr.addClass('shown');
}
});
};
</script>
</asp:Content>Chúc các bạn thành công!
Quang Bình
Share This:
No Comment to " Show Hide Rows of jQuery DataTables in Asp.net "