Creating an ASP.NET MVC 4 RESTful API for a SQL Stored Procedure

ASP.NET MVC Web API logo

In order to use the information from the SQL stored proc I implemented that would create a JSON Object from a SQL table, I needed to build an RESTful API web service that would retrieve the JSON for use elsewhere.

Finding information on building an ASP.NET-based API that used a stored proc to pull the data was not easy, as there were plenty of pages that had part of the info, but none I found had everything I needed. The article “Build RESTful API’s with ASP.NET Web API” was very helpful in giving me a starting point.

One of the things I did was to eliminate the Model (the “M” in MVC) from the site, as I did not want the column name to appear in the string returned by the API.

(“RESTfulAPI” is the name of the namespace for this project.)

The Controller that I created has the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using RESTfulAPI.Services;

namespace RESTfulAPI.Controllers
{
    public class JsonController : ApiController
    {
        private JsonRepository jsonRepository;

        public JsonController()
        {
            this.jsonRepository = new JsonRepository();
        }

        public string Get(string tableName)
        {
            return jsonRepository.GetAllJsons(tableName, null);
        }

        public string Get(string tableName, string department)
        {
            return jsonRepository.GetAllJsons(tableName, department);
        }
    }
}

I created a folder called “Services” at the same level of Controllers and added the following class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace RESTfulAPI.Services
{
    public class JsonRepository
    {
        public string GetAllJsons(string tableName, string department)
        {
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            var connectionString = string.Empty;
            var json = string.Empty;
            connectionString = "Server=.SQLEXPRESS2008;Database=HackHou2008;Integrated Security=SSPI";

            conn = new SqlConnection(connectionString);
            command = new SqlCommand("GetJSON", conn);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@table_name", tableName));
            if (department != null)
            {
                command.Parameters.Add(new SqlParameter("@department", department));
            }

            conn.Open();
            rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                json += rdr["json"];
            }
            return json;
        }
    }
}

When the website is installed, the API can be accessed at “(site home)/API/json?tablename=tablename”. If you add other parameters to the stored proc, they will need to be added in the Get() method above, and the URL calling the API will have those parameters appended using ampersands (i.e., “(site home)/API/json?tablename=tablename&parameter2=parameter2&parameter3=parameter3”, etc.).

Converting a SQL Server Table into a JavaScript Object Using a Stored Procedure

JSON logo

A couple of weeks ago, I attended Houston’s first Open Innovation Hackathon and joined the team that had the goal of redeveloping the City of Houston City-Wide Fee Schedule.

The current fee schedule is an ASP.NET Web application with a SQL Server back end.

At the end of the hackathon, my team had put together a JavaScript-based site that used JSON to read in the data for the new site.

Since the current infrastructure was based on SQL Server, I sought a way to convert the two SQL tables into JSON Objects so the current back end would not have to be changed. As it turns out, I found a good starting point on Stack Overflow. The only problem with this stored procedure was that it would not work properly if there were single quotes in the results, or if the column names had spaces.

I was able to modify the stored proc to allow for these changes as well as a few others, such as accounting for NULL values. The modified SP is posted below for your reading pleasure!

(Link to gist)

One Year of Blogging at “Deep In The Code”!

cupcake with one year candle

I’ve been writing this blog for just over a year now; my first post was on May 23, 2012. I haven’t written as many articles as I would have liked over the last year, I think due to the fact that I’ve been too selective in what I wrote about (for fear of moving the blog off-topic) and some of the articles have been too long. I will endeavor to write shorter, but more frequent articles for the second year of this blog!

I enjoy the feedback I get (when it’s not spam) as it tells me if I’m writing about something that people care about, so please continue writing back!

My goals from last year never fully materialized, as I intended to learn OS X / iOS / Objective-C programming when I bought my MacBook Pro last year. While I did dip into the pool of Xcode programming, it was only a shallow dip. Instead, I have focused more on learning open source technologies – mostly Python and Ruby on Rails. Once I get a handle on these two, I intend to revisit iOS programming – though I may end up using RubyMotion instead of Objective-C. Only time will tell!

Thanks for reading my blog, and please feel free to make suggestions on what you would like to read about in the future!