Issues with Binding Datatable to Grid

[Akash]

Hi,

I have created a Telerik MVC Grid as follows. I have binded Datatable to the grid as the number of columns vary time to time. So, I cannot use the in-build CRUD methods.

1. Can you please help me how to proceed?

2. Also I have tried using Client Template and it throws exception "System.InvalidOperationException: Child actions are not allowed to perform redirect actions." 

I have gone through the 

 @(Html.Kendo().Grid(Model.Department).Name("Model.Department")
        .Columns(col =>
        {
        foreach (System.Data.DataColumn column in Model.Department.Columns)
        {
           if (column.ColumnName != "DEPTID")
                col.Bound(column.ColumnName).EditorTemplateName("string");
        }

        col.Template(@<text></text>).ClientTemplate(Html.ActionLink("Delete","DeleteDepartment",new {id = "#=DEPTID#"}).ToHtmlString());
        })
        .ToolBar(toolbar =>
        {
            toolbar.Save();
        })
        .Pageable()
        .Sortable()
        .Editable(ed => ed.Mode(GridEditMode.InCell))
        .Filterable()
        .DataSource(dataSource => dataSource
            .Ajax()
            .Batch(true)
            .ServerOperation(true)
            .Model(model =>
            {
                var id = Model.Department.PrimaryKey[0].ColumnName;
                model.Id(id);
                foreach (System.Data.DataColumn column in Model.Department.Columns)
                {
                    var field = model.Field(column.ColumnName, column.DataType);
                    if (column.ColumnName == id)
                    {
                        field.Editable(false);
                    }

                }
            })
            .Create("CreateDepartment", "Administration")
            .Read("GetDepartments", "Administration")
            .Update("UpdateDepartment", "Administration")
        ))

I

[Alex Hajigeorgieva]

Hello, Akash,

We have an example which demonstrates Binding to a DataTable and editing using the built-in functionality here:

https://github.com/telerik/ui-for-aspnet-mvc-examples/tree/master/grid/binding-to-datatable

Here are some proposed changes:

// remove the model from grid constructor
@(Html.Kendo().Grid<dynamic>()

// for batch operations, set ServerOperation(false)
ServerOpration(false)

// for the column template, create action link like this
columns.Bound(p => p.ProductID).ClientTemplate(
    "<a href='" +
        Url.Action("Delete", "DeleteDepartment") +
        "/#= DEPTID #'" +
    ">Delete Department ID</a>"
);

Documentation links:

https://docs.telerik.com/aspnet-mvc/helpers/grid/faq#how-to-use-action-links
https://docs.telerik.com/aspnet-mvc/helpers/grid/editing/batch-editing
https://demos.telerik.com/aspnet-mvc/grid/editing-custom-validation

Regards,
Alex Hajigeorgieva
Progress Telerik

[Akash]

Hi Alex,

I have tried the code snippet and followed the link provided, it does not throw any error, but the data is not rendered. I can see the data in the model but the grid is empty. I can only see the headers. Please see attached screenshots.

@model System.Data.DataTable
@{
    ViewBag.Title = "Index";
}

@(Html.Kendo().Grid<dynamic>()
    .Name("Grid")
    .Columns(columns =>
    {
        foreach (System.Data.DataColumn column in Model.Columns)
        {
            if(column.ColumnName != "DEPTID")
            columns.Bound(column.ColumnName);                     
        }
        columns.Command(cmd => cmd.Edit());
    })
    .Pageable()
    .Sortable()
    .Editable(ed => ed.Mode(GridEditMode.PopUp))
    .Filterable()
    .Groupable()
    .Scrollable()
    .DataSource(dataSource => dataSource
        .Ajax()
        .Model(model =>
        {
            var id = Model.PrimaryKey[0].ColumnName;
            model.Id(id);
            foreach (System.Data.DataColumn column in Model.Columns)
            {
                var field = model.Field(column.ColumnName, column.DataType);
                if (column.ColumnName == id)
                {
                    field.Editable(false);
                }

            }
        })       
        .Read(read => read.Action("Read", "Dept_Telerik"))
        .Update(update => update.Action("Update", "Dept_Telerik"))
    )
)

 

[Akash]

Hi Alex,

I have tried the code from the link provided, the grid with headers is displayed but there is no data populated.Please see screenshots. Below is the code:

@model System.Data.DataTable
@{
    ViewBag.Title = "Index";
}

@(Html.Kendo().Grid<dynamic>()
    .Name("Grid")
    .Columns(columns =>
    {
        foreach (System.Data.DataColumn column in Model.Columns)
        {
            if(column.ColumnName != "DEPTID")
            columns.Bound(column.ColumnName);                     
        }
        columns.Command(cmd => cmd.Edit());
    })
    .Pageable()
    .Sortable()
    .Editable(ed => ed.Mode(GridEditMode.PopUp))
    .Filterable()
    .Groupable()
    .Scrollable()
    .DataSource(dataSource => dataSource
        .Ajax()
        .Model(model =>
        {
            var id = Model.PrimaryKey[0].ColumnName;
            model.Id(id);
            foreach (System.Data.DataColumn column in Model.Columns)
            {
                var field = model.Field(column.ColumnName, column.DataType);
                if (column.ColumnName == id)
                {
                    field.Editable(false);
                }

            }
        })       
        .Read(read => read.Action("Read", "Dept_Telerik"))
        .Update(update => update.Action("Update", "Dept_Telerik"))
    )
)

[Alex Hajigeorgieva]

Hi, Akash,

Thank you very much for the provided code snippets. The grid declaration looks fine now. Currently, the Razor definition will only define the columns, grid options and data source. Then a read request for data is made to the Read action.

I am just wondering if the read method ("Read", "Dept_Telerik") actually returns any data?

Here is what a correctly configured read action method looks like, could you share yours with us?

public ActionResult Read([DataSourceRequest] DataSourceRequest request)
{
    DataTable products = Products();

    if (request.Aggregates.Any())
    {
        request.Aggregates.Each(agg =>
            {
                agg.Aggregates.Each(a =>
                    {
                        a.MemberType = products.Columns[agg.Member].DataType;
                    });
            });
    }

    return Json(products.ToDataSourceResult(request));
}

To verify that it actually returns data, you can inspect the network tab response, here is what we expect to see there:



If you like, you can send me the project for review and advise. Look forward to hearing back from you.

Regards,

Alex Hajigeorgieva
Progress Telerik

[Danny]

Greetings, this works very well, but when the search box is applied, for each key that is pressed it consumes the Read again, and additional that it does not search, as it would be done so that the dataSoruce is performed once, and specify where to search the Grid search box. try this but it doesn't work

.cshtml
@ (Html.Kendo (). Grid <dynamic> ()
           .Name ("rgvList")
           .HtmlAttributes (new {style = "height: 650px;"})
           .Scrollable ()
           .ToolBar (t => t.Search ())
           .Sortable ()
           .Groupable ()
           .BindTo (Model)
           .Selectable (selectable => selectable
             .Mode (GridSelectionMode.Single)
             .Type (GridSelectionType.Row))
           .DataSource (dataSource => dataSource
             .Ajax ()))

.cs
var Model = GListas.DataSetListados.Tables [table] .AsEnumerable ();
             return View (Model);

[Alex Hajigeorgieva]

Hello, Danny,

The Kendo UI Grid for ASP.NET MVC search bar is expected to issue requests when the search functionality is used with server operations enabled (Ajax() transport has them enabled by default).

You can set them to false, if you do not wish to issue read requests.

To search specific fields, you can select them in the Seach() configurator as shown in the documentation:

https://docs.telerik.com/aspnet-mvc/html-helpers/data-management/grid/search-panel#getting-started

Let me know in case you need further assistance.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Danny]

