Retrieve data from database (SQL) and display in graph using MVC?

Question

What I am wanting to do is take data from two rows as x & y axis and that data should be displayed in a bar chart.

Here is what I am doing:

<div class="wrapper wrapper-content animated fadeInRight">
    <div class="row">
        <div class="col-lg-6">
            <div class="ibox float-e-margins">
                <div class="ibox-title">
                    <h5>Bar Chart Example <small>With custom colors.</small></h5>
                    <div class="ibox-tools">
                        <a class="collapse-link">
                            <i class="fa fa-chevron-up"></i>
                        </a>
                        <a class="dropdown-toggle" data-toggle="dropdown" href="#">
                            <i class="fa fa-wrench"></i>
                        </a>
                        <ul class="dropdown-menu dropdown-user">
                            <li>
                                <a href="#">Config option 1</a>
                            </li>
                            <li>
                                <a href="#">Config option 2</a>
                            </li>
                        </ul>
                        <a class="close-link">
                            <i class="fa fa-times"></i>
                        </a>
                    </div>
                </div>
                <div class="ibox-content">
                    <div class="flot-chart">
                        <div class="flot-chart-content" id="flot-bar-chart"></div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

@section Scripts {
    @Scripts.Render("~/plugins/flot")

    <script type="text/javascript">
        $(document).ready(function () {
            var barOptions = {
                series: {
                    bars: {
                        show: true,
                        barWidth: 0.6,
                        fill: true,
                        fillColor: {
                            colors: [{
                                opacity: 0.8
                            }, {
                                opacity: 0.8
                            }]
                        }
                    }
                },
                xaxis: {
                    tickDecimals: 0
                },
                colors: ["#1ab394"],
                grid: {
                    color: "#999999",
                    hoverable: true,
                    clickable: true,
                    tickColor: "#D4D4D4",
                    borderWidth: 0
                },
                legend: {
                    show: false
                },
                tooltip: true,
                tooltipOpts: {
                    content: "x: %x, y: %y"
                }
            };
            var barData = {
                label: "bar",
                data: [          **//here i want to set a query to get data from database**
                        [1, 40],
                        [2, 25],
                        [3, 19],
                        [4, 34],
                        [5, 32],
                        [6, 22]
                    ]
                };
            $.plot($("#flot-bar-chart"), [barData], barOptions);
            var barOptions = {
                series: {
                    lines: {
                        show: true,
                        lineWidth: 2,
                        fill: true,
                        fillColor: {
                            colors: [{
                                opacity: 0.0
                            }, {
                                opacity: 0.0
                            }]
                        }
                    }
                },
                xaxis: {
                    tickDecimals: 0
                },
                colors: ["#1ab394"],
                grid: {
                    color: "#999999",
                    hoverable: true,
                    clickable: true,
                    tickColor: "#D4D4D4",
                    borderWidth: 0
                },
                legend: {
                    show: false
                },
                tooltip: true,
                tooltipOpts: {
                    content: "x: %x, y: %y"
                }
            };
            var barData = {
                label: "bar",
               data: [          **//here i want to set a query to get data from database**
                        [1, 40],
                        [2, 25],
                        [3, 19],
                        [4, 34],
                        [5, 32],
                        [6, 22]
                    ]
            };

            doPlot("right");

            $("button").click(function () {
                doPlot($(this).text());
            });
        });

    </script>
}

How to achieve it? Can I set a query here or should I manage this in controller? like this

E.g If I have a table in SQL named Sale and having two records "Sales & Year", I want to show years at x-axis and sales at y-axis.

Varooneh · Answer 1 · 2020-04-04T15:45:56.277

Maybe help you this way:

First separate code in functions:

/**
 * Get data from server
 */
const getChartData = function(data) {
  return new Promise((resolve, reject) => {
    try {
      $.ajax({
        data: data,        
        method: 'GET', // or POST
        url: '[Controller/Action/????]'
      }).done(data => {
        resolve({state: 'done', data: data});
      }).fail(errorThrown => {
        reject({state: 'fail', data: errorThrown});
      });
    }
    catch(error) {
      reject({state: 'catch', data: error});
    }
  });
};

