How to dynamically bind and configure column’s metadata in kendo grid for your MVC application

As a blogger you always strive for some interesting topic to write for your blog. Recently in one of our MVC application i got the chance to work with kendo grid control, but with some interesting requirement. Although i have already used kendo ui controls in other mvc application, but in this application i have had got very interesting requirement and somewhat challenging also.

Now let me explain you the requirement. Normally you will bind your kendo grid with some predefined columns. But in this case i need to bind the kendo grid where column type and number of columns to bind will decide at run-time. Let me add further one more point in this requirement – each column’s metadata will also decide at run-time. Well, column’s metadata mean column’s caption, datatype, width, format, header alignment and text alignment etc. will decide based on the type of column. Don’t worry, if you don’t get this point. Let’s see this practically.

For demo purpose, i have made two new classes. One class for actual data to bind in kendo grid. Name of that class is let say – RowValue. Another class is for column’s metadata. Name of that class is – ColumnMetaDataInfo
Here is the code of our both the classes.


public class RowValue
{
	public string PatientName { get; set; }

	public decimal OrderAmount { get; set; }

	public int PatientAge { get; set; }

	public DateTime OrderDate { get; set; }
}

public class ColumnMetaDataInfo
{
	public string FieldName { get; set; }

	public int Width { get; set; }

	public string Caption { get; set; }

	public string Format { get; set; }

	public string Align { get; set; }

	public bool Display { get; set; }

	public Type DataType { get; set; }
}

Well, for demo purpose i have defined the two classes, In real scenario i have two separate tables in the database – one for actual data and another for column metadata. Now let’s use both of these classes in Model which we’ll pass it in our index.cshtml View.


public class GridViewModel
{
       public List<ColumnMetaDataInfo> Columns { get; set; }

       public List<RowValue> RowValues { get; set; }
}

Now its time to initialize the above viewModel class and fill the defined property with required data in “Home” controller’s action method.


public class HomeController : Controller
{

	public ActionResult Index()
	{
		var gridViewModel = new GridViewModel()
		{
			Columns = new List<ColumnMetaDataInfo>()
			{
				new ColumnMetaDataInfo
					{
						Caption = "Patient Name",
						FieldName = nameof(ColumnInfo.PatientName),
						Width = 50,
						Display = true,
						Format = "",
						Align = "left",
						DataType = typeof(string)
					},
				new ColumnMetaDataInfo
					{
						Caption = "Order Amount",
						FieldName = nameof(ColumnInfo.OrderAmount),
						Width = 15,
						Display = true,
						Format = "{0:C2}",
						Align = "right",
						DataType = typeof(decimal)
					},
				new ColumnMetaDataInfo
					{
						Caption = "Patient Age",
						FieldName = nameof(ColumnInfo.PatientAge),
						Width = 15,
						Display = false,
						Format = "{0:N2}",
						Align = "right",
						DataType = typeof(int)
					},
				new ColumnMetaDataInfo
					{
						Caption = "Order Date",
						FieldName = nameof(ColumnInfo.OrderDate),
						Width = 20,
						Display = true,
						Format = "{0:MM/dd/yyyy}",
						Align = "center",
						DataType = Type.GetType("System.DateTime")
					}
			},
			// Actual value to bind for each row
			RowValues = new List<ColumnInfo>()
			{
				new ColumnInfo { PatientName = "Krishnraj Rana", OrderAmount = 100.1M, PatientAge = 25, OrderDate = new DateTime(2016, 11, 25) },
				new ColumnInfo { PatientName = "Vishal Vagadia", OrderAmount = 200.1M, PatientAge = 35, OrderDate = new DateTime(2016, 11, 29) },
				new ColumnInfo { PatientName = "Birju Patel", OrderAmount = 300, PatientAge = 45, OrderDate = new DateTime(2016, 11, 28) }
			}
		};

		return View(gridViewModel);
	}
}

Now its time to bind the model with kendo grid and here is the code of Index.cshtml view in which we pass the above ViewModel class.


@model KendoGridDynamicColumn.Models.GridViewModel

@{
    ViewBag.Title = "Home Page";
}
	<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.common.min.css" />
	<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.default.min.css" />
<script src="~/Scripts/jquery-1.12.3.min.js"></script>
<script src="~/Scripts/kendo.all.min.js"></script>
<script src="~/Scripts/kendo.aspnetmvc.min.js"></script>


<div class="jumbotron">

<h2>Dynamic Kendo Grid</h2>


<h4>POC - V.1.0.0</h4>

</div>



<div class="row">

