Grid with Date column from DataTable

[Robert]

Hello,

I'm developing a ASP.NET MVC3 application using the Kendo UI Extensions for MVC.
I now got in troubles using the Grid control with a DataTable as data source (It must be a DataTable). When I try to display a date it always shows like this: /Date(1374066855137)/

I can't figure out how to display the date correctly. As written in many forums an blogs i tried to set the member type of the column to DateTime. However, there is no difference.
I attached a small demo project, witch should show my problem.

Any help is highly appreciated
Robert

(i removed the kendo script files, otherwise the solution was bigger than 2MB. Version is 2013.1.514)

[Jayesh Goyani]

Hello,

Please try with the below code snippet.

  (if you know about the date-field/Column-Name name)

@using System.Data;
@model KendoGridDateDemo.Models.DemoModel
@{
    ViewBag.Title = "Home Page";
}
<script>
    function onDataBound(arg) {
        if (arg.sender.dataSource._data.length > 0) {
            for (var i = 0; i < arg.sender.dataSource._data.length; i++) {
                var objDate = kendo.parseDate(arg.sender.dataSource._data[i].Date)
                arg.sender.dataSource._data[i].Date = objDate.getDate() + "-" + objDate.getMonth() + "-" + objDate.getFullYear();
            }
        }
    }
</script>
<h2>@ViewBag.Message</h2>
@{
    Html.Kendo().Grid(Model.DataSource) // Set Grid datasource
    .Name("Demo")
    .DataSource(datasource => datasource.Ajax()
        .Read("_GetData", "Home"))
    .Columns(columns => columns.LoadSettings(Model.Columns as IEnumerable<Kendo.Mvc.UI.GridColumnSettings>))
    .Events(events => events.DataBinding("onDataBound"))

    .Render();
}

 
(if you do not know about the date-field/Column-Name name)

@using System.Data;
@model KendoGridDateDemo.Models.DemoModel
@{
    ViewBag.Title = "Home Page";
}
<script>
    function onDataBound(arg) {
        if (arg.sender.dataSource._data.length > 0) {
            var keys = Object.keys(arg.sender.dataSource._data[0]);
            for (var i = 0; i < arg.sender.dataSource._data.length; i++) {
                for (var k = 0; k < keys.length; k++) {
                    var mykey = keys[k];
                    var KeyValue = arg.sender.dataSource._data[i][mykey];
                    if (KeyValue != null && KeyValue != undefined && typeof (KeyValue) == "string" && KeyValue.indexOf("/Date(") != -1) {
                        var objDate = kendo.parseDate(arg.sender.dataSource._data[i][mykey]);
                        arg.sender.dataSource._data[i][mykey] = objDate.getDate() + "-" + objDate.getMonth() + "-" + objDate.getFullYear();
                    }
                }


            }
        }
    }
</script>
<h2>@ViewBag.Message</h2>
@{
    Html.Kendo().Grid(Model.DataSource) // Set Grid datasource
    .Name("Demo")
    .DataSource(datasource => datasource.Ajax()
        .Read("_GetData", "Home"))
    .Columns(columns => columns.LoadSettings(Model.Columns as IEnumerable<Kendo.Mvc.UI.GridColumnSettings>))
    .Events(events => events.DataBinding("onDataBound"))

    .Render();
}

Thanks,
Jayesh Goyani

[Vladimir Iliev]

Hi Robert,

 
After reviewing the provided project it seems that the dates are rendered as a string as you never set the type for that field, however we already provide such example of setting correctly the type of each field when the Grid is bound to DataTable:

• Grid binding to DataTable

Kind Regards,
Vladimir Iliev
Telerik

[Robert]

Hello,

Thank you for your replies! Both of your suggestions work.

Vladimir, is there a way to set the date format for a whole project? Like setting the UI Culture?

Cheers

[Vladimir Iliev]

Hi Robert,

 
I'm afraid that there is no build-in way of setting the Date format for the whole project in single place. 

Kind Regards,
Vladimir Iliev
Telerik

[Christoph]

Hi All,
Ihad the same problem and I used @Jayesh Goyani solution. Now it works great. My only problem is that if I make my Grid editable by adding something like this .Editable(editable => editable.Mode(GridEditMode.InLine)) I get an exception saying : "System.InvalidOperationException: Templates can be used only with field access, property access, single-dimension array index, or single-parameter custom indexer expressions"

