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
  1. Home
  2. Forum
  3. ASP.NET MVC
  4. Grid binding datatble and Json

Grid binding datatble and Json

Hi,

I need fill a grid with the results of a store procedure. The store procedure returns a datatable, so I need convert the datatable in JSON and bind this results with the grid. But I get an error

I do this

public ActionResult GridFeatures()
{
var DataSource = GetDataTale();
ViewBag.datasource = ConvertDataTableToJSON(DataSource);
return View();
}

private string ConvertDataTableToJSON(DataTable dt)
{
JavaScriptSerializer jSonString = new JavaScriptSerializer();
List> rows = new List>();
Dictionary row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return jSonString.Serialize(rows);
}


@(Html.EJ().Grid<object>;("FlatGrid")
.Datasource((IEnumerable<object>)ViewBag.datasource)
.AllowScrolling()
.AllowFiltering()
.FilterSettings(filter => { filter.ShowFilterBarStatus().StatusBarWidth(500).FilterBarMode(FilterBarMode.Immediate); }) /*Filtering Enabled*/
.AllowSorting() /*Sorting Enabled*/
.AllowPaging() /*Paging Enabled*/
.SelectionType(SelectionType.Single)
.AllowResizing()
.Locale("es-ES")
.Columns(col =>
{
col.Field("Id").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(75).Add();
col.Field("Name").Width(80).Add();
col.Field("Country").TextAlign(TextAlign.Right).Width(75).Add();
}))

I attach an example project

Attachment: gridDataTable_1f1688de.zip

8 Replies
MS Mani Sankar Durai Syncfusion Team October 10, 2016 11:35 AM UTC

Hi Manolo, 

Thanks for contacting Syncfusion support. 

We have analyzed your query and found the root cause of the issue. Since you have passed the data as string to the grid dataSource instead of passing as list. This makes the data not to bind on the grid. So we suggest you to pass the data as a list to the grid instead of string. 
Please refer the below code example, 
[GridController.cs] 
public ActionResult GridFeatures() 
        { 
            var DataSource = GetDataTale(); 
            ViewBag.datasource = ConvertDataTableToJSON(DataSource); 
            return View(); 
        } 
 
         private static object ConvertDataTableToJSON(DataTable dt) 
        { 
            JavaScriptSerializer jSonString = new 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); 
            } 
            string serialize = jSonString.Serialize(rows); 
            var data = jSonString.Deserialize<IEnumerable<object>>(serialize); 
            return data; 
            
        } 
 
 

Refer the screenshot below, 
 

We have also modified your sample that can be downloaded from the below link, 

Also we can bind the dataTable directly to the grid as dataSource without converting it into JSON separately. 
Please refer the below code example, 
(Html.EJ().Grid<object> 
        ("FlatGrid") 
        .Datasource((System.Data.DataTable)ViewBag.dataSource) 
        ) 

Also please refer the documentation link of how we bound the dataTable to grid. 

Please let us know if you need further assistance. 

Regards, 
Manisankar Durai. 


MA Manolo October 11, 2016 07:21 AM UTC

Thank you!

MS Mani Sankar Durai Syncfusion Team October 12, 2016 08:48 AM UTC

Hi Manolo, 

Thanks for your feedback. 

Please let us know if you need further assistance. 

Regards, 
Manisankar Durai. 


IG indrani Gajjarapu October 12, 2016 11:24 AM UTC

Hi Manolo,

I did bind my grids with DataTables.

But, one of the datatable have a photo column in it and hence Maxjsonlength error is returned.

I tried to Implement Load on Demand which gives me a circular reference exception..

Please help me how to bind a datatable to the grid which has huge data in it.

Thank you

MA Manolo October 13, 2016 07:31 AM UTC

Hi indrani,

For circular reference exception you can see this post

https://www.syncfusion.com/kb/6290/circular-reference-error-while-using-entity-framework-linq-2-sql

if you remove the photo field, yo also get the exception?

MS Mani Sankar Durai Syncfusion Team October 13, 2016 12:45 PM UTC

