» » Jquery DataTables Serverside By Generic Handler in Asp.net

(Jquery DataTables Serverside Processing  By Generic Handler) – Trong các bài viết trước chúng tôi đã giới thiệu các phương thức lấy dữ liệu của Jquery DataTables như: Ajax sourced data, HTML (DOM) sourced data, Server-side by WebServices, Server-side processing.  Hôm nay chúng tôi sẽ giới thiệu thêm một phương thức lấy dữ liệu nữa đó là: Serverside By Generic Handler.


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



Code Example C#, Code Example VB.NET
Code Example C#, Code Example VB.NET

- B1: Download CSDL Northwind tại đây và thực hiện công việc Restore Data.

- B2: Tạo stored procedure Pro_Customers_List

Bạn có thể tải về Script bằng cách nhấn vào liên kết tải về dưới đây

- B3: 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 DataTablesServersideByGenericHandler
{
    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 ProcName, params ObjectPara[] Para)
        {
            try
            {
                DataTable tb = new DataTable();
                SqlDataAdapter adap = new SqlDataAdapter(ProcName, _connectionString);
                adap.SelectCommand.CommandType = CommandType.StoredProcedure;
                if (Para != null)
                {
                    foreach (ObjectParap in Para)
                    {
                        adap.SelectCommand.Parameters.Add(new SqlParameter(p.Name, p.Value));
                    }
                }
                adap.Fill(tb);
                return tb;
            }
            catch
            {
                return null;
            }
        }

        #endregion
    }

    public class ObjectPara
    {
        string _name;

        object _Value;
        public ObjectPara(stringPname, object PValue)
        {
            _name = Pname;
            _Value = PValue;
        }

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        public object Value
        {
            get { return _Value; }
            set { _Value = value; }
        }
    }

    public class Customers
    {
        public string CustomerID { 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 DataTablesServersideByGenericHandler

    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 ProcName As String, ByVal ParamArrayPara() As ObjectPara) As DataTable
            Try
                Dim tb AsNew DataTable
                Dim adap AsNew SqlDataAdapter(ProcName, _connectionString)
                adap.SelectCommand.CommandType = CommandType.StoredProcedure
                If NotPara Is NothingThen
                    For Eachp As ObjectParaIn Para
                        adap.SelectCommand.Parameters.Add(New SqlParameter(p.Name, p.Value))
                    Next
                End If
                adap.Fill(tb)
                Return tb
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

#End Region

    End Class

    Public Class ObjectPara
        Dim _name As String
        Dim _Value As Object

        Sub New(ByVal Pname As String, ByVal PValue As Object)
            _name = Pname
            _Value = PValue
        End Sub

        Public PropertyName() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property

        Public PropertyValue() As Object
            Get
                Return _Value
            End Get
            Set(ByVal value As Object)
                _Value = value
            End Set
        End Property

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

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

- B5: Tạo file Generic Handle và đặt tên thành  CustomerDataHandler.ashx

- B6: Viết Code cho file CustomerDataHandler.ashx
C# Code
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Text;
using System.Web.Services;
using System.Web.Script.Serialization;
using System.Configuration;

namespace DataTablesServersideByGenericHandler
{
    public class CustomerDataHandler : System.Web.IHttpHandler
    {
        public voidProcessRequest(HttpContext context)
        {
            SqlDataProvider objSQL = newSqlDataProvider();
            int sortCol = 0;
            string sortDir = "";
            string Keyword = "";
            string OrderByClause = "";
            int totalDisplayRecords = 0;
            int totalRecords = 0;
            int displayLength = 10;
            int displayStart = 0;
            StringBuilder sb = newStringBuilder();
            string cs = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
            List<Customers> ListCustomers = new List<Customers>();

            if (context.Request["iDisplayLength"] != null)
            {
                displayLength = int.Parse(context.Request["iDisplayLength"]);
            }

            if (context.Request["iDisplayStart"] != null)
            {
                displayStart = int.Parse(context.Request["iDisplayStart"]);
            }

            sortDir = context.Request["sSortDir_0"];
            sortCol =Convert.ToInt32(context.Request["iSortCol_0"]);

            if (context.Request["sSearch"] != null)
            {
                Keyword = context.Request["sSearch"].Trim();
            }

            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";
            }
            DataTable objBind = objSQL.FillTable("Pro_Customers_List", new ObjectPara("@DisplayStart", displayStart), new ObjectPara("@DisplayLength", displayLength), new ObjectPara("@Keyword", Keyword), new ObjectPara("@SortField", OrderByClause.Trim()));

            sb.Clear();

            if(objBind != null)
            {
                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);
                }
            }

            dynamic result = new
            {
                iTotalRecords = totalRecords,
                iTotalDisplayRecords = totalDisplayRecords,
                aaData = ListCustomers
            };

            JavaScriptSerializer js = newJavaScriptSerializer();
            context.Response.Write(js.Serialize(result));
        }

        public boolIsReusable
        {
            get { return false; }
        }
    }
}
VB.NET Code
Imports System.Web
Imports System.Web.Services
Imports System.Web.Script.Serialization

