A Very Frustrating But Also Very Rewarding Experience with AmCharts and Complex JSON Responses

Tags

amCharts, arrays, charts, javascript, JSON

Presenting data in amCharts and Chart.js from simple two-column tables was relatively straightforward. I had three Web APIs that each returned a two-column table that the charting scripts could easily read from. As I was finishing up the presentation, the application spec changed – all the data is now returned as a complex table by one stored procedure. What followed was a moderately frustrating couple of days, as I Bill Nyed the code multiple times trying to extract and group items from the JSON objects.

Given the main reason for using a single stored procedure was to reduce the load on the Service Broker, a single Web API call in my code is better than three. It also makes sense to implement all the querying features as JavaScript, since the browser fetches everything when the page loads.

The code for my solution is published on GitHub (click here).

Revisiting Arrays and Objects
My solution was to populate an array, or multiple arrays, with items from the JSON response, so it’s worth looking at JavaScript arrays to see the similarity between that and JSON.

An array could be static and predefined, e.g.
var users = ["michael", "john", "andy"];

Or it could be an empty array that’s populated during runtime, for example, in a script that populates the array from another source, such as:
var users = new Array("michael", "john", "andy");

The other type of variable I’m working with here is an object with multiple attributes. e.g.
var user = {userName:"michael", userID:"515", role:"Developer"};

You’ll notice this looks somewhat like a message object within our JSON response, because that’s precisely because the JSON response is an array of such objects. For example, the JSON response for the Dashboard is:

[{"Id":"0001","Date":"2017-05-05","MessageType":"Pathology","HealthBoard":"7A6","HealthBoardDescription":"BC1","MessagesProcessed":1},
{"Id":"0002","Date":"2017-05-05","MessageType":"Pathology","HealthBoard":"7A4","HealthBoardDescription":"BC2","MessagesProcessed":2}]


Getting Chart Data from a JSON Response Body
For the Messages by Type chart, I want a count of the number of instances for each messageType name in the Service Broker queue. If these counts could be presented as a doughnut chart, the user could readily see which category of systems is generating the most traffic – typically they’re pathology systems, so if cardiology systems are sending most the traffic, we know something’s not right.

Anyway, what I did first was initialise an array called ‘everything‘, and push all the JSON response objects to it. From that I extracted the messageType items and pushed them into another array called ‘myMessageType[]‘.

This enabled me to use ‘myMessageType.length‘ to loop over it and increment the counter variables for each instance of ‘Pathology’, ‘Radiology’, ‘Cardiology’ and ‘unknown’. More observant readers will notice I’m counting instances of rows, not what’s actually contained in the MessagesProcessed column. Most rows in that column have a value of ‘1’, so I can get away with that for now and add further logic in later.

(Update: It looks much better after the counters are placed in a single loop:)

At this stage, I should have a set of counter variables that provide data for the chart. Since that might become a problem solving task in itself, now’s a good time to establish, using a debugging tool and SQL Server Management Studio, whether the counter variables are indeed incremented.

If everything’s good at this point, the counter variables can now be used as the amCharts dataProvider source:

Triggering
On running the application, the charts still aren’t rendered even with the counter variables incrementing correctly. This is a timing issue, with the charts attempting to render before the arrays are populated and counted. The code needs to be modified so the sections of code are executed in the correct order.

The chart code can be encapsulated within a function. Here it’s called chartByType().

function chartByTypes()
{
// Charting code here
}


And add code for calling the above after a short delay when the counter arrays/variables have been populated:

// Insert call here to Chart 2
setTimeout(function () { chartByTypes(); }, 500);

And here was the result:

Making JavaScript Charts work with Stored Procedures and Entity Framework

Tags

amCharts, ASP.NET, Dashboard, Entity Framework, javascript, JSON, MVC, Web API

After adding one of the AmCharts examples in the CSHTML source, I had the graphics rendering code and a static array providing the chart data, and everything was displayed as it should. This application needed to be adapted so it displayed the metrics relating to the messages being processed by systems using Service Broker.
Again I used a stored procedure. This returns a table of dates against the number of messages processed on those dates, and takes as inputs startDate and endDate, both of which could be null. We’ll call this stored procedure ‘prMessagesProcessedByDay‘.

What I needed to achieve here was: a) Use Entity Framework to model the stored procedure and its response body, b) Add a controller to pass data between the Entity Framework model and the view layer, and c) Add some JavaScript to call the controller and render the data set as a chart.

