Simple Way of Using SQL DataTables to JSON in MVC

• Download MVC application - 850.8 KB

Introduction

In every programmer's life, there comes a time when they have to display tabular data. We are all very familiar with the use of DataTables, SQL queries and stored procedures. MVC is a great framework, but it is coupled very tightly with the use of Entity Framework and LINQ, at least in every book or article I have read so far. There might be cases though, where someone might like to use DataTables to push data to the browser.

There is a very simple way to do that and I hope someone might find the method described here useful.

Using the Code

Let's start with the data access layer. It is in this sample located in the ViewModels to save space. Generally it should be in a separate file or set of files, but this is just a sample.  

It has two methods:

• GetDataTable - Generates a stub DataTable with some sample data. In real life scenarios, you will have of course an SQL server with some stored procedures.
• GetTableRows - A helper method, that will transform any DataTable in a list of Dictionary objects with a Key, corresponding to the name of the DataTable column. This method is placed in the ViewModel of this example just to save space. Methods like this usually belong in Helper Libraries.

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
 
namespace MvcApplication31.ViewModels
{
    public class DataAccessLayer
    {
        public DataTable GetTable()
        {
            DataTable dtTable = new DataTable();
            dtTable.Columns.Add("UserID", typeof(int));
            dtTable.Columns.Add("FirstName", typeof(string));
            dtTable.Columns.Add("LastName", typeof(string));
 
            dtTable.Rows.Add(25, "Ave", "Maria");
            dtTable.Rows.Add(50, "Bill", "Doe");
            dtTable.Rows.Add(75, "John", "Gates");
            dtTable.Rows.Add(99, "Julia", "Griffith");
            dtTable.Rows.Add(100, "Mylie", "Spears");
            return dtTable;
        }
 
        public List<Dictionary<string, object>> GetTableRows(DataTable dtData)
        {
            List<Dictionary<string, object>> 
            lstRows = new List<Dictionary<string, object>>();
            Dictionary<string, object> dictRow = null;
 
            foreach (DataRow dr in dtData.Rows)
            {
                dictRow = new Dictionary<string, object>();
                foreach (DataColumn col in dtData.Columns)
                {
                    dictRow.Add(col.ColumnName, dr[col]);
                }
                lstRows.Add(dictRow);
            }
            return lstRows;
        }
    }
}

Next, let's see the controller that will push the DataTable to the browser. It is the GetJsonPersons method. It initializes the DataAccessLayer and fetches the DataTable that is hardcoded in the DataAccessLayer. The next step is to pass the DataTable dtPersons to the helper method GetTableRows and store the resulting List of Dictionaries in lstPersons. Finally lstPersons is passed to the method Controller.Json that will serialize lstPersons and return it to the browser.

using System; 
using System.Collections.Generic; 
using System.Data; 
using System.Linq; 
using System.Web; 
using System.Web.Mvc; 
 
namespace MvcApplication31.Controllers 
{
    public class CPArticle1Controller : Controller
    {
        // GET: /CPArticle1/  
        public ActionResult Index()
        { 
            return View(); 
        }
 
        public JsonResult GetJsonPersons() 
        { 
            ViewModels.DataAccessLayer oDAL = new ViewModels.DataAccessLayer(); 
            DataTable dtPersons = oDAL.GetTable(); 
            List<Dictionary<string, object>> lstPersons = oDAL.GetTableRows(dtPersons); 
            return Json(lstPersons, JsonRequestBehavior.AllowGet); 
        }
    }
}

The controller GetJsonPersons can be accessed by the URL: /CPArticle1/GetJsonPersons and will generate the following output:

[{"UserID":25,"FirstName":"Ave","LastName":"Maria"},
  {"UserID":50,"FirstName":"Bill","LastName":"Doe"},
  {"UserID":75,"FirstName":"John","LastName":"Gates"},
  {"UserID":99,"FirstName":"Julia","LastName":"Griffith"},
  {"UserID":100,"FirstName":"Mylie","LastName":"Spears"}] 

And finally the View, that will fetch the JSON data and display it. It uses jQuery to make an Ajax call to the controller GetJsonPersons in the JavaScript function getPersons on a click of the button btnGetPersons. The Json data is stored in the variable dtPersons and is passed to the function displayJSONData. The individual data items can be accessed by simply referencing dtPersons. Per example: dtPersons[2].LastName.

@{ Layout = null; }

<!DOCTYPE html>

<html>
<head>
<title>CPArticle1</title>
<script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" 
          type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(initPage);

    function initPage() {
        $('#btnGetPersons').click(getPersons);
    }

    function getPersons() {
        try {
            $.getJSON("/CPArticle1/GetJsonPersons", null, function (data) {
                try {
                    var dtPersons = eval(data);
                    displayJSONData(dtPersons);
                }
                catch (ex1) {
                    alert(ex1); // you can of course use a better way of displaying errors
                }
            });
        }
        catch (ex) {
            alert(ex);  // you can of course use a better way of displaying errors
        }
    }

    function displayJSONData(dtPersons) {
        var strColumnData1 = "";
        var strColumnData2 = "";
        var strColumnData3 = "";

        for (i = 0; i < 10; i++) {
            var divRow = document.getElementById("divRow_"+i);

            if (i < dtPersons.length) {
                strColumnData1 = dtPersons[i].UserID;       
                strColumnData2 = dtPersons[i].FirstName;
                strColumnData3 = dtPersons[i].LastName;
            }
            else {
                strColumnData1 = "";
                strColumnData2 = "";
                strColumnData3 = "";
            }
            divRow.appendChild(getColumnDiv(i, 0, strColumnData1));
            divRow.appendChild(getColumnDiv(i, 1, strColumnData2));
            divRow.appendChild(getColumnDiv(i, 2, strColumnData3));
        }
    }

    //i is row number j is column number
    function getColumnDiv(i, j, jString) {
        var divCol = document.createElement('div');
        divCol.id = "divCol_" + j + "_" + i;

        divCol.innerHTML = jString;

        divCol.style.position = "absolute";
        divCol.style.left = eval(j * 100) + "px";
        return divCol;
    }

</script>

<style type="text/css">
    #btnGetPersons
    {
        position:absolute; left:20px;top:320px;
    }

    div[id^="divRow_"] 
    {
        position:absolute; left:20px; width:300px; height:25px; overflow:hidden;
        border:1px solid green;
    }
    
    #divRow_0 { top:20px; }
    #divRow_1 { top:50px; }
    #divRow_2 { top:80px; }
    #divRow_3 { top:110px; }           
    #divRow_4 { top:140px; }   
    #divRow_5 { top:170px; }   
    #divRow_6 { top:200px; }   
    #divRow_7 { top:230px; }   
    #divRow_8 { top:260px; }   
    #divRow_9 { top:290px; }
    
</style>

</head>
<body>

<input id="btnGetPersons" type="button" value="Get Persons" />

<div id="divRow_0"></div>
<div id="divRow_1"></div>
<div id="divRow_2"></div>
<div id="divRow_3"></div>
<div id="divRow_4"></div>
<div id="divRow_5"></div>
<div id="divRow_6"></div>
<div id="divRow_7"></div>
<div id="divRow_8"></div>
<div id="divRow_9"></div>

</body>
</html>

The displayJSONDat function is indeed very primitive. Just for demo purposes, but can be easily extended to an object that could behave like a DataGrid of some sorts.

That's about it. A simple way to get some things done if you don't want to create a big Entity Framework model, or don't want to use it in some cases.

Please let me know what you think, it is possible to use the same approach for displaying non Ajax views of data in MVC too.