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 ", "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