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
  2. Jason Smith 11/14/2015 01:30:58
    I get an error when I try to invoke the web method. The type System.Collections.Generic.List`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] may not be used in this context. I'm using the following libraries along with the web service as copied from the post: using System; using System.Collections.Generic; using System.Web.Services; using System.Web.Script.Services; using System.Configuration; using System.Data; using System.Data.SqlClient; Any suggestions? Thanks
  3. Francis 03/02/2016 01:58:31
    thank you for this tutorial, it's great!! just want to ask, how to fill the color for legends? My linechart is able to show the legend text but the color is not the same with what I have set with them. thank you!
  4. Danny 09/04/2016 14:19:06
    Thank you for uploading this tutorial. I am getting the error below when trying to invoke the webmethod. The type System.Collections.Generic.List`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] may not be used in this context. Any tips or suggestions ? Thanks
  5. charan 10/04/2016 22:41:42
    Hi, Satinder u r article is really nice. Upload the source code so that it will clarify certain doubts where we got struck.
  6. Satinder singh 10/05/2016 07:22:31
    Hi Charan thanks for reading, well regarding source code am not sure whether I have with me right now, will let you when I found it
  7. First 01/29/2017 16:42:54
    HI, I can't get the line to appear. My web method returns an array of objects - List(Of Object). There are 3 objects each with 98 entries. The first is the label - a date/time and the other 2 represents the points on the line graph integers. Running the web method from a console app, I can see the lists returned. iData Count = 3 (0) Count = 98 (1) Count = 98 (2) Count = 98 For example: The first: (0) #1/29/2017 10:10:30 AM (1) #1/29/2017 10:12:00 AM (2) #1/29/2017 10:12:30 AM (3) #1/29/2017 10:13:30 AM I display the chart data in alerts just before painting the line. For the label, it supposed to be date time, but I get: labels /Date(1485703380827),/Date(1485703410233)/,.... to 96 For the 1st dataset, I get: data 4,2,2,3,4,.... to 96 For the 2nd dataset, I get: data 1,0,2,5,1,.... to 96 The line chart does not appear in the canvas. Any ideas?
  8. Dan 01/29/2017 16:47:54
    Hi, I can't get the line to appear on the canvas. My web method returns an array of objects - List(Of Object). There are 3 objects each with 98 entries. The first is the label - a date/time and the other 2 represents the points on the line graph integers. Running the web method from a console app, I can see the lists returned. iData Count = 3 (0) Count = 98 (1) Count = 98 (2) Count = 98 For example: The first node: (0) #1/29/2017 10:10:30 AM (1) #1/29/2017 10:12:00 AM (2) #1/29/2017 10:12:30 AM (3) #1/29/2017 10:13:30 AM....up to 98. I display the chart data in alerts just before painting the line. For the label, it supposed to be date time, but I get: labels /Date(1485703380827),/Date(1485703410233)/,.... to 96 For the 1st dataset, I get: data 4,2,2,3,4,.... to 96 For the 2nd dataset, I get: data 1,0,2,5,1,.... to 96 The line chart does not appear in the canvas. Any ideas?
  9. First 01/29/2017 16:49:21
    Hi, I can't get the line to appear on the canvas. My web method returns an array of objects - List(Of Object). There are 3 objects each with 98 entries. The first is the label - a date/time and the other 2 represents the points on the line graph integers. Running the web method from a console app, I can see the lists returned. iData Count = 3 (0) Count = 98 (1) Count = 98 (2) Count = 98 For example: The first node: (0) #1/29/2017 10:10:30 AM (1) #1/29/2017 10:12:00 AM (2) #1/29/2017 10:12:30 AM (3) #1/29/2017 10:13:30 AM....up to 98. I display the chart data in alerts just before painting the line. For the label, it supposed to be date time, but I get: labels /Date(1485703380827),/Date(1485703410233)/,.... to 96 For the 1st dataset, I get: data 4,2,2,3,4,.... to 96 For the 2nd dataset, I get: data 1,0,2,5,1,.... to 96 The line chart does not appear in the canvas. Any ideas?
  10. Satinder singh 01/30/2017 07:08:35
    Hi, To display the line chart properly, your dataset data type should be the integer, Make sure you return dataset 1 and 2 as integer value.
  11. Linda 02/16/2017 10:58:21
    Hi Satinder! Thanks for the amazing post. I have repeated your code in my system but getting stuck with the webservice, which is simply not working :( Could you pls help me with the error I am getting: System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: The type System.Collections.Generic.List`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] may not be used in this context.
  12. crispin owuor 02/17/2017 21:03:02
    Wow, great project, took me while but i got it running well, building a dashboard at my company and this knowledge is quite useful
  13. Satinder singh 04/06/2017 15:28:50
    Hi Crispin, Am glad to know you find this article useful, Its would if you share the screenshot of your dashboard :)
  14. Dc 04/15/2017 16:09:05
    I believe this is an older article but very helpful to me. Were you able to find your source code and post anywhere? Also
  15. ajeet 07/31/2017 22:48:35
    y-axis data is not working prorperly Why? how to set data of y axis just like as x axis
  16. Sarita 08/18/2017 11:30:29
    Provide name to Data table in web method.this will fix the issue
  17. ERJ 12/14/2017 03:28:03
    Very good tutorial, any insight you could provide on how to approach constructing a line chart with chart.js with this same method but for a scenario that has a variable number of data points and series? Thanks in advance! Thanks, E
  18. Person23456 01/17/2018 13:23:25
    Excellent tutorial.. is the source code available?
  19. Dinesh 10/30/2018 12:38:44
    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