从数据表绘制图表 [英] Drawing a chart from a datatable

问题描述

我正在尝试绘制带有多个序列的图形。数据是通过 DataAdapter 来自数据库到 DataTable 的数据库中的，像这样；

I am trying to draw a graph with multiple series on it. the data for it comes from a database through a DataAdapter into a DataTable, like this;

public DataTable deaths_per_reason_per_year(string farmerid)
 {
     dt = new DataTable();
     try
     {
         conn.Open();  // (select formatted to fit SO lines)
         SqlCommand cmd = new SqlCommand("SELECT DATEPART(yyyy, DeathDate) AS 'Year',   
              Reason,   
              COUNT(DeathID) AS 'Number of Deaths' FROM [Farmstat_V1.0].[dbo].[Death] 
              WHERE FarmerID = '" + farmerid + "' 
              GROUP BY  Reason, YEAR(DeathDate);", conn);
         SqlDataReader reader = cmd.ExecuteReader();
         dt.Load(reader);
     }
     catch (Exception)
     {
         throw;
     }
     finally
     {
         conn.Close();
     }
     return dt;
 }

这给了我以下输出；

当我尝试将DataTable分配给表单上的图表时， ;

When I try to assign the DataTable to my chart on the form, with this;

StatsDataAccess sda = new StatsDataAccess();

chart1.DataSource = sda.deaths_per_reason_per_year(FarmerID);
chart1.Series["Series1"].XValueMember = "Year";
chart1.Series["Series1"].YValueMembers = "Number of Deaths";
chart1.Series["Series2"].XValueMember = "Reason";
chart1.Series["Series2"].YValueMembers = "Number of Deaths";
chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
chart1.Series["Series1"].IsValueShownAsLabel = true;
chart1.DataBind();

我得到一个看起来像这样的图形，我通过属性窗格在其中添加了系列图表；

I get a drawn graph that looks like this, I have added the series in there through the properties pane of the chart;

但是我想要的输出应该是看起来像这样；

But I want an output that should look like this;

我尝试了Google搜索，但没有成功。有人可以帮我吗？
非常感谢。
谢谢

I've tried googleing without success. Can somebody please help me with this? I'll appreciate it very much. Thanks

推荐答案

最明显的问题是有关x值的数据类型。

The most visible problem is all about the data type of the x-Values.

所有X值在内部都是 double 类型。

All X-Values are internally of type double.

简介

• 如果您添加的 DataPoints X值将按预期处理和存储


• 如果您添加 DataPoints 和 DateTime X值也会转换为两倍（通过OLE自动化日期转换）

• If if you add DataPoints with numbers in the X-Values they are treated and stored as expected
• If you add DataPoints with DateTime X-Values they are converted to double as well (via OLE Automation Date conversion)

但是如果您添加类型无法转换为数字，例如字符串，事情变得很奇怪。 内部 X值均为 0 ，因为除了标签显示以外，它们还可能是其他值。

But if you add types that can't be converted to a number, like strings, things get weird. Internally the X-Values are all 0, because what else could they be but the labels display the strings.

显示的 DataPoints 被视为X值分别为0、1、2、3。

The displayed DataPoints are treated as if the had X-Values of 0,1,2,3... and the points are shown with nice and equal spacing.

对于许多新手来说，这看起来还不错，他们从不知道自己已经输了

To many many newbies this looks fine and they never know that they have effectively lost the X-Values.

当尝试使用数字格式或尝试使用X值进行计算时，就会遇到问题。

Their problems come when the try to use number formatting or try to calculate with the X-Values.

您的问题

您的问题是您选择的 从 DATEPART 函数提供整数，所以您做对了，实际上您做得很好，无法在盒子中得出结果。.

Your 'problem' is that your select delivers integers from the DATEPART function, so you did it right, in fact you did it too well to work out f the box..

听起来很不错，它是。

..但是图表尝试显示许多具有自动设置的项目，尽管它们通常可以正常工作，但在这里却不能，因为它们试图启动 x = 0 的图表，当然还有三个实际值 2014-2016 的诱因被挤压到无法识别的地步。

..however the Chart tries to display many items with Auto settings and while they usually work fine, here they don't because they try to start the chart at x = 0 and of course the three actual values of 2014-2016 get squeezed to the right beyond recognition.

解决方案很简单：设置最小显示：

The solution is simple: Set the display minimum:

chart1.ChartAreas["ChartArea1"].AxisX.Minimum = 2014;

一切都很好。您可能想根据 Points 中的x值来计算该值，而且，万岁，可以，因为您有它们。.

and all is well. You may want to calculate that value from the x-Values in your Points, and, hooray, you can, because you have them..

这是最好的方法，只要您的系列中的X值具有有意义的数字即可。

This is the best way, as long as your series has meaningful numbers for X-Values.

不幸的是，第二个系列似乎没有

Unfortunately the second series doesn't seem to have that..

丑陋的选择是将年份转换为字符串，但是除非确实需要，否则不要这样做！

The ugly alternative would be to convert the year to a string, but, don't do that unless you really must!

解决了缩放问题后，代码的另一个大问题就变得可见了：

After fixing the scaling problem the other big issue of your code gets visible:

您的数据映射错误。

将数据表与所需的输出进行比较，您的数据绑定根本不会映射到这样的图表。

Comparing the data table and the desired output, your data binding simply won't map to a chart like that.

解决方案：您应该将数据拆分成尽可能多的独立来源，如原因，即，根据要绑定的系列。

Solution: You should split the data into as many separate sources as you have reasons, i.e. as you have Series to bind.

我将为每个 DataViews 创建一个单独的code>原因，每个基于相同的 DataTable ，但具有不同的 Filter （reason ='reason1'..），然后将每个 DataView 到一个 Series 。

I would create a separate DataViews for each reason, each based on the same DataTable but with a different Filter (reason = 'reason1'..) and then bind each DataView to one Series.

我的顶部

DataTable DT = yourTable;

DataView dv1 = new DataView(DT);
DataView dv2 = new DataView(DT);
..
dv1.RowFilter = "reason=='Sold'";
dv2.RowFilter = "reason=='Other'";
..
yourSeries1.Points.DataBind(dv1, "year", "deathcount", "");
..

现在所有 Series DataPoints 以相同的方式绑定，所有 Series 都显示，并且缩放比例固定。

Now all Series DataPoints are bound the same way, all Series show and the scaling is fixed.

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