(jQuery DataTables Serverside processing for Asp.net) – jQuery DataTables ngoài việc hiển thị dữ liệu kiểu JSON nó còn có thể hiển thị với các CSDL như: MySQL, SQL Server… Bài viết dưới đây sẽ giới thiệu với các bạn cách lấy dữ liệu từ SQL Server.
- 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 DataTablesServersideProcessing
{
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 DataTablesServersideProcessing
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>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 WebServices.aspx, mở file dưới dạng HTML và nhập mã HTML
<%@ PageTitle="Example jQuery DataTables Server-Side Processing in Asp.net" Language="C#"AutoEventWireup="true"CodeBehind="WebServices.aspx.cs"Inherits="DataTablesServersideProcessing.WebServices"%>
<!DOCTYPE HTML PUBLIC "-//W3C//Dtd HTML 4.0 Transitional//EN">
<html>
<head>
<title><asp:Literal id="popTitle"runat="server"/></title>
</head>
<body MS_POSITIONING="FlowLayout">
<form id="form1" runat="server">
</form>
</body>
</html>
- B5: Viết Code cho file WebServices.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.Text;
namespace DataTablesServersideProcessing
{
public partial class WebServices: System.Web.UI.Page
{
private voidProcessing()
{
SqlDataProvider objSQL = newSqlDataProvider();
StringBuilder sb = newStringBuilder();
string outputJson = "";
int totalDisplayRecords = 0;
int totalRecords = 0;
string SortCol = "0";
string Keyword = "";
string SortDir = "CustomerID";
string OrderByClause = "";
int displayLength = 10;
int displayStart = 0;
if (Request.Params["iDisplayLength"] != null)
{
displayLength = Int32.Parse(Request.Params["iDisplayLength"]);
}
if (Request.Params["iDisplayStart"] != null)
{
displayStart = Int32.Parse(Request.Params["iDisplayStart"]);
}
SortDir = Context.Request["sSortDir_0"];
SortCol = Context.Request["iSortCol_0"];
if (Context.Request["sSearch"] != null)
{
Keyword = Context.Request["sSearch"];
}
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 (Request.Params["bSortable_" + i] == "true")
{
sb.Append(Request.Params["iSortCol_"+ i]);
sb.Append(" ");
sb.Append(Request.Params["sSortDir_"+ i]);
}
}
OrderByClause = sb.ToString();
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 FROM Customers {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND {3}";
sQuery = String.Format(sQuery, OrderByClause, whereClause, displayStart + 1, displayStart + displayLength);
DataTable objBind = objSQL.FillTableBySQL(sQuery);
sb.Clear();
foreach (DataRowrow in objBind.Rows)
{
if (totalRecords == 0)
{
totalRecords = Int32.Parse(row["TotalRecords"].ToString());
}
if (totalDisplayRecords == 0)
{
totalDisplayRecords = Int32.Parse(row["TotalDisplayRows"].ToString());
}
sb.Append("[");
sb.Append("\"" + row["CustomerID"].ToString() + "\",");
sb.Append("\"" + row["CompanyName"].ToString() + "\",");
sb.Append("\"" + row["ContactName"].ToString() + "\",");
sb.Append("\"" + row["ContactTitle"].ToString() + "\",");
sb.Append("\"" + row["Address"].ToString() + "\"");
sb.Append("],");
}
outputJson = sb.ToString();
if (!string.IsNullOrEmpty(outputJson))
{
outputJson = outputJson.Remove(outputJson.Length - 1);
}
sb.Clear();
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();
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Write(outputJson);
Response.Flush();
Response.End();
}
protected voidPage_Load(object sender, System.EventArgs e)
{
Processing();
}
}
}
VB.NET Code
Imports System.Text
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace DataTablesServersideProcessing
Partial Public Class WebServices
Inherits System.Web.UI.Page
Private SubProcessing()
Dim objSQL As New SqlDataProvider
Dim sb As New StringBuilder()
Dim outputJson As String = ""
Dim totalDisplayRecords AsInteger = 0
Dim totalRecords As Integer = 0
Dim SortCol As String = 0
Dim Keyword As String = ""
Dim SortDir As String = "CustomerID"
Dim OrderByClause As String = ""
Dim displayLength As Integer = Integer.Parse(Context.Request("iDisplayLength"))
Dim displayStart As Integer = Integer.Parse(Context.Request("iDisplayStart"))
SortDir = Context.Request("sSortDir_0")
SortCol = Context.Request("iSortCol_0")
If NotContext.Request("sSearch") Is Nothing Then
Keyword = Context.Request("sSearch")
End If
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
IfRequest.Params("bSortable_" & i) = "true" Then
sb.Append(Request.Params("iSortCol_"& i))
sb.Append(" ")
sb.Append(Request.Params("sSortDir_"& i))
End If
Next
orderByClause = sb.ToString()
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 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.FillTableBySQL(sQuery)
sb.Clear()
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
sb.Append("[")
sb.Append(""""& row("CustomerID").ToString() & """,")
sb.Append(""""& row("CompanyName").ToString() & """,")
sb.Append(""""& row("ContactName").ToString() & """,")
sb.Append(""""& row("ContactTitle").ToString() & """,")
sb.Append(""""& row("Address").ToString() & """")
sb.Append("],")
Next
outputJson = sb.ToString()
If outputJson <> ""Then
outputJson = outputJson.Remove(outputJson.Length - 1)
End If
sb.Clear()
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()
Response.Clear()
Response.ClearHeaders()
Response.ClearContent()
Response.Write(outputJson)
Response.Flush()
Response.End()
End Sub
Protected SubPage_Load(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Me.Load
Processing()
End Sub
End Class
- B6: Mở file Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="Example jQuery DataTables Server-Side Processing in Asp.net" Language="C#"MasterPageFile="~/Site.master"AutoEventWireup="true"CodeBehind="Default.aspx.cs"Inherits="DataTablesServersideProcessing._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>Example jQuery DataTables Server-Side Processing 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 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 pageLoad() {
$('#tblData').DataTable({
"sPaginationType": "full_numbers",
bServerSide: true,
sAjaxSource: 'WebServices.aspx'
});
};
</script>
</asp:Content>
Chúc các bạn thành công!
Quang Bình
0 comments Blogger 0 Facebook
Post a Comment