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¶meter2=parameter2¶meter3=parameter3”, etc.).
