Overview: This article explains using Chart.js we create a Line Chart with database MS SQL server connectivity via jQuery ajax call in Asp.net c#. You can also check my previous article related to Chartjs, or check Using HTML5 Canvas Chart.js Generate Simple Pie Chart example, Simple Bar Chart example using html5 canvas jQuery, Dynamically Create Pie chart with database JQuery Chart.js Ajax Asp.net C#.
Now in this post here we create a Line chart by using the chart.js library and bind data from our database MS SQL Server, with jQuery ajax WebMethod calling.
Chart.js: Simple, clean, and engaging charts for designers and developers developed by Nick Downie. It uses the HTML5 canvas element.
It's supported in all modern browsers, and polyfills support for IE7/8. Chart.js is dependency-free and super lightweight.
Output:
What is Line Chart?
A line chart is a way of plotting data points on a line. Often, it is used to show trend data and the comparison of two data sets.
The line chart requires an array of labels for each of the data points. This is shown on the X-axis. The data for line charts are broken up into an array of datasets.
Each dataset has a color for the fill, a color for the line, and colors for the points and strokes of the points. These colors are strings just like CSS. You can use RGBA, RGB, HEX, or HSL notation.
The label key on each dataset is optional and can be used when generating a scale for the chart.
Steps to generate Line Chart in Asp.net C#:
- Create Database and insert Data check Fig 1, Fig 2
- Download Chart.js and jQuery latest files.
- HTML Markup: add an HTML5 canvas tag that is used for Line Chart data display.
- Server-side code: C# - Add WebMethod service which returns JSON format data.
- Client-Side code: making jquery ajax call to WebMethod, and initialize chart.js
Here we dynamically create a Line Chart, which shows data from the database ( MS SQL server ). In my database, I have already created a table that stores data ( Product [Mobile] Sales order per year ).
So basically our Line chart data will display selected mobile Sales Orders for selected year month-wise .i.e. ( how many X and Y mobiles sales on the nth month ).
# Database Schema and Data:
Fig 1: Database Schema-
Fig 2: Database data values- used to create a dynamic line chart in asp.net c# with Chart.js
Here's the query to get distinct month names from a DateTime datatype
Select
distinct( DateName( month , DateAdd( month , DATEPART(MONTH,orders_date) , -1 ) ))
as month_name
from mobile_sales
where DATEPART(YEAR,orders_date)='" + year + "' order by month_number
select
month(orders_date) as month_number ,
sum (orders_quantity) as total_quantity
from mobile_sales
where YEAR(orders_date)='" + year + "' and mobile_id='" + mobileId_one + "'
group by month(orders_date) order by month_number
# Download: Chart.js file and include in your Asp.net C# Web Application
First, we need to download and include the latest files of the chart.js, and the latest jQuery library on our web page. By doing that our HTML markup under the head tag looks like as shown below.
//*
<head runat="server">
<title>Dynamically Generate Line Chart, Asp.net with database MS SQL server</title>
<script src="js/jquery-1.10.2.min.js" type="text/javascript"></script>
<script src="js/Chart.js" type="text/javascript"></script>
</head>
//*# Html Markup : Adding HTML5 Canvas Tag
I have added 3 drop-down list control for selecting Mobile 1, Mobile 2, and the year, along with a button and the HTML5 canvas tag.
Now on button click will make a jQuery ajax call, and then on its success method, our Line chart gets generated dynamically by using Chart.js library and HTML5 Canvas tag.
<div style="float: left; margin-left: 30px;">
<select id="ddl_one">
<option value="1">Moto G</option>
<option value="2">IPhone 5</option>
<option value="3">Samsung S5</option>
</select>
<select id="ddl_two">
<option value="1">Moto G</option>
<option value="2">IPhone 5</option>
<option value="3">Samsung S5</option>
</select>
<select id="ddlYear">
<option>2013</option>
<option>2014</option>
<option>2015</option>
</select>
<input id="btn_line_chart" type="button" value="Show" /></div>
</div>
<br />
<div>
<canvas id="myChart"> </canvas>
</div># Code Behind: Ajax call WebMethod and returns JSON format data to create Line Chart
First, we add a WebService in our application .i.e ( myWebService.asmx file), and then we write our WebMethod which returns JSON format data.
I must suggest you have a look at how to make a simple jQuery ajax JSON example in Asp.net with SQL database. Here is the full code, this is how our WebMethod looks like.
[WebMethod]
public List<object> getLineChartData(string mobileId_one, string mobileId_two, string year)
{
List<object> iData = new List<object>();
List<string> labels = new List<string>();
//First get distinct Month Name for select Year.
string query1 = "Select distinct( DateName( month , DateAdd( month , DATEPART(MONTH,orders_date) , -1 ) )) as month_name, ";
query1 += " DATEPART(MONTH,orders_date) as month_number from mobile_sales where DATEPART(YEAR,orders_date)='" + year + "' ";
query1 += " order by month_number;";
DataTable dtLabels = commonFuntionGetData(query1);
foreach (DataRow drow in dtLabels.Rows) {
labels.Add(drow["month_name"].ToString());
}
iData.Add(labels);
string query_DataSet_1 = " select DATENAME(MONTH,DATEADD(MONTH,month(orders_date),-1 )) as month_name, month(orders_date) as month_number ,sum ";
query_DataSet_1 += " (orders_quantity) as total_quantity from mobile_sales ";
query_DataSet_1 += " where YEAR(orders_date)='" + year + "' and mobile_id='" + mobileId_one + "' ";
query_DataSet_1 += " group by month(orders_date) ";
query_DataSet_1 += " order by month_number ";
DataTable dtDataItemsSets_1 = commonFuntionGetData(query_DataSet_1);
List<int> lst_dataItem_1 = new List<int>();
foreach (DataRow dr in dtDataItemsSets_1.Rows){
lst_dataItem_1.Add(Convert.ToInt32(dr["total_quantity"].ToString()));
}
iData.Add(lst_dataItem_1);
string query_DataSet_2 = " select DATENAME(MONTH,DATEADD(MONTH,month(orders_date),-1 )) as month_name, month(orders_date) as month_number ,sum ";
query_DataSet_2 += " (orders_quantity) as total_quantity from mobile_sales ";
query_DataSet_2 += " where YEAR(orders_date)='" + year + "' and mobile_id='" + mobileId_two + "' ";
query_DataSet_2 += " group by month(orders_date) ";
query_DataSet_2 += " order by month_number ";
DataTable dtDataItemsSets_2 = commonFuntionGetData(query_DataSet_2);
List<int> lst_dataItem_2 = new List<int>();
foreach (DataRow dr in dtDataItemsSets_2.Rows)
{
lst_dataItem_2.Add(Convert.ToInt32(dr["total_quantity"].ToString()));
}
iData.Add(lst_dataItem_2);
return iData;
}
public DataTable commonFuntionGetData(string strQuery)
{
SqlDataAdapter dap = new SqlDataAdapter(strQuery, cn);
DataSet ds = new DataSet();
dap.Fill(ds);
return ds.Tables[0];
}# jQuery:- Client-side code, initializing Chart.js
Now we do our client-side coding, we have already done with the server-side code by creating WebMethod which returns JSON format data.
So now in our Ajax call on success will initialize the Chart.js variable. Client-side code looks like as written below
$(document).ready(function () {
$("#btn_line_chart").on('click', function () {
var mb_one = $("#ddl_one").val();
var mb_two = $("#ddl_two").val();
var getYear = $("#ddlYear").val();
var jsonData = JSON.stringify({
mobileId_one: mb_one,
mobileId_two: mb_two,
year: getYear
});
$.ajax({
type: "POST",
url: "AjaxFunction/myFunction.asmx/getLineChartData",
data: jsonData,
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess_,
error: OnErrorCall_
});
function OnSuccess_(reponse) {
var aData = reponse.d;
var aLabels = aData[0];
var aDatasets1 = aData[1];
var aDatasets2 = aData[2];
var data = {
labels: aLabels,
datasets: [{
label: "My First dataset",
fillColor: "rgba(220,220,220,0.2)",
strokeColor: "rgba(220,220,220,1)",
pointColor: "rgba(220,220,220,1)",
pointStrokeColor: "#fff",
pointHighlightFill: "#fff",
pointHighlightStroke: "rgba(220,220,220,1)",
data: aDatasets1
},
{
label: "My Second dataset",
fillColor: "rgba(151,187,205,0.2)",
strokeColor: "rgba(151,187,205,1)",
pointColor: "rgba(151,187,205,1)",
pointStrokeColor: "#fff",
pointHighlightFill: "#fff",
pointHighlightStroke: "rgba(151,187,205,1)",
data: aDatasets2
}]
};
var ctx = $("#myChart").get(0).getContext('2d');
ctx.canvas.height = 300; // setting height of canvas
ctx.canvas.width = 500; // setting width of canvas
var lineChart = new Chart(ctx).Line(data, {
bezierCurve: false
});
}
function OnErrorCall_(repo) {
alert("Woops something went wrong, pls try later !");
}
});
});You can also check these articles:
- Chart.js Asp.net: Create Pie chart with database Jquery Ajax C#.
- Chart.js Asp.net: Create Line chart with database Jquery Ajax C#.
- Generic Handler ashx file: Post send JSON data in Asp.net C#.
- jQuery Ajax JSON Example in Asp.net with SQL database.
Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.
PS: If you found this content valuable and want to thank me? 👳 Buy Me a Coffee
Post Comment
Your email address will not be published. Required fields are marked *