Click here to Skip to main content
12,953,937 members (49,161 online)
Click here to Skip to main content
Add your own
alternative version


11 bookmarked
Posted 5 Dec 2013

Simple Way of Using SQL DataTables to JSON in MVC

, 6 Dec 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
A simple way to serialize DataTables to JSON and push the data to a browser in MVC


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]);
            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:


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>

<script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" 
<script type="text/javascript">

    function initPage() {

    function getPersons() {
        try {
            $.getJSON("/CPArticle1/GetJsonPersons", null, function (data) {
                try {
                    var dtPersons = eval(data);
                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_" + j + "_" + i;

        divCol.innerHTML = jString; = "absolute"; = eval(j * 100) + "px";
        return divCol;


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

        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; }


<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>


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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Termi Nater
Software Developer (Senior) GL
Canada Canada
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 3 Pin
Braj Panda8-Dec-13 17:45
memberBraj Panda8-Dec-13 17:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170525.1 | Last Updated 6 Dec 2013
Article Copyright 2013 by Termi Nater
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid