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‘.
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:
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:
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: