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

ASP.NET MVC Web API logo

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

Google Glass, GPS, and Bluetooth using an HTC Evo 4G Phone

Google Glass logo

After Google announced several months ago that they were expanding the original pool of 2000 Google Glass Explorers (from the 2012 Google I/O Conference) by up to 8000 more people with the “If I had Glass” program, I decided to throw my hat in the ring and signed up, hoping to get an opportunity to buy Glass before it went on the open market.  I was selected to be in this second pool, and I picked up my Glass at the Googleplex in Mountain View, CA a couple of weeks ago.

To be able to use the turn-by-turn directions app for Glass, it currently has to be connected using Bluetooth to an Android device running the MyGlass app.  (MyGlass for iOS is rumored to be released soon after Glass is released into production.)  MyGlass for Android requires Ice Cream Sandwich 4.0.3 or better.  Here’s where my problem comes in.  I don’t use an Android phone – I have an iPhone 5.  So, no GPS for me.  Considering that this is one of the main features of Glass, I thought about possible solutions.  I wasn’t going to buy a new Android phone just for GPS, but I could buy a tablet.  I looked at many of the tablets out there, and there are certainly quite a few to choose from.   The only problem for me was that about the only ones that get good reviews are those made by Google, Samsung, and Sony.  All of these will set me back by at least $200.  Most of those that cost less than that are accompanied by reviews that say “don’t buy it” or “it’s not worth the money”.

As it turns out, I had an old Sprint HTC Evo 4G Android phone at home that was deactivated.  Perhaps I could use this!  Unfortunately, it ran Android Gingerbread 2.3.5 – several versions below what MyGlass needed to function.  At that point, I decided that the trouble to root the device and load a custom ROM might be worth it.  After all, I wasn’t using the phone for anything else, and if I could get Ice Cream Sandwich loaded, I could make good use of this otherwise obsolete phone and I wouldn’t have to buy a tablet that I didn’t really need, aside from it being a source for GPS data.

After looking around, I found that the best ROM for this phone would have not Ice Cream Sandwich, but Android’s newest incarnation, Jelly Bean (4.2.1, not 4.2.2).  This ROM is known as MazWoz EVO, beta 4.  As is usual with these custom ROMs, there are things that no longer work after loading them.  With this one, the documentation said that the forward-facing camera (FFC) and WiMax don’t work, and the other camera/video functions may be buggy.  This was not surprising to me – and I didn’t need these things anyway.  Using these directions, I was able to load Jelly Bean 4.2.1 onto the old Evo 4G in about an hour and a half.

I was ready to pair the Google Glass to the Evo 4G when, lo and behold….Bluetooth doesn’t work on the Evo.  By this time, which was at about 3 AM, I was not pleased.  No one had mentioned this snag in anything I had read up to that point, so I figured there must be a workaround.  Surely not everyone that had loaded the ROM never tried to use Bluetooth.  After searching for a bit, I discovered that there was an easy fix!  Using Root Explorer or some other file manager that will allow read-write access to the system, delete the file “/etc/bluetooth/bt_vendor.conf” and reboot the phone.  After doing this, I have no problems pairing my Glass with the Evo, and now I can ask Glass for directions!

Exporting an HTML Table to Excel While Applying Relative Cell References Using ColdFusion Markup Language

Adobe ColdFusion logo

A new problem I was tasked with was to make enhancements to a report built using CFML by changing the totals column to a SUM() function when the report is exported to Microsoft Excel.

The trick here was that the table was being exported as HTML code, and then opened in Excel. Since the location of the totals row would vary depending on the number of rows in the report, it was uncertain as to how the SUM() function would be constructed. Regular cell references (such as A1:A10, $A1:$A10, etc.) would not be sufficient. The key was to use a combination of the Excel functions INDIRECT(), ADDRESS(), ROW(), and COLUMN().

After much experimentation, I discovered that the magic expression that will give sum all of the rows in a given column and place that value in the cell beneath those rows is:

=SUM(INDIRECT(ADDRESS(1,COLUMN())  ":"  ADDRESS(ROW()-1,COLUMN())))

Finding this was a huge step in being able to complete this task. However, this report had multiple sections and I didn’t want every total to SUM all of the rows in previous sections. Fortunately, I knew how to get the number of rows in each query run by ColdFusion against the database using the RecordCount variable that is a result of the cfquery function.

By changing the CFML code to include the Excel expression in this format:

=SUM(INDIRECT(ADDRESS(ROW()-#whatever.RecordCount#,COLUMN())  ":"  ADDRESS(ROW()-1,COLUMN())))

the resulting spreadsheet replaced the ColdFusion-calculated totals with the Excel expression, and the Excel-calculated totals.

<cfif variables.Export EQ 'True' AND Isdefined("variables.ExportToExcel")>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td colspan="9" style="visibility:hidden;display:none;">&nbsp;</td>
    <td class="num" style="visibility:hidden;display:none;">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
<cfelse>
    <td class="num">#Numberformat(variables.TotalYear,'999,999,999,999')#</td>
    <td class="num">#Numberformat(variables.TotalOMPI,'999,999,999,999')#</td>
    <td class="num">#Numberformat(variables.TotalNBC,'.99')#</td>
    <td class="num">#Numberformat(variables.TotalNetBen,'999,999,999,999.9')#</td>
    <td colspan="9" style="visibility:hidden;display:none;">&nbsp;</td>
    <td class="num" style="visibility:hidden;display:none;">#Numberformat(variables.TotalInv,'999,999,999,999')#</td>
</cfif>