DataTables

This site contains the legacy documentation for DataTables v1.9 and earlier for reference only.
DataTables 1.9 was End Of Life in 2014. Do not use it for new work.
The current release of DataTables can always be found on DataTables.net.

Server-side processing | ASP.NET with SQL Server 2008

Important - Do not use this script for new development - it will not work! It is no longer maintained and does not support DataTables 1.10+'s server-side processing mode. This page is retained only for legacy users and it will likely be removed in the near future.

This script is very kindly provided by LionHeart and allows DataTables with server-side processing to retrieve data from a server configured with ASP.NET and SQL Server 2008.

To use the code on your own server you will need to modify the script to read and deal with the column names in your database (the standard DataTables example schema is used here). You will also need to modify the database connection variables: SQL_SERVER, USERNAME etc.

Feature set

Compatibility - DataTables 1.6+
Pagination - yes
Filtering - yes
Sorting - yes
Multi-column sorting - no
Individual column filter - no
Column filter enabled check (bSearchable_) - no
Column sort enabled check (bSortable_) - no
Regex support for filtering - no

Code

Imports System.Data.SqlClient

Partial Public Class DTServeur
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Instanciation des variables
        'Creation of variables
        Dim str As String = "" 'Chaîne JSON en sortie - Will be the output JSON string
        Dim strClassement As String = "" 'Colonne sur laquelle le tableau est trié - Will be the sorted column
        Dim strSens As String = "" 'ASC or DESC
        Dim strSensInverse As String = "" 'DESC or ASC

        'Détection de la colonne de tri
        'Detection of sorted column
        Select Case Request("iSortCol_0")
            Case 0
                strClassement = "engine"
            Case 1
                strClassement = "browser"
            Case 2
                strClassement = "platform"
            Case 3
                strClassement = "version"
            Case 4
                strClassement = "grade"
            Case Else
                strClassement = "engine"
        End Select

        'Détection du sens du tri
        'detection of direction of the sort
        Select Case Request("sSortDir_0")
            Case "asc"
                strSens = " ASC"
                strSensInverse = " DESC"
            Case "desc"
                strSens = " DESC"
                strSensInverse = " ASC"
        End Select

        'Connexion SQL
        'SQL connection
        Dim maConnexion As SqlConnection
        Dim strConnect As String = "server={SQL_SERVER}" & "; uid={USERNAME}" & ";pwd={PASSWORD}" & "; database={DATABASE}"
        maConnexion = New SqlConnection(strConnect)
        maConnexion.Open()

        Dim dataset As New DataSet

        'Création de la requête SQL
        'SQL request creation
        Dim strRequeteA As New String("")
        Dim strRequeteC As New String("")
        Dim strRequeteB_1 As New String("")
        Dim strRequeteB_2 As New String("")
        Dim strRequeteB_3 As New String("")

        'Le requête est bâtie selon une méthode permettant la pagination avec SQL Server
        'The request is built with a method which allow pagination with SQL Server
        ' http://troels.arvin.dk/db/rdbms/
        strRequeteA += "SELECT * FROM ("
        strRequeteA += "SELECT TOP "
        strRequeteA += Request("iDisplayLength").ToString
        strRequeteA += " * FROM ("
        strRequeteB_1 = "SELECT "
        strRequeteB_2 = "TOP " + (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))).ToString
        strRequeteB_3 += " id, engine, browser, platform, version, grade "
        strRequeteB_3 += "FROM ajax "

        If Request("sSearch") <> "" Then
            If Request("sSearch") <> " " Then
                strRequeteB_3 += "WHERE "
                Dim chaine As String
                For i As Integer = 0 To (Split(Request("sSearch").ToString, " ").Length - 1)
                    chaine = Split(Request("sSearch").ToString, " ")(i)
                    If chaine <> "" Then
                        If i <> 0 Then
                            strRequeteB_3 += "AND ("
                        Else
                            strRequeteB_3 += "("
                        End If
                        strRequeteB_3 += "engine LIKE '%" + chaine + "%'"
                        strRequeteB_3 += " OR "
                        strRequeteB_3 += "browser LIKE '%" + chaine + "%'"
                        strRequeteB_3 += " OR "
                        strRequeteB_3 += "platform LIKE '%" + chaine + "%'"
                        strRequeteB_3 += " OR "
                        strRequeteB_3 += "version LIKE '%" + chaine + "%'"
                        strRequeteB_3 += " OR "
                        strRequeteB_3 += "grade LIKE '%" + chaine + "%'"
                        strRequeteB_3 += ") "
                    End If
                Next
            End If
        End If

        strRequeteC += " ORDER BY "
        strRequeteC += strClassement
        strRequeteC += strSens

        strRequeteC += ") AS foo ORDER BY "
        strRequeteC += strClassement
        strRequeteC += strSensInverse

        strRequeteC += ") AS bar ORDER BY "
        strRequeteC += strClassement
        strRequeteC += strSens

        'Exécution de la requête
        'Request execution
        Dim MaCommande As New SqlCommand(strRequeteA + strRequeteB_1 + strRequeteB_2 + strRequeteB_3 + strRequeteC, maConnexion)
        Dim MonAdapteur As SqlDataAdapter = New SqlDataAdapter(MaCommande)
        MonAdapteur.Fill(dataset)
        MaCommande.Dispose()

        If dataset.Tables.Count > 0 Then
            Dim table As New DataTable
            table = dataset.Tables(0)

            'Ecriture des données JSON
            'JSON data writing
            str = "{"
            str += """sEcho"": " + Request("sEcho") + ","

            'Requête permettant l'écriture du nombre total d'enregistrements
            'Request finding the total records number
            strRequeteA = "SELECT COUNT(id) FROM ajax"
            dataset = New DataSet
            MaCommande = New SqlCommand(strRequeteA, maConnexion)
            MonAdapteur = New SqlDataAdapter(MaCommande)
            MonAdapteur.Fill(dataset)
            MaCommande.Dispose()
            str += """iTotalRecords"": " + dataset.Tables(0).Rows(0)(0).ToString + ","

            'Requête permettant l'écriture du nombre d'enregistrement filtrés
            'Request finding the displayed records number
            strRequeteA = "SELECT COUNT(id) FROM (" + strRequeteB_1 + strRequeteB_2 + strRequeteB_3 + ") AS P1"
            dataset = New DataSet
            MaCommande = New SqlCommand("SELECT COUNT(id) FROM (" + strRequeteB_1 + strRequeteB_3 + ") AS P1", maConnexion)
            MonAdapteur = New SqlDataAdapter(MaCommande)
            MonAdapteur.Fill(dataset)
            MaCommande.Dispose()
            str += """iTotalDisplayRecords"": " + dataset.Tables(0).Rows(0)(0).ToString + ","

            str += """aaData"": ["

            'Procédure évitant les doublons dans la dernière page
            'Procedure avoiding double records in last page
            Dim nbRowANePasAfficher As Integer = 0
            If (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))) > dataset.Tables(0).Rows(0)(0) Then
                nbRowANePasAfficher = (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))) - dataset.Tables(0).Rows(0)(0)
            End If
            If Convert.ToInt32(Request("iDisplayStart")) = 0 Then
                nbRowANePasAfficher = 0
            End If

            'Ecriture des données JSON
            'JSON data writing
            Dim autre As Boolean = False
            For Each row As DataRow In table.Rows
                If nbRowANePasAfficher > 0 Then
                    nbRowANePasAfficher -= 1
                    Continue For
                End If
                If autre = True Then
                    str += ","
                End If
                str += "["
                str += """" + row("engine").ToString + ""","
                str += """" + row("browser").ToString + ""","
                str += """" + row("platform").ToString + ""","
                str += """" + row("version").ToString + ""","
                str += """" + row("grade").ToString + """"
                str += "]"
                autre = True
            Next
        End If
        str += "]"
        str += "}"

        Response.Write(str)

        maConnexion.Close()

    End Sub

End Class

DataTables designed and created by SpryMedia © 2008-2014
DataTables is dual licensed under the GPL v2 license or a BSD (3-point) license.