≡ Menu
Deep in the Code

Converting a SQL Table to JavaScript Objects Using the ASP.NET MVC Web API

Happy Independence Day!

Here I will describe an alternative to an earlier post that described using a SQL stored procedure to convert a SQL table into a JavaScript object.

One of the comments left on that post made me wonder why someone would not want to use the stored procedure method. After querying a large table with this method, I do understand why it’s not ideal. It takes quite a long time to pull back large quantities of data.

The method described below, using the Newtonsoft JsonWriter class in an ASP.NET MVC Web API, is much faster and does not require any SQL stored procs. The code I used was modified from Hristo‘s answer on Stack Overflow.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Text;
using System.IO;
using Newtonsoft.Json;

namespace RESTfulAPI.Services
    public class JsonRepository
        public string GetAllJsons(string tableName)
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            HttpContext context = null;
            String connectionString = string.Empty;
            String json = string.Empty;
            connectionString = ConfigurationManager.ConnectionStrings["/* (your named connection string in web.config) */"].ConnectionString;
            conn = new SqlConnection(connectionString);
            command = new SqlCommand("SELECT * FROM " + tableName, conn);
            context = HttpContext.Current;

            StringBuilder allJSONs = new StringBuilder();
            // ... SQL connection and command set up
            command.CommandTimeout = 3600;
            rdr = command.ExecuteReader();

            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            JsonWriter jsonWriter = new JsonTextWriter(sw);
            while (rdr.Read()) {
                int fieldcount = rdr.FieldCount; // count how many columns are in the row
                object[] values = new object[fieldcount]; // storage for column values
                rdr.GetValues(values); // extract the values in each column
                for (int index = 0; index < fieldcount; index++)
                { // iterate through all columns
                    jsonWriter.WritePropertyName(rdr.GetName(index)); // column name
                    jsonWriter.WriteValue(values[index]); // value in column
            json = sb.ToString();
            //End of method
        catch (SqlException sqlException)
        { // exception
            json = "Connection Exception: " + sqlException.ToString() + "n";
            conn.Close(); // close the connection

        return json;

About the author: I solve problems. Solutions Architect / Senior Software Engineer / Business Analyst / Full-Stack Developer / IT Generalist