How come my SQL query overwrites data at each Series in my Chart using C# in Windows Forms?

Question

I am using C# to create a Windows Application using .NET Framework 4.8.

In my application, I have a chart Chart performanceChart which I am attempting to feed data through some SQL queries.

Because of how the project I'm working on is set up, I run the method that performs the query and feeds the data to the chart for each Series I have in the chart (in this case there are 2 series: A and B).

Here is my code for the first query:

 public static void getDataA(Chart chart, string wc)
    {
        string commandSql = "SELECT TOP 8 Example1, Example2, Example3, Example4 FROM ***** WHERE Example5 = @wc AND Example6 = 'A' ORDER BY Example7 Desc";
        SqlConnection conn = new SqlConnection("Data Source = ****; Initial Catalog = ****; Integrated Security = True");
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand(commandSql, conn);
        cmd.Parameters.AddWithValue("wc", wc);
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();

        conn.Open();
        da.Fill(ds);

        chart.DataSource = ds.Tables[0];

        //Mapping a field with x-value of chart
        chart.Series[0].XValueMember = "Production_Hour";

        //Mapping a field with y-value of Chart
        chart.Series[0].YValueMembers = "Cuts";

        //Bind the DataTable with Chart
        chart.DataBind();

        conn.Close();
    }

I then call a similar method, but the SQL query has a minor change that will return entirely different records, and I seemingly add that data to the Series in the correct location (in this case it would be in the X and Y Series 1 location, because we already did this for Series 0). Here is my code:

public static void getDataB(Chart chart, string wc)
    {
        string commandSql = "SELECT TOP 8 Example1, Example2, Example3, Example4 FROM ***** WHERE Example5 = @wc AND Example6 = 'B' ORDER BY Example7 Desc";
        SqlConnection conn = new SqlConnection("Data Source = ****; Initial Catalog = ****; Integrated Security = True");
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand(commandSql, conn);
        cmd.Parameters.AddWithValue("wc", wc);
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();

        conn.Open();
        da.Fill(ds);

        chart.DataSource = ds.Tables[0];

        //Mapping a field with x-value of chart
        chart.Series[1].XValueMember = "Production_Hour";

        //Mapping a field with y-value of Chart
        chart.Series[1].YValueMembers = "Cuts";

        //Bind the DataTable with Chart
        chart.DataBind();

        conn.Close();
    }

My question is, why is the method/query ran last populating the data for each Series in my chart? As you can see, all the data in my chart is the same (and yes, I use SSMS to check and ensure that this isn't a coincidence that the data is the same, I can ensure that the data for each series is the data from the last SQL query performed).

I know there are 3 Series in the above image. To get my point across I only needed to share snipped for 2 out of the 3 methods to save space/time. The point still stands: the last method running the query is that data that populates ALL Series X and Y values in the chart.

Also, I know that these methods could probably be refactored to be the same method, perhaps just requiring an extra parameter in the method arguments. I do intend to refactor at some point, but for now my intentions are just to prevent overwriting of data.

Answer 1

The problem is this line:

chart.DataSource = ds.Tables[0];

The chart may have multiple series, but it only has one data source.

One option (among several) that can fix this is to run multiple statements in the same SqlCommand.

public static void getData(Chart chart, string wc)
{
    string SQL = @"
        SELECT TOP 8 Example1, Production_Hour, Cuts
        FROM ***** 
        WHERE Example5 = @wc AND Example6 = 'A' 
        ORDER BY Example7 Desc;
        SELECT TOP 8 Example1, Production_Hour, Cuts 
        FROM ***** 
        WHERE Example5 = @wc AND Example6 = 'B' 
        ORDER BY Example7 Desc;
        SELECT TOP 8 Example1, Production_Hour, Cuts 
        FROM ***** 
        WHERE Example5 = @wc AND Example6 = 'C' 
        ORDER BY Example7 Desc;";

    DataSet ds = new DataSet();
    using (var conn = new SqlConnection("Data Source = ****; Initial Catalog = ****; Integrated Security = True") )
    using (var cmd = new SqlCommand(SQL, conn))
    using (var da = new SqlDataAdapter(cmd))
    {
        // guessing at parameter type and length here.
        // Setting the exact type to match the database column,
        // instead of AddWithValue(), can GREATLY increase performance
        cmd.Parameters.Add("@wc", SqlDbType.NVarChar, 20).Value = wc;

        da.Fill(ds);
        // Now .Tables[0] is the result from the first statement
        //   .Tables[1] is the result from the second statement
        //   .Tables[2] is the result from the third statement      
    }

    chart.DataSource = ds;

    chart.Series[0].XValueMember = "Production_Hour";
    chart.Series[0].YValueMembers = "Cuts";

    chart.Series[1].XValueMember = "Production_Hour";
    chart.Series[1].YValueMembers = "Cuts";

    chart.Series[2].XValueMember = "Production_Hour";
    chart.Series[2].YValueMembers = "Cuts";

    //Bind the DataTable with Chart
    chart.DataBind();
}

It's been a looong time since I've used this control, but I think it's smart enough to map each series to the corresponding table in the dataset in order.

It may also be you need to bind each series separately:

public static void getData(Chart chart, string wc)
{
   using (...)
   {
      // ...
   }

   chart.Series[0].Points.DataBind(ds.Tables[0].Rows, "Production_Hour", "Cuts", "");
   chart.Series[1].Points.DataBind(ds.Tables[1].Rows, "Production_Hour", "Cuts", "");
   chart.Series[2].Points.DataBind(ds.Tables[2].Rows, "Production_Hour", "Cuts", "");
}

For what it's worth, I would also tend to separate the code retrieving the data from the code binding it to the chart. That is, getData() should be defined to the return the dataset, and the code that calls it should then receive that result and use it to render the chart.

Answer 2

The problem is that your second call is essentially nullifying the results of the first call, but not all of the setup of the chart, so you end up with each series being setup, but you're resetting the DataSource to the last set of data only, and redoing the DataBind. Effectively you are doing

1. Create a new DataSet
2. Query and fill from a DataAdapter
3. Setup the series.
4. Set the DataSource to this DataSet
5. Call DataBind()
6. Create a new DataSet
7. Query and fill from a DataAdapter
8. Setup the series.
9. Set the DataSource to this DataSet and thus destroy the prior data
10. Call DataBind() to bind to your new data, losing the prior data for good

A DataSet can be filled multiple times, and this is what you need to do. The order in your case should be

1. Create the DataSet
2. Query and fill from the first DataAdapter
3. Query and fill from the second...Nth DataAdapter
4. Set the DataSource to the DataSet
5. Call DataBind()

So, you'd have to refactor that method into multiple ones:

1. Initialization-style method (create the DataMethod)
2. N number of query/fill methods
3. Finish-up method that sets the source and binds.

This refactoring means passing around the DataSet as one of the parameters.

DataBind() is usually only meant to be called once, but it is certainly allowed to be called multiple times. Some examples

• adding a refresh button if you expect data to change
• making a "live" chart that updates on a timer
• getting data from a different source for some reason
• etc.