How to add server side Seachbuilder to a Webforms project

Soundwaves

I have an older Webforms application that contains many DataTables, some of witch use the SearchBuilder and its great.

However, I can't seem to combine the server side tables with the SearchBuilder. The page renders properly but filters have no effect an the output as I am not integrating it on the server.

On the backend I use a Generic Handler (and Class) that calls a stored procedure.
Here is a somewhat simple example.
I am just unclear how to pass and use the query parameters or if this is even possible with this structure.

This is my current use case. Only changes are the addition of "dom: 'Qlfrtip'" to the JavaScript and the script references used on the page.

Reference:
<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<link href="https://cdn.datatables.net/searchbuilder/1.0.1/css/searchBuilder.dataTables.min.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/searchbuilder/1.0.1/js/dataTables.searchBuilder.min.js"></script>

JavaScript:

$(document).ready(function () {
            var simple_checkbox = function (data, type, full, meta) {
                var is_checked = data == true ? "checked" : "";
                return '<input type="checkbox" class="checkbox disabled" ' + is_checked + ' />';
            }
            var table = $('#PartsList').dataTable({
                dom: 'Qfrtip',
                columns: [
                    { 'data': 'Part_Id' },
                    { 'data': 'Part_Number' },
                    { 'data': 'Part_Description' },
                    { 'data': 'Part_Is_Active', 'render': simple_checkbox },
                    { 'data': 'Part_Is_Active', 'class': 'hide' },
                    {
                        'data': null,
                        'defaultContent': '<button id="btnPartEdit" onclick="DoSomething(); return false" Class="btn btn-default">Edit/Delete</button>'
                    }
                ],
                'columnDefs': [
                    {
                        "targets": 3,
                        "td.dt-center": 'text-align: center'
                    }
                ],
                "stateSave": true,
                bServerSide: true,
                sAjaxSource: '../Handlers/PartDataHandler.ashx',
                deferRender: true
            });

This is the Handler:

Public Class PartDataHandler : Implements IHttpHandler

    Public Sub ProcessRequest(context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim displayLength As Integer = Integer.Parse(context.Request("iDisplayLength"))
        Dim displayStart As Integer = Integer.Parse(context.Request("iDisplayStart"))
        Dim sortCol As Integer = Integer.Parse(context.Request("iSortCol_0"))
        Dim sortDir As String = context.Request("sSortDir_0")
        Dim search As String = context.Request("sSearch")

        Dim cs As String = ConfigurationManager.ConnectionStrings("MCS").ConnectionString
        Dim listParts As New List(Of Part)()
        Dim filteredCount As Integer = 0
        Using con As New SqlConnection(cs)
            Dim cmd As New SqlCommand("spGetParts", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@DisplayLength", displayLength)
            cmd.Parameters.AddWithValue("@DisplayStart", displayStart)
            cmd.Parameters.AddWithValue("@SortCol", sortCol)
            cmd.Parameters.AddWithValue("@SortDir", sortDir)
            cmd.Parameters.AddWithValue("@Search", If(String.IsNullOrEmpty(search), Nothing, search))
            con.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Dim part As New Part()
                part.Part_Id = Convert.ToInt32(rdr("Part_Id"))
                filteredCount = Convert.ToInt32(rdr("TotalCount"))
                part.Part_Number = rdr("Part_Number").ToString()
                part.Part_Description = rdr("Part_Description").ToString()
                part.Part_Is_Active = Convert.ToBoolean(rdr("Part_Is_Active"))
                listParts.Add(part)
            End While
        End Using


        Dim result = New With { _
            Key .iTotalRecords = GetPartTotalCount(), _
            Key .iTotalDisplayRecords = filteredCount, _
            Key .aaData = listParts _
        }


        Dim js As New JavaScriptSerializer()
        context.Response.Write(js.Serialize(result))
    End Sub


    Private Function GetPartTotalCount() As Integer
        Dim totalPartCount As Integer = 0
        Dim cs As String = ConfigurationManager.ConnectionStrings("MCS").ConnectionString
        Using con As New SqlConnection(cs)
            Dim cmd As New SqlCommand("SELECT COUNT(*) FROM Part_Numbers", con)
            con.Open()
            totalPartCount = CInt(cmd.ExecuteScalar())
        End Using
        Return totalPartCount
    End Function


    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
End Class

I can supply the Class and/or Stored procedure if you like, but I think this should be enough to get the discussion started.
Thanks!

tangerine

Try updating SearchBuilder. It only supports server-side since late August.

Soundwaves

Thanks @tangerine, I just tried that and it didn't help.

I think I need to add additional code to consume the .searchBuilder.getDetails(), just don't know how to apply that server side.

sandy

Hi @Soundwaves ,

Do you also have the latest version of the Editor Libraries? There were changes in there that are also needed to make SearchBuilder work with the backend.

If that doesn't work I will need to see a test case to understand this further. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Thanks,
Sandy