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.