U±I=UThe world is another hell: January 2012

1. Create a ASP.NET webform

a. the WebForm header must contain the following

$(document).ready(function () {

var lastsel2;

jQuery.jgrid.edit = {

addCaption: "Add New Doctor",

editCaption: "Edit Doctor Information",

bSubmit: "Save",

bCancel: "Cancel",

width: 300,

left: 100,

top: 50,

closeAfterEdit: true,

processData: "Processing...",

msg: {

required: "Field is required.",

number: "Please enter valid number!",

minValue: "value must be greater than or equal to ",

maxValue: "value must be less than or equal to"

}

};

jQuery.jgrid.del = {

caption: "Confirmation to delete Doctor",

msg: "Do you really want to delete selected Doctor?",

bSubmit: "Yes",

bCancel: "No",

processData: "Processing...",

left: 100,

top: 50,

width: 400,

modal: true

};

$("#DoctorGrid").jqGrid({

url: 'DoctorHandler.ashx',

datatype: 'json',

height: 300,

colNames: ['DoctorID', 'Doctor Name', 'Speciality', 'Contact No.', 'Email', 'Extra Info'],

colModel: [

{ name: 'DoctorID', index: 'DoctorID', width: 50, hidden: true, sortable: false },

{ name: 'DoctorName', width: 150, sortable: true, editable: true },

{name: 'Speciality', width: 150, editable: true },

{ name: 'ContactNo', width: 100, editable: true },

{ name: 'Email', width: 100, editable: true },

{ name: 'ExtraInfo', width: 200, editable: true, edittype: "textarea", editoptions: { rows: 3, width: 200} }

rowNum: 10,

rowList: [10, 30, 50, 100],

pager: '#DoctorGridPager',

sortname: 'DoctorName',

viewrecords: true,

sortorder: 'asc',

altRows: true,

sortable: true,

ondblClickRow: function (id) { jQuery('#DoctorGrid').editGridRow(id, { width: 300, left: 100, editCaption: 'Edit Doctor Information', closeAfterEdit: true }); },

editurl: 'DoctorHandler.ashx',

caption: 'Doctor Information',

});

$("#DoctorGrid").jqGrid('navGrid', '#DoctorGridPager', { edit: false, add: true, del: true }).navButtonAdd('#DoctorGridPager', {

caption: "Show/Hide Columns", buttonimg: "fullpath/row-insert-under.gif", onClickButton: function () {

$("#DoctorGrid").setColumns(options);

return false;

}

});

options = { autosearch: true };

$("#DoctorGrid").filterToolbar(options);

var sgrid = $("#DoctorGrid")[0];

sgrid.triggerToolbar();

});

</script>

b. in body section put

</table>

2. Create these classes in App_code

public struct JqGridResults

{

public int page;

public int total;

public int records;

public JqGridRow[] rows;

}

public struct JqGridRow

{

public int id;

public string[] cell;

}

public class JqGrid

{

#region Constructors

public JqGrid()

{

_connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

}

public JqGrid(HttpContext context)

{

_connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

_TotalRecords = 0;

HttpRequest request = context.Request;

if (request["_search"] == null)

_isSearch = false;

else

_isSearch = (request["_search"].ToLower() == "true");

_rows = request["rows"] == null ? 0 : Convert.ToInt32(request["rows"]);

_pageIndex = request["page"] == null ? 0 : Convert.ToInt32(request["page"]);

_SortIndex = request["sidx"];

_SortOrder = request["sord"];

_SearchField = request["searchField"];

_SearchString = request["searchString"];

_SearchOperator = request["searchOper"];

_Operation = request["oper"];

_nd = request["nd"];

_OtherConditions = getOtherConditions(request);

_SearchCondition = getSearchCondition();

_FilterExpression = null;

if (_SearchCondition != "")

_FilterExpression = _SearchCondition;

if (_FilterExpression == null)

_FilterExpression = _OtherConditions == "" ? null : _OtherConditions;

else

_FilterExpression = _OtherConditions == "" ? _FilterExpression : _SearchCondition + " AND " + _OtherConditions;

}

#endregion

#region Fields

private bool _isSearch;

private string _nd;

private int _rows;

private int _pageIndex;

private string _SortIndex;

private string _SortOrder;

private string _SearchField;

private string _SearchString;

private string _SearchOperator;

private string _Operation;

private string _OtherConditions;

private string _SearchCondition;

private string _FilterExpression;

private string _connectionString;

private int _TotalRecords;

#endregion

#region Properties

public int TotalRecords

{

get { return _TotalRecords; }

set { _TotalRecords = value; }

}

public string FilterExpression

{

get { return _FilterExpression; }

set { _FilterExpression = value; }

}

public string SearchCondition

{

get { return _SearchCondition; }

set { _SearchCondition = value; }

}

public string OtherConditions

{

get { return _OtherConditions; }

set { _OtherConditions = value; }

}

public string Operation

{

get { return _Operation; }

set { _Operation = value; }

}

public string SearchOperator

{

get { return _SearchOperator; }

set { _SearchOperator = value; }

}

public string SearchString

{

get { return _SearchString; }

set { _SearchString = value; }

}

public string SearchField

{

get { return _SearchField; }

set { _SearchField = value; }

}

public string SortOrder

{

get { return _SortOrder; }

set { _SortOrder = value; }

}

public string SortIndex

{

get { return _SortIndex; }

set { _SortIndex = value; }

}

public int PageIndex

{

get { return _pageIndex; }

set { _pageIndex = value; }

}

public int NoOfRows

{

get { return _rows; }

set { _rows = value; }

}

public string Nd

{

get { return _nd; }

set { _nd = value; }

}

public bool IsSearch

{

get { return _isSearch; }

set { _isSearch = value; }

}

#endregion

private string getOtherConditions(HttpRequest request)

{

string _where = "";

foreach (string qs in request.QueryString.AllKeys)

{

if (qs != "_search" && qs != "rows" && qs != "page" && qs != "sidx" && qs != "sord" && qs != "searchField" && qs != "searchString" && qs != "searchOper" && qs != "oper" && qs != "nd")

{

_where += (_where == "" ? "" : " AND ") + " CAST(" + qs + " AS VARCHAR) LIKE '" + request.QueryString[qs] + "' ";

}

return _where;

}

private string getSearchCondition()

{

string _where = "";

if (_SearchField != null && _SearchOperator != null && _SearchString != null)

{

switch (_SearchOperator)

{

case "eq": //equal

_where = " CAST( " + _SearchField + " AS VARCHAR) ='" + _SearchString + "' ";

break;

case "ne": //not equal

_where = " CAST( " + _SearchField + " AS VARCHAR) <>'" + _SearchString + "' ";

break;

case "lt": //less

_where = " " + _SearchField + " < " + _SearchString + " ";

break;

case "le": //less or equal

_where = " " + _SearchField + " <= " + _SearchString + " ";

break;

case "gt": //greater

_where = " " + _SearchField + " > " + _SearchString + " ";

break;

case "ge": //greater or equal

_where = " " + _SearchField + " >= " + _SearchString + " ";

break;

case "bw": //begins with

_where = " CAST( " + _SearchField + " AS VARCHAR) LIKE '" + _SearchString + "%' ";

break;

case "bn": //does not begin with

_where = " CAST( " + _SearchField + " AS VARCHAR) NOT LIKE '" + _SearchString + "%' ";

break;

case "in": //is in

_where = " " + _SearchField + " IN (" + _SearchString + ") ";

break;

case "ni": //is not in

_where = " " + _SearchField + " NOT IN (" + _SearchString + ") ";

break;

case "ew": //ends with

_where = " CAST( " + _SearchField + " AS VARCHAR) LIKE '%" + _SearchString + "' ";

break;

case "en": //does not end with

_where = " CAST( " + _SearchField + " AS VARCHAR) NOT LIKE '%" + _SearchString + "' ";

break;

case "cn": //contains

_where = " CAST( " + _SearchField + " AS VARCHAR) LIKE '%" + _SearchString + "%' ";

break;

case "nc": //does not contain

_where = " CAST( " + _SearchField + " AS VARCHAR) NOT LIKE '%" + _SearchString + "%' ";

break;

default:

_where = "";

break;

};

}

return _where;

}

public string getSelectData(string Table, string ValueField, string TextField)

{

string Data = "";

int i, nR;

DataTable dt = new DataTable();

string strQ = string.Format("SELECT {0},{1} FROM {2} ORDER BY {1}", ValueField, TextField, Table);

SqlDataAdapter da = new SqlDataAdapter(strQ, _connectionString);

da.Fill(dt);

da.Dispose();

nR = dt.Rows.Count;

if (nR == 0)

Data = "0: ";

else

{

for (i = 0; i < nR; i++)

{

Data += Convert.ToString(dt.Rows[i].Field<object>(0)) + ":" + Convert.ToString(dt.Rows[i].Field<object>(1));

if (i != nR - 1)

Data += ";";

}

return "'" + Data + "'";

}

public string getSelectData(string Table, string ValueField, string TextField, string nullText)

{

string Data = "";

int i, nR;

DataTable dt = new DataTable();

string strQ = string.Format("SELECT {0},{1} FROM {2} ORDER BY {1}", ValueField, TextField, Table);

SqlDataAdapter da = new SqlDataAdapter(strQ, _connectionString);

da.Fill(dt);

da.Dispose();

nR = dt.Rows.Count;

if (nR == 0)

Data = "0: ";

else

{

for (i = 0; i < nR; i++)

{

Data += Convert.ToString(dt.Rows[i].Field<object>(0)) + ":" + Convert.ToString(dt.Rows[i].Field<object>(1));

if (i != nR - 1)

Data += ";";

}

return "'" + "0:" + nullText + ";" + Data + "'";

}

private DataTable getData(string SelectedColumns, string FromTables)

{

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter("SP_GET", _connectionString);

da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;

da.SelectCommand.Parameters.Add("@NumberOfRows", SqlDbType.Int).Value = _rows;

da.SelectCommand.Parameters.Add("@SelectedColumns", SqlDbType.NVarChar, 500).Value = SelectedColumns;

da.SelectCommand.Parameters.Add("@FromTables", SqlDbType.NVarChar, 500).Value = FromTables;

da.SelectCommand.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = _FilterExpression == null ? " 1 = 1 " : _FilterExpression;

da.SelectCommand.Parameters.Add("@OrderBy", SqlDbType.NVarChar, 500).Value = _SortIndex + " " + _SortOrder;

//da.SelectCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Value = _pageIndex;

SqlParameter paramTotalRecords = new SqlParameter("@TotalRecords", SqlDbType.Int);

_TotalRecords = 0;

paramTotalRecords.Value = _TotalRecords;

paramTotalRecords.Direction = ParameterDirection.Output;

da.SelectCommand.Parameters.Add(paramTotalRecords);

da.Fill(dt);

_TotalRecords = (int)paramTotalRecords.Value;

da.Dispose();

return dt;

}

public string BuildJQGridResults(string SelectedColumns, string FromTables, string pkColumnName)

{

DataTable dtData = getData(SelectedColumns, FromTables);

JqGridResults result = new JqGridResults();

List<JqGridRow> rows = new List<JqGridRow>();

if (dtData.Columns[0].ColumnName.ToLower() == "rowid")

{

foreach (DataRow r in dtData.Rows)

{

JqGridRow row = new JqGridRow();

row.id = Convert.ToInt32(r[pkColumnName]);

row.cell = new string[dtData.Columns.Count - 1];

for (int i = 1; i < dtData.Columns.Count; i++)

{

row.cell[i - 1] = (r[i] == null || r[i] == DBNull.Value) ? "" : r[i].ToString();

}

rows.Add(row);

}

else

{

foreach (DataRow r in dtData.Rows)

{

JqGridRow row = new JqGridRow();

row.id = Convert.ToInt32(r[pkColumnName]);

row.cell = new string[dtData.Columns.Count];

for (int i = 0; i < dtData.Columns.Count; i++)

{

row.cell[i] = (r[i] == null || r[i] == DBNull.Value) ? "" : r[i].ToString();

}

rows.Add(row);

}

result.rows = rows.ToArray();

result.page = _pageIndex;

result.total = _TotalRecords / _rows;

if (_TotalRecords % _rows != 0) result.total += 1;

result.records = _TotalRecords;

return new JavaScriptSerializer().Serialize(result);

}

3.Create a Generic Handler

public class DoctorHandler : IHttpHandler, IReadOnlySessionState

{

public void ProcessRequest(HttpContext context)

{

JqGrid jqgrid = new JqGrid(context);

if (jqgrid.Operation == "edit")

{

Doctor objDoctor = CreateObject(context);

DalDoctor.InsertUpdateDoctor(objDoctor);

}

else if (jqgrid.Operation == "add")

{

Doctor objDoctor = CreateObject(context);

DalDoctor.InsertUpdateDoctor(objDoctor);

}

else if (jqgrid.Operation == "del")

{

DalDoctor.DeleteFromDoctor(" DoctorID=" + Convert.ToInt32(context.Request["ID"]));

}

else

{

string output = jqgrid.BuildJQGridResults("*", "Doctor", "DoctorID");

context.Response.Write(output);

}

public bool IsReusable

{

get

{

return false;

}

private Doctor CreateObject(HttpContext context)

{

Doctor obj = new Doctor();

HttpRequest request = context.Request;

if (request["oper"] == "add")

obj.DoctorID = 0;

else

obj.DoctorID = Convert.ToInt32(request["ID"]);

obj.CompanyID = Convert.ToInt32(context.Session["CompanyID"]);

obj.ContactNo = Convert.ToString(request["ContactNo"]);

obj.DoctorName = Convert.ToString(request["DoctorName"]);

obj.Email = Convert.ToString(request["Email"]);

obj.ExtraInfo = Convert.ToString(request["ExtraInfo"]);

obj.Speciality = Convert.ToString(request["Speciality"]);

return obj;

}

4. database SP

CREATE PROC [dbo].[SP_GET]

@PageIndex INT ,

@NumberOfRows INT ,

@SelectedColumns NVARCHAR(500) ,

@FromTables NVARCHAR(500) ,

@Where NVARCHAR(500) ,

@OrderBy NVARCHAR(500) ,

@TotalRecords INT OUTPUT

BEGIN

Declare @TotalRecordsQuery NVARCHAR(MAX),@CTEQuery NVARCHAR(MAX);

SET @TotalRecordsQuery=N'SELECT @TotalRecords=COUNT(*) FROM '+@FromTables+N' WHERE '+@Where;

EXECUTE SP_EXECUTESQL @TotalRecordsQuery,N'@TotalRecords INT OUTPUT',@TotalRecords OUTPUT;

SET @CTEQuery=N' WITH myCTE AS (

SELECT ROW_NUMBER() OVER ( ORDER BY '+@OrderBy+N' ) AS RowID,'+ @SelectedColumns+N' FROM

'+@FromTables+N' WHERE '+@Where +N' )

SELECT '+@SelectedColumns+N' FROM myCTE WHERE RowID BETWEEN @StartRow - @NumberOfRows

AND @StartRow - 1';

DECLARE @StartRow INT

SET @StartRow = ( @PageIndex * @NumberOfRows ) + 1 ;

EXECUTE SP_EXECUTESQL @CTEQuery,N'@StartRow INT,@NumberOfRows INT',@StartRow,@NumberOfRows;

END

More Help of Server Side Paging

CREATE PROC [dbo].[SP_GET]

@PageIndex INT =1,

@PageSize INT =50,

@SelectedColumns NVARCHAR(500)='*' ,

@FromTables NVARCHAR(500)='sysobjects' ,

@Where NVARCHAR(500)='1=1' ,

@OrderBy NVARCHAR(500)='id' ,

@TotalRecords INT OUTPUT

BEGIN

Declare @TotalRecordsQuery NVARCHAR(MAX),@CTEQuery NVARCHAR(MAX);

SET @TotalRecordsQuery=N'SELECT @TotalRecords=COUNT(*) FROM '+@FromTables+N' WHERE '+@Where;

EXECUTE SP_EXECUTESQL @TotalRecordsQuery,N'@TotalRecords INT OUTPUT',@TotalRecords OUTPUT;

SET @CTEQuery=N' WITH myCTE AS (

SELECT ROW_NUMBER() OVER ( ORDER BY '+@OrderBy+N' ) AS RowID,'+ @SelectedColumns+N' FROM

'+@FromTables+N' WHERE '+@Where +N' )

SELECT '+@SelectedColumns+N' FROM myCTE WHERE RowID BETWEEN @StartRow AND @EndRow ';

DECLARE @StartRow INT,@EndRow INT

SET @StartRow = ( (@PageIndex-1) * @PageSize ) + 1 ;

SET @EndRow = @PageIndex * @PageSize ;

EXECUTE SP_EXECUTESQL @CTEQuery,N'@StartRow INT,@EndRow INT',@StartRow,@EndRow;

END

exec SP_GET_ROWCOUNT '*','Invoices','1=1'

alter PROC [dbo].[SP_GET_ROWCOUNT]

@SelectedColumns NVARCHAR(500)='*' ,

@FromTables NVARCHAR(500)='sysobjects' ,

@Where NVARCHAR(500)='1=1'

BEGIN

Declare @TotalRecordsQuery NVARCHAR(MAX), @TotalRecords INT;

SET @TotalRecordsQuery=N'SELECT @TotalRecords=COUNT(*) FROM '+@FromTables+N' WHERE '+@Where;

EXECUTE SP_EXECUTESQL @TotalRecordsQuery,N'@TotalRecords INT OUTPUT',@TotalRecords OUTPUT;

RETURN @TotalRecords;

END

alter PROC [dbo].[SP_GET_DATA]

@PageIndex INT =0,

@PageSize INT =50,

@SelectedColumns NVARCHAR(500)='*' ,

@FromTables NVARCHAR(500)='sysobjects' ,

@Where NVARCHAR(500)='1=1' ,

@OrderBy NVARCHAR(500)='id'

BEGIN

Declare @CTEQuery NVARCHAR(MAX);

SET @CTEQuery=N' WITH myCTE AS (

SELECT ROW_NUMBER() OVER ( ORDER BY '+@OrderBy+N' ) AS RowID,'+ @SelectedColumns+N' FROM

'+@FromTables+N' WHERE '+@Where +N' )

SELECT '+@SelectedColumns+N' FROM myCTE WHERE RowID BETWEEN @StartRow AND @EndRow ';

DECLARE @StartRow INT,@EndRow INT

SET @StartRow = ( @PageIndex * @PageSize ) + 1 ;

SET @EndRow = (@PageIndex+1) * @PageSize ;

EXECUTE SP_EXECUTESQL @CTEQuery,N'@StartRow INT,@EndRow INT',@StartRow,@EndRow;

END

U±I=U^The _world is _another ^hell

Total Pageviews

Tuesday 24 January 2012

how to jqgrid in ASP.NET