(Footer callback of a DataTables in Asp.net) – Ngoài tính năng hiển thị dữ liệu tuyệt vời, plugin DataTables còn hỗ trợ người sử dụng nhiều tính năng khác. Một trong những tính năng đó chính là việc tính toán, tổng hợp số liệu tại các cột dữ liệu. Bài viết dưới đây sẽ hướng dẫn cách tính tổng của 1 trang và tổng của tất cả các trang trong 1 cột dữ liệu.
- B1: Download CSDL Northwind tại đây và thực hiện công việc Restore Data.
- 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.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DisplayingTotalinFooterofDataTables
{
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
}
}
VB.NET Code
Imports System.Data.SqlClient
Imports System.Data
Namespace DisplayingTotalinFooterofDataTables
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
End Namespace
- 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>Displaying Total in Footer of DataTables</title>
<link href="~/Styles/Site.css"rel="stylesheet"type="text/css"/>
<link rel="stylesheet"type="text/css"href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"/>
<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://code.jquery.com/jquery-1.11.3.min.js"></script>
<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:ContentPlaceHolderID="HeadContent"runat="server">
</asp:ContentPlaceHolder>
</head>
- B4: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="DataTables with column rendering in Asp.net" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DisplayingTotalinFooterofDataTables._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>Displaying Total in Footer of DataTables</h3>
</div>
<div class="panel-body">
<asp:Repeater ID="dlObject"runat="server">
<HeaderTemplate>
<tableid="tblData"class="table table-striped table-bordered" cellpadding="0" width="100%">
<thead>
<tr>
<th align="center">OrderDate</th>
<th align="center">RequiredDate</th>
<th align="center">ShippedDate</th>
<th align="center">ShipName</th>
<th align="center">ShipVia</th>
<th align="center">Freight</th>
</tr>
</thead>
<tfoot>
<tr>
<th colspan="4" style="text-align:right">Page Total<br />Grand Total</th>
<th style="text-align:right"></th>
<th style="text-align:right"></th>
</tr>
</tfoot>
<tbody>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center"><%# Eval("OrderDate", "{0:d}")%></td>
<td align="center"><%# Eval("RequiredDate", "{0:d}")%></td>
<td align="center"><%# Eval("ShippedDate", "{0:d}")%></td>
<td align="left"><%# Eval("ShipName")%></td>
<td align="right"><%# Eval("ShipVia")%></td>
<td align="right"><%# Eval("Freight")%></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</tbody>
</table>
</FooterTemplate>
</asp:Repeater>
</div>
</div>
<script type="text/javascript">
function pageLoad() {
$('#tblData').DataTable({
"footerCallback": function (row, data, start, end, display) {
var api = this.api(), data;
var intVal = function(i) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '') * 1 :
typeof i === 'number'?
i : 0;
};
//=========Start Column ShipVia=========
// Total all pages
total = api
.column(4)
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
});
// Total this page
pageTotal = api
.column(4, { page: 'current' })
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
}, 0);
// Update footer
$(api.column(4).footer()).html(
pageTotal + ' <br />' + total
);
//=========End Column ShipVia===========
//=========Start Column Freight=========
// Total all pages
total = api
.column(5)
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
});
// Total this page
pageTotal = api
.column(5, { page: 'current' })
.data()
.reduce(function (a, b) {
return intVal(a) + intVal(b);
}, 0);
// Update footer
$(api.column(5).footer()).html(
pageTotal.toFixed(2) + ' <br />' + total.toFixed(2)
);
//=======End Column Freight=======
}
});
};
</script>
</asp:Content>- B5: Viết Code cho file Default.aspx
C# Code
//Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Web.UI;
using System.Web.Caching;
namespace DisplayingTotalinFooterofDataTables
{
public partial class _Default : System.Web.UI.Page
{
#region"Bind Data"
private voidBindData()
{
DataTable objBind = newDataTable();
objBind = GetData();
dlObject.DataSource = objBind;
dlObject.DataBind();
}
private DataTableGetData()
{
SqlDataProvider objSQL = newSqlDataProvider();
DataTable objBind = null;
//Caching
if (Cache["Cache_Orders"] == null)
{
objBind = objSQL.FillTable("select OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress from Orders");
Cache["Cache_Orders"] = objBind;
}
else
{
objBind = (DataTable)Cache["Cache_Orders"];
}
return objBind;
}
#endregion
#region"Event Handles"
protected voidPage_Load(object sender, System.EventArgs e)
{
try
{
if (!IsPostBack)
{
BindData();
}
}
catch
{
}
}
#endregion
}
}VB.NET Code
'Visit http://www.laptrinhdotnet.com for more ASP.NET Tutorials
Namespace DisplayingTotalinFooterofDataTables
Public Class _Default
Inherits System.Web.UI.Page
#Region "Bind Data"
Private SubBindData()
Dim objBind As New DataTable
objBind = GetData()
dlObject.DataSource = objBind
dlObject.DataBind()
End Sub
Private FunctionGetData() As DataTable
Dim objSQL As New SqlDataProvider
Dim objBind As DataTable
'Caching
If Cache("Cache_Orders") Is Nothing Then
objBind = objSQL.FillTable("select OrderDate, RequiredDate, ShippedDate,ShipVia, Freight,ShipName,ShipAddress from Orders")
Cache("Cache_Orders") = objBind
Else
objBind = CType(Cache("Cache_Orders"), DataTable)
End If
Return objBind
End Function
#End Region
#Region "Event Handles"
Protected SubPage_Load(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Me.Load
Try
If Page.IsPostBack = False Then
BindData()
End If
Catch ex As Exception
End Try
End Sub
#End Region
End Class
Chúc các bạn thành công!
Quang Bình
Footer Call Back Does not Return Correct Values of sum in c# what I Do?
ReplyDeletePlease tell me any suggestion or send me the sample Project to aravindscv@gmail.com