We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Bind JSON data to Grid

I am using syncfusion grid in my project and to bind data to grid I have used following code but the data that I assigned to result in JSON format so that data not display in grid.

public JsonResult GetData([Bind(Prefix = "$top")]int top = 24, [Bind(Prefix = "$skip")]int skip = 0)
        {
            int count = 0;
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string JSONString = null;
            string connectionStr = System.Configuration.ConfigurationManager.AppSettings["connstring"].ToString();

            using (SqlConnection cn = new SqlConnection(connectionStr))
            {
                cn.Open();

                //Getting the records based on skip and top value.
                SqlCommand cmd = new SqlCommand("SELECT A1,A2,A3,A4,A5,A6,A7 FROM TBLA order by UNIQUESRL OFFSET " + skip + " rows  FETCH NEXT " + top + " ROWS ONLY", cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                dt = (DataTable)ds.Tables[0];

                JSONString = JsonConvert.SerializeObject(dt);
                //Get the total record count
                SqlCommand cmd1 = new SqlCommand("SELECT COUNT(*) FROM TBLA", cn);
                using (SqlDataReader dr1 = cmd1.ExecuteReader())
                {
                    while (dr1.Read())
                        count = (int)dr1.GetValue(0);
                }
                cn.Close();
            }
            return Json(new { result = JSONString, count = count }, JsonRequestBehavior.AllowGet); //result - records, count-total records from database
        }

I called above method using following code.I have attached output of above code for your reference.

 var dataManger = ej.DataManager({ url: "http://localhost:52679/Grid/GetData" });
        $("#Grid").ejGrid({
            // the datasource "window.gridData" is referred from jsondata.min.js
            dataSource: dataManger,
            allowPaging: true,
            enableAltRow: true,
            columns: [
                        { field: "A1", headerText: "Order ID", textAlign: ej.TextAlign.Right },
                        { field: "A2", headerText: "Customer ID" },
                        { field: "A3", headerText: "Employee ID", textAlign: ej.TextAlign.Right },
                        { field: "A4", headerText: "Freight" },//textAlign: ej.TextAlign.Right, width: 75, format: "{0:C}"
                        { field: "A5", headerText: "Ship City" }
            ]
        });

Please let me know how can I display JOSN data return by MVC controller action.


Attachment: output_4baa73de.rar

7 Replies

GU guoxuefeng April 22, 2016 02:53 PM UTC

 var dataManger = ej.DataManager({ url: "http://localhost:52679/Grid/GetData" ,adapter=new urlAdapter});


PA Pankaj April 23, 2016 03:07 PM UTC

Thanks for the replay.

I have tried above code but its not working.can you provide any example that explain the same.


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team April 25, 2016 11:43 AM UTC

Hi Pankaj, 

You are serializing the object to json string using SerializeObject before binding them to Grid which is the cause of the problem. The Grid will accept the result as a LIST of JSON objects and not the JSON string. So we suggest to convert the dataTable and return them as a list. Refer to the following code example. 

        public ActionResult DataSource(Syncfusion.JavaScript.DataManager dm) 
        { 
 
            using (SqlConnection cn = new SqlConnection(connectionStr)) 
            { 
                cn.Open(); 
 
                //Getting the records based on skip and top value. 
                SqlCommand cmd = new SqlCommand("SELECT OrderID,CustomerID,EmployeeID,Freight,OrderDate FROM Orders", cn); 
                SqlDataAdapter da = new SqlDataAdapter(cmd); 
                da.Fill(ds); 
                dt = (DataTable)ds.Tables[0]; 
                . . . . . 
            }  
            var order = (from DataRow row in dt.Rows 
                         select new Order 
                         { 
                             OrderID = Convert.ToInt32(row["OrderID"]), 
                             CustomerID = row["CustomerID"].ToString(), 
                             Freight = Convert.ToDecimal(row["Freight"]), 
                             EmployeeID = Convert.ToInt32(row["EmployeeID"]), 
                             OrderDate = Convert.ToDateTime(row["OrderDate"]) 
                         }).Skip(dm.Skip).Take(dm.Take).ToList(); 
            return Json(new { result = order, count = count }, JsonRequestBehavior.AllowGet);  
        } 

We have prepared a sample that can be downloaded from the following location. 


Regards, 
Seeni Sakthi Kumar S. 



PA Pankaj April 25, 2016 12:40 PM UTC

Hi,

Thanks for the replay.

But in my case column definition is not fixed it change dynamically.So not possible to used following lines of code.

var order = (from DataRow row in dt.Rows 
                         select new Order 
                         { 
                             OrderID = Convert.ToInt32(row["OrderID"]), 
                             CustomerID = row["CustomerID"].ToString(), 
                             Freight = Convert.ToDecimal(row["Freight"]), 
                             EmployeeID = Convert.ToInt32(row["EmployeeID"]), 
                             OrderDate = Convert.ToDateTime(row["OrderDate"]) 
                         }).Skip(dm.Skip).Take(dm.Take).ToList(); 

Can it is not possible the data which I get in datatable on which apply skip,take.where and filter conditions or any alternative.Please suggest me?


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team April 26, 2016 02:28 PM UTC

Hi Pankaj 

We have achieved your requirement “Dynamically define the columns without using any class name” using TypeBuilder. Refer to the following code example. 

        public ActionResult DataSource(Syncfusion.JavaScript.DataManager dm) 
        { 
            int count = 0; 
            DataSet ds = new DataSet(); 
            DataTable dt = new DataTable(); 
            string JSONString = null; 
            string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString(); 
            using (SqlConnection cn = new SqlConnection(connectionStr)) 
            { 
                cn.Open(); 
                    . . . . 
            } 
            var order = ToDynamicList(dt); 
            order = order.Skip(dm.Skip).Take(dm.Take).ToList(); 
 
            return Json(new { result = order, count = count }, JsonRequestBehavior.AllowGet); //result - records, count-total records from database 
        } 
        public List<dynamic> ToDynamicList(DataTable dt) 
        { 
            List<string> cols = (dt.Columns.Cast<DataColumn>()).Select(column => column.ColumnName).ToList(); 
            var dictData = ToDictionary(dt); 
            return ToDynamicList(dictData, getNewObject(cols, dictData)); 
        } 
        public List<dynamic> ToDynamicList(List<Dictionary<string, object>> list, Type TypeObj) 
        { 
            dynamic temp = new List<dynamic>(); 
            foreach (Dictionary<string, object> step in list) 
            { 
                object Obj = Activator.CreateInstance(TypeObj); 
                PropertyInfo[] properties = Obj.GetType().GetProperties(); 
                Dictionary<string, object> DictList = (Dictionary<string, object>)step; 
                foreach (KeyValuePair<string, object> keyValuePair in DictList) 
                { 
                    foreach (PropertyInfo property in properties) 
                    { 
                        if (property.Name == keyValuePair.Key) 
                        { 
                            property.SetValue(Obj, keyValuePair.Value, null); 
                            break; 
                        } 
                    } 
                } 
                temp.Add(Obj); 
            } 
            return temp; 
 
        } 
                . . . .  
    } 