Namespace DataTablesServersideByGenericHandler
    Public Class CustomerDataHandler
        Implements System.Web.IHttpHandler

        Sub ProcessRequest(ByValcontext As HttpContext) Implements IHttpHandler.ProcessRequest
            Dim objSQL As New SqlDataProvider()
            Dim sortCol As Integer = 0
            Dim sortDir As String = ""
            Dim Keyword As String = ""
            Dim OrderByClause As String = ""
            Dim totalDisplayRecords AsInteger = 0
            Dim totalRecords As Integer = 0
            Dim displayLength As Integer = 10
            Dim displayStart As Integer = 0
            Dim sb As New StringBuilder()
            Dim cs As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
            Dim ListCustomers As New List(Of Customers)()

            If Notcontext.Request("iDisplayLength") Is Nothing Then
                displayLength = Integer.Parse(context.Request("iDisplayLength"))
            End If

            If Not context.Request("iDisplayStart") Is Nothing Then
                displayStart = Integer.Parse(context.Request("iDisplayStart"))
            End If

            sortDir = context.Request("sSortDir_0")
            sortCol = context.Request("iSortCol_0")

            If Not context.Request("sSearch") Is Nothing Then
                Keyword = context.Request("sSearch")
            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

            Dim objBind As DataTable = objSQL.FillTable("Pro_Customers_List", New ObjectPara("@DisplayStart", displayStart), _
                                                                         New ObjectPara("@DisplayLength", displayLength), _
                                                                         New ObjectPara("@Keyword", Keyword.Trim), _
                                                                         New ObjectPara("@SortField", OrderByClause.Trim))

            sb.Clear()

            If Not objBind Is Nothing Then
                For Eachrow As DataRowIn 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
            End If

            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

        ReadOnly PropertyIsReusable() As BooleanImplements IHttpHandler.IsReusable
            Get
                Return False
            End Get
        End Property

    End Class

End Namespace

- B7: Mở file Default.aspx dưới dạng HTML và  nhập mã HTML
<%@ PageTitle="Jquery DataTables Serverside By Generic Handler in Asp.net" Language="C#"MasterPageFile="~/Site.master"AutoEventWireup="true"CodeBehind="Default.aspx.cs"Inherits="DataTablesServersideByGenericHandler._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 Generic Handler 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'}
                ],
                "sPaginationType": "full_numbers",
                serverSide: true,
                sAjaxSource: 'CustomerDataHandler.ashx',
                sServerMethod: 'post'
            });
        };
    </script>
</asp:Content>

Code Example C#, Code Example VB.NET
Code Example C#, Code Example VB.NET



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

Quang Bình
1:57:00 AM
Gia Khang

0 comments Blogger 0 Facebook

Post a Comment

Subscribe to: Post Comments ( Atom )
 
lập trình đốt nét © 2013. All Rights Reserved. Powered by Blogger
Top