多折线图

一尘不染

多折线图

sql

我一直在尝试制作从数据库中提取数据的多线图。
为了做到这一点，我编写了以下代码来填充
图表：

    SqlConnection con1 = new SqlConnection();
    con1.ConnectionString = ConfigurationManager.ConnectionStrings["Removed"].ToString();
    SqlCommand cmd1 = new SqlCommand("SELECT YEAR(Start_Date) AS Year, Month(Start_Date) As Month, Cost, Utility_Type FROM import_Utilities WHERE YEAR(Start_Date) = 2011  AND Utility_Type = 'Water'");
    cmd1.Connection = con1;
    con1.Open();

    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    DataTable dt1 = new DataTable();
    da1.Fill(dt1);
    Chart1.DataSource = dt1;

    Chart1.Series["Series2"].XValueMember = "Month";
    Chart1.Series["Series2"].YValueMembers = "Cost";
    Chart1.Legends.Add(new Legend("Default") { Docking = Docking.Right });


    Chart1.DataBind();
    con1.Close();

    SqlConnection con = new SqlConnection();
    con.ConnectionString = ConfigurationManager.ConnectionStrings["Removed"].ToString();
    con.Open();
    SqlCommand cmd = new SqlCommand("SELECT YEAR(Start_Date) AS Year, Month(Start_Date) As Month, Cost, Utility_Type FROM import_Utilities WHERE YEAR(Start_Date) = 2012 AND Utility_Type = 'Water'");
    cmd.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    Chart1.DataSource = dt;
    Chart1.Series["2010"].XValueMember = "Month";
    Chart1.Series["2010"].YValueMembers = "Cost";

    Chart1.DataBind();
    con.Close();

该代码有效，除了它具有两个数据源之外，因此仅底部sqlconnection被填充到图表中。我不知道如何解决此问题，我想他们是这样做的更好方法，但我很茫然。

更新：

在下面的例子中，我最终使用数据透视表sql创建了一个表。数据透视表然后我将代码更新为以下内容：

 SqlCommand cmd = new SqlCommand("SELECT * FROM ( SELECT YEAR(Start_Date), CASE MONTH(Start_Date) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September'WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END as [Month], [Cost] FROM [HousingAccountingReports].[dbo].[import_Utilities] Where [Building] = 'Building B' AND [Utility_Type] = 'Electric') TableDate PIVOT (SUM([Cost]) FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December] )) PivotTable");
    cmd.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    Chart3.DataSource = dt;
    Chart3.Series["Series1"].XValueMember = "Month";
    Chart3.Series["Series1"].YValueMembers = "2010";
    Chart3.Series["Series2"].XValueMember = "Month";
    Chart3.Series["Series2"].YValueMembers = "2011";
    Chart3.Series["Series3"].XValueMember = "Month";
    Chart3.Series["Series3"].YValueMembers = "2012";
    Chart3.DataBind();`

当我运行页面时，错误提示找不到“月份”列。如果我添加：dt.Columns.Add（“ Month”）; dt.Columns.Add（“ Year”）;`找不到2010，如何
正确驾驶我的图表？他们是我的数据透视sql的问题吗？

阅读 153

2021-05-16

共1个答案

一尘不染

您不能更改图表数据源，而无需像上面代码中那样重置数据。

就像昨天的问题一样，我建议您先创建一个数据表，然后再在数据表中引用特定系列的列

从昨天的回答

因此，作为清理代码的建议，我将执行以下操作

获取2个数据表以“月”为主键合并数据表将每一列作为新系列添加到图表中

so drop

Chart1.DataSource = dt
Chart1.Series["2010"].XValueMember = "Month";
Chart1.Series["2010"].YValueMembers = "Cost";

and replace it with

For Each row As DataRow In dt1.Rows
     Chart1.Series("Series2").Points.AddXY(row.Item("Month"), row.Item("Cost"))
Next

For Each row As DataRow In dt.Rows
     Chart1.Series("2010").Points.AddXY(row.Item("Month"), row.Item("Cost"))
Next

如果您设法将所有数据归为一个Datatable并创建了包含两个系列的图表，则可以执行以下操作

    Dim dt As New DataTable
    dt.Columns.Add("Month", GetType(String))
    dt.Columns.Add("2010", GetType(Integer))
    dt.Columns.Add("2011", GetType(Integer))

    dt.Rows.Add("January", 15, 25)
    dt.Rows.Add("February", 18, 32)
    dt.Rows.Add("March", 12, 34)
    dt.Rows.Add("April", 12, 34)

    Chart1.DataSource = dt
    Chart1.Series(0).XValueMember = "Month"
    Chart1.Series(0).YValueMembers = "2010"

    Chart1.Series(1).XValueMember = "Month"
    Chart1.Series(1).YValueMembers = "2011"

    Chart1.DataBind()

Based on your code this should work to my understanding:

SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["removed"].ToString();
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT YEAR(Start_Date) AS Year, Month(Start_Date) As Month, Cost, Utility_Type, Building FROM import_Utilities WHERE YEAR(Start_Date) = 2012 AND YEAR(Start_Date) = 2011 AND Utility_Type = 'Water'");

        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

    Chart1.DataSource = dt;
    Chart1.Series(0).XValueMember = "Month";
    Chart1.Series(0).YValueMembers = "2010";

    Chart1.Series(1).XValueMember = "Month";
    Chart1.Series(1).YValueMembers = "2011"

    Chart1.DataBind()

数据表dt中应包含所有数据（因为我在两年中都在作画）（我如何将其绑定到表以便可以进行验证）

目前，它的抱怨系列不能用作一种方法。

我是一个非常新的程序员，只能在业余时间这样做，所以请原谅我的简单错误。

我是否需要像这里一样设置数据表：Chart1.DataSource = dt;Chart1.Series（0）.XValueMember =“月”；Chart1.Series（0）.YValueMembers =“ 2010”；

    Chart1.Series(1).XValueMember = "Month";
    Chart1.Series(1).YValueMembers = "2011"

    Chart1.DataBind()

还是基于sql表来做？

2021-05-16