We have prepared a sample that can be downloaded from the following location. 


Regards, 
Seeni Sakthi Kumar S. 



AA Avishake Attari January 2, 2017 05:22 AM UTC

I am trying to bind syncfusion grid using ajax and asp.net and not mvc. so how can I bind the grid .
this is the ajax calling caode:
   $.ajax({
                type: "POST",
                url: "StockTransfer.aspx/LoadStockTransfer",
                data: '{ }',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    alert(response.d);

                    $("#<%=gvCallLog.ClientID%>").ejGrid({
                        dataSource: response.d,
                    });
                    //$("#spanSupplierAddress").html(response.d);
                },
                failure: function (response) {
                    alert(response);
                }
            });

my aspx.cs method is
 [System.Web.Services.WebMethod]
        public static string LoadStockTransfer()
        {

            List<EmptyTable> objEmptyTable = new List<EmptyTable>();

            EmptyTable obj1 = new EmptyTable();
            obj1.SrNo = 1;
            obj1.ProductCode = "aa";
            obj1.UOMNo = "1";
            obj1.ProductName = "p1";
            obj1.Quantity = "12";
            obj1.UOMName = "pcs";
            obj1.Price = "12.50";
            obj1.Amount = "150.00";

            objEmptyTable.Add(obj1);

            EmptyTable obj2 = new EmptyTable();
            obj2.SrNo = 1;
            obj2.ProductCode = "aa";
            obj2.UOMNo = "1";
            obj2.ProductName = "p1";
            obj2.Quantity = "12";
            obj2.UOMName = "pcs";
            obj2.Price = "12.50";
            obj2.Amount = "150.00";
            objEmptyTable.Add(obj2);

            StringBuilder sbResult = new StringBuilder();

            int rowCounterExported = 0;
            foreach (var item in objEmptyTable)
            {
                rowCounterExported++;
                if (rowCounterExported > 1)
                    sbResult.Append(",");

                sbResult.Append("{" + enq("SrNo") + ":" + enq(item.SrNo.ToString()) + ",");
                sbResult.Append(enq("ProductCode") + ":" + enq(item.ProductCode.ToString()) + ",");
                sbResult.Append(enq("UOMNo") + ":" + enq(item.UOMNo.ToString()) + ",");
                sbResult.Append(enq("ProductName") + ":" + enq(item.ProductName.ToString()) + ",");
                sbResult.Append(enq("Quantity") + ":" + enq(item.Quantity.ToString()) + ",");
                sbResult.Append(enq("UOMName") + ":" + enq(item.UOMName.ToString()));
                sbResult.Append(enq("Price") + ":" + enq(item.Price.ToString()));
                sbResult.Append(enq("Amount") + ":" + enq(item.Amount.ToString()));
                sbResult.Append("}");

            }
            string resultText = " { identifier: 'ProductCode', label: 'Products', items:[";
            resultText += sbResult.ToString();
            resultText += "]}";
            Daten = resultText;
return Daten;
}

