Currently, I am using the following Datahandler, which takes lots of time to load the data. Could anybody send me a working sample which really works with huge amount of records.
Is there any way to bind the data directly to JSON ( I feel the for loop also taking time to load).
JQGridHandler.ahsx
<%@ WebHandler Language="C#" Class="JQGridHandler" %>
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Collections.Specialized;
public struct JQGridResults
{
public int page;
public int total;
public int records;
public JQGridRow[] rows;
}
public struct JQGridRow
{
public Int64 Col1;
public string Col2 ;
public string Col3 ;
public string Col4;
public Int64 Col5;
public Int64 Col6;
public Int64 Col7;
}
[Serializable]
public class User
{
public Int64 Col1 { get; set; }
public string Col2 { get; set; }
public string Col3 { get; set; }
public string Col4 { get; set; }
public Int64 Col5 { get; set; }
public Int64 Col6 { get; set; }
public Int64 Col7 { get; set; }
}
public class JQGridHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
System.Collections.Specialized.NameValueCollection forms = context.Request.Form;
HttpRequest request = context.Request;
HttpResponse response = context.Response;
string _search = forms.Get("_search");
string numberOfRows = forms.Get("rows");
string pageIndex = forms.Get("page");
string sortColumnName = forms.Get("sidx");
string sortOrderBy = forms.Get("sord");
string strOperation = forms.Get("oper");
int totalRecords;
Collection<User> users = new Collection<User>();
var provider = DbProviderFactories.GetFactory("Cassandra.Data.CqlProviderFactory");
var connection = provider.CreateConnection();
connection.ConnectionString = "Contact Points=localhost;Port=9042";
connection.Open();
var command = connection.CreateCommand();
string keyspaceName = "EmpTableSpace";
command = connection.CreateCommand();
command.CommandText = "select Col2, Col3, Col4, Col5, Col6, Col7 from " + keyspaceName + ".Emptable;";
DbDataReader reader = command.ExecuteReader();
User user;
int idx = 1;
while (reader.Read())
{
user = new User();
user.Col1 = idx++;
user.Col2 = Convert.ToString(reader["Col2"]);
user.Col3 = Convert.ToString(reader["Col3"]);
user.Col4 = String.Format("{0:MM/dd/yyyy HH:mm:ss}", reader["Col4"].ToString());
user.Col5 = Convert.ToInt16(reader["Col5"]);
user.Col6 = Convert.ToInt16(reader["Col6"]);
user.Col7 = Convert.ToInt16(reader["Col7"]);
users.Add(user);
}
totalRecords = Convert.ToInt32(reader["Col2"]);
string strResponse = string.Empty;
if (strOperation == null)
{
string output = BuildJQGridResults(users, Convert.ToInt32(numberOfRows), Convert.ToInt32(pageIndex), Convert.ToInt32(totalRecords));
response.Write(output);
}
////else if (strOperation == "del")
////{
//// var query = Query.EQ("_id", forms.Get("Col2").ToString());
//// users.Remove(query);
//// strResponse = "Employee record successfully removed";
//// context.Response.Write(strResponse);
////}
else
{
string strOut = string.Empty;
AddEdit(forms, users, out strOut);
context.Response.Write(strOut);
}
}
private string BuildJQGridResults(Collection<User> users, int numberOfRows, int pageIndex, int totalRecords)
{
JQGridResults result = new JQGridResults();
List<JQGridRow> rows = new List<JQGridRow>();
foreach (User user in users)
{
JQGridRow row = new JQGridRow();
row.Col1 = Convert.ToInt64(user.Col1);
row.Col2 = user.Col2;
row.Col3 = user.Col3;
row.Col4 = user.Col4;
row.Col5 = user.Col5;
row.Col6 = user.Col6;
row.Col7 = user.Col7;
rows.Add(row);
}
result.rows = rows.ToArray();
result.page = pageIndex;
result.total = totalRecords / totalRecords;
result.records = totalRecords;
return new JavaScriptSerializer().Serialize(result);
}
public bool IsReusable
{
get
{
return false;
}
}
private void AddEdit(NameValueCollection forms,Collection<User> users, out string strResponse)
{
string strOperation = forms.Get("oper");
Int64 strRowID = 0;
if (strOperation == "add")
{
strRowID = Convert.ToInt64(forms.Get("Col1"));
}
else if (strOperation == "edit")
{
var result = users.AsQueryable<User>().Select(c => c.Col2).Max();
strRowID = (Convert.ToInt32(result) + 1);
}
string strCol2 = forms.Get("Col2").ToString();
string strCol3 = forms.Get("Col3").ToString();
string dtCol4 = String.Format("{0:MM/dd/yyyy HH:mm:ss}", forms.Get("Col4").ToString());
int intCol5 = Convert.ToInt16(forms.Get("Col5"));
int intCol6 = Convert.ToInt16(forms.Get("Col6"));
int intCol7 = Convert.ToInt16(forms.Get("Col7"));
User objEmp = new User();
objEmp.Col1 = strRowID;
objEmp.Col2 = strCol2;
objEmp.Col3 = strCol3;
objEmp.Col4 = dtCol4;
objEmp.Col5 = intCol5;
objEmp.Col6 = intCol6;
objEmp.Col7 = intCol7;
users.Add(objEmp);
strResponse = "Record(s) successfully updated";
}
}
Default.aspx
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<h2>
LOADING DATA IN JQGRID
</h2>
<table id="jQGridDemo">
</table>
<div id="jQGridDemoPager">
</div>
<script type="text/javascript">
jQuery("#jQGridDemo").jqGrid({
url: 'JQGridHandler.ashx',
datatype: "json",
colNames: ['Col1', 'Col2', 'Col3', 'Col4', 'Col5', 'Col6', 'Col7'],
colModel: [
{ name: 'Col1', index: 'Col1', width: 20, sortable: true },
{ name: 'Col2', width: 30, sortable: true },
{ name: 'Col3', width: 50, sortable: true },
{ name: 'Col4', width: 55, sortable: true },
{ name: 'Col5', width: 30, sortable: true },
{ name: 'Col6', width: 30, sortable: true },
{ name: 'Col7', width: 30, sortable: true }
],
rowNum: 10,
mtype: 'GET',
height: 450,
width: 1200,
scroll: true,
loadonce: false,
rowList: [1000, 2000, 3000],
prmNames: { npage: 'npage' },
pager: '#jQGridDemoPager',
sortname: 'Col2',
viewrecords: true,
sortorder: 'desc',
caption: "Response Times",
editurl: 'JQGridHandler.ashx'
});
$('#jQGridDemo').jqGrid('navGrid', '#jQGridDemoPager',
{
refreshstate: 'current',
edit: true,
add: true,
del: true,
search: true,
searchtext: "Search",
addtext: "Add",
edittext: "Edit",
deltext: "Delete"
},
{sopt: ['eq', 'ne', 'lt', 'le', 'gt', 'ge', 'bw', 'bn', 'ew', 'en', 'cn', 'nc', 'nu', 'nn', 'in', 'ni']},
{
closeOnEscape: true,//Closes the popup on pressing escape key
reloadAfterSubmit: true,
multipleSearch:true,
drag: true,
afterSubmit: function (response, postdata) {
if (response.responseText == "") {
$(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid');//Reloads the grid after edit
return [true, '']
}
else {
$(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid'); //Reloads the grid after edit
return [false, response.responseText]//Captures and displays the response text on th Edit window
}
},
editData: {
EmpId: function () {
var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
return value;
}
}
},
{
closeAfterAdd: true,//Closes the add window after add
afterSubmit: function (response, postdata) {
if (response.responseText == "") {
$(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid')//Reloads the grid after Add
return [true, '']
}
else {
$(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid')//Reloads the grid after Add
return [false, response.responseText]
}
}
},
{ //DELETE
closeOnEscape: true,
closeAfterDelete: true,
reloadAfterSubmit: true,
closeOnEscape: true,
drag: true,
afterSubmit: function (response, postdata) {
if (response.responseText == "") {
$("#jQGridDemo").trigger("reloadGrid", [{ current: true }]);
return [false, response.responseText]
}
else {
$(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid')
return [true, response.responseText]
}
},
delData: {
EmpId: function () {
var sel_id = $('#jQGridDemo').jqGrid('getGridParam', 'selrow');
var value = $('#jQGridDemo').jqGrid('getCell', sel_id, '_id');
return value;
}
}
},
{//SEARCH
closeOnEscape: true
}
);
</script>
</asp:Content>
