Want a free Ebook & latest Update Subscribe Now
  [email protected]      beta
Donate

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

 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 Line  chart by using chart.js library and bind data from our database Ms Sqlserver, 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.

Screenshot:

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

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 is broken up into an array of datasets.

Each dataset has a colour for the fill, a colour for the line and colours for the points and strokes of the points. These colours 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 HTML5 canvas tag which 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 jQuey 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 which 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-

MS SQL server Database Schema for dynamically creating Line chart using HTML5 canvas Chart.js Asp.net c# example

Fig 2: Database data values- used to create dynamic line chart in asp.net c# with Chart.js

Ms sql server data for create Line chart using HTML5 canvas Chart.js Asp.net c# example

 

Here’s the query to get distinct month name 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 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 latest files of the chart.js, and latest jQuery library in our web page. By doing that our HTML markup under 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 on how to make a simple jQuery ajax JSON example in Asp.net with SQL database. Here 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 return JSON format data.

So now in our Ajax call on success will initialize 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 user selects any value from the drop-down list and click show button, then Line chart gets created from the live database.

OUTPUT :-

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

 

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.

Hope you enjoyed this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

Share:    
Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }


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

  1. Iman

    Good tutorial.
    I made some changes to make it works on my laptop.

    var aLabels = reponse[0];
    var aDatasets1 = reponse[1];
    var aDatasets2 = reponse[2];

    //
    //
    //

    var ctx = $(“#myChart”).get(0).getContext(‘2d’);
    ctx.canvas.height = 300; // setting height of canvas
    ctx.canvas.width = 500; // setting width of canvas

    var myChart = new Chart(ctx, {
    type: ‘line’,
    data: data,
    bezierCurve: false
    });

  2. crispin owuor

    Wow, great project, took me while but i got it running well, building a dashboard at my company and this knowledge is quite useful

  3. Linda

    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.

  4. First

    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?

  5. Dan

    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?

  6. First

    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?

  7. charan

    Hi, Satinder u r article is really nice. Upload the source code so that it will clarify certain doubts where we got struck.

  8. Danny

    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

  9. Francis

    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!

  10. Jason Smith

    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

  11. Johnny

    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


Post Comment


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