Firstly, we will learn how to fetch your data from a database (here we will use MsSql ) and then present it in the View using HighCharts. To understand the complete article, you must have basic knowledge/understanding about the below mentioned technologies/ methods,
- Asp.Net MVC.
- MSSQL Server.
- JavaScript
- JSON (JavaScript Object Notation).
- ADO.Net
- Entity Framework
We will be using HighCharts for representing our record as chart/graph (given at the end of the article).
What is HighCharts?
Highcharts is a charting library written in pure JavaScript, offering an easy way of adding interactive charts to your web site or web application. In the vast majority of charts we will be using a basic column chart here to represent our data.
You can visit here to check more variety and select which is best for your scenario.
Steps 1 - Creating a basic Asp.Net MVC Web Based Application.
Create an Application in ASP.NET MVC web application using Visual Studio.
Go to File Menu > New > Project.
Select ASP.NET Web Application ( .NET Framework ) and change the application name:
e.g. : HighCharts, and then click OK
Choose MVC>
Now, the MVC web application project is created with the default ASP.NET MVC template.
Step 2 - Create/Step up your DataBase and Table.
Here, we are using an existing MsSql Database of a “Grievance Portal” and a table (T_Complaint) which stores the data of the person from whom the Complaint has been received and we will show the same number of applications which are received by year, by grouping them by years of application received and then sorting them by year.
The Structure of the table (T_Complaint) is as Below,
There will be many more columns like address, district, state, father’s name, complaint description, but as of now the above-mentioned columns are enough for our task.
Since the data that we will fetch could be huge here we are using “Stored Procedure” to retrieve it by just calling it from the controller as Stored Procedures take less time to fetch a huge amount of data as compared to SQL queries.
The below code will help you to create a Stored procedure.
Create PROCEDURE [dbo].[SP_YearlyRec]
AS
select sum(Total)as 'Total',max(t1.concluded)as concluded,MAX(t1.Not_concluded)as Not_concluded,Complainletteryear from (
select count(Complainletterdate) as 'Total',case when Conclusion=1 then count(year(Complainletterdate))end as concluded,
case when Conclusion=0 then count(year(Complainletterdate)) else 0 end as Not_concluded,YEAR(Complainletterdate) as [Complainletteryear] from T_Complaint
where Complainletterdate is not null
group by year(Complainletterdate),Conclusion
)t1 group by t1.Complainletteryear order by t1.Complainletteryear.
Step 3 - Working on Controller
Here we will use the default HomeController that is created automatically when we create a new MVC application.
In the HomeController we will use default Index() ActionMethod and modify it as per our need or you can also create a new ActionMethod for yourself.
public ActionResult Index()
{
return View();
}
Now create a view for your ActionMethod and give it the same name as your ActionMethod.
<view code is given below>
Next, we will create the WebMethod where we will call the Stored procedure which we have created previously.
[WebMethod]
public JsonResult GetText() {
var db1 = new Grievance_Entities1();
var list = db1.SP_YearlyRec();
return Json(list.ToList(), JsonRequestBehavior.AllowGet);
}
In the above code, Grievance_Entities1 is the entity we have created of our database which has a Stored Procedure “SP_YearlyRec” using Entity Framework.
Once it is created, open the index view and write the below code in the view.
We are dividing the View/HTML Code in 3 parts, Which are,
Head
The Head Will contain the links to HighCharts important Library as well as the styling of the complete graph inside a style tag . Every change in our design of our HighCharts need to be done here only in the Style tag itself.
Body
The body tag will contain the body of our HTML page which is HighCharts Graph in this Case.
<div class="container">
<h2 id="h2">Index</h2>
<figure class="highcharts-figure container" >
<div id="container-fluid" class="container-fluid"></div>
<p class="highcharts-description">
A basic column chart .
</p>
</figure>
</div>
Script
The Script tag will be the functioning body of our View as here we will use Ajax to fetch our data from the database by calling the WebMethod which is in our HomeController and will return Json Result as an output.
As this is the most important part of our module so I will be explaining the working of this module.
The javascript here has two parts first is the $(document).ready() ,and the other is a function which we have created that is LoadChart().
$(document).ready() is automatically triggered when the document is loaded. Whatever code we write inside the $(document ). ready() method will run once the page DOM is ready to execute JavaScript code.
So, here we will call the WebMethod in our controller using Ajax which is GetText using URL format “../{ControllerName}/{MethodName}”. And if the data is fetched correctly then we will call the LoadChart() function to Load the HighCharts with the fetched data.
Below is the code that will be placed in $(document ). ready() method.
$(document).ready(function() {
var Complainletteryear = [];
var Total = [];
var concluded = [];
var N_concluded = [];
$.ajax({
type: "POST",
url: "../Home/GetText",
data: "{}",
contentType: "application/json; charset=utf-8",
dataType: "json",
async: true,
success: function(result) {
$.each(result, function(key, item) {
Complainletteryear.push(item.Complainletteryear);
Total.push(item.Total);
concluded.push(item.concluded);
N_concluded.push(item.Not_concluded);
});
loadChart(Complainletteryear, Total, concluded, N_concluded);
},
error: function(errormessage) {
$('#h2').html(errormessage.responseText);
return false;
}
});
In the above code, after the data is fetched we are selecting each Row using the foreach loop and putting every column in a specific array as the data accepted by HighCharts is either in the form of Array or Objects. In our case we need our data in the form of array. Refer to foreach loop in the above code.
Below we can see the Code where we will be working with HighCharts.
function loadChart(category, val2, val3, val4) {
Highcharts.chart('container-fluid', {
chart: {
type: 'column',
},
title: {
text: 'No. of Application Received yearly '
},
subtitle: {
text: 'Source: LocalDb.com'
},
xAxis: {
categories: category,
crosshair: true
},
yAxis: {
min: 0,
title: {
text: 'Number of Application'
}
},
tooltip: {
headerFormat: '<span style="font-size:10px">{point.key}</span><table>',
pointFormat: '<tr href="#"><td style="color:{series.color};padding:0">{series.name}: </td>' + '<td style="padding:0"><b>{point.y} </b></td></tr>',
footerFormat: '</table>'
},
shared: true,
useHTML: true
}, series: [{
name: 'Total',
data: val2,
color: '#ccb1fc',
}, {
name: 'Concluded',
data: val3,
color: '#b0ceff',
}, {
name: 'Not Concluded',
data: val4,
color: '#fcb1f0'
}]
});
}
In the above code, we have 4 input parameters which are category, val2,val3,val4.
Each variable is of array type which represents each column provided as an output from our stored procedure.
The variable category here defines X-Axis Categories which will be shown in the chart as a category for each column. The remaining three arrays consist of data which is to be represented as a vertical bar in each column.
The title here defines the title of the Chart/Graph.
Chart Type tells what chart we will be using in the module.
Below is the figure which shows the output after implementing the above HighCharts code in Asp.Net MVC
Graphs In ASP.NET MVC Using HighCharts
Conclusion
To conclude , HighCharts are a user friendly graphical representation of data which can easily be represented using the above implementation in Asp.Net MVC application.
