多线图 [英] Multi Line Chart

问题描述

我一直在试图让一个多行图从数据库中提取数据。
在努力做到这一点，我写了下面的代码来填充图：

  SqlConnection的CON1 =新的SqlConnection （）; 
 con1.ConnectionString = ConfigurationManager.ConnectionStrings [删除]的ToString（）; 
的SqlCommand CMD1 =新的SqlCommand（SELECT YEAR（开始）年，月（开始）为月，成本，Utility_Type FROM import_Utilities WHERE YEAR（开始）= 2011 = Utility_Type水）; 
 cmd1.Connection = CON1; 
 con1.Open（）; 
 
 SqlDataAdapter的DA1 =新SqlDataAdapter的（CMD1）; 
 DataTable的DT1 =新的DataTable（）; 
 da1.Fill（DT1）; 
 Chart1.DataSource = DT1; 
 
 Chart1.Series [系列2] = XValueMember月。 
 Chart1.Series [系列2] YValueMembers =成本。 
 Chart1.Legends.Add（新传奇（默认）{对接= Docking.Right}）; 
 
 
 Chart1.DataBind（）; 
 con1.Close（）; 
 
的SqlConnection CON =新的SqlConnection（）; 
 con.ConnectionString = ConfigurationManager.ConnectionStrings [删除]的ToString（）; 
 con.Open（）; 
的SqlCommand CMD =新的SqlCommand（SELECT YEAR（开始）年，月（开始）为月，成本，Utility_Type FROM import_Utilities WHERE YEAR（开始）= 2012 = Utility_Type水）; 
 cmd.Connection = CON; 
 SqlDataAdapter的大=新SqlDataAdapter的（CMD）; 
 DataTable的DT =新的DataTable（）; 
 da.Fill（DT）; 
 Chart1.DataSource = DT; 
 Chart1.Series [2010] = XValueMember月; 
 Chart1.Series [2010] YValueMembers =成本。 
 
 Chart1.DataBind（）; 
 con.Close（）; 
  

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

更新

使用波纹管我结束了使用SQL支点，使一个表（见图片）的例子。
然后，我更新了我的代码如下所示：

 的SqlCommand CMD =新的SqlCommand（SELECT * FROM（SELECT YEAR（开始），CASE MONTH（开始），当1，则1月份的WHEN 2 THEN二月 WHEN 3 THEN三八WHEN 4 THEN'四月'WHEN 5 THEN'五一'当6 THEN六一WHEN 7 THEN七一当8 THEN八一WHEN 9 THEN'September'WHEN 10，则十一11时THEN'月'当12 THEN'月'作为[月] END，[费用] FROM [HousingAccountingReports]。[DBO]。[import_Utilities]其中[建筑] ='大厦B'和[Utility_Type] ='电器'）TableDate PIVOT（SUM（[费用]）在[月] IN（[月]，[日]，[月]，[月]，[日]，[日]，[月]，[月]，[月] 【10月】，【11月]，[月]））的数据透视表）; 
 cmd.Connection = CON; 
 SqlDataAdapter的大=新SqlDataAdapter的（CMD）; 
 DataTable的DT =新的DataTable（）; 
 da.Fill（DT）; 
 Chart3.DataSource = DT; 
 Chart3.Series [系列1] = XValueMember月。 
 Chart3.Series [系列1] = YValueMembers2010。 
 Chart3.Series [系列2] = XValueMember月。 
 Chart3.Series [系列2] = YValueMembers2011。 
 Chart3.Series [系列3] = XValueMember月。 
 Chart3.Series [系列3] = YValueMembers2012。 
 Chart3.DataBind（）;`
  

当我运行页面错误，它可以以'找不到列一个月。如果我补充一下：结果
dt.Columns.Add（月）;
dt.Columns.Add（年）;`
它无法找到2010年，如何让我的图表试点正确;他们是我的支点SQL的问题吗？

解决方案

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

你不能改变一个图表的数据源没有CH375复位，你在上面的代码做的数据。

昨天一样的问题，我会建议你创建一个数据表第一然后引用列在数据表中您的特定系列。

从昨日的回答

因此，作为一个建议，清理的代码，我会做以下

1. 获取2数据表


2. 合并以月的数据表作为主键


3. 每一列图表添加为新系列

或你可以手工绘制的系列

因此下降

  Chart1.DataSource DT = 
 Chart1.Series [2010] = XValueMember月。 
 Chart1.Series [2010] YValueMembers =成本。 
  

和

替换

 的每一行作为的DataRow在dt1.Rows 
 Chart1.Series（系列2）。Points.AddXY（row.Item（月），row.Item（费用） ）
下一步
 
的每一行作为的DataRow在dt.Rows 
 Chart1.Series（2010）。Points.AddXY（row.Item（月），行.Item（成本））
下一步
  

如果您设法得到所有的数据到一个数据表并与创建那么你可以做下面的

两个系列的图表

 昏暗DT作为新的DataTable 
 dt.Columns.Add（月，的GetType（字符串））
 dt.Columns.Add（2010，的GetType （整数））
 dt.Columns.Add（2011，的GetType（整数））
 
 dt.Rows.Add（月，15，25）
 DT .Rows.Add（二月，18，32）
 dt.Rows.Add（三八，12，34）
 dt.Rows.Add（四月，12，34）
 
 Chart1.DataSource DT = 
 Chart1.Series（0）= .XValueMember月
 Chart1.Series（0）= .YValueMembers2010
 
 Chart1.Series（1）.XValueMember =月
 Chart1.Series（1）.YValueMembers =2011
 
 Chart1.DataBind（）
  

基于您的代码应该工作，以我的理解：

 的SqlConnection CON =新的SqlConnection（）; 
 con.ConnectionString = ConfigurationManager.ConnectionStrings [删除]的ToString（）; 
 con.Open（）; 
的SqlCommand CMD =新的SqlCommand（SELECT YEAR（开始）年，月（开始）为月，成本，Utility_Type，建筑，距离import_Utilities WHERE YEAR（开始）= 2012和Year（开始）= 2011年和Utility_Type = '水'）; 
 
 cmd.Connection = CON; 
 SqlDataAdapter的大=新SqlDataAdapter的（CMD）; 
 DataTable的DT =新的DataTable（）; 
 da.Fill（DT）; 
 
 Chart1.DataSource = DT; 
 Chart1.Series（0）= .XValueMember月; 
 Chart1.Series（0）= .YValueMembers2010; 
 
 Chart1.Series（1）.XValueMember =月; 
 Chart1.Series（1）.YValueMembers =2011
 
 Chart1.DataBind（）
  

数据表DT应该有所有的数据在它{因为我graving的两个年度）（我怎样才能将其绑定到一个表，所以我可以证实）

现在其抱怨系列不能用作然而的方法。

我很新的程序员，只有做到这一点在我的空闲时间，所以请原谅我的简单的错误。

我是否需要设置数据表像你这样在这里：
Chart1.DataSource = DT;
Chart1.Series（0）= .XValueMember月;
Chart1.Series（0）= .YValueMembers2010;

  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屋！