Entity Framework Model
Right-click the Models folder in the Solution Explorer, and add new item. Here we want to add ADO.NET Entity Data Model, which will be a Database First (EF Designer from database).
When generating the model Entity Framework should have assigned the returned data as a ‘Complex Type’, which didn’t happen for some reason. In the Model Browser, I right-clicked on the ‘Complex Types‘ object for the model, and ‘Add New Complex Type…‘.

After selecting the stored procedure to import, the Context.cs code should look something like this:

Again both input variables are nullable, as the entire table should be returned if no date range is specified, and I should have the option of adding a feature for doing this later. And the returned variables were also nullable in case there was an empty table:

The next problem is that Entity Framework runs on the application server, whereas the JavaScript executes in the client’s browser, so the application would need to fetch the data through a controller that calls the stored procedure whenever the page loads.

Web API Controller
The way I’m doing this is through a Web API controller. Apparently it handles JSON and serialisation, which is required for the JavScript to populate an array. Autogenerating an empty Web API controller gives us the following:

When doing this, you might encounter error messages about variable types. The first thing to check is whether the Stored Procedure is assigning a primary key to the returned table – especially if the Web API template includes select, edit and delete actions. Here I needed to modify the stored procedure by prefixing one of the instructions with ‘SELECT NEWID()as Id‘.

Second problem that might be encountered is an HTTP 404 error when attempting to call the Web API when the application’s running. Removing all the NuGet packages and re-installing them fixed the problem.

Thirdly, the controller needed to perform some typecasting, as it didn’t work well with ‘complex types’. It needed the object GetprMessagesProcessedByDay_Result() to be declared as a list.

Eventually I ended up with something like this:

View Layer and Testing the Controller with JavaScript
Now there’s hopefully an Entity Framework model that’s accesible to the Web API controller, the next requirement is some JavaScript to send requests to it. The code for that would look something like:

This JavaScript section was repurposed from another tutorial, just to ascertain there was was a JSON response. After a few modifications it passed the following when the application was run:

Loading the Data into AmCharts
The chartData array included with the AmCharts example is in the same format as the JSON response, so switching the two should be straightforward.

To adapt the AmCharts code, I imported dataloader.min.js and inserted the following JSON request code in place of the dataProvider section.

And set the categoryField and valueField variables to the JSON response field names. Here’s the prototype:

Binning Service Broker Messages

Tags

controller, messaging, MVC, Remove Message, Service Broker, SQL Server, Stored Procedure

Removing messages from a Service Broker queue isn’t straightforward as deleting a database table row, because a message is chained into a conversation and Service Broker expects it to be received eventually instead of dropped.

Initially I tried loading the entire queue into memory as objects (which Entity Framework already does) and removing the selected object before dumping everything back into the queue. After running into problems with that, I decided on a much simpler solution that involved creating a database table (dbo.QueueDump here) where selected messages could be dumped and later deleted.

When creating this table, it’s a good idea to add a timestamp and primary key, along with the main data column for the message body.

