locked
How to dynamically take data from database and display them in chartJS plot? RRS feed

  • Question

  • User-1313071134 posted

    I am using ASP.NET web application template.

    I have data stored in the SQL database. I want to select certain columns and add them to a chart using chartJS, my ultimate goal is to make the process happen dynamically without refreshing the whole webpage. Unfortunately, until now I have been able to render the chartJs canvas in C# as shown below:

    String chart = "";
    chart = "<canvas id=\"line-chart\" width=\"120%\" height=\"30\"></canvas>";
    chart += "<script>";
    chart += "new Chart(document.getElementById(\"line-chart\"), { type: 'line', data: {labels: [";
    
    
    //Select the first 75 data sample
    for (int i = 0; i < 75; i++)
          chart += i.ToString() + ",";
          chart = chart.Substring(0, chart.Length - 1);
          chart += "],datasets: [{ data: [";
    
           //Select data from the database and add to chart
           String value = "";
           for (int i = 0; i < 75; i++)
               value += tb.Rows[i]["Engine_Hours"].ToString() + ",";
               value = value.Substring(0, value.Length - 1);
               chart += value;
               chart += "],label: \"Engine Hours\",borderColor: \"#cd3e3e\",fill: true}"; // Chart color
               chart += "]},options: { title: { display: false,text: 'Engine Hours (hr)'} }"; // Chart title
               chart += "});";
               chart += "</script>";
               //Render the chart
               Literal1.Text = chart;

    This works good and everything. However, this process only displays the charts as a static image and the data won't change unless I refresh the whole webpage.

    I am new to ajax and their implementations in javascript, though I need some guidance, I really need only one working example and from there I will be able to re-create all the chart the same way.

    These are the columns in my DADLoggerTable dataset 

    SELECT TOP (1000) [Accelerometer_X_Axis_Data]
          ,[Accelerometer_Y_Axis_Data]
          ,[Accelerometer_Z_Axis_Data]
          ,[Gyroscope_X_Axis_Data]
          ,[Gyroscope_Y_Axis_Data]
          ,[Gyroscope_Z_Axis_Data]
          ,[Temperature_Data]
          ,[Accelerometer_Magnitude]
          ,[UTC_Time]
          ,[Local_Time]
          ,[Local_Time_Seconds]
          ,[Methane_Value]
          ,[T1]
          ,[T2]
          ,[T3]
          ,[T4]
          ,[T5]
          ,[Engine_Hours]
      FROM [DADLoggerTable].[dbo].[DADLoggerTable]

    I tried the code below in the .aspx for the gauge chart from google chart 

    <script>
            //var randomScalingFactor = function () {
            //    return Math.round(Math.random() * 100);
    
            //};
    
            var randomData = function () {
                $.ajax({
                    type: "POST",
                    url: "2168615.aspx/getTrafficSourceData",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess_,  
                });
                function OnSuccess_(response) {
                    var aData = response.d;
                    var arr = [];
                    $.each(JSON.parse(aData), function (inx, val) {
                        // What to put in here?
                    });
                     return arr;
                }
                
            };
    
            var randomValue = function (data) {
                return Math.max.apply(null, data) * Math.random();
            };
    
            var data = randomData();
            var value = randomValue(data);
    
            var config = {
                type: 'gauge',
                data: {
                    labels: ['Success', 'Warning', 'Warning', 'Fail'],
                    datasets: [{
                        data: data,
                        value: value,
                        backgroundColor: ['green', 'yellow', 'orange', 'red'],
                        borderWidth: 2
                    }]
                },
                options: {
                    responsive: true,
                    title: {
                        display: true,
                        text: 'Gauge chart with datalabels plugin displaying labels'
                    },
                    layout: {
                        padding: {
                            bottom: 30
                        }
                    },
                    needle: {
                        // Needle circle radius as the percentage of the chart area width
                        radiusPercentage: 2,
                        // Needle width as the percentage of the chart area width
                        widthPercentage: 3.2,
                        // Needle length as the percentage of the interval between inner radius (0%) and outer radius (100%) of the arc
                        lengthPercentage: 80,
                        // The color of the needle
                        color: 'rgba(0, 0, 0, 1)'
                    },
                    valueLabel: {
                        display: false
                    },
                    plugins: {
                        datalabels: {
                            display: true,
                            formatter: function (value, context) {
                                return context.chart.data.labels[context.dataIndex];
                            },
                            //color: function (context) {
                            //  return context.dataset.backgroundColor;
                            //},
                            color: 'rgba(0, 0, 0, 1.0)',
                            //color: 'rgba(255, 255, 255, 1.0)',
                            backgroundColor: null,
                            font: {
                                size: 20,
                                weight: 'bold'
                            }
                        }
                    }
                }
            };
    
            window.onload = function () {
                var ctx = document.getElementById('chart').getContext('2d');
                window.myGauge = new Chart(ctx, config);
            };
    
            //document.getElementById('randomizeData').addEventListener('click', function () {
            //    config.data.datasets.forEach(function (dataset) {
            //        dataset.data = randomData();
            //        dataset.value = randomValue(dataset.data);
            //    });
    
            //    window.myGauge.update();
            //});
        </script>

    for the page loader in .aspx.cs

    protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            [WebMethod]
            public static string getTrafficSourceData()
            {
    
    
                string conn = ConfigurationManager.ConnectionStrings["DataBaseConnectionString"].ConnectionString;
    
                using (SqlConnection cn = new SqlConnection(conn))
                {
                    string myQuery = "SELECT* FROM DADLoggerTable";
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = myQuery;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = cn;
                    cn.Open();
                    List<trafficSourceData> t = new List<trafficSourceData>();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        //int counter = 0;
                        while (dr.Read())
                        {
                            trafficSourceData tsData = new trafficSourceData();
                            tsData.value = dr["T1"].ToString();
                            //tsData.label = dr["traffic_source"].ToString();
                            //tsData.color = arrColor[counter];
                            t.Add(tsData);
                            //counter++;
                        }
                    }
                    return "{\"data\":" + JsonConvert.SerializeObject(t) + "}";
                }
    
            }

    But unfortunately, the chart is not rendered.

    Any idea please on how to solve this problem. I would greatly appreciate that. 

    Friday, July 31, 2020 11:28 AM

