Chart.js + Asp.net : Dynamically create Line chart with database MS SQL using Jquery Ajax C# example

/ / 19 Comments

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:

Dynamically create Line chart in Asp.net C# using Chart.js

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#:

  1. Create Database and insert Data check Fig 1, Fig 2
  2. Download Chart.js and jQuery latest files.
  3. HTML Markup: add an HTML5 canvas tag that is used for Line Chart data display.
  4. Server-side code: C# - Add WebMethod service which returns JSON format data.
  5. Client-Side code:  making jquery ajax call to WebMethod, and initialize chart.js
Introduction :

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
Query to get the monthly mobile total quantity of orders

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 !");
	}
    });
});
Finally, we are done with coding, Now every time when the user selects any value from the drop-down list and clicks the show button, the Line chart gets created from the live database as you have seen in the above output image.

You can also check these articles:

  1. Chart.js Asp.net: Create Pie chart with database Jquery Ajax C#.
  2. Chart.js Asp.net: Create Line chart with database Jquery Ajax C#.
  3. Generic Handler ashx file: Post send JSON data in Asp.net C#.
  4. 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

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.


Post Comment

Your email address will not be published. Required fields are marked *

19 Comments

  1. Johnny 06/25/2015 05:46:12
    Excellent exhibition of complex line chart tutorial with database. Was looking for it .. Very neat and clean. Thanks a lot. Do keep posting such useful stuffs