Popular graphic Canvas with database list

Question

Popular graphic Canvas with database list

Navigation

#1 by (0 votes)

0

I need to make a dashboard and I'm having a hard time getting a line graph of a graphic canvas.

I need the data and label arrays to be populated with the data in my list but I do not know how to do it.

What do I need to do to achieve 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 is with the Chart.JS version referenced below

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

MyconnectionisinSQL,Imadeamethodtopopulatealistfromaselect...IgettheList<>IjustdonotknowhowtotransferthisdatatoJS

Follow

//methodthatiscalledfromthecontroller

publicvoidteste(List<Pessoa>lista){SqlConnectionconnection=newSqlConnection("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"]) });
        }
    }

// SQL string

    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 the Entity Framework in the project

javascript c# asp.net html5 mvc

asked by anonymous 13.04.2018 / 19:12

1 answer

How to create a truly global Global Scope Error ASP.NET MVC

score 0 · Accepted Answer

Assembling a dashboard chart since the database will need integration and data transformation across layers of your system.

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

Example in the Controller (a private method to get the 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 switch to the view in MVC there are options: model, in a strongly typed view or viewbag and viewdata, this article HERE explains better the differences between both, but for now, I used viewbag for the following reason: to leave the model for the general purpose of the view, and for viewbag to be 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 missing most in your code, deliver that data to the javascript by razor (or aspx).

Example in the 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 be sure to run the script when all dependencies are already present on the page.