Here, we will learn about creating dynamic Highcharts in ASP.NET MVC 5. We have already seen how we can create the Highcharts with static data, Now its time to create Highcharts with Dynamic data which is used in real-world applications. We will be creating line charts with dynamic data.
Roadmap for Developing the application
- Creating the ASP.NET application
- Setting up the Database
- Inserting Dummy Data for Creating Highcharts
- Reading data from database
- Integrating Highcharts Library in the application
- Creating Line Charts.
Creating the ASP.NET application
Create a new ASP.NET MVC 5 application or use any existing application in which you have to integrate the HighCharts. Install the Entity Framework and EpPlus package from the Nuget using the following command. We will be using excel file for inserting the dummy data.
Install-Package EPPlus Install-Package EntityFramework
Setting up the Database
We will start by adding the connection string to the Web Config. Add the following connection string in the application.
<connectionStrings>
<add name="DBConnectionString" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DynamicHighCharts.mdf;Initial Catalog=DynamicHighCharts;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
Now, we will create the folder for storing the class file for the database. Create two folders. One main folder Entity and inside it add another folder as POCO like this.
Create a Database Context file as CodeHubsContext.cs file in the POCO folder.
public class CodeHubsContext : DbContext
{
public CodeHubsContext() : base("DBConnectionString") { }
public DbSet<Analysis> Analyses { get; set; }
}
Also, create one file as Analysis.cs file in the same folder.
public class Analysis
{
[Key]
public int Id { get; set; }
public string Description { get; set; }
public string Date { get; set; }
public string Value { get; set; }
}
Open the Package Manager Console from the Tools options and Type the following command.
Enable-Migrations Add-Migration Initial Update-Database
Inserting Dummy Data for Creating Highcharts
We will be using the Excel file for inserting the dummy data for highcharts. You can find the excel file from my GitHub account. I’ll be adding the link at last.
Add the following code in the HomeController
public void AddExcelData()
{
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
List<Analysis> excelData = new List<Analysis>();
FileInfo existingFile = new FileInfo($"{AppDomain.CurrentDomain.BaseDirectory}/Content/SampleDBData.xlsx");
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
int rowCount = worksheet.Dimension.End.Row;
for (int row = 1; row <= rowCount; row++)
{
try
{
excelData.Add(new Analysis()
{
Description = worksheet.Cells[row, 1].Value.ToString().Trim(),
Date = worksheet.Cells[row, 2].Value.ToString().Trim(),
Value = worksheet.Cells[row, 3].Value.ToString().Trim()
});
}
catch (Exception)
{
}
}
_context.Analyses.AddRange(excelData);
_context.SaveChanges();
}
}
catch (Exception ex)
{
}
}
Reading data from database
Now, we will actually add the logic for reading the data from the database in proper format. The most important thing here is grouping the data according to our needs. We will be adding group by Description and Date and then sum up the values from the group result. Before that, we will need to apply Order By Date as HighCharts accepts Date in ascending order.
public JsonResult GetHighChartsData()
{
try
{
if (_context.Analyses.Count() == 0)
{
AddExcelData();
}
var highChartsData = _context.Analyses.AsEnumerable().OrderBy(x => Convert.ToDateTime(x.Date)).GroupBy(x => new { x.Description, x.Date }).Select(x => new
{
Title = x.Key.Description,
Date = x.Key.Date,
Value = x.Sum(y => decimal.Parse(y.Value, CultureInfo.InvariantCulture))
}).ToList();
return Json(new { isSuccess = true, data = JsonConvert.SerializeObject(highChartsData) }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new { isSuccess = false, message = ex.Message });
}
}
Here, we have inserted the data first if there is no data in the database. After that our main Logic is applied as explained above.
Whole code will look like this together
public class HomeController : Controller
{
private readonly CodeHubsContext _context = null;
public HomeController()
{
_context = new CodeHubsContext();
}
public ActionResult Index() => View();
public void AddExcelData()
{
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
List<Analysis> excelData = new List<Analysis>();
FileInfo existingFile = new FileInfo($"{AppDomain.CurrentDomain.BaseDirectory}/Content/SampleDBData.xlsx");
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
int rowCount = worksheet.Dimension.End.Row;
for (int row = 1; row <= rowCount; row++)
{
try
{
excelData.Add(new Analysis()
{
Description = worksheet.Cells[row, 1].Value.ToString().Trim(),
Date = worksheet.Cells[row, 2].Value.ToString().Trim(),
Value = worksheet.Cells[row, 3].Value.ToString().Trim()
});
}
catch (Exception)
{
}
}
_context.Analyses.AddRange(excelData);
_context.SaveChanges();
}
}
catch (Exception ex)
{
}
}
public JsonResult GetHighChartsData()
{
try
{
if (_context.Analyses.Count() == 0)
{
AddExcelData();
}
var highChartsData = _context.Analyses.AsEnumerable().OrderBy(x => Convert.ToDateTime(x.Date)).GroupBy(x => new { x.Description, x.Date }).Select(x => new
{
Title = x.Key.Description,
Date = x.Key.Date,
Value = x.Sum(y => decimal.Parse(y.Value, CultureInfo.InvariantCulture))
}).ToList();
return Json(new { isSuccess = true, data = JsonConvert.SerializeObject(highChartsData) }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new { isSuccess = false, message = ex.Message });
}
}
}
Integrating Highcharts Library in the application
Open the _Layout.cshtml file from the Shared folder and replace it with the following code.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - Dynamic HighCharts</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Application name", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - Dynamic HighCharts</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<script src="https://code.highcharts.com/modules/export-data.js"></script>
<script src="https://code.highcharts.com/modules/accessibility.js"></script>
@RenderSection("scripts", required: false)
</body>
</html>
Now, its time to write the jQuery code for building the line charts. So open the Index.cshtml file from the Home folder and add the following code in it.
Creating Line Charts
@{
ViewBag.Title = "Home Page";
}
<div class="row">
<div class="col-md-12" id="lineCharts">
</div>
</div>
@section scripts{
<script>
var lineSeries = [];
function groupBy(array, f) {
var groups = {};
array.forEach(function (o) {
var group = JSON.stringify(f(o));
groups[group] = groups[group] || [];
groups[group].push(o);
});
return Object.keys(groups).map(function (group) {
return groups[group];
});
}
$(document).ready(function () {
fetch('/Home/GetHighChartsData').then(res => res.json()).then(res => {
if (res.isSuccess) {
var data = JSON.parse(res.data);
var groupedData = groupBy(data, function (item) {
return [item.Title]
});
lineSeries = [];
for (var i = 0; i < groupedData.length; i++) {
var name = groupedData[i][0]["Title"];
var tempLine = [];
for (var j = 0; j < groupedData[i].length; j++) {
tempLine.push({
"x": new Date(groupedData[i][j]["Date"]).valueOf(),
"y": parseFloat(groupedData[i][j]["Value"])
})
}
lineSeries.push({
"name": name,
"data": tempLine
});
}
$('#lineCharts').highcharts({
chart: {
type: 'line',
zoomType: 'x'
},
title: {
text: 'Analysis Range',
},
xAxis: {
type: 'datetime',
labels: {
format: "{value:%b %e }",
}
},
yAxis: {
title: {
text: 'Analysis Range Values',
},
},
series: lineSeries
});
}
});
});
</script>
}
Here, we will first group by title for our data and converts the dates into the timestamp format. It will be required for making the data in a format that highcharts accept.
Code in action:
You can find the source code from my Github account from here
