Auto populate DataTable columns to the Views in ASP.NET MVC

Sheonarayan
Posted by in ASP.NET MVC category on for Advance level | Points: 250 | Views : 52151 red flag
Rating: 5 out of 5  
 4 vote(s)

In this article, we will learn how to automatically populate DataTable columns to the Views in ASP.NET MVC just like GridView automatically binds DataSource (DataTable in this case) columns automatically in ASP.NET web form.

Introduction


Being control driven development, ASP.NET Web form has  many advantages in terms of using the ready made controls. Like GridView control has many in built features that doesn't need developer intervention, like binding datasource data automatically without explicitly specifying column names in the GridView etc.

If we want to achieve the same behaviour in ASP.NET MVC, its not that easy as ASP.NET MVC is model based development where almost every view expect a model (with fixed sets of properties corresponding to the database table columns) that gets bounded to the Views.

Objective

The objectives of this article are following
  1. Binding the view with DataTable as data source rather than a Model
  2. Automatically populate the DataTable columns (irrespective of number of columns and from which Database table) data to the View page, thanks to JSON to HTML Table converter plug in (we will talk about it as we progress in this article).
Our UI for this demonstration looks like below image, where we will write SELECT statement in the TextArea and hit Submit button and whatever columns and data comes from that particular database table, gets populated on the View.



Steps to follow to auto populate DataTable columns to ASP.NET MVC View


We will follow below steps to auto populate DataTable columns to the ASP.NET MVC view.
  1. We will take SQL SELECT statement to be executed against the database in a TextArea
  2. Call a method to execute SQL statement and get the data into a DataTable
  3. Convert the DataTable data into JSON string
  4. Pass this JSON string to the Json To HTML Table converter plug in that returns the HTML table code
  5. Populate the HTML table code to the container

Using the code


Our above View Code looks like this

The Form Code

<script src="/Scripts/json-to-table.js"></script>

@using(Html.BeginForm()){
        @Html.AntiForgeryToken()
Command<br />
                     <textarea name="txtCommand" rows="10" cols="50">@ViewBag.txtCommand</textarea><br />
            <span onclick="return confirm('Are you sure to proceed?')"><input type="submit" value="Submit" /></span>
                    <div class="message-success">
                        @ViewBag.ResultMessage
                    </div>
}
In the above code snippet, notice the Script statement where we are using json-to-table.js file from the Scripts folder. As discussed above, we are going to use a plug-in freely available at GitHub, the download contains a .js file that we have kept into the Scripts folder.

Then we have a TextArea and a button, clicking on the button simply calls the Controller DbControl method in our case.


The data list code
    <div id="resultList"></div>

    @if (ViewBag.ResultData != null)
    {        
        <script>
            var data = @Html.Raw(ViewBag.ResultData);
            var jsonResultData = ConvertJsonToTable(data, null, null, null);
            $("#resultList").html(jsonResultData);
        </script>        
    }

In the above code snippet, we have a container (with id as resultList) where the data from the DataTable gets populated. Next, we are checking for the ViewBag.ResultData and if its not null then getting the ViewBag.ResultData (its basically JSON string, we will talk about it later on) into data variable and that variable is intern being passed to ConvertJsonToTbale function that exists into json-to-table.js file (the GitHub plug-in we have downloaded). ConvertJsonToTable function gives formatted HTML table code based on the JSON data, the same is being written to the container (resultList).

ConvertJsonToTable has few more parameters however those are optional and that can be explored separately.

Controller method

Namespace to be used

using System.Data
using System.Data.Common;
using System.Data.Entity;
using System.Data.Objects;

        [Authorize]
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult DbControl(string txtCommand)
        {
                string sql = txtCommand;
                try
                {
                    ViewBag.ResultData = GetJson(ExecuteDataTable(db, sql));
                    ViewBag.ResultMessage = "Command executed successfully !";
                }
                catch (Exception ee)
                {
                    ViewBag.ResultMessage = ee.ToString();
                }
                ViewBag.txtCommand = txtCommand;
                return View();
            }
        }
In the above method, whatever SQL statement is written in the textarea comes as txtCommand that is being set locally as sql variable (for good understanding).Now notice the bold code where we are calling two methods

ExecuteDataTable method

        DataTable ExecuteDataTable(System.Data.Entity.DbContext c, string sql)
        {
            DataTable table = new DataTable();
            DbConnection conn = c.Database.Connection;
            ConnectionState initialState = conn.State;
            try
            {
                if (initialState != ConnectionState.Open)
                    conn.Open();  // open connection if not already open
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    using (DbDataAdapter ad = CreateDataAdapter(conn))
                    {
                        ad.SelectCommand = cmd;
                        ad.Fill(table);
                    }
                }
            }
            finally
            {
                if (initialState != ConnectionState.Open)
                    conn.Close(); // only close connection if not initially open
            }
            return table;
        }

        private DbDataAdapter CreateDataAdapter(DbConnection conn)
        {
            DbDataAdapter ad;
            if (conn is System.Data.SqlClient.SqlConnection)
                ad = new System.Data.SqlClient.SqlDataAdapter();
            else if (conn is System.Data.OleDb.OleDbConnection)
                ad = new System.Data.OleDb.OleDbDataAdapter();
            else
            {
                throw new Exception("Didn't get known connection type: " + conn.GetType().FullName);
            }

            return ad;
        }
This method, takes DbContext (Entity Framework) and the Sql to execute as a parameter.  It uses the underline connection available with DbContext, creates a DbCommand and then DbDataAdapter with the help of CreateDateAdapter method and fires fill method to execute the Sql statement being passed and populates the data into the DataTable that is being returned by this method.

GetJson method

 string GetJson(DataTable table)
        {
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row = null;
            foreach (DataRow dataRow in table.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn column in table.Columns)
                {
                    row.Add(column.ColumnName.Trim(), dataRow[column]);
                }
                rows.Add(row);
            }
            return serializer.Serialize(rows);
        }

This method accepts the DataTable being returned from ExecuteDataTable method and converts into JSON string. The same JSON string is being set to ViewBag.ResultData.

One more screenshot



The data list code (same code, copy pasted from above)
    <div id="resultList"></div>

    @if (ViewBag.ResultData != null)
    {        
        <script>
            var data = @Html.Raw(ViewBag.ResultData);
            var jsonResultData = ConvertJsonToTable(data, null, null, null);
            $("#resultList").html(jsonResultData);
        </script>        
    }
As you can see in the above code, the same ViewBag.ResultData is being used to pass the JSON data to the ConvertJsonToDataTable function.

Conclusion


Auto populating DataTable columns to the ASP.NET MVC View is not straight forward, thanks to JSON to HTML Table converter that helps us in populating whatever data comes as JSON string to the HTML table.

Hope you liked this article, do let me know your feedback and spread the word about this article to the world !

Reference


JSON to HTML Table converter - https://github.com/afshinm/Json-to-HTML-Table
Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Ermahesh2009 on: 4/19/2014 | Points: 25
Dear sir please share source code too with as per each task sequence . I am new in MVC . if you have source code please mail me er.mahesh2009 at gmail com
Posted by: Raja on: 7/23/2014 | Points: 25
Nice article Sheo !

Login to post response

Comment using Facebook(Author doesn't get notification)