jQuery DataTables Server-side Processing Using asp.net Web services

By Unknown - Thursday, September 10, 2015 - No Comments

(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.

Nghe những bài hát đỉnh nhất về Thấy cô giáo - Nghe trên Youtube

- 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 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; }

}

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 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

#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

#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

<title>Scrolling and Bootstrap Tabs of DataTables in Asp.net</title>

<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 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();

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 (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();

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 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 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

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 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()

End With

ListCustomers.Add(Customers)

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

<%@ Page Title="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>

<h3>Jquery DataTables Serverside By WebService in Asp.net</h3>

</div>

<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>

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

News Ticker