Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
how to include result of if statement into datareader execution of sql command in c# windows forms with sql server2008.

Given below is my code:
C#
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
SqlCommand cmd = new SqlCommand("Select r.errorCode,Convert(varchar,r.dialysis_date,106),pn.patient_first_name,pn.patient_last_name,d.manufacturer,d.dialyzer_size,Convert(varchar,r.start_date,108),Convert(varchar,r.end_date,108),d.packed_volume,r.bundle_vol,t.Technician_first_name,t.Technician_last_name from dialyser d,patient_name pn,reprocessor r,Techniciandetail t where pn.patient_id=d.patient_id and r.dialyzer_id=d.dialyserID and t.technician_id=r.technician_id and d.deleted_status=0 and d.closed_status=0 and pn.status=1 and r.errorCode>105 order by pn.patient_first_name,pn.patient_last_name", conn);
            conn.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
table.AddCell(new Phrase(new Chunk("Reprocessing Date", NormalFont)));
            table.AddCell(new Phrase(new Chunk("Patient Name", NormalFont)));
            table.AddCell(new Phrase(new Chunk("Dialyzer(Manufacturer,Size)", NormalFont)));
            table.AddCell(new Phrase(new Chunk("Time Start", NormalFont)));
            table.AddCell(new Phrase(new Chunk("Time End", NormalFont)));
            table.AddCell(new Phrase(new Chunk("Time Taken", NormalFont)));
            table.AddCell(new Phrase(new Chunk("PV", NormalFont)));
            table.AddCell(new Phrase(new Chunk("BV", NormalFont)));
            table.AddCell(new Phrase(new Chunk("Error",NormalFont)));
table.AddCell(new Phrase(new Chunk("Verification", NormalFont)));
            while(dr.Read())
            {
                table.AddCell(new Phrase(new Chunk(dr[1].ToString(),regular)));
                table.AddCell(new Phrase(new Chunk(dr[2].ToString()+" "+dr[3].ToString(),regular)));
                table.AddCell(new Phrase(new Chunk(dr[4].ToString()+" "+dr[5].ToString(),regular)));
                table.AddCell(new Phrase(new Chunk(dr[6].ToString(),regular)));
                table.AddCell(new Phrase(new Chunk(dr[7].ToString(),regular)));
                TimeSpan minL=Convert.ToDateTime(dr[7].ToString())-Convert.ToDateTime(dr[6].ToString());
                table.AddCell(new Phrase(new Chunk(minL.ToString(),regular)));
                table.AddCell(new Phrase(new Chunk(dr[8].ToString(),regular)));
                table.AddCell(new Phrase(new Chunk(dr[9].ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[10].ToString()+" "+dr[11].ToString(),regular)));
            }
            dr.Close();

The above code works OK.
However i have one problem since in my errorCode field has a value of 106or 107 or 108 in my table named:reprocessor in sql server2008.
What i want is
if r.errorCode==106 then i want text "Leak Test Failed" to be displayed in my column "Error" in pdf report from c# windows forms with sql server2008 using itextsharp.
Similarly if r.errorCode==107 then i want text "Bundle volume failed" to be displayed in my column "Error" in pdf report from c# windows forms with sql server2008 using itextsharp.
Similarly if r.errorCode==108 then i want text "Priming Volume Failed" to be displayed in my column "Error" in pdf report from c# windows forms with sql server2008 using itextsharp.
I have tried many ways to solve the problem but with no success. Can anyone help me please? I repeat this question has nothing to do with ItextSharp with pdf report from c# windows forms.
This question is mainly on c# windows forms with sql server2008. Can anyone help me please?Any help/guidance in solving of this problem would be greatly appreciated!
Posted
Updated 27-May-14 1:07am
v4
Comments
Sunasara Imdadhusen 27-May-14 6:01am    
What is r? need more details.
Member 10248768 27-May-14 6:21am    
Hi Sunasara Imdadhusen
My name is vishal.Thanks for your help on such short notice.I have solved the problem by myself. Given below is my code which solved it.:
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
SqlCommand cmd = new SqlCommand("Select r.errorCode,Convert(varchar,r.dialysis_date,106),pn.patient_first_name,pn.patient_last_name,d.manufacturer,d.dialyzer_size,Convert(varchar,r.start_date,108),Convert(varchar,r.end_date,108),d.packed_volume,r.bundle_vol,t.Technician_first_name,t.Technician_last_name from dialyser d,patient_name pn,reprocessor r,Techniciandetail t where pn.patient_id=d.patient_id and r.dialyzer_id=d.dialyserID and t.technician_id=r.technician_id and d.deleted_status=0 and d.closed_status=0 and pn.status=1 and r.errorCode>105 order by pn.patient_first_name,pn.patient_last_name", conn);
conn.Open();
SqlDataReader dr;
dr = cmd.ExecuteReader();
table.AddCell(new Phrase(new Chunk("Reprocessing Date", NormalFont)));
table.AddCell(new Phrase(new Chunk("Patient Name", NormalFont)));
table.AddCell(new Phrase(new Chunk("Dialyzer(Manufacturer,Size)", NormalFont)));
table.AddCell(new Phrase(new Chunk("Time Start", NormalFont)));
table.AddCell(new Phrase(new Chunk("Time End", NormalFont)));
table.AddCell(new Phrase(new Chunk("Time Taken", NormalFont)));
table.AddCell(new Phrase(new Chunk("PV", NormalFont)));
table.AddCell(new Phrase(new Chunk("BV", NormalFont)));
table.AddCell(new Phrase(new Chunk("Error", NormalFont)));
table.AddCell(new Phrase(new Chunk("Verification", NormalFont)));
while(dr.Read())
{
table.AddCell(new Phrase(new Chunk(dr[1].ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[2].ToString()+" "+dr[3].ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[4].ToString()+","+dr[5].ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[6].ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[7].ToString(),regular)));
TimeSpan minL=Convert.ToDateTime(dr[7].ToString())-Convert.ToDateTime(dr[6].ToString());
table.AddCell(new Phrase(new Chunk(minL.ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[8].ToString(),regular)));
table.AddCell(new Phrase(new Chunk(dr[9].ToString(),regular)));
string recur="";
if (Convert.ToInt32(dr[0].ToString()) == 106)
{
recur = "Leak Test Failed";
}
else if (Convert.ToInt32(dr[0].ToString()) == 107)
{
recur = "Bundle Volume Failed";
}
else if (Convert.ToInt32(dr[0].ToString()) == 108)
{
recur = "Priming Volume Failed";
}
table.AddCell(new Phrase(new Chunk(recur.ToString(), regular)));
table.AddCell(new Phrase(new Chunk(dr[10].ToString()+" "+dr[11].ToString(),regular)));
}
dr.Close();

Have a look at CASE[^] (see the examples).
 
Share this answer
 
Comments
Member 10248768 28-May-14 5:16am    
Thanks for guiding me CPallini Mam i will look into it and solve my problem Mam.Thank you.
Yes, like CPallini said. Use a case statement.

Google TSQL Case example
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900