Drawing a chart from a datatable

Question

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;
 }

This gives me the following output;

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;

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

Solution

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

All X-Values are internally of type double.

Introduction

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

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.

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.

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

Your problem

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..

It sounds perfectly fine and it is..

..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;

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..

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:

Your data mapping is wrong.

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.

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.

Off the top of my head this is how it could look like:

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", "");
..

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