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…‘

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

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. This JavaScript section was repurposed from another tutorial, just to ascertain there was was a JSON response.

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.