Sunday 4 January 2015

Bind data to jqgrid using WebMethod in asp.net

We need add following pre-defined jquery plugins and css files to web form.Compare to asp.net Gridview,Jquery grid performance is past and lightweight.
As well as add Newtonsoft.json.dll


Code in jqGridWebMethod.aspx   
    <script src="JQGridReq/grid.locale-en.js"></script>
    <script src="JQGridReq/jquery-1.9.0.min.js"></script>
    <link href="JQGridReq/jquery-ui-1.9.2.custom.css" rel="stylesheet" />
    <script src="JQGridReq/jquery.jqGrid.js"></script>
    <link href="JQGridReq/ui.jqgrid.css" rel="stylesheet" />
    <script type="text/javascript">

        $(document).ready(function () {
            $.ajax({
                type: "POST",
                contentType: "application/json",
                data: "{}",
                url: "jqGridWebMethod.aspx/getEmployee",
                dataType: "json",
                success: function (data) {
                    data = data.d;
                    $("#list").jqGrid({
                        datatype: "local",
                        colNames: ['IdentityNumber', 'FirstName', 'Email', 'SurName',
                        'Password', 'BirthDate', 'Gender'],
                        colModel: [
                           { name: 'IdentityNumber', index: 'IdentityNumber', width:100},
                           {name: 'FirstName', index: 'FirstName', width: 100},
                           {name: 'Email', index: 'Email', width: 100},
                           {name: 'SurName', index: 'SurName', width: 100},
                           { name: 'Password', index: 'Password', width: 100},
                           {name: 'BirthDate', index: 'BirthDate', width: 100},
                           {name: 'Gender', index: 'Gender', width: 80}
                        ],
                        data: JSON.parse(data),
                        caption: "Employee Data"
                    });
                }
            });
        });
<table id="list"></table>
Code in jqGridWebMethod.aspx.cs
[WebMethod]
 public static string getEmployee()

    {

        SqlConnection con = new SqlConnection("Data Source=SHANKAR\\SQLEXPRESS;Initial

        Catalog=SMPL;Persist Security Info=True;User ID=sa;Password=hits@123");

        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "SELECT
        IdentityNumber,FirstName,SurName,Email,[Password],BirthDate,Gender FROM   
        dbo.tbl_Registration";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        con.Close();
        DataSet ds = new DataSet();
        da.Fill(ds);
        return Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[0]);
    }
Final output could be