Popular Canvas Chart with Database List

Question

I need to make a Dashboard and I’m having a hard time to popular a line chart of a canvas chart.

I need date and label arrays to be filled with data from my list but don’t know how to do it

What I need to do to get this

<script>

    var ctx = document.getElementsByClassName("line-chart");

    var teste = new Chart(ctx, {
        type: 'line',
        data: {
            labels: ["Jan", "Fev", "Mar", "Abr", "Mai", "Jun", "Jul", "Ago", "Set", "Out", "Nov", "Dez"],
            datasets: [{
                label: "taxa de cliques 2017",
                data: [5, 10, 15, 12, 20, 30, 8, 7, 2, 3, 6, 1],
                borderWidth: 6,
                borderColor: 'rgba(77,166,253,0.85)',
                backgroundColor: 'transparent',
            },
            {
                label: "taxa de cliques 2017",
                data: [8, 11, 13, 2, 80, 40, 84, 71, 22, 43, 46, 11],
                borderWidth: 6,
                borderColor: 'rgba(6,204,6,0.85)',
                backgroundColor: 'transparent',
            }]
        },
        options: {
            title: {
                display: true,
                fontSize: 20,
                text: "Relatorio de CTR anual"
            },
            labels: {
                fontStyle: "bold",

            }
        }
    })


</script>

My project has the Chart.JS version referenced below

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

My connection is in SQL, I made a method to fill a list from a select... I can popular the List<> just do not know how to transfer this data to the JS

Follows

// method that is called from the controller

    public void teste(List<Pessoa> lista)
    {
        SqlConnection connection = new SqlConnection("DBConecction");
        connection.Open();
        SqlCommand command = new SqlCommand(SQL(), connection);
        var datatable = new DataTable();
        SqlDataAdapter DA = new SqlDataAdapter();

        DA.SelectCommand = command;

        DA.Fill(datatable);

        foreach (DataRow row in datatable.Rows)
        {
            lista.Add(new Pessoa { dia = Convert.ToInt32(row["dia"]), peso = Convert.ToDouble(row["peso"]) });
        }
    }

// string SQL

    public string SQL()
    {
        string sSQL = "select day(data_entrega) 'dia'  " + 
                            ",sum(peso_roadnet) 'peso' " +
                        "from ConsultaFreteRot2 " +
                        "where DATA_ENTREGA between '2018-03-01' " +
                               " and '2018-03-28' " +
                        "group by day(DATA_ENTREGA) " +
                        "order by dia";
        return sSQL;
    }

NOTE: I am not using Entity Framework in the project

Answer 1

Mounting a Dashboard chart from the database will need integration and data transformations across your system layers.

Starting from your example and considering that you are using Asp.net mvc by the question tag, it will be necessary for your controller to pass the ready data to the view. An example would be a List with an object containing 'label' and 'date' as variable.

Example in Controller (a private method for retrieving data):

private List<GraficoDiaPeso> teste()
{
    var lista = List<GraficoDiaPeso>();

    SqlConnection connection = new SqlConnection("DBConecction");
    connection.Open();
    SqlCommand command = new SqlCommand(SQL(), connection);
    var datatable = new DataTable();
    SqlDataAdapter DA = new SqlDataAdapter();

    DA.SelectCommand = command;

    DA.Fill(datatable);

    foreach (DataRow row in datatable.Rows)
    {
        lista.Add(new GraficoDiaPeso { label = row["dia"].ToString(), data = row["peso"].ToString() });
    }

    return lista;    
}

To move to the MVC view there are options: model, in a strongly typed view or viewbag and viewdata, this article HERE explains the differences between the two, but for now, I used viewbag for the following reasons: leave the model for the general purpose of the view, and because viewbag is of the Dynamic type which makes the view flexible.

Example in Controller (call to private method and pass data through viewbag):

public ActionResult Index()
{
    ViewBag.Grafico_Pessoa_Peso = teste();
    return View();
}

Now comes what was most missing in your code, deliver to javascript this data by Razor (or aspx).

Example in view (with Razor):

@{
    var pesoDia = ViewBag.Grafico_Pessoa_Peso as List<pesoDia>;

    var Label = string.Join("\",\"", pesoDia.Select(n => n.Label));
    Label = "\"" + Label + "\"";

    var Value = string.Join(",", pesoDia.Select(n => n.Value));
}

<script>
var ctx = document.getElementById("line-chart");

var teste = new Chart(ctx, {
    type: 'line',
    data: {
        labels: [@Html.Raw(Label)],
        datasets: [{
            label: "taxa de cliques 2017",
            data: [@Value],
            borderWidth: 6,
            borderColor: 'rgba(77,166,253,0.85)',
            backgroundColor: 'transparent',
        },
        {
            label: "taxa de cliques 2017",
            data: [@Value],
            borderWidth: 6,
            borderColor: 'rgba(6,204,6,0.85)',
            backgroundColor: 'transparent',
        }]  
    },
    options: {
        title: {
            display: true,
            fontSize: 20,
            text: "Relatorio de CTR anual"
        },
        labels: {
            fontStyle: "bold",

        }
    }
    })
</script>

Thanks to the MVC framework your script has been rendered by the server and is ready to run in the browser, just make sure to run the script when all dependencies are already present on the page.