(Server-Side Data in jQuery DataTables with ASP.NET Web Services) – Một trong những tính năng tuyệt vời của jQuery DataTables plugin đó là khả năng xử lý dữ liệu từ phía máy chủ. Bài viết dưới đây sẽ giới thiệu cách làm việc của DataTables JQuery trong Asp.net với CSDL SQL Server được xử lý với Ajax và WebServices.
- 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
- B1: Download CSDL Northwind tại đây và thực hiện công việc Restore Data.
- 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 DataTablesServersideByWebService
{
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; }
}
}
VB.NET Code
Imports System.Data.SqlClient
Imports System.Data
Namespace DataTablesServersideByWebService
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 As New 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
#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
#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.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
using System.Text;
namespace DataTablesServersideByWebService
{
[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();
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.FillTable(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());
}
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();
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 DataTablesServersideByWebService
<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)
DimobjSQL 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()
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.FillTable(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
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()
End With
ListCustomers.Add(Customers)
Next
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 Default.aspx dưới dạng HTML và nhập mã HTML
<%@ PageTitle="Jquery DataTables Serverside By WebService in Asp.net" Language="C#"MasterPageFile="~/Site.master"AutoEventWireup="true"CodeBehind="Default.aspx.cs"Inherits="DataTablesServersideByWebService._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>Jquery DataTables Serverside By WebService 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({
columns: [
{ 'data': 'CustomerID'},
{ 'data': 'CompanyName'},
{ 'data': 'ContactName'},
{ 'data': 'ContactTitle'},
{ 'data': 'Address'}
],
bServerSide: true,
sAjaxSource: 'WebService.asmx/ListCustomers',
sServerMethod: 'post'
});
};
</script>
</asp:Content>Chúc các bạn thành công!
Quang Bình
0 comments Blogger 0 Facebook
Post a Comment