<div class="kendolist">
        @(Html.Kendo().Grid(Model.RowValues)
            .Name("grdDynamicCol")
            .Columns(columns =>
            {
                foreach (var column in Model.Columns)
                {
                    columns.Bound(column.FieldName).Title(column.Caption)
                        //.Visible(column.Display)
                        .Format(column.Format)
                        .Width($"{column.Width}%")
                        .HtmlAttributes(new { @style = $"text-align:{column.Align.ToLower()}" })
                        .HeaderHtmlAttributes(new { @style = $"text-align:{column.Align.ToLower()}" });
                }
            })
            .Pageable()
            .Sortable()
            .Resizable(resize => resize.Columns(true))
            .DataSource(dataSource => dataSource
                .Ajax()
                .Model(model =>
                {
                    foreach (var column in Model.Columns)
                    {
                        model.Field(column.FieldName, column.DataType);
                    }
                })
            )
        //.ColumnMenu()
        //.HtmlAttributes(new { @class = "gridview" })
        )
</div>

</div>

<script>
    $(document).ready(function () {

        HideGridColumn();
    });

    function HideGridColumn() {
        var grid = $("#grdDynamicCol").data("kendoGrid");
        @foreach (var column in Model.Columns.Where(col => col.Display == false))
        {
            <text>grid.hideColumn('@column.FieldName');</text>
        }
    }

    function gvchange(e) {
        var grid = $("#grdDynamicCol").data("kendoGrid");
        if (e.checked) {
            grid.showColumn(e.id);
        } else {
            grid.hideColumn(e.id);
        }
    }
</script>

and when grid binds, this is how it look in the browser.

result

That’s it. For any query or doubt, write in the comment section. Hope you enjoyed this post.

Tip of the day: SQL Server – In Stored Procedure if passing parameter is not null then update column value else keep old column value in Update query.

As a developer, we always try to write better code and try to reduce the possible bugs in the application. But at the same time its also important that how you write the code, how much readable it is. Well, we all are human beings and its almost impossible to write bug free code irrespective of your work experience. That’s where the process of code review comes into the picture. Importance of code review is not limited to code refactoring or bug fixing,  at-least for me it is also a solid mechanism of knowledge sharing / gathering.

So as a part of it, I was reviewing the code of stored procedure and found some unwanted if conditions. In that SP there were 3 optional parameters and those if conditions was something like this – check if parameter passed is not null then use it in update statement. And same condition for other two columns. Here is the sample code of that stored procedure.


CREATE PROCEDURE [dbo].[App_UpdateAccountInfo]
 @AcntId INT
,@CompanyID INT
,@FrnID INT = NULL
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL

AS
BEGIN

SET NOCOUNT ON;

	UPDATE [dbo].[tblAccount]
	SET
		CompanyID = @CompanyID
		,AccountNickName = @AccountNickName
		,LastUpdateOn = GETDATE()
	 WHERE AccontID = @AcntId

	 -- Check if @SystemName is not null
	 IF @SystemName IS NOT NULL
	 BEGIN

		UPDATE [dbo].[tblAccount]
		 SET
			SystemName = @SystemName
		 WHERE AccontID = @AcntId

	 END

	 -- Check if @FrnID is not null
	 IF @FrnID IS NOT NULL
	 BEGIN

		UPDATE [dbo].[tblAccount]
		SET
			FrnID = @FrnID
		 WHERE AccontID = @AcntId

	 END

END

Now what if there were more than 5 optional parameters in above SP! As a developer do you think to write 5 or more if conditions and make SP more verbose? So what should be the proper way?

Well, Let me give you one hint. What if you update column with itself in update statement. Hmm no need to think much, here is the modified code of above SP.


CREATE PROCEDURE [dbo].[App_UpdateAccountInfo]
 @AcntId INT
,@CompanyID INT
,@FrnID INT = NULL
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL

AS
BEGIN

	SET NOCOUNT ON;

	UPDATE [dbo].[tblAccount]
	SET
		CompanyID = @CompanyID
		,AccountNickName = @AccountNickName
		,SystemName = ISNULL(@SystemName, SystemName)
		,FrnID = ISNULL(@FrnID, FrnID)
		,LastUpdateOn = GETDATE()
	 WHERE AccontID = @AcntId

END

Well, SQL Server is smart enough to not write the same value on the disk which results in minimal log activity. However this is not true in some cases. There are several factors which is nicely described in Paul White’s article and this stackexchange link.

That’s it. Have you ever faced this type of scenario? write it down in the comment section. Hope you like this post.