CREATE TABLE [dbo].[QueueDump](
[Id] [int] IDENTITY(1,1) NOT NULL,
[message_body] [xml] NULL,
[dateCreated] [datetime] NULL CONSTRAINT [DF_QueueDump_dateCreated] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My application already calls stored procedures for moving messages between queues, taking ‘conversation_handle‘ as the message identifier, so it’s just a matter of repurposing one of them to start a new conversation on a different queue and terminate the old conversation.

Next, in the application project, update the Entity Framework model to include the stored procedure (‘Update Model from Database…‘), or just add the following to the model’s DbContext class:

The controller for calling the stored procedure would look something like this:

And remember to modify the View layer also for the Delete function:

Untangling Some MVC 5 Problems

Tags

controller, CS1061, Entity Framework, MVC 5, Service Broker, Visual Studio

The following are fixes to some problems I encountered while building a Messaging Dashboard application using MVC 5 and Entity Framework 6.

Message Queue Doesn’t Appear in Entity Framework Model
Unlike a conventional database table, a message queue in the Service Broker part of the database cannot be imported into the Entity Framework model, and Entity Framework can’t read the queue through a stored procedure either (at least not without going through a Web Service).
As a workaround, in the SQL Server Management Studio, create a view for whichever message queue in the Service Broker. Next, import that view into the Entity Framework model for the application.

Error 404: Cannot Find Resource
In my case the problem was related to routing and the startup order of the application components. To resolve this, open the project’s properties and enter the Web section. Clear the Specific Page text box, and check the ‘Current Page‘ radio button.

After doing this, the application should be able to execute in debug mode, either by right-clicking whichever .cshtml file and selecting ‘Set As Start Page‘ or ‘View in Browser‘.

If the same problem persists, run the IIS Manager and ensure the application has an entry under Default Sites while it’s executing. In the Browse for Directory, find and select the location where the Visual Studio project is stored:

Back in the Visual Studio project’s properties, within the Servers section, click the Create Virtual Directory button, to load the virtual directory into IIS.

CS1061: Does Not Contain Definition and no Extension Method

As far as I can determine, the problem is a mismatch somewhere between the Model, Controller and View layers when referencing the object, and I think this happened because Visual Studio didn’t build the Controller-View ‘scaffold’ properly.
Scrap the Controller and Views for the database table/queue, and leave the Model in place. Next, create a new Controller, this time by selecting ‘Add‘ – ‘Controller…‘ instead of ‘New Scaffolded Item…‘, and as before, select the ‘MVC 5 Controller with with views, using Entity Framework‘.

This time, double-check the ‘Reference script libraries‘ option is not selected, and leave the ‘Use a layout page‘ field is blank.

Exploring a Web Service Architecture

Tags

api, c, Generic, UML, Web Service

At an ACM lecture in 1972, Edsger Dijkstra made a point about the human mind’s limited capacity to contain the logic of a sophisticated computer program from start to finish. Complexity is one of the reasons it’s easier to work with object-oriented code and abstraction, instead of creating something that might be unstructured but functional and syntactically correct. A book that I’ve found useful over the past couple of weeks is Code Complete (Steve McConnell, Microsoft Press), which goes into the best practices for designing and implementing complex software.

Web Services are straightforward to understand when they do something basic and exist in isolation. However, they can also be considerably more complex – the ones I’m developing are actually messengers carrying requests to stored procedures on a SQL server and carrying back the response to the requesting application. They consist of multiple components spanning multiple repositories. While this method of development had the purpose of reducing the complexity of the source code, it increased the complexity of the program’s structure, and I ended up creating a UML representation (using ArgoUML) as a way to visualise it.

Since this Web Service is one of many, the components are called from three separate repositories: Web Service APIs, Web Service Helpers and Data Access.

Web Service API
A very simple example is given below:

As we can see, this is just a public class that contains a call to method/class GetMyWebServiceResponse(),. This function is the Web Service helper, and its return value is declared as the Web Service response.

Web Service Helper
A helper class is the core of the Web Service. A helper can be any program or function that returns a value to the exposed API. An important point is there’s a separation of the logic from the API, and the logic can only be called by the API since it’s an ‘internal static’ method.

1. The stored procedure name and parameters are declared as fetchedData.
2. fetchedData is passed to myDataItemsResponseBody().
3. myDataItemsResponseBody(fetchedData) is passed to myDataItemsResponse().
4. Whatever is returned from myDataItemsResponse() is returned to the requesting application/service.

Data Item
The first thing to notice here is the namespace. The classes and methods for constructing the response exist in a different namespace (and repository) to the APIs and helpers.

The properties set the data type as being read-only and write-only when they need to be ( { get; set; } ). One of the potential advantages of using { get; set } is the accessibility of a variable/property wouldn’t be dependent on whether the class was declared private, e.g.
public int Number {get; protected set;}

Also, think of myDataItem in terms of being a ‘blueprint’ with properties userName and profileCode, rather than as a class with variables. The properties of an instance of myDataItems can be over-ridden in a derived class.

Data Response Body
The following code is for taking the data structure defined by myDataItem and forming the body of the Web Service response.

As we can see, ‘public class myDataItemResponseBody‘ is derived from a base class called ‘BaseResponseBody‘. This means we have an instance of that base class. This is also referred to as an ‘inherited virtual class’. It is a ‘virtual class’ that can be modified to handle different data types in particular.

You’ll notice that myDataItemResponseBody() was declared twice here, but with different inputs. This is known as ‘overloading’. One reason to do this is if you wanted just one method capable of adapting to the number of inputs during runtime, rather than having more than one method name to deal with.
Here we use this because we know that something’s going to call myDataItemResponseBody(), but we wouldn’t know whether a data row would be passed to it. If there is a data row, the method will use it to populate an array. If there is no data row when the method is called, nothing is returned.

Also notice the method below that, the ‘public override myDataItem‘. This overrides the base class that was instantiated. The override modifies the instance of the base class that was declared with whatever was declared in the derived class.

Data Response
Forms the entirety of what’s returned from a query.

Again this myDataItemResponse is also derived from a base class. The body is returned as an XmlElement, as whatever is returned from the Web Service is an XML formatted response.