Wednesday, April 13, 2016

Jquery DataTable in Asp.Net with server side Paging and Filtering (fast)

This is fast when compared to the previous post from here.






















WebForm.aspx :-

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="JqueryDT_ServerSide.WebForm1" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div class="row">
            <br />
            <br />
            <table id="tblAjaxData" class="widthFull fontsize10 displayNone" cellpadding="0" cellspacing="0"
                border="0">
                <thead>
                    <tr>
                        <th width="5%">ID</th>
                        <th>engine</th>
                        <th>browser</th>
                        <th>platform</th>
                        <th>version</th>
                        <th>grade</th>
                        <th>marketshare</th>
                        <th>released</th>
                    </tr>
                </thead>
                <tbody>
                </tbody>
                <tfoot>
                    <tr>
                    <th colspan="5"></th>
                    <th colspan="2" align="left"></th>
                    <th colspan="1" align="right"></th>
                </tr>
                    <tr>
                        <th>ID</th>
                        <th>engine</th>
                        <th>browser</th>
                        <th>platform</th>
                        <th>version</th>
                        <th>grade</th>
                        <th>marketshare</th>
                        <th></th>
                    </tr>
                </tfoot>
            </table>
            <script src="JScript/Scripts/js/jquery.js"></script>
            <script src="JScript/Scripts/js/jquery.dataTables.js"></script>
            <script src="JScript/Scripts/js/jquery.dataTables.columnFilter.js"></script>
 
            <link href="../JScript/media/css/themes/overcast/jquery-ui.css" rel="stylesheet"
                type="text/css" />
            <link href="../JScript/media/css/themes/overcast/jquery.ui.theme.css" rel="stylesheet"
                type="text/css" />
 
            <link href="../JScript/media/css/demo_table_jui.css" rel="stylesheet" type="text/css" />
            <link href="../JScript/media/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
            <link href="../JScript/media/css/jquery.dataTables_themeroller.css" rel="stylesheet"
                type="text/css" />
            <script type="text/javascript">
 
                $(document).ready(function () {
 
                    var oTable = $("[id$=tblAjaxData]").dataTable({
                        "oLanguage": {
                            "sZeroRecords""There are no Records that match your search critera",
                            "sLengthMenu""Display _MENU_ records&nbsp;&nbsp;&nbsp;&nbsp;",
                            "sInfo""Displaying _START_ to _END_ of _TOTAL_ records",
                            "sInfoEmpty""Showing 0 to 0 of 0 records",
                            "sInfoFiltered""(filtered from _MAX_ total records)",
                            "sSearch""Search : "
                        },
                        "aLengthMenu": [[50, 100, 200, 500, 1000, -1], [50, 100, 200, 500, 1000, 'ALL']],
 
                        "iDisplayLength": 50,
                        //'bSortable': false,
                        //"aTargets": [],
                        "bSortClasses"false,
                        "bStateSave"false,
                        "bPaginate"true,
                        "bAutoWidth"false,
                        "bProcessing"true,
                        "bServerSide"true,
                        "bDestroy"true,
                        "sAjaxSource""../WebService2.asmx/GetAjaxData",
                        "bJQueryUI"true,
                        "sPaginationType""full_numbers",
                        "bDeferRender"true,
                        //Scrolling--------------
                        "sScrollY""300px",
                        "sScrollX""100%",
                        "sScrollXInner""100%",
 
                        "fnServerData"function (sSource, aoData, fnCallback) {
                            $.ajax({
                                "dataType"'json',
                                "contentType""application/json; charset=utf-8",
                                "type""GET",
                                "url": sSource,
                                "data": aoData,
                                "success":
                                            function (msg) {
                                                var json = jQuery.parseJSON(msg.d);
                                                fnCallback(json);
                                                $("#tblAjaxData").show();
                                            }
                            });
                        }
                    });
 
                    $("#tblAjaxData").dataTable().columnFilter({
                        "aoColumns": [{ "type""text" },
                                        { "type""text" },
                                        { "type""text" },
                                        { "type""text" },
                                        { "type""text" },
                                        { "type""text" },
                                        { "type""text" },
                                        null]
                    });
 
                    /* Init the table */
                    oTable = $("#tblAjaxData").dataTable();
                });
 
            </script>
        </div>
    </form>
</body>
</html>


WebService2.asmx.cs:



using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
using System.Web.Script.Services;
using System.Web.Services;
using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
 
namespace JqueryDT_ServerSide
{
    /// <summary>
    /// Summary description for WebService2
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class WebService2 : System.Web.Services.WebService
    {
 
        [WebMethod(EnableSession = true)]
        [ScriptMethod(UseHttpGet = true)]
        [WebInvoke(ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Bare, Method = "GET")]
        public string GetAjaxData()
        {
            try
            {
                int sEcho = ToInt(HttpContext.Current.Request.Params["sEcho"]);
                int iDisplayLength = ToInt(HttpContext.Current.Request.Params["iDisplayLength"]);
                int iDisplayStart = ToInt(HttpContext.Current.Request.Params["iDisplayStart"]);
                string rawSearch = HttpContext.Current.Request.Params["sSearch"];
 
                string id = HttpContext.Current.Request.Params["sSearch_0"];
                string engine = HttpContext.Current.Request.Params["sSearch_1"];
                string browser = HttpContext.Current.Request.Params["sSearch_2"];
                string platform = HttpContext.Current.Request.Params["sSearch_3"];
                string version = HttpContext.Current.Request.Params["sSearch_4"];
                string grade = HttpContext.Current.Request.Params["sSearch_5"];
                string share = HttpContext.Current.Request.Params["sSearch_6"];
 
 
                var sb = new StringBuilder();
                StringBuilder s = new StringBuilder();
                s.Append("where released = released ");
                if (id != "")
                    s.Append(" and engine LIKE '%" + id + "%'");
                if (engine != "")
                    s.Append(" and engine LIKE '%" + engine + "%'");
                if (browser != "")
                    s.Append(" and browser LIKE '%" + browser + "%'");
                if (platform != "")
                    s.Append(" and platform LIKE '%" + platform + "%'");
                if (version != "")
                    s.Append(" and version LIKE '%" + version + "%'");
                if (grade != "")
                    s.Append(" and grade LIKE '%" + grade + "%'");
                if (share != "")
                    s.Append(" and marketshare LIKE '%" + share + "%' ");
 
                string orderByClause = string.Empty;
                sb.Append(ToInt(HttpContext.Current.Request.Params["iSortCol_0"]));
 
                sb.Append(" ");
 
                sb.Append(HttpContext.Current.Request.Params["sSortDir_0"]);
 
                orderByClause = sb.ToString();
 
                if (!String.IsNullOrEmpty(orderByClause))
                {
 
                    orderByClause = orderByClause.Replace("0"", ID ");
                    orderByClause = orderByClause.Remove(0, 1);
                }
                else
                {
                    orderByClause = "ID ASC";
                }
                orderByClause = "ORDER BY " + orderByClause;
 
                sb.Clear();
 
                var numberOfRowsToReturn = "";
                numberOfRowsToReturn = iDisplayLength == -1 ? "TotalRows" : (iDisplayStart + iDisplayLength).ToString();
 
                string query = @"Select ID '0', engine '1', browser '2', platform '3', version '4', grade '5', marketshare '6',released '7' 
                                FROM ajax {1} {4}";
 
 
                query = String.Format(query, orderByClause, s.ToString(), iDisplayStart + 1, numberOfRowsToReturn, " order by ID");
 
                var connectionString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
 
                string outputJson = string.Empty;
                int rows_returned;
                DataTable dt = new DataTable();
 
                using (SqlConnection connection = new SqlConnection(connectionString))
                using (SqlCommand cmd = connection.CreateCommand())
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    connection.Open();
                    rows_returned = sda.Fill(dt);
                    connection.Close();
                }
                DataTable dtt = dt;
                if (dt.Rows.Count > 0)
                    dtt = dtt.AsEnumerable().Skip(iDisplayStart).Take(iDisplayLength).CopyToDataTable();
 
                dynamic newtonresult = new
                {
                    sEcho = Convert.ToInt32(sEcho == 0 ? 0 : sEcho),
                    iTotalRecords = dt.Rows.Count,
                    iTotalDisplayRecords = dt.Rows.Count,
                    aaData = dtt
                };
                outputJson = JsonConvert.SerializeObject(newtonresult);
 
                return outputJson;
 
            }
            catch (SqlException sx)
            {
                string ErrMsg = sx.Message;
                int res = sx.Number;
                return "";
            }
            catch (Exception ex)
            {
                string ErrMsg = ex.Message;
                return "";
            }
        }
 
        public static int ToInt(string toParse)
        {
            int result;
            if (int.TryParse(toParse, out result)) return result;
 
            return result;
        }
    }
}



No comments:

Post a Comment