Last post of the year, I promise! Something I forgot to mention in yesterday’s post: I found a great tool for finding the correct JSON selector!
The JSON Selector Generator allows you to put in a JSON string, and it parses it such that you can then select which block or item you wish to reference in your code. When your JSON string is small and well-formatted, it’s easy to see what selector to use without a tool like this. However, if an API returns a JSON string that fills your screen, eyeballing it is not likely to get you anywhere quickly.
Processing the long JSON string returned by the call to my Duolingo profile takes only a few seconds with this great tool.
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.
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;
try
{
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
conn.Open();
command.CommandTimeout = 3600;
rdr = command.ExecuteReader();
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
JsonWriter jsonWriter = new JsonTextWriter(sw);
jsonWriter.WriteStartArray();
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
jsonWriter.WriteStartObject();
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
}
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEndArray();
rdr.Close();
json = sb.ToString();
//End of method
}
catch (SqlException sqlException)
{ // exception
json = "Connection Exception: " + sqlException.ToString() + "n";
}
finally
{
conn.Close(); // close the connection
}
return json;
}
}
}
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!
