Can I bind jqGrid source from DataTable directly as done in asp DataGrid control

Question

In my MVC application I am using jqGrid for displaying records throughout the application. To load the grid I am iterating over the Dataset object retrieved from stored procedure to build List of structured object. See the code below

List<CustomerOrders> ResultRows = new List<CustomerOrders>();
        JQGridResult Result = new JQGridResult()
        {
            page = 1,
            records = 0,
            orders = new List<CustomerOrders>(),
            total = 1
        };
        DataSet SearchResult = DB.ExecuteDataset("ConnectionString", "pc_ADMDbdPOXOrderStatsDetails",
                                new SqlParameter("@AggregateFlag", 1),
                                new SqlParameter("@CustomerID", SessionManager.GetSession().CustomerID),
                                new SqlParameter("@QualifierType", QualifierType),
                                new SqlParameter("@FromDate", FromDate),
                                new SqlParameter("@ToDate", ToDate),
                                new SqlParameter("@RoleList", null),// remove SqlParameter; when SP is modified to remove this param.
                                new SqlParameter("@BranchList", BranchList == "" ? null : BranchList),
                                new SqlParameter("@Display", null));
        //Export to Excel
        Common.SetSession(SearchResult, null, "POXOrderStatistics");

        DataRowCollection DataRows = SearchResult.Tables[0].Rows;
        foreach (DataRow item in DataRows)
        {
            ResultRows.Add(new CustomerOrders()
            {
                CustomerID = Convert.ToInt32(item["CustomerID"]),
                CompanyName = Convert.ToString(item["CompanyName"]),
                ReadingCount = Convert.ToInt32(item["ReadingCount"]),
                DateRange = string.Format("From: {0} To: {1}", FromDate, ToDate)
            });
        }
        Result.orders = ResultRows;
        Result.records = ResultRows.Count;
        return Json(Result, JsonRequestBehavior.AllowGet);

Now what I want is do eliminate this iteration over the dataset & need to find out way by which I can directly return my Dataset/ DataTable object as records source for jqGrid. Somwhat similar to how we bind DataSet object to asp grid control. If anybody have done such kind implementation please help me in this code elimination

score 2 · Accepted Answer · edited May 23 '17 at 10:25

Sorry, but I don’t understand your question. You wrote that you want "directly return myDataset/ DataTable object as records source for jqGrid". jqGrid is pure JavaScript solution implemented as jQuery plugin. It works inside of web browser on the client side. If you use datatype: "json" jqGrid can get the input data from the server. For example it can get the data returned from an Action of ASP.NET MVC controller. Nevertheless one can’t speak about "DataSet as the direct source for jqGrid". Instances of DataSet class can exist only on the server side "inside" of your C# code. To be used in jqGrid the data should be first serialized to JSON string and then be sent per HTTP from the server to the client. The web browser should deserialize JSON string to JavaScript object and then jqGrid can use the JavaScript object as the input for the grid.

If you want to optimize your code I would recommend you to use SqlCommand and SqlDataReader instead of DataSet and ResultRows. In the way you can improve performance of your access to the database. For more details see the answer, the source, the source, the source and many other. In general if you need just read the data from SQL Server then SqlCommand and SqlDataReader are the most quick way to do this. The code is very simple and it could be about like below

...
var con = new SqlConnection(Settings.Default.DB);
var cmd = new SqlCommand("dbo.pc_ADMDbdPOXOrderStatsDetails", con) {
              CommandType = CommandType.StoredProcedure
          };
cmd.Parameters.AddRange(new[] {
    new SqlParameter("@AggregateFlag", SqlDbType.Bit) { Value = 1 },
    new SqlParameter("@CustomerID", SqlDbType.Int) {
        Value = (int)SessionManager.GetSession().CustomerID
    },
    ...
});

con.Open();
SqlDataReader dr = cmd.ExecuteReader();
int CustomerID = dr.GetOrdinal("CustomerID"),
    CompanyName = dr.GetOrdinal("CompanyName"),
    ReadingCount = dr.GetOrdinal("ReadingCount");
var dateRange = string.Format("From: {0} To: {1}", FromDate, ToDate);

var listOfOrders = new List<CustomerOrders>();
while (dr.Read()) {
    listOfOrders.Add(new CustomerOrders() {
        CustomerID = dr.GetInt32(CustomerID),
        CompanyName = Convert.ToString(CompanyName),
        ReadingCount = dr.GetInt32(ReadingCount),
        DateRange = dateRange
    });
}

return Json(listOfOrders, JsonRequestBehavior.AllowGet);

The action would returns array of CustomerOrders items which can be read by jqGrid. I suppose that you use loadonce: true because your code have no server side paging of data.

Hi Oleg please can you review my [this](http://stackoverflow.com/questions/21191473/how-to-assign-option-string-containing-br-to-editions-value-attribute-of-jqgri) quetion and suggest me solution for it. This needs to be urgently resolved. — Shaggy, Jan 17 '14 at 16:57

Can I bind jqGrid source from DataTable directly as done in asp DataGrid control

1 Answers1