Hi Alex
But if I deactivate the read it doesn't load anything.
as it loads very well, the only detail is that every time I write in the search box, for each letter the read is executed again and the controller travels again to the database and obtains the data that is needed , what is required is that the DataTable is loaded and when it searches do not travel back to the controller.

In the documentation for customizing the Seach () that put the link, it works fine but with models, but here no model is used but a result of a DataTable.

[Alex Hajigeorgieva]

Hi, Danny,

Setting ServerOperation(false) will not disable the read action. It will read all of the data available in the Read Action method and perform sorting, paging, filtering, grouping and aggregating on the client side. Here is some sample code for your reference:

@(Html.Kendo().Grid<dynamic>()
    .Name("Grid")
    .ToolBar(t=>t.Search())
    .Columns(columns =>
    {
        foreach (System.Data.DataColumn column in Model.Columns)
        {
            var c = columns.Bound(column.ColumnName);

            if (column.DataType == typeof(DateTime))
            {
                c.Format("{0:dd MMM yyyy}");
            }
        }
    })
    .Pageable()
    .Sortable()
    .Scrollable()
    .DataSource(dataSource => dataSource
    .Ajax()
    .Model(model =>
    {
        foreach (System.Data.DataColumn column in Model.Columns)
        {
            var field = model.Field(column.ColumnName, column.DataType);
        }
    })
    .Read(read => read.Action("GetDataTable", "Home"))
    .ServerOperation(false)
    )
)

// Home controller
public ActionResult Index()
        {
            DataTable products = GetDataTable(500);

            return View(products);
        }

        public ActionResult GetDataTable([DataSourceRequest] DataSourceRequest request)
        {
            System.Data.DataTable dt = GetDataTable(500);
            return Json(dt.ToDataSourceResult(request));
        }

        private static DataTable GetDataTable(int howMany)
        {
            var dataSource = new DataTable();
            dataSource.Columns.Add("Name", typeof(string));
            dataSource.Columns.Add("EmailAddress", typeof(string));
            dataSource.Columns.Add("MemberId", typeof(int));
            dataSource.Columns.Add("Tourism", typeof(bool));
            for (int i = 0; i < howMany; i++)
            {
                dataSource.Rows.Add("Name " + i, "EmailAddress " + i, i, i%3==0?true:false);
            }
            return dataSource;
        }

I am also attaching a ruinnable project for your reference, you will need to just include a reference to the Kendo.Mvc.dll.

I hope this helps.

Regards,
Alex Hajigeorgieva
Progress Telerik

[Danny]

Hi Alex

It was perfect, understood, thanks for the answers, successes.