Dynamic Line Chart in ASP .NET from SQL database using Chart.js

-
This article is about Chart in ASP .NET. Most of the web application's require data to be presented in graphical representation. Charts are easy to understand and gives clear idea about the data to the user. It is best to use chart for representing some data in web application. In this article we will use Line chart for data representation. The data will be retrieved from database and represented in graph on Web Page. Let's consider the example of shop system. The user requires the report to view daily sales. We can show it in tabular format. But it would be best to represent this report in line chart so that user can analyse the progress. Create simple system for this situation. We require database from which we will retrieve the data.Create the table tblProduct.


  Insert dump data in the tblProduct. 


  To get the quantity sold per day the query is
SELECT CONVERT(VARCHAR,date,106) 'Date',SUM(quantity_sold) 'Qty' FROM tblProduct GROUP BY date;
Create page Default.aspx. 
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

</head>
<body>
    <form id="form1" runat="server">
    <div>
    <canvas id="Chart" width="600" height="500"></canvas>
    </div>
    </form>
    
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
   
     <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/1.0.2/Chart.js"></script>

   
</body>
</html>
canvas tag is HTML tag.canvas is actually container for graphics which can be drawn using script (mostly JavaScript). To draw line chart we must get data from database in JavaScript in the correct format. For data we have to create WebMethod which will return data. For Line chart we require two data-sets: 1.Label (X-axis) 2.Data (Y-axis) So the WebMethod will send the List of string containing Labels and Data.The WebMethod is: 
 [System.Web.Services.WebMethod]
    public static List<string> getChartData()
    {
        var returnData = new List<string>();
        var Con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString);
        var sql = new SqlCommand("select convert(varchar,date,106) 'Date',sum(quantity_sold) 'Qty' from tblProduct group by date;", Con);
        var dataAdapter = new SqlDataAdapter(sql);
        var dataset = new DataSet();
        dataAdapter.Fill(dataset);


        var chartLabel = new StringBuilder();
        var chartData = new StringBuilder();
        chartLabel.Append("[");
        chartData.Append("[");


        foreach (DataRow row in dataset.Tables[0].Rows)
        {
            

            chartLabel.Append(string.Format("'{0}',", row["Date"].ToString()));
            chartData.Append(string.Format("{0},", row["Qty"].ToString()));


        }

        chartData.Length--; //For removing ','  
        chartData.Append("]");
        chartLabel.Length--; //For removing ',' 
        chartLabel.Append("]");

        returnData.Add(chartLabel.ToString());
        returnData.Add(chartData.ToString());
        return returnData;
    }
We have done server side coding. Now for client-side, we have to initialize the chart,call the WebMethod and draw the line chart based on the data WebMethod returns. The Client-side JavaScript code is: 
  $(function () {
            var ctx = document.getElementById("Chart").getContext('2d');
           $.ajax({
            
                url: "Default.aspx/getChartData",
                type: "POST",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success:function(response)
                {
                    var chartLabel = eval(response.d[0]); //Labels
                    var chartData = eval(response.d[1]); //Data
                    var barData = {
                        labels: chartLabel,
                        datasets: [
                            {
                                label: 'July Sales',
                                fillColor: "rgba(225,225,225,0.2)",
                                strokeColor: "Blue",
                                pointColor: "rgba(220,220,220,1)",
                                pointStrokeColor: "Green",
                                pointHighlightFill: "#fff",
                                pointHighlightStroke: "rgba(220,220,220,1)",
                                data: chartData
                            }
                        ]
                    };
                    var skillsChart = new Chart(ctx).Line(barData);
                }
                
            });
        }
        );
When we run the Default.aspx. The WebMethod gets called and the data it sends is:
  And the browser shows


 Hope you have got idea about how to use chart.js in ASP .NET for line chart using data from SQL Database. If you any query/suggestions please let me know in comment below.

Comments

Post a Comment