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

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

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)

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>

<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

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

0 comments Blogger 0 Facebook

Post a Comment

Popular Posts

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

Next

Newer Post

Previous

Older Post

0 comments Blogger 0 Facebook

Post a Comment