I receive the required data in ajax success but grid is not binding with the rows. Please let me know where and what am I doing wrong.


SS Seeni Sakthi Kumar Seeni Raj Syncfusion Team January 3, 2017 12:12 PM UTC

Hi Avishake, 
 
Thanks for contacting Syncfusion Support.  
 
We could see you are assigning a string to the Grid which is the cause of the problem. Grid will accept and bind Array of JSON objects but you have assigned a JSON string. So we recommend to return JSON object from the Server and assign them to the Grid. Refer to the following code example. 
 
    <input type="button" id="btn1"> 
    <ej:Grid ID="Grid" ClientIDMode="Static" runat="server" AllowPaging="True"> 
        <Columns> 
            <ej:Column Field="SrNo" TextAlign="Left"></ej:Column> 
              . . 
 
        </Columns> 
    </ej:Grid> 
    <script type="text/javascript"> 
 
        $("#btn1").ejButton({ 
            text: "click", 
            click: function (args) { 
                $.ajax({ 
                    type: "POST", 
                    url: "Default.aspx/LoadStockTransfer", 
                           . . 
                    success: function (response) { 
                        var gridObj = $("#Grid").ejGrid("instance"); 
                        gridObj.dataSource(response.d.objEmptyTable) 
                    } 
                }); 
 
            } 
        }) 
    </script> 
 
        [System.Web.Services.WebMethod] 
        public static object LoadStockTransfer() 
        { 
 
            List<EmptyTable> objEmptyTable = new List<EmptyTable>(); 
 
            EmptyTable obj1 = new EmptyTable(); 
            obj1.SrNo = 1; 
                  . . . 
 
            objEmptyTable.Add(obj1); 
                . .  
            return new { objEmptyTable }; 
        } 
 
We have used the dataSource method of the Grid to update them. Refer to the following API Reference Section. 
 
 
We have also prepared a sample that can be downloaded from the following location. 
 
 
Regards, 
Seeni Sakthi Kumar S. 


Loader.
Up arrow icon