如何在Razor页面中创建一个从数据库中拉取数据的图表？ [英] How to create a charts in Razor Pages pulling data from the database?

问题描述

如何创建从数据库中提取的图表？我试过MorrisJS、ChartJS、Canvas JS、HighChats。但我认为我最接近的是ChartJS，所以希望我们能坚持下去。

我需要创建一个从数据库中提取数据的图表，并使用数据库中的多个表，例如，数据库名称&Finance&；，我需要一个条形图来比较dbo.Revenue中的金额和dbo.Expense中的金额总和。月份是x轴。Something like this ，但我通过在代码中添加值来手动完成此操作。

我尝试从数据库中拉出的内容是表名(收入)

public async Task<JsonResult> OnGetRevenueData()
        {
            ar21 =  await _context.ar21.ToListAsync();
            var arChart = new arChartModel();
            arChart.AmountList = new List<decimal?>();
            arChart.CategoryList = new List<string>();

            foreach (var r in ar21)
            {
                arChart.AmountList.Add(r.Amount);
                arChart.MonthList.Add(r.Month);
            }

            return new JsonResult(arChart);
        }

推荐答案

以下是您可以遵循的工作演示：

型号：

public class Revenue
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public string Month { get; set; }
}
public class Expense
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public string Month { get; set; }
}

页面：

@page
@model IndexModel

<canvas id="Chart1" width="500" height="300"></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>
        $(document).ready(function () {
            $.ajax({
                type: "Get",
                url: "/?handler=RevenueData",   
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    console.log(response.revenue);
                    console.log(response.revenue.length);
                    var data = response.revenue;
                    var data2 = response.expense;
                    var labels = response.month;
                    var color1 = [];
                    var color2 = [];
                    for (var i = 0; i < response.month.length; i++)
                    {
                        color1.push("rgba(255, 99, 132, 0.2)");
                        color2.push("rgba(54, 162, 235, 0.2)");
                    }
  
                    var ctx = document.getElementById("Chart1");
                    var myChart = new Chart(ctx, {
                        type: 'bar',
                        data: {
                            labels: labels,
                            datasets: [{
                                label: 'Revenue',
                                data: data,
                                backgroundColor: color1,
                            },
                            {
                                label: 'Expense',
                                data: data2,
                                backgroundColor: color2
                            }]
                        },
                        options: {
                            responsive: false,
                            scales: {
                                xAxes: [{
                                    ticks: {
                                        maxRotation: 90,
                                        minRotation: 80
                                    },
                                    gridLines: {
                                        offsetGridLines: true 
                                    }
                                },
                                {
                                    position: "top",
                                    ticks: {
                                        maxRotation: 90,
                                        minRotation: 80
                                    },
                                    gridLines: {
                                        offsetGridLines: true 
                                    }
                                }],
                                yAxes: [{
                                    ticks: {
                                        beginAtZero: true
                                    }
                                }]
                            }
                        }
                    });
                  
                },
                error: function (response) {
                    alert(response.responseText);
                }
            });
        });
    </script>
}

处理程序：

public class IndexModel : PageModel
{
    private readonly YourDbContext _context;

    public IndexModel(YourDbContext context)
    {
        _context = context;
    }
    public void OnGet()
    {

    }
    public JsonResult OnGetRevenueData()
    {
        var countRevenue = (from t in _context.Revenue
                           group t by new
                           {
                               Month = t.Month
                           } into g
                           select  g.Sum(a=>a.Amount)
                           ).ToArray();
        var countExpense = (from t in _context.Expense
                            group t by new
                            {
                                Month = t.Month
                            } into g
                            select g.Sum(a => a.Amount)
                           ).ToArray();
        var monthList = _context.Revenue.Select(a => a.Month).Distinct().ToArray();
        return new JsonResult(new { revenue = countRevenue, expense = countExpense, month = monthList });
    }
}

这篇关于如何在Razor页面中创建一个从数据库中拉取数据的图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！