bind chart data to datepicker to show custom timeline

Question

Hello all ,

i a very new to c#

I have created a bar chart & some labels in my WinForms application , by connecting them to MSSQL stored procedure as the data source , but i remembered i needed to provide a date picker function (from and to) and when user selects the date , only that much filtered data should be populated to the chart , i really don't know how to achive this kindly help me:

my stored procedure for labels:

Create proc DashboardData
@totalAcc int out,
@totalACD int out,
@totalRej int out,
@totalRew int out,
@NumAcc int out,
@NumACD int out,
@NumRej int out,
@NumRew Int Out,
@TotSpools Int Out,
@TotalQty Int Out
as
set @totalAcc=(select sum(NetWt) as TotalAccepted From IP_Spools where Status = 'Accepted' group by Status)
set @totalACD=(select sum(NetWt) as TotalofACD From IP_Spools where Status = 'Accepted on Deviation' group by Status)
set @totalRej=(select sum(NetWt) as TotalReject From IP_Spools where Status = 'Reject' group by Status)
set @totalRew=(select sum(NetWt) as TotalRework From IP_Spools where Status = 'Rework' group by Status)
set @NumAcc=(select Count(SpoolID) as CountAccepted From IP_Spools where Status = 'Accepted' group by Status)
set @NumACD=(select Count(SpoolID) as CountACD From IP_Spools where Status = 'Accepted on Deviation' group by Status)
set @NumRej=(select Count(SpoolID) as CountReject From IP_Spools where Status = 'Reject' group by Status)
set @NumRew=(select Count(SpoolID) as CountRework From IP_Spools where Status = 'Rework' group by Status)
set @TotSpools=(select Count(NetWt) as TotalCount From IP_Spools)
set @TotalQty=(select sum(NetWt) as TotalSum From IP_Spools)
go

my stored procedure for charts:

create proc Toprew
as
select top 10 RejectReason1 as Reason, Sum(NetWt) as Quantity
from IP_Spools
Where status = 'Rework'
group by RejectReason1
order by Sum(NetWt) desc
go
create proc Toprej
as
select top 10 RejectReason1 as Reason, Sum(NetWt) as Quantity
from IP_Spools
Where status = 'Reject'
group by RejectReason1
order by Sum(NetWt) desc
go

my code in winform for showing these:

private void FrmMain_Load(object sender, EventArgs e)
        {
            GraphToprej();
            GraphToprew();
            dashboarddata();
        }
        ArrayList Rejcategory = new ArrayList();
        ArrayList RejSum = new ArrayList();

        ArrayList Rewcategory = new ArrayList();
        ArrayList RewSum = new ArrayList();


        private void GraphToprej()
        {
            SqlConnection con = new SqlConnection(cs);
            SqlCommand cmd = new SqlCommand("Toprej", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Rejcategory.Add(dr.GetString(0));
                RejSum.Add(dr.GetInt32(1));
            }
            ChartTopRej.Series[0].Points.DataBindXY(Rejcategory, RejSum);
            dr.Close();
            con.Close();
        }
        private void GraphToprew()
        {
            SqlConnection con = new SqlConnection(cs);
            SqlCommand cmd = new SqlCommand("Toprew", con);
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                Rewcategory.Add(dr.GetString(0));
                RewSum.Add(dr.GetInt32(1));
            }
            ChartTopRew.Series[0].Points.DataBindXY(Rewcategory, RewSum);
            dr.Close();
            con.Close();
        }

        private void dashboarddata()
        {
            SqlConnection con = new SqlConnection(cs);
            SqlCommand cmd = new SqlCommand("DashboardData", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter total = new SqlParameter("@TotalQty", 0); total.Direction = ParameterDirection.Output;
            SqlParameter TotSpools = new SqlParameter("@TotSpools", 0); TotSpools.Direction = ParameterDirection.Output;
            SqlParameter NumRew = new SqlParameter("@NumRew", 0); NumRew.Direction = ParameterDirection.Output;
            SqlParameter NumRej = new SqlParameter("@NumRej", 0); NumRej.Direction = ParameterDirection.Output;
            SqlParameter NumACD = new SqlParameter("@NumACD", 0); NumACD.Direction = ParameterDirection.Output;
            SqlParameter NumAcc = new SqlParameter("@NumAcc", 0); NumAcc.Direction = ParameterDirection.Output;
            SqlParameter totalRew = new SqlParameter("@totalRew", 0); totalRew.Direction = ParameterDirection.Output;
            SqlParameter totalRej = new SqlParameter("@totalRej", 0); totalRej.Direction = ParameterDirection.Output;
            SqlParameter totalACD = new SqlParameter("@totalACD", 0); totalACD.Direction = ParameterDirection.Output;
            SqlParameter totalAcc = new SqlParameter("@totalAcc", 0); totalAcc.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(total);
            cmd.Parameters.Add(TotSpools);
            cmd.Parameters.Add(NumRew);
            cmd.Parameters.Add(NumRej);
            cmd.Parameters.Add(NumACD);
            cmd.Parameters.Add(NumAcc);
            cmd.Parameters.Add(totalRew);
            cmd.Parameters.Add(totalRej);
            cmd.Parameters.Add(totalACD);
            cmd.Parameters.Add(totalAcc);

            con.Open();
            cmd.ExecuteNonQuery();

            LblTotalQty.Text = cmd.Parameters["@TotalQty"].Value.ToString();
            lbltotspools.Text = cmd.Parameters["@TotSpools"].Value.ToString();
            lbltotacc.Text = cmd.Parameters["@totalAcc"].Value.ToString();
            lbltotacd.Text = cmd.Parameters["@totalACD"].Value.ToString();
            lbltotrej.Text = cmd.Parameters["@totalRej"].Value.ToString();
            lbltotrew.Text = cmd.Parameters["@totalRew"].Value.ToString();
            lblspoolacc.Text = cmd.Parameters["@NumAcc"].Value.ToString();
            lblspoolacd.Text = cmd.Parameters["@NumACD"].Value.ToString();
            lblspoolrej.Text = cmd.Parameters["@NumRej"].Value.ToString();
            lblspoolrew.Text = cmd.Parameters["@NumRew"].Value.ToString();

            con.Close();
        }

now all my labels has to be linked wth "DTpfrom" and "DTpto" datepickers , where user can filter out the data and see the same in label and chart :kinldy help me how to do this.

Answer 1

Assuming that you have save the data from SP to a DataTable. Refer to this.

Then you can create a new DataView with this DataTable and use DataView.RowFilter Property to filter rows.

// dt is the DataTable (columns: Id(char(10)), Date(date), Value(tinyint))  
DataView dv = new DataView(dt);  
string dateFrom = dateTimePickerFrom.Value.ToString().Split(' ')[0];  
string dateTo = dateTimePickerTo.Value.ToString().Split(' ')[0];  
dv.RowFilter = $"Date > #{dateFrom}# AND Date < #{dateTo}#"; // dateFrom and dateTo are string in format "dd/mm/yyyy"; dateTimePicker.Value.ToString().Split(' ')[0]  
// call ToTable to get a filter table  
DataTable filtertable = dv.ToTable();  

Now, you can create a bar chart via the following code.

chart1.ChartAreas[0].Axes[0].MajorGrid.Enabled = false;  
chart1.ChartAreas[0].Axes[1].MajorGrid.Enabled = false;  
  
List<string> ids = new List<string>(); // X  
List<int> values = new List<int>(); // Y  
  
for (int i = 0; i < filtertable.Rows.Count; i++)  
{  
    ids.Add(filtertable.Rows[i]["Id"].ToString());  
    values.Add(Convert.ToInt32(filtertable.Rows[i]["Value"]));  
}  
  
// Clear the exist Series and add new series  
chart1.Series.Clear();  
chart1.Series.Add("Value");  
  
// Add data to chart  
chart1.Series["Value"].Points.DataBindXY(ids, values);  
  
// Set chart type  
chart1.Series[0].ChartType = SeriesChartType.Bar;  

Does this what you want?