Friday, 6 December 2013

Bind JQgrid in MVC

Hello Friends,
Here is simple example how to bind JQgrid in MVC.

<table id="grid" class="scroll"></table>
 <div id="pager" class="scroll" style="text-align: center;"></div>

 $(document).ready(function () {

        $("#grid").jqGrid({
            url: "/QueryPad/GetData",
            datatype: "json",
            mtype: "GET",
            colNames: ["Product ID", "Name", "Price"],
            colModel: [
                        { name: "ProductID", index: "ProductID" },
                        { name: "Name", index: "Name" },
                        { name: "Price", index: "Price" }
            ],
            rowNum: 1,
            rowList: [20, 50, 100],
            pager: "#pager-json",
            sortname: "ProductID",
            sortorder: "ASC",
            viewrecords: true,
            caption: "Get Data"
        });
});

public string GetData()
{
string ConnectionString = ""; // Put your connection string here
string Query = "Select * from Product";
SqlConnection con = new SqlConnection(ConnectionString);
try
{
SqlCommand cmd1 = new SqlCommand(Query, con);
DataTable dt = new DataTable();
SqlDataReader dr = cmd1.ExecuteReader();
dt.Load(dr);
string StringData = ConvertDataTabletoString(dt);
con.Close();
return StringData;
}
catch (Exception ex)
{
con.Close();
ViewBag.Result = ex.Message;
return "";
}
}

public string ConvertDataTabletoString(DataTable dt)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);              
}

No comments:

Post a Comment