How to create chartjs chart with data from database C#

Question

I am trying to create a chart.js graph in my .Net Core Web app, with data from the database. I am using ajax to call a method that will pull the data from the database, but I'm unsure how to group the data to display in the graph.

At the moment I have a database that looks like so:

I am looking to show the time along the bottom and count how many jobs success and how m any jobs exception. At the moment my graph is hard coded.

// Area Chart Example
var ctx = document.getElementById("canvas")
var lineChartData = {
labels: ['January', 'February', 'March', 'April', 'May', 'June', 'July'],
datasets: [{
    label: 'Failed',
    borderColor: "MediumSeaGreen",
    backgroundColor: "MediumSeaGreen",
    fill: false,
    data: [
        30000, 30162, 26263, 18394, 18287, 28682, 31274, 33259, 25849, 
 24159, 32651, 31984, 38451
    ],
    yAxisID: 'y-axis-1',
}, {
    label: 'Exceptioned',
    borderColor: "Tomato",
    backgroundColor: "Tomato",
    fill: false,
    data: [
        20000, 30162, 26263, 33259, 18287, 28682, 25849, 18394, 25849, 
 24159, 32651, 31984, 38451
    ],
    yAxisID: 'y-axis-2'
}]
};

window.myLine = Chart.Line(ctx, {
    data: lineChartData,
    options: {
        responsive: true,
        hoverMode: 'index',
        stacked: false,
        title: {
            display: true,
            text: 'Processes'
        },
        scales: {
            yAxes: [{
                type: 'linear', 
                display: true,
                position: 'left',
                id: 'y-axis-1',
            }, {
                type: 'linear',
                display: true,
                position: 'right',
                id: 'y-axis-2',

                // grid line settings
                gridLines: {
                    drawOnChartArea: false, // only want the grid lines for one axis to show up
                },
            }],
        }
    }
});

@Tseng I havent tried anything as I can't get my head around it. — user11928577
– user11928577, Commented Nov 12, 2019 at 10:56

Rena · Accepted Answer · 2019-11-13 08:44:46Z

Here is a simple demo like below:

1.Model:

public class Job
{
    public int JobId { get; set; }
    public string JobName { get; set; }
    public string JobStatus { get; set; }
    public DateTime JobCompletion { get; set; }
}

2.View:

<canvas id="canvas" width="400" height="400"></canvas>
@section Scripts{
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.bundle.js" integrity="sha256-qSIshlknROr4J8GMHRlW3fGKrPki733tLq+qeMCR05Q=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.bundle.min.js" integrity="sha256-xKeoJ50pzbUGkpQxDYHD7o7hxe0LaOGeguUidbq6vis=" crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.css" integrity="sha256-IvM9nJf/b5l2RoebiFno92E5ONttVyaEEsdemDC6iQA=" crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.js" integrity="sha256-arMsf+3JJK2LoTGqxfnuJPFTU4hAK57MtIPdFpiHXOU=" crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.css" integrity="sha256-aa0xaJgmK/X74WM224KMQeNQC2xYKwlAt08oZqjeF0E=" crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js" integrity="sha256-Uv9BNBucvCPipKQ2NS9wYpJmi8DTOEfTA/nH2aoJALw=" crossorigin="anonymous"></script>
    <script>
        function GetJSON_Simple() {
          var resp = [];
              $.ajax({
                  type: "GET",
                  url: '/Jobs/Index',
                  async: false,
                  contentType: "application/json",
                  success: function (data) {
                      resp.push(data);
                  },
                  error: function (req, status, error) {
                      // do something with error
                      alert("error");
                  }
              });
          return resp;
     }
        var simpleData = GetJSON_Simple();
        var ctx = document.getElementById("canvas")
        var lineChartData = {
            labels: simpleData[0].myDate, 
            datasets: [{
                label: 'Sucess',
                borderColor: "MediumSeaGreen",
                backgroundColor: "MediumSeaGreen",
                fill: false,
                data:   simpleData[0].mySuccess,
                yAxisID: 'y-axis-1',
            }, {
                label: 'Exceptioned',
                borderColor: "Tomato",
                backgroundColor: "Tomato",
                fill: false,
                data: simpleData[0].myException,
                yAxisID: 'y-axis-2'
            }]
        };

        window.myLine = Chart.Line(ctx, {
            data: lineChartData,
            options: {
                responsive: true,
                hoverMode: 'index',
                stacked: false,
                title: {
                    display: true,
                    text: 'Processes'
                },
                scales: {
                    yAxes: [{
                        type: 'linear',
                        display: true,
                        position: 'left',
                        id: 'y-axis-1',
                    }, {
                        type: 'linear',
                        display: true,
                        position: 'right',
                        id: 'y-axis-2',

                        // grid line settings
                        gridLines: {
                            drawOnChartArea: false, // only want the grid lines for one axis to show up
                        },
                    }],
                }
            }
        });

    </script>
}

3.Controller:

public class JobsController : Controller
{
    private readonly YourContext _context;

    public JobsController(YourContext context)
    {
        _context = context;
    }