Any ideas?

[Jayesh Goyani]

Hi,

Could you please provide your code snippet?

Thanks,
Jayesh Goyani

[Christoph]

My Model
namespace PixieKendoMVC.Models
{
    public class QueryModel
    {
        public IEnumerable<Kendo.Mvc.UI.GridColumnSettings> Columns { get; set; }
        public DataTable ModelDataSource { get; set; }
    }
}
My Controller has 2 actions, one for returning the view and one for the json data

        public ActionResult ReadQuery([DataSourceRequest] DataSourceRequest request)
        {
            IDbConnection Connection = Repository.GetConnection();
            string SQLText = "Select * from Products"
            var dataTable = new DataTable();
            var model = new QueryModel();
            using (var dataAdapter = new SqlDataAdapter(SQLText, (SqlConnection)Connection))
            {
                dataAdapter.Fill(dataTable);
                dataAdapter.FillSchema(dataTable, SchemaType.Mapped);

            }
            var settings = new List<Kendo.Mvc.UI.GridColumnSettings>();
            foreach (DataColumn column in dataTable.Columns)
            {
                settings.Add(new GridColumnSettings { Member = column.ColumnName, Title = column.ColumnName, MemberType = column.DataType });

            }
            model.Columns = settings;
            model.ModelDataSource = dataTable;

            return Json(dataTable.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);

        }

        public ActionResult Query([DataSourceRequest] DataSourceRequest request)
        
        {
            var SQLText = "Select * from Products"
            var dataTable = new DataTable();
            var model = new QueryModel();
            using (var dataAdapter = new SqlDataAdapter(SQLText, (SqlConnection)Connection))
            {
            
                dataAdapter.Fill(dataTable);
                dataAdapter.FillSchema(dataTable, SchemaType.Mapped);
             
            }
           
            var settings = new List<Kendo.Mvc.UI.GridColumnSettings>();
            foreach (DataColumn column in dataTable.Columns)
            {
                settings.Add(new GridColumnSettings { Member = column.ColumnName, Title = column.ColumnName, MemberType = column.DataType });
 
            }
            model.Columns = settings;
            return View(model);

        } 

And My view



@model PixieKendoMVC.Models.QueryModel


@{ Html.Kendo().Grid(Model.ModelDataSource) // Set Grid datasource
    .Name("Demo")
    .DataSource(datasource => datasource
        .Ajax()
         .PageSize(19)
        .Read(read => read.Action("ReadQuery", "Product",  new { id = 1 }))
         .Update(update => update.Action("ReadQuery", "Product"))
        )
    .Columns(columns => columns.LoadSettings(Model.Columns as IEnumerable<Kendo.Mvc.UI.GridColumnSettings>))
    .Pageable()
    .Sortable()
    .Groupable()
   
    .Events(events => events.DataBinding("onDataBound"))
    .Resizable(resize => resize.Columns(true))
  //  .Editable(editable => editable.Mode(GridEditMode.InLine))
    .Scrollable(s => s.Height("auto"))
    .Filterable()
    .ColumnMenu()
    
    .Render();

    }


<script>
    function onDataBound(arg) {
        this.tbody.find("script").each(function () {
            eval($(this).text());})
        if (arg.sender.dataSource._data.length > 0) {
            var keys = Object.keys(arg.sender.dataSource._data[0]);
            for (var i = 0; i < arg.sender.dataSource._data.length; i++) {
                for (var k = 0; k < keys.length; k++) {
                    var mykey = keys[k];
                    var KeyValue = arg.sender.dataSource._data[i][mykey];
                    if (KeyValue != null && KeyValue != undefined && typeof (KeyValue) == "string" && KeyValue.indexOf("/Date(") != -1) {
                        var objDate = kendo.parseDate(arg.sender.dataSource._data[i][mykey]);
                        arg.sender.dataSource._data[i][mykey] = objDate.getDate() + "-" + objDate.getMonth() + "-" + objDate.getFullYear();
                    }
                }


            }
        }
    }
</script>

[Christoph]

There is a small typo on my previous post. line 
.Read(read => read.Action("ReadQuery", "Product",  new { id = 1 }))
should be
.Read(read => read.Action("ReadQuery", "Product"))