/**
 * Draw chart
 */
const drawChart = function(chartData) {
  var barOptions = {
                series: {
                    bars: {
                        show: true,
                        barWidth: 0.6,
                        fill: true,
                        fillColor: {
                            colors: [{
                                opacity: 0.8
                            }, {
                                opacity: 0.8
                            }]
                        }
                    }
                },
                xaxis: {
                    tickDecimals: 0
                },
                colors: ["#1ab394"],
                grid: {
                    color: "#999999",
                    hoverable: true,
                    clickable: true,
                    tickColor: "#D4D4D4",
                    borderWidth: 0
                },
                legend: {
                    show: false
                },
                tooltip: true,
                tooltipOpts: {
                    content: "x: %x, y: %y"
                }
            };
            var barData = {
                label: "bar",
                data: chartData // Chart data input parameter to function 
                };
            $.plot($("#flot-bar-chart"), [barData], barOptions);
            var barOptions = {
                series: {
                    lines: {
                        show: true,
                        lineWidth: 2,
                        fill: true,
                        fillColor: {
                            colors: [{
                                opacity: 0.0
                            }, {
                                opacity: 0.0
                            }]
                        }
                    }
                },
                xaxis: {
                    tickDecimals: 0
                },
                colors: ["#1ab394"],
                grid: {
                    color: "#999999",
                    hoverable: true,
                    clickable: true,
                    tickColor: "#D4D4D4",
                    borderWidth: 0
                },
                legend: {
                    show: false
                },
                tooltip: true,
                tooltipOpts: {
                    content: "x: %x, y: %y"
                }
            };
            var barData = {
                label: "bar",
               data: chartData // Chart data input parameter to function                     
            };
  doPlot("right");
}

And next in button click can use:

$('button').on('click', function(e) {
  getChartData(...).then(result => { // ... means parameters need to send to server
    drawChart(result['data']); 
    doPlot($(this).text()); //??
  }).catch(error => { 
    console.log(error); 
  });
});

=========================================================================== Update [based on comments]

public class BaseWorkerModel
{
  public string ChartName { get; set; }
  public List<WorkerModel> WorkerData { get; set; }
}

public class WorkerModel 
{ 
  public int ID { get; set; } 
  public string Name { get; set; } 
  public string Position { get; set; } 
  public string Office { get; set; } 
  public string Extn { get; set; } 
  public string Salary { get; set; } 
}

In action you need create list from data

public ActionResult GetChartData()
{
  // with this code you can retrieve list of WorkerModel based on your data
  // note: if salary is float or double change data type to double
  var wokerData = db.Workers.Select(s => new WorkerModel { Name = s.Name, Salary = s.Salary }).Tolist();

  var baseWorkerModel = new BaseWorkerModel()
  {
    ChartName = "Anything",
    WorkerData = wokerData
  };

  return View(baseWorkerModel);
}

Now in first line of view add

@model BaseWorkerModel

If js code inline in your view, You can access to model data see this link Access model in js

=========================================================================== NEW UPDATE

1- I used chart.js library to draw any chart type. 2- In Models folder I created model with this structure

public class ChartDataModel
{
  public string ChartName { get; set; }

  public List<string> ChartLabel { get; set; }

  public List<int> ChartData { get; set; }
}

3- In our controller I initialized instance of model and set data and labels and pass model to view

public ActionResult Index()
{
  var chartDataModel = new Models.ChartDataModel()
  {
    ChartData = new List<int>() { 12, 19, 3, 5, 2, 3 },
    ChartLabel = new List<string>() { "Red", "Blue", "Yellow", "Green", "Purple", "Orange" },
    ChartName = "Chart JS demo"
};

  return View(chartDataModel);
}