    // GET: Jobs
    public async Task<ActionResult> Index()
    {
        var date = await _context.Job.Select(j => j.JobCompletion).Distinct().ToListAsync();
        var success =_context.Job
            .Where(j => j.JobStatus == "Success")
            .GroupBy(j => j.JobCompletion)
            .Select(group=>new {
                JobCompletion = group.Key,
                Count=group.Count()
            });
        var countSuccess = success.Select(a => a.Count).ToArray();
        var exception = _context.Job
            .Where(j => j.JobStatus == "Exception")
            .GroupBy(j => j.JobCompletion)
            .Select(group => new {
                JobCompletion = group.Key,
                Count = group.Count()
            });
        var countException = exception.Select(a => a.Count).ToArray();
        return new JsonResult(new { myDate=date,mySuccess= countSuccess, myException= countException });
    }
}

4.Database:

5.Result:

Thank you son much, this has worked for me and it explains it so well :)

Code · Accepted Answer · 2021-03-23 08:57:07Z

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

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

    <script src="jquery-3.3.1.min.js" type="text/javascript"></script>
    <script src="chart.js" type="text/javascript"></script>

</head>
<body>
    <form id="form1" runat="server">
    

    <select id="ddlyear">
    <option>2010</option>
    <option>2011</option>
    <option>2012</option>
    <option>2013</option>
    <option>2014</option>
    <option>2015</option>
</select>

<select id="ddlMonth">
    <option value="1">Jan</option>
    <option value="2">Feb</option>
    <option value="3">Mar</option>.......... ...........</select>
<button id="btnGeneratePieChart">Show</button>
<br/>

<script type="text/javascript">

    $(document).ready(function () {

        $("btnGeneratePieChart").on('click', function (e) {
            e.preventDefault();
            var gData = [];
            gData[0] = $("#ddlyear").val();
            gData[1] = $("#ddlMonth").val();

            var jsonData = JSON.stringify({
                gData: gData
            });
            $.ajax({
                type: "POST",
                url: "WebService.asmx/getTrafficSourceData",
                data: jsonData,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess_,
                error: OnErrorCall_
            });

            function OnSuccess_(response) {
                var aData = response.d;
                var arr = [];
                $.each(aData, function (inx, val) {
                    var obj = {};
                    obj.color = val.color;
                    obj.value = val.value;
                    obj.label = val.label;
                    arr.push(obj);
                });
                var ctx = $("#myChart").get(0).getContext("2d");
                var myPieChart = new Chart(ctx).Pie(arr);
            }

            function OnErrorCall_(response) { }
            e.preventDefault();
        });
    });

</script>


<canvas id="myChart" width="200" height="200"></canvas>


    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Web.Services;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    public class trafficSourceData
    {
        public string label { get; set; }
        public string value { get; set; }
        public string color { get; set; }
        public string hightlight { get; set; }
    }

    [WebMethod]

    public List<trafficSourceData> getTrafficSourceData(List<string> gData)
    {
        List<trafficSourceData> t = new List<trafficSourceData>();
        string[] arrColor = new string[] { "#231F20", "#FFC200", "#F44937", "#16F27E", "#FC9775", "#5A69A6" };

        string conn = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

        using (SqlConnection cn = new SqlConnection(conn))
        {
            string myQuery = "select * from traffic_data where YEAR =@year and MONTH=@month";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = myQuery;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@year", gData[0]);
            cmd.Parameters.AddWithValue("@month", gData[1]);
            cmd.Connection = cn;
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                int counter = 0;
                while (dr.Read())
                {
                    trafficSourceData tsData = new trafficSourceData();
                    tsData.value = dr["visit_count"].ToString();
                    tsData.label = dr["traffic_source"].ToString();
                    tsData.color = arrColor[counter];
                    t.Add(tsData);
                    counter++;
                }
            }
        }
        return t;
    }

}

Ossevet · Accepted Answer · 2023-09-23 23:29:53Z

For webforms here is a simple example that generates a line graph with a red line and a green line:

Backend (i use static data but you can get it from a database here):

    public string _xValues = "";
    public string _greenData = "";
    public string _redData = "";        

    protected void Page_Load(object sender, EventArgs e)
    {
        _xValues = "100, 200, 300, 400, 500, 600, 700, 800, 900, 1000";
        _greenData = "1600, 1700, 1700, 1900, 1600, 1600, 1600, 1600, 1600, 1700";
        _redData = "860, 1140, 1060, 1060, 1070, 1110, 1330, 2210, 7830, 2478";            
    }

html:

    <canvas id="myChart" style="width:100%;max-width:700px"></canvas>

javascript:

    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js"></script>
 <script>

    const xValues = [<%=_xValues%>];

    new Chart("myChart", {
        type: "line",
        data: {
            labels: xValues,
            datasets: [{
                data: [<%=_redData%>],
                borderColor: "red",
                fill: false
            }, {
                data: [<%=_greenData%>],
                borderColor: "green",
                fill: false
            }]
        },
        options: {
            legend: { display: false }
        }
    });



</script>

Collectives™ on Stack Overflow

How to create chartjs chart with data from database C#

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related