(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 stringConnectionString
{
get { return_connectionString; }
}
#endregion
#region"Functions"
public DataTableFillTable(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 stringCustomerID { get; set; }
public stringCompanyName { get; set; }
public stringContactName { get; set; }
public stringContactTitle { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Country { get; set; }
public stringPostalCode { 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 AsString
#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() AsString
Get
Return _connectionString
End Get
End Property
#End Region
#Region "Functions"
Public FunctionFillTable(ByVal sql AsString) As DataTable
Try
Dim tb AsNew DataTable
Dim adap AsNew 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 AsString
Private _CompanyName AsString
Private _ContactName AsString
Private _ContactTitle AsString
Private _Address As String
Private _City As String
Private _Country As String
Private _PostalCode AsString
#End Region
#Region "Properties"
Public PropertyCustomerID() As String
Get
Return _CustomerID
End Get
Set(ByVal value As String)
_CustomerID = value
End Set
End Property
Public PropertyCompanyName() As String
Get
Return _CompanyName
End Get
Set(ByVal value As String)
_CompanyName = value
End Set
End Property
Public PropertyContactName() As String
Get
Return _ContactName
End Get
Set(ByVal value As String)
_ContactName = value
End Set
End Property
Public PropertyContactTitle() As String
Get
Return _ContactTitle
End Get
Set(ByVal value As String)
_ContactTitle = value
End Set
End Property
Public PropertyAddress() As String
Get
Return _Address
End Get
Set(ByVal value As String)
_Address = value
End Set
End Property
Public PropertyCity() As String
Get
Return _City
End Get
Set(ByVal value As String)
_City = value
End Set
End Property
Public PropertyCountry() As String
Get
Return _Country
End Get
Set(ByVal value As String)
_Country = value
End Set
End Property
Public PropertyPostalCode() As String
Get
Return _PostalCode
End Get
Set(ByVal value As String)
_PostalCode = value
End Set
End Property
#End Region
End Class
- B3: Mở file Site.Master dạng HTML và bổ xung đoạn mã phía dưới trong thẻ Head
<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 voidListCustomers(int iDisplayLength, int iDisplayStart, intiSortCol_0,
string sSortDir_0, stringsSearch)
{
SqlDataProvider objSQL = newSqlDataProvider();
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 = newStringBuilder();
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 (DataRowrow in objBind.Rows)
{
if(totalRecords == 0)
{
totalRecords = Int32.Parse(row["TotalRecords"].ToString());
}
if(totalDisplayRecords == 0)
{
totalDisplayRecords = Int32.Parse(row["TotalDisplayRows"].ToString());
}
CustomersCustomers = 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 = newJavaScriptSerializer();
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 SubListCustomers(ByVal iDisplayLength As Integer, ByVal iDisplayStart AsInteger, ByValiSortCol_0 As Integer, ByVal sSortDir_0 AsString, ByValsSearch 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 AsInteger = 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 Eachrow As DataRowIn 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 AsNew 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 centercenter;
cursor: pointer;
width:5%;
}
tr.shown td.details-control{
background: url('Images/collapse.jpg')no-repeat centercenter;
width:5%;
}
- B7: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="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
0 comments Blogger 0 Facebook
Post a Comment