Hi Indrani, 

We have analyzed your query and we are not able to reproduce the reported issue. We have also prepared a sample based on your requirement by passing the huge amount of data from the data table with photo column and the sample can be downloaded from the below link. 

Please refer the below code example. 
[HomeController.cs] 
{ 
            var DataSource = GetDataTale(); 
            ViewBag.datasource = datalist; 
            return View(); 
        } 
        
        private DataTable GetDataTale() 
        { 
            DataTable dt = new DataTable("Table"); 
            dt.Columns.AddRange(new DataColumn[4] { 
                            new DataColumn("pictureURL", typeof(string)), 
… 
          DataRow dr = null; 
             
            for (int i = 0; i <= 10000; i++) 
            { 
                dr = dt.NewRow(); 
                dr[0] = "<img src ='../ejThemes/Employee/6.png'>"; 
                … 
            } 
            return dt; 
 
[Index.cshtml] 
 
@(Html.EJ().Grid<object>("DetailGrid") 
                .Datasource((IEnumerable<object>)ViewBag.datasource) 
        .AllowPaging(true) 
    
        .Columns(col => 
        { 
            col.Field("pictureURL").HeaderText("Employee Image").TextAlign(TextAlign.Center).Width(110).Add(); 
            … 
        }) 
) 
 

In the above sample we have passed the 10000 Records data with photo column using dataTable. 

To avoid the circular reference error please refer the Knowledge base from the below link. 
 
If you still face the issue please get back to us with the following details. 
1.       Have you faced any script error in console window while binding the data to the grid? 
2.       If so please expand and send the screenshot. 
3.       Send the full grid code with controller. 
4.       If possible please replicate the issue in the above attached sample. 
The provided information will help us to analyze the issue and provide you the response as early as possible. 

Regards, 
Manisankar Durai. 


MA Manolo December 15, 2016 11:24 AM UTC

Hi again,

In my grid, that is binding with a DataTable, now I need edit the grid adding or edit elemens inline. But I need save the results with a custom store procedures.

I can't execute the function in server side for save the records.

How can I do it?

Thanks

MS Mani Sankar Durai Syncfusion Team December 16, 2016 09:39 AM UTC

Hi Manolo, 


We have analyzed your query and we suggest you to perform the CRUD operations in server side by specifying the adaptor type as RemoteSaveAdaptor adaptor and also specify the CRUD actions Url to the corresponding DataManager properties of grid. The corresponding server side CRUD actions methods has been triggered when we perform any CRUD action in grid. 


Please refer the below code example. 

[Index.cshtml] 
 
@(Html.EJ().Grid<object>("DetailGrid") 
         .Datasource(ds => ds.Json((IEnumerable<object>)ViewBag.datasource).UpdateURL("/Home/Update") 
                   .InsertURL("/Home/Insert").RemoveURL("/Home/Remove").Adaptor(AdaptorType.RemoteSaveAdaptor)) 
               
        .AllowPaging(true) 
    
        .Columns(col => 
        { 
            col.Field("pictureURL").HeaderText("Employee Image").TextAlign(TextAlign.Center).Width(110).Add(); 
            col.Field("Id").HeaderText("ID1").TextAlign(TextAlign.Right).Width(125).Add(); 
            col.Field("Name").HeaderText("Value").Width(100).Add(); 
            col.Field("Qty").HeaderText("Qty").Width(100).TextAlign(TextAlign.Right).Priority(2).Add(); 
        }) 
) 
 
[Controller.cs] 
public ActionResult Update(EditableOrder value) 
        { 
            // perform update opertion 
        } 
        public ActionResult Insert(EditableOrder value) 
        { 
              //perform add operation 
        } 
        public ActionResult Remove(int key) 
        { 
            //perform delete operation 
        } 


Please refer the documentation link of about RemoveSaveAdaptor in grid. 

Please refer the online sample link about Cell Edit Type in grid. 

Also please refer the documentation link of about inline editing. 



Please let us know if you need further assistance. 


Regards,
Manisankar Durai 


Loader.
Up arrow icon