4- In index.cshtml first import model and in javascript code section I used this model properties

@model ChartDemo1.Models.ChartDataModel
<!-- canvas for draw chart -->
<canvas id="myChart" width="400" height="100"></canvas>

@section scripts {
    <script type="text/javascript">
        var ctx = document.getElementById('myChart').getContext('2d');
        var myChart = new Chart(ctx, {
            type: 'bar',
            data: {
                labels: @Html.Raw(Json.Encode(Model.ChartLabel)),
                datasets: [{
                    label: "@Model.ChartName",
                    data: @Html.Raw(Json.Encode(Model.ChartData)),
                    backgroundColor: [
                        'rgba(255, 99, 132, 0.2)',
                        'rgba(54, 162, 235, 0.2)',
                        'rgba(255, 206, 86, 0.2)',
                        'rgba(75, 192, 192, 0.2)',
                        'rgba(153, 102, 255, 0.2)',
                        'rgba(255, 159, 64, 0.2)'
                    ],
                    borderColor: [
                        'rgba(255, 99, 132, 1)',
                        'rgba(54, 162, 235, 1)',
                        'rgba(255, 206, 86, 1)',
                        'rgba(75, 192, 192, 1)',
                        'rgba(153, 102, 255, 1)',
                        'rgba(255, 159, 64, 1)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                scales: {
                    yAxes: [{
                        ticks: {
                            beginAtZero: true
                        }
                    }]
                }
            }
        });
    </script>
}

Now, you've a bar chart drawed by Chart.js, I used static data but you can fill chart from any data source.

Note: remember add chart.min.js and chart.min.css in your main layout.

Note: you can pass chart config as model like backgrounds or etc...

but i want to get data from database and then display it in char shape using char.js. — Abdul Qayyum, Mar 31 '20 at 07:49
@AbdulQayyum Do you have model assigned to view? With getData function you can get data from controller, In your controller you can create connection and query to database and result data as json format, Except this, If your script in [viewname].cshtml you can use model or use TempData or ViewBag and in js code call this viewbag or tempdata ``` data: @ViewBag.ChartData OR data: @TempData["ChartData"] or data: @Model.ChartData ``` — Varooneh, Mar 31 '20 at 07:53
i just added this in controller `public ActionResult Index() `{` `ArrayList xvalues = new ArrayList();` `ArrayList yvalues = new ArrayList();` `var results = (from c in db.Workers select c);` `results.ToList().ForEach(rs => xvalues.Add(rs.Name));` `results.ToList().ForEach(rs => yvalues.Add(rs.Salary));` `return View();` }` — Abdul Qayyum, Mar 31 '20 at 08:09
For access to data from view, You need pass data with model or view bag or temp data or access to data from ajax request. You create data but not passed to view. Create model [C# class] based on data structure and pass to view then you can access chart data from model in view body or inline javascript codes. — Varooneh, Mar 31 '20 at 08:49
public class Worker { public int ID { get; set; } public string Name { get; set; } public string Position { get; set; } public string Office { get; set; } public string Extn { get; set; } public string Salary { get; set; } } Here is my model class, i am willing to get only Name as x-axis and Salary as y-axis, so can you please help me what kind of query should i use to get the desired result? please — Abdul Qayyum, Apr 01 '20 at 05:02
var wokerData = db.workerModels.Select(s => new WorkerModel { Name = s.Name, Salary = s.Salary }).ToList(); this line gives the "An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information: The entity or complex type 'ChartJS.Models.WorkerModel' cannot be constructed in a LINQ to Entities query." — Abdul Qayyum, Apr 01 '20 at 15:58
@AbdulQayyum (I can not put comment in last post) everythings is ok, I checked code, can you share view? Check data types, check wokerData variable not null. — Varooneh, Apr 02 '20 at 07:17
yeah everything i have checked.. can we have a session through TW? please? i shall be highly grateful to you, or give me the link of this example if you posted anywhere. — Abdul Qayyum, Apr 04 '20 at 06:53
@AbdulQayyum Which lib you used for chart? chartjs? or other? — Varooneh, Apr 04 '20 at 15:14
i am using flot chart lib, i just want to set a query and pass it to data. — Abdul Qayyum, Apr 07 '20 at 14:57
i am using this `public ActionResult Index( ) { var worker = db.Workers.ToList(); ViewBag.Data = from d in worker select d.Salary; ViewBag.ObjectName = from n in worker select n.Name; return View(worker);` and this is how i am passing it: `var barData = { label: [@Html.Raw(ViewBag.ObjectName)], data: [@ViewBag.Data]};` — Abdul Qayyum, Apr 07 '20 at 16:00
@AbdulQayyum Ok, I hopefully this way help you and fix your problem. You need multiple child array [string, number] format with parent array. Use this way: In your action body ```var chartData = new List(); foreach(var item in worker) { chartData.Add(new dynamic[] { item.Name, item.Salary }); } ViewBag.ChartData = chartData;``` Chart data variable output sample: ```[["1",10],["1",10],["1",10]]``` ```@Html.Raw(Json.Encode(ViewBag.ChartData)``` I think this way can help you. — Varooneh, Apr 07 '20 at 17:37
Dear @Varooneh, I just used your what is been told, please have a look at updated answer, it helped me in a way that it starts displaying label. — Abdul Qayyum, Apr 10 '20 at 09:40

score 0 · Answer 2 · answered Apr 01 '20 at 16:45

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information:

var wokerData = db.Workers.Select(s => new WorkerModel { Name = s.Name, Salary = s.Salary }).ToList();


            var baseWorkerModel = new BaseWorkerModel()
            {
                ChartName = "Anything",
                ChartData = wokerData
            };

            return View(baseWorkerModel);

}

public class BaseWorkerModel
    {
        public string ChartName { get; set; }
        public List<WorkerModel> ChartData { get; set; }
        public List<WorkerModel> WorkerData { get; set; }
    }

score 0 · Answer 3 · edited Apr 10 '20 at 21:11

I used chart.js this time as mentioned in your last answer, here is the example:

public ActionResult Index( )
{
  var worker = db.Workers.ToList<Worker>();
  //var chartData = new List<dynamic[]>();
  //var chartLabel = new List<dynamic[]>();
  //foreach (var item in worker) 
  //{
    //chartData.Add(new dynamic[] {item.Salary});
    //chartLabel.Add(new dynamic[] {item.Name});
  //}
  var chartData = worker.Select(s => s.Salary).ToList();
  var chartLabel = worker.Select(s => s.Name).ToList();
  ViewBag.ChartData = chartData;
  ViewBag.ChartLabel = chartLabel;
  return View();
}

    <script type="text/javascript">
      $(document).ready(function () {
          var barData = {
              labels: @Html.Raw(Json.Encode(ViewBag.ChartLabel)),
              datasets: [
                  {
                      label: "Bar Chart Demo",
                      backgroundColor: 'rgba(26,179,148,0.5)',
                      borderColor: "rgba(26,179,148,0.7)",
                      pointBackgroundColor: "rgba(26,179,148,1)",
                      pointBorderColor: "#fff",
                      data: @Html.Raw(Json.Encode(ViewBag.ChartData)),
                  }
              ]
          };

          var barOptions = {
              responsive: true
          };

          var ctx2 = document.getElementById("barChart").getContext("2d");
          new Chart(ctx2, { type: 'bar', data: barData, options: barOptions });
      });
    ...

Here is the output, as it is displaying the labels from database but no chart data "Salary".

First if you checked my chart.js sample, you can see I used model and get data as base type, not dynamic type. Next, you told me used another chart library. For chart.js just check my ***New Update on my answer*** . Also I edited your post and fix bugs, hopefully help you. — Varooneh, Apr 10 '20 at 14:34

Retrieve data from database (SQL) and display in graph using MVC?

3 Answers3