All replies

  • User475983607 posted

    The key is following standards.  Return a complex type that contains the ChartJs JSON types rather than a string.  The framework automatically serializes the type.  It is much easier and less error prone to populate a complex type rather building a JSON string.

    https://forums.asp.net/p/2129509/6169081.aspx

    Friday, July 31, 2020 11:48 AM
  • User-1330468790 posted

    Hi samiarja,

     

    I can see that your problem occurs due to the ajax request.

    If you want to make an ajax call and use the response data, then you have to complete the chart construction inside the success function.

     

    Apart from that, I think you will need to focus on the meaning of the order for each labels since it might be different from what you expected.

     

    More details, you could refer to below codes and demo.

    html: 

    <head runat="server">
        <title></title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
        <script src="https://unpkg.com/chart.js@2.8.0/dist/Chart.bundle.js"></script>
        <script src="https://unpkg.com/chartjs-gauge@0.2.0/dist/chartjs-gauge.js"></script>
        <script src="https://unpkg.com/chartjs-plugin-datalabels@0.7.0/dist/chartjs-plugin-datalabels.js"></script>
    
        <script>
    
    
            var randomValue = function (data) {
                var result = Math.max.apply(null, data) * Math.random();
                console.log(result);
                return result;
            };
    
    
            var randomData = function () {
                $.ajax({
                    type: "POST",
                    url: "GoogleChartGauge.aspx/getTrafficSourceData",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess_,
                });
                function OnSuccess_(response) {
                    var aData = response.d;
    // aData: [{"T1":50},{"T1":60},{"T1":79}, {"T1":100}] need and only need 4 values var data = []; $.each(JSON.parse(aData), function (inx, val) { // What to put in here? Answer: construct an array with 4 values to match 4 labels data.push(val.T1); }); // If we set four areas, 25, 50, 75, 100 and value with 50, then the pointer will be pointing to the middle of the gauge var value = randomValue(data); var config = { type: 'gauge', data: { labels: ['Success', 'Warning', 'Warning', 'Fail'], datasets: [{ data: data, value: value, backgroundColor: ['green', 'yellow', 'orange', 'red'], borderWidth: 2 }] }, options: { responsive: true, title: { display: true, text: 'Gauge chart with datalabels plugin displaying labels' }, layout: { padding: { bottom: 30 } }, needle: { // Needle circle radius as the percentage of the chart area width radiusPercentage: 2, // Needle width as the percentage of the chart area width widthPercentage: 3.2, // Needle length as the percentage of the interval between inner radius (0%) and outer radius (100%) of the arc lengthPercentage: 80, // The color of the needle color: 'rgba(0, 0, 0, 1)' }, valueLabel: { display: false }, plugins: { datalabels: { display: true, formatter: function (value, context) { return context.chart.data.labels[context.dataIndex]; }, color: 'rgba(0, 0, 0, 1.0)', backgroundColor: null, font: { size: 20, weight: 'bold' } } } } }; // Get ctx var ctx = document.getElementById('chart').getContext('2d'); window.myGauge = new Chart(ctx, config); } }; // Start the construction of the chart window.onload = function () { randomData(); }; </script> </head> <body> <form id="form1" runat="server"> <div id="canvas-holder" style="width: 100%"> <canvas id="chart"></canvas> <canvas id="canvas"></canvas> </div> </form> </body>

    Demo:

     

    Hope this can help you.

    Best regards,

    Sean
     

    Monday, August 3, 2020 9:41 AM
  • User-1313071134 posted

    Hi Sean,

    Thanks for your comments.

    unfortunately, the gauge is not rendered. I modified the success function as you mentioned, but nothing appeared on the web page.

    Am I doing the correct thing by keeping the page loader in .aspx.cs empty?

    I look forward to your assistance.

    Sami

    Thursday, August 6, 2020 10:43 AM
  • User-1330468790 posted

    Hi samiarja,

     

    Am I doing the correct thing by keeping the page loader in .aspx.cs empty?

    It depends on what you want to do. From my demo, I don't add anything on the method "Page_Load".

    protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            [WebMethod]
            public static string getTrafficSourceData()
            {
                List<trafficSourceData> t = new List<trafficSourceData>
                {
                    new trafficSourceData
                    {
                         T1= 50,
    
                    },
                    new trafficSourceData
                    {
                         T1= 60
                    },
                    new trafficSourceData
                    {
                         T1= 79
                    },
                    new trafficSourceData
                    {
                         T1= 100
                    }
                };
    
                return JsonConvert.SerializeObject(t) ;
    
            }
    
    
            public class trafficSourceData
            {
                public int T1 { set; get; }
            }

    If you simply want to get the data, I think it is correct.

     

    Could you please share more code? That way, I will be able to target the problem much easier.

    You could try my codes to see if it is working. Then we could find out where the problem is in the next step.

     

    Best regards,

    Sean

    Friday, August 7, 2020 10:45 AM
  • User-1313071134 posted

    Thanks Sean.

    Basically I have a SQL table and I aim to read these data and plot it in ASP.NET using Ajax call in javascript.

    I created an ASP.NET web application project on visual studio, and I am trying to get this thing working but unfortunately, until now I wasn't able to crack it and display a chart yet which is driving me crazy.

    Below is the full code for the aspx and the aspx.cs

    My Dashboard.aspx.cs is shown below:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Data;
    using FusionCharts.DataEngine;
    using FusionCharts.Visualization;
    using Newtonsoft.Json;
    using System.Web.Services;
    using System.Web.Script.Services;
    
    
    
    namespace SW53400206
    {
        public partial class WebForm1 : System.Web.UI.Page
        {
    
    protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            [WebMethod]
            public static string getTrafficSourceData()
            {
    
    
                string conn = ConfigurationManager.ConnectionStrings["DataBaseConnectionString"].ConnectionString;
    
                using (SqlConnection cn = new SqlConnection(conn))
                {
                    string myQuery = "SELECT* FROM DADLoggerTable";
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = myQuery;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = cn;
                    cn.Open();
                    List<trafficSourceData> t = new List<trafficSourceData>();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        //int counter = 0;
                        while (dr.Read())
                        {
                            trafficSourceData tsData = new trafficSourceData();
                            tsData.value = dr["T1"].ToString();
                            //tsData.label = dr["traffic_source"].ToString();
                            //tsData.color = arrColor[counter];
                            t.Add(tsData);
                            //counter++;
                        }
                    }
                    return "{\"data\":" + JsonConvert.SerializeObject(t) + "}";
                }
    
            }
      }
    }

    and this is my Dashboard.aspx code:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Dashboard.aspx.cs" Inherits="SW53400206.WebForm1" %>
    <%@ Register assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" namespace="System.Web.UI.DataVisualization.Charting" tagprefix="asp" %>
    
    <!DOCTYPE html>
    
    <head runat="server">
        <title></title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
        <script src="https://unpkg.com/chart.js@2.8.0/dist/Chart.bundle.js"></script>
        <script src="https://unpkg.com/chartjs-gauge@0.2.0/dist/chartjs-gauge.js"></script>
        <script src="https://unpkg.com/chartjs-plugin-datalabels@0.7.0/dist/chartjs-plugin-datalabels.js"></script>
    
        <script>
    
    
            var randomValue = function (data) {
                var result = Math.max.apply(null, data) * Math.random();
                console.log(result);
                return result;
            };
    
    
            var randomData = function () {
                $.ajax({
                    type: "POST",
                    url: "GoogleChartGauge.aspx/getTrafficSourceData",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: OnSuccess_,
                });
                function OnSuccess_(response) {
                    var aData = response.d;
                    // aData: [{"T1":50},{"T1":60},{"T1":79}, {"T1":100}] need and only need 4 values
                    var data = [];
                    $.each(JSON.parse(aData), function (inx, val) {
                        // What to put in here? Answer: construct an array with 4 values to match 4 labels
                        data.push(val.T1);
                    });
    
                    // If we set four areas, 25, 50, 75, 100 and value with 50, then the pointer will be pointing to the middle of the gauge
                    var value = randomValue(data);
    
    
                    
                    var config = {
                        type: 'gauge',
                        data: {
                            labels: ['Success', 'Warning', 'Warning', 'Fail'],
                            datasets: [{
                                data: data,
                                value: value,
                                backgroundColor: ['green', 'yellow', 'orange', 'red'],
                                borderWidth: 2
                            }]
                        },
                        options: {
                            responsive: true,
                            title: {
                                display: true,
                                text: 'Gauge chart with datalabels plugin displaying labels'
                            },
                            layout: {
                                padding: {
                                    bottom: 30
                                }
                            },
                            needle: {
                                // Needle circle radius as the percentage of the chart area width
                                radiusPercentage: 2,
                                // Needle width as the percentage of the chart area width
                                widthPercentage: 3.2,
                                // Needle length as the percentage of the interval between inner radius (0%) and outer radius (100%) of the arc
                                lengthPercentage: 80,
                                // The color of the needle
                                color: 'rgba(0, 0, 0, 1)'
                            },
                            valueLabel: {
                                display: false
                            },
                            plugins: {
                                datalabels: {
                                    display: true,
                                    formatter: function (value, context) {
                                        return context.chart.data.labels[context.dataIndex];
                                    },
                                    color: 'rgba(0, 0, 0, 1.0)',
                                    backgroundColor: null,
                                    font: {
                                        size: 20,
                                        weight: 'bold'
                                    }
                                }
                            }
                        }
                    };
    
    
                    // Get ctx
                    var ctx = document.getElementById('chart').getContext('2d');
                    window.myGauge = new Chart(ctx, config);
    
                }
    
    
    
            };
    
           
            
            // Start the construction of the chart
    
            window.onload = function () {
                randomData();
            };
    
            
    
    
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div id="canvas-holder" style="width: 100%">
                <canvas id="chart"></canvas>
                 <canvas id="canvas"></canvas>
            </div>
        </form>
    </body>

    I want to be able to select the last element in column T1, T2, T3, T4, T5 and display it in the gauge chart using ajax. Hope this helps

    Thanks in advance.

    Friday, August 7, 2020 11:51 AM