Example jQuery DataTables ServerSide Processing in Asp.net

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

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

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

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

<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

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

</head>

</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 void Processing()

{

SqlDataProvider objSQL = new SqlDataProvider();

StringBuilder sb = new StringBuilder();

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 (DataRow row 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 void Page_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 Sub Processing()

Dim objSQL As New SqlDataProvider

Dim sb As New StringBuilder()

Dim outputJson As String = ""

Dim totalDisplayRecords As Integer = 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 Not Context.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

If Request.Params("bSortable_" & i) = "true" Then

sb.Append(Request.Params("iSortCol_" & i))

sb.Append(" ")

sb.Append(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.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("],")

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 Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Processing()

End Sub

End Class

End Namespace

- B6: Mở file Default.aspx dưới dạng HTML và nhập mã HTML

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

<h3>Example jQuery DataTables Server-Side Processing 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({

"sPaginationType": "full_numbers",

bServerSide: true,

sAjaxSource: 'WebServices.aspx'

});

};

</script>

</asp:Content>

Chúc các bạn thành công!

Quang Bình

News Ticker