Default.aspx
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JqueryDT_ServerSide._Default" %> <asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" 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>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/jquery-1.8.2.min.js" type="text/javascript"></script> <script src="../JScript/jquery.sticky.js" type="text/javascript"></script> <script src="../JScript/media/js/jquery.dataTables.min.js" type="text/javascript"></script> <script src="../JScript/Scripts/js/jquery.dataTables.columnFilter.js" type="text/javascript"></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_page.css" rel="stylesheet" type="text/css" /> <link href="../JScript/media/css/demo_table.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"> var oTable; $(document).ready(function () { //$("#tblAjaxData").dataTable({ $("[id$=tblAjaxData]").dataTable({ "oLanguage": { "sZeroRecords": "There are no Records that match your search critera", "sLengthMenu": "Display _MENU_ records ", "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']], //"aLengthMenu": [[5000, -1], [5000, "All"]], "iDisplayLength": 50, //'bSortable': false, //"aTargets": [], "bSortClasses": false, "bStateSave": false, "bPaginate": true, "bAutoWidth": false, "bProcessing": true, "bServerSide": true, "bDestroy": true, "sAjaxSource": "../WebService1.asmx/GetAjaxData", "bJQueryUI": true, "sPaginationType": "full_numbers", "bDeferRender": true, //Scrolling-------------- "sScrollY": "300px", "sScrollX": "100%", "sScrollXInner": "100%", ////------------ //"fnServerParams": function (aoData) { // aoData.push({ "name": "iAllSelectedItems", "value": $("#HFAllSelectedItems").val() }); // aoData.push({ "name": "iItemDescription", "value": $("#txtItmDscrip").val() }); // aoData.push({ "name": "iPartNumber", "value": $("#txtItmPrtNmbr").val() }); // aoData.push({ "name": "iSpecification", "value": $("#txtspec").val() }); //}, "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> </asp:Content>
WebService1.asmx.cs :
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Web; using System.Web.Script.Services; using System.Web.Services; using System.ServiceModel.Web; namespace JqueryDT_Json { [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 WebService1 : System.Web.Services.WebService { [WebMethod] [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"]; var sb = new StringBuilder(); var filteredWhere = string.Empty; var wrappedSearch = "'%" + rawSearch + "%'"; if (rawSearch.Length > 0) { sb.Append(" WHERE engine LIKE "); sb.Append(wrappedSearch); sb.Append(" OR browser LIKE "); sb.Append(wrappedSearch); sb.Append(" OR platform LIKE "); sb.Append(wrappedSearch); sb.Append(" OR version LIKE "); sb.Append(wrappedSearch); sb.Append(" OR grade LIKE "); sb.Append(wrappedSearch); sb.Append(" OR marketshare LIKE "); sb.Append(wrappedSearch); filteredWhere = sb.ToString(); } ////ORDERING sb.Clear(); 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 = @" declare @MAA TABLE(ID int, engine nvarchar(300), browser nvarchar(300), platform nvarchar(300), version decimal(18,2), grade nvarchar(20), marketshare decimal(18,2),released datetime) INSERT INTO @MAA (ID , engine , browser , platform , version , grade , marketshare ,released ) Select ID , engine , browser , platform , version , grade , marketshare ,released FROM ajax {4} SELECT * FROM (SELECT row_number() OVER ({0}) AS RowNumber , * FROM (SELECT (SELECT count([@MAA].ID) FROM @MAA) AS TotalRows , ( SELECT count( [@MAA].ID) FROM @MAA {1}) AS TotalDisplayRows ,[@MAA].ID ,[@MAA].engine ,[@MAA].browser ,[@MAA].platform ,[@MAA].version ,[@MAA].grade ,[@MAA].marketshare ,[@MAA].released FROM @MAA {1}) RawResults) Results WHERE RowNumber BETWEEN {2} AND {3} order by ID"; query = String.Format(query, orderByClause, filteredWhere, iDisplayStart + 1, numberOfRowsToReturn, " order by ID"); var connectionString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString; SqlConnection conn = new SqlConnection(connectionString); if (conn.State == ConnectionState.Closed) conn.Open(); var DB = new SqlCommand(); DB.Connection = conn; DB.CommandText = query; var data = DB.ExecuteReader(); var totalDisplayRecords = ""; var totalRecords = ""; string outputJson = string.Empty; var rowClass = ""; while (data.Read()) { if (totalRecords.Length == 0) { totalRecords = data["TotalRows"].ToString(); totalDisplayRecords = data["TotalDisplayRows"].ToString(); } sb.Append("{"); sb.AppendFormat(@"""DT_RowId"": ""{0}""", data["ID"].ToString()); // count++); sb.Append(","); sb.AppendFormat(@"""DT_RowClass"": ""{0}""", rowClass); sb.Append(","); sb.AppendFormat(@"""0"": ""{0}""", data["ID"]); sb.Append(","); string engine = data["engine"].ToString(); sb.AppendFormat(@"""1"": ""{0}""", engine); sb.Append(","); string browser = data["browser"].ToString(); sb.AppendFormat(@"""2"": ""{0}""", browser); sb.Append(","); string platform = data["platform"].ToString(); sb.AppendFormat(@"""3"": ""{0}""", platform); sb.Append(","); string vrsn = data["version"].ToString(); sb.AppendFormat(@"""4"": ""{0}""", vrsn); sb.Append(","); string grade = data["grade"].ToString(); sb.AppendFormat(@"""5"": ""{0}""", grade); sb.Append(","); string share = data["marketshare"].ToString(); sb.AppendFormat(@"""6"": ""{0}""", share); sb.Append(","); sb.AppendFormat(@"""7"": ""{0:dd-MM-yyyy}""", data["released"].ToString()); sb.Append("},"); } conn.Close(); // handles zero records if (totalRecords.Length == 0) { sb.Append("{"); sb.Append(@"""sEcho"": "); sb.AppendFormat(@"""{0}""", sEcho); sb.Append(","); sb.Append(@"""iTotalRecords"": 0"); sb.Append(","); sb.Append(@"""iTotalDisplayRecords"": 0"); sb.Append(", "); sb.Append(@"""aaData"": [ "); sb.Append("]}"); outputJson = sb.ToString(); return outputJson; } outputJson = sb.Remove(sb.Length - 1, 1).ToString(); sb.Clear(); sb.Append("{"); sb.Append(@"""sEcho"": "); sb.AppendFormat(@"""{0}""", sEcho); 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(); return outputJson; } 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