sql-server - 使用 Google Charts asp.net 从数据库中获取两个日期之间的数据

Question

我想使用两个日期从数据库中获取数据并显示在图表中。

我使用了图表的链接：https ://www.aspsnippets.com/Articles/Google-Charts-in-ASPNet-MVC-Google-Pie-Doughnut-Chart-example-with-database-in-ASPNet-MVC .aspx

我已使用以下视频作为日期并从数据库中获取数据：https ://youtu.be/Rm4uiny5Ano

     **CONTROLLER:** 

     public ActionResult Index()
    {
        mymodel db = new mymodel();
        db.slips = AjaxMethod();
        return View(db);
    }


        [HttpPost]
    public JsonResult AjaxMethod(DateTime? start, DateTime? end)
    {
        string query = "SELECT [status], sum(total_amount) as Payment";
        query += " FROM slips WHERE convert(varchar,date_paid, 101) BETWEEN '" + start + "' AND '" + end + "' and status='Paid' and inv_type='Valid' GROUP BY [status]";

        string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
        List<object> chartData = new List<object>();
        chartData.Add(new object[]
                        {
                        "status", "Payment"
                        });
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        chartData.Add(new object[]
                        {
                        sdr["status"], sdr["Payment"]
                        });
                    }
                }

                con.Close();
            }
        }

        return Json(chartData);
    }


     private DbSet<slip> AjaxMethod()
    {
        throw new NotImplementedException();

    }

它应该使用图表显示来自数据库的数据。但是它会在“AjaxMethod()”方法上抛出一个错误。我不知道我在“索引”上的代码是否正确。

图表数据

标签： sql-serverasp.net-mvcchartsgoogle-visualization

Answer 1

还有更多内容，但仅针对您的问题和评论，您可以创建一个Payment类，然后创建一个列表并返回该数据。这应该都在正确的文件（每个类）中才能正确执行。

你如何使用这个列表payments我将留给你，但你也许可以从这个开始。例如，您的模型可能包含列标题的文本。

using PaymentRepository;

public ActionResult Index()
{
    PaymentModel db = new PaymentModel();
    // the model might also do this directly
    var chartData = PaymentRepository.GetPaidPaymentList();
    db.slips = chartData;
    return View(db);
}


[HttpPost]
public JsonResult GetPaymentList(DateTime start, DateTime end)
{
     var chartData = PaymentRepository.GetPaymentByStatusList(start,end);
     return Json(chartData); 
}

// put in a class with using Payment
public class PaymentRepository
{
    public static List<Payment> GetPaidPaymentList()
    {
        DateTime startDate = DateTime.MinValue;
        DateTime endDate = DateTime.Now;
        string status = "Paid";
        return GetPaymentByStatusList(startDate, endDate, status = );
    }

    public static List<Payment> GetPaymentByStatusList(DateTime startDate, DateTime endDate, string status = "Paid")
    {
        List<Payment> payments = new List<Payment>();
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Constring"].ConnectionString))
        {
            var sqlQuery = @"
            SELECT [status], SUM(total_amount) as Payment
            FROM slips 
            WHERE WHERE date_paid >= @startDate AND date_paid < @endDate 
                AND status = @status' 
                AND inv_type = 'Valid' 
            GROUP BY [status];
            ";
            connection.Open();
            using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
            {
                cmd.Parameters.Add("@startDate", System.Data.SqlDbType.DateTime);
                cmd.Parameters["@startDate"].Value = startDate;
                cmd.Parameters.Add("@endDate", System.Data.SqlDbType.DateTime);
                cmd.Parameters["@endDate"].Value = endDate;
                cmd.Parameters.Add("@status", System.Data.SqlDbType.VarChar);
                cmd.Parameters["@status"].Value = status;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var payment = new Payment()
                        {status = (string)reader["status"], payment = (decimal)reader["Payment"]};
                        payments.Add(payment);
                    }
                }
            }

            connection.Close();
        }

        return payments;
    }
}

public class Payment
{
    public string status
    {
        get;
        set;
    }

    public decimal payment
    {
        get;
        set;
    }
}