多线图 [英] Multi Line Chart

查看：184
本文介绍了多线图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我一直在尝试创建一个从数据库中提取数据的多线图。
为了这样做，我写了以下代码来填充图表：

  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（）; $ b $ d da.Fill（dt）; 
 Chart1.DataSource = dt; 
 Chart1.Series [2010]。XValueMember =Month; 
 Chart1.Series [2010]。YValueMembers =Cost; 
 
 Chart1.DataBind（）; 
 con.Close（）;

此代码工作除了它有两个数据源，所以只有底部的sqlconnection被填充到图表。
我失去了如何解决这个问题，我想象他们是一个更好的方式去这样做，但我失去了。



UPDATE ：



使用例子，我最终使用pivot sql来创建表（见图）。
然后我将我的代码更新为以下内容：



SqlCommand cmd = new SqlCommand（SELECT * FROM（SELECT YEAR（Start_Date），CASE MONTH（Start_Date）WHEN 1 THEN'January'WHEN 2 THEN'February'当3月3月3月4月4月4月5月5月5月6月6月7月7月7月8月8月8月9月9月10月10月10月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（[Cost]）FOR [Month] IN（[January]，[February]，[March]，[April]，[May]，[June]，[July]，[August]，[September] [10月]，[11月]，[12月]））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（）;`



当我运行页面时， t找到列Month。如果我添加：

dt.Columns.Add（Month）;
dt.Columns.Add（Year）;`
它找不到2010，如何让我的图表正确导航;是他们的问题，我的枢轴sql？

解决方案

我昨天回答了类似的问题。 分隔图表系列





像昨天的问题，我建议你先创建一个数据库，然后引用列




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


  1. 获取2个数据表

  2. 以Month作为主键合并数据库

  3. 将每列添加到图表作为新系列


或者您可以手动绘制系列



/ p> 

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

并替换为

 对于每一行As DataRow In dt1.Rows 
 Chart1.Series（Series2）。Points.AddXY（row.Item（Month），row.Item ）
 Next 
 
每行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 （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（）



根据您的代码，这应该有助于我理解： 

  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 = '水'）; 
 
 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 =Month;
Chart1.Series（0）.YValueMembers =2010; 

  Chart1.Series（1）.XValueMember = 月; 
 Chart1.Series（1）.YValueMembers =2011
 
 Chart1.DataBind（）

还是它是根据sql表做的？


I have been trying to make a multiline graph which pulls data from a database. In an effort to do this i have written the following code to populate the chart:

    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();

This code works except it has two data sources so only the bottom sqlconnection is populated to the chart. I am at lost of how to fix this, and i imagine their is a better way to go about doing this but i am at a loss.

UPDATE:

Using the example bellow i ended up using pivot sql to make a table (see picture). Then i updated my code to be the following:

 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();`

When i run the page it errors to can't find column Month. And if i add:
dt.Columns.Add("Month"); dt.Columns.Add("Year");` It can't find 2010, How do i get my chart piloting correctly; is their an issue with my pivot sql?

解决方案

I answered a similar question yesterday. Separating Chart Series

You cant change a charts datasource without reseting the data as you are doing in the above code.

Like yesterdays question I'd recommended you create one datatable first then reference columns in the datatable to your particular series

From yesterdays answer

So as a suggestion to cleaning up the code, I would do the below

  1. Get the 2 datatables
  2. Merge the datatables with "Month" as the primary key
  3. add each column to the chart as a new series

Or you could manually plot the series

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

if you manage to get all your data into one Datatable and have a chart with two series created then you can do the below

    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()

Data Table dt should have all data in it {because i am graving both years) (how can i bind it to a table so i can verify)

Right now its complaining series can't be used as a method however.

I am very new programmer, and only do this in my free time so forgive my simple mistakes.

Do i need to setup the data table like you did here: 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()

Or is it doing it based on the sql table?

这篇关于多线图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