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
- Binding the view with DataTable as data source rather than a Model
- 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.
- We will take SQL SELECT statement to be executed against the database in a TextArea
- Call a method to execute SQL statement and get the data into a DataTable
- Convert the DataTable data into JSON string
- Pass this JSON string to the Json To HTML Table converter plug in that returns the HTML table code
- 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.
<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