Total Pageviews

Tuesday, 24 January 2012

how to jqgrid in ASP.NET


1. Create a ASP.NET webform

   a. the WebForm header must contain the following 
 <link href="../themes/redmond/jquery-ui-1.8.1.custom.css" rel="stylesheet" type="text/css" />
    <link href="../themes/ui.jqgrid.css" rel="stylesheet" type="text/css" />
    <script src="../js/jquery-1.6.2.js" type="text/javascript"></script>
    <script src="../js/grid.locale-en.js" type="text/javascript"></script>
    <script src="../js/jquery.jqGrid.min.js" type="text/javascript"></script>
    <script type="text/javascript">

        $(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 id="DoctorGrid" cellpadding="0" cellspacing="0">
    </table>
    <div id="DoctorGridPager">

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
 AS
 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
 AS
 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'
  AS
 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'
 AS
 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

at

1 comment:

  1. Anonymous12 April 2022 at 18:09

    This comment has been removed by a blog administrator.

    ReplyDelete

Subscribe to: Post Comments (Atom)