How to avoid The connection was not closed. The connection's

Question

 

 Hi Below code using automatic update  using background worker ,i set background time execution time  from timer ,some i get the error 

The connection was not closed. The connection's current state is open. error but properly connection open and close  ,even also check connection status after i can open ,still throwing error ,sometime getting error like  there is already an open datareader associated with this command which must be closed first.

 this my final stage my project ,please help me to complete project ,reference code below

 

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)

{

downloadStart();

} 

private void timer1_Tick(object sender, EventArgs e)

{

if (backgroundWorker1.IsBusy != true)

{

backgroundWorker1.DoWork += new DoWorkEventHandler(backgroundWorker1_DoWork);

backgroundWorker1.RunWorkerAsync();

}

}

protected void downloadStart()

{

try

{

con.Open();

string result;

SqlCommand cmdip = new SqlCommand("select IPId,IPAddress,PortNo,SlaveId from mstr_ipdetail where IPStatus='Y'", con);

SqlDataReader dr = cmdip.ExecuteReader();

if (dr.HasRows)

{

while(dr.Read())

{

short ContID = Convert.ToInt16(dr["IPId"].ToString());

string IP = dr["IPAddress"].ToString();

string rdata = "";

short SlaveID = Convert.ToInt16(dr["SlaveId"].ToString());

short PortNum = 1234;

dr.Close();

short Contresult = objSmart.GetControllerInfo(ContID, ref rdata);

string res1 = Contresult.ToString();

if (res1 != "0")

{

result = objSmart.AddController(ref ContID, ref IP, ref PortNum, ref SlaveID).ToString();

if (result == "0")

{

short cnt = 1, b = 0, trcnt = 100;

string rOut = "";

short wer = objSmart.GetMultiTransaction(cnt, trcnt, ref rOut, b);

string input = rOut;

string[] values = input.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

List<string> list = values.ToList<string>();

int j = 1;

for (int i = 0; i < values.Length - 2; i++)

{

String newValue = values[i];

list.Add(newValue);

if (j % 10 == 0)

{

string Chn = list[0].ToString();

string Evn = list[1].ToString();

string Pass = list[2].ToString();

string hh = list[3].ToString();

string dd = list[4].ToString();

string Notr = list[5].ToString();

string readerpt = list[6].ToString();

string Accty = list[7].ToString();

string chksum = list[8].ToString();

string Notrns = list[9].ToString();

char[] arraytime = hh.ToCharArray();

string hours1 = arraytime[0].ToString();

string hours2 = arraytime[1].ToString();

string min1 = arraytime[2].ToString();

string min2 = arraytime[3].ToString();

string sec1 = arraytime[4].ToString();

string sec2 = arraytime[5].ToString();

String Totalhr = hours1 + "" + hours2 + ":" + min1 + "" + min2 + ":" + sec1 + "" + sec2;

char[] arraydate = dd.ToCharArray();

string d0 = arraydate[0].ToString();

string d1 = arraydate[1].ToString();

string d2 = arraydate[2].ToString();

string d3 = arraydate[3].ToString();

string d4 = arraydate[4].ToString();

string d5 = arraydate[5].ToString();

string Yprfix = "20";

//String fullDate = d0 + "" + d1 + "/" + d2 + "" + d3 + "/" + Yprfix + "" + d4 + "" + d5;

string fullDate = Yprfix + "" + d4 + "" + d5 + "/" + d2 + "" + d3 + "/" + d0 + "" + d1;

string Datetime = fullDate + " " + Totalhr; con1.Open();

SqlCommand cmdchkpass = new SqlCommand("select BarcodeId from mstr_workforce where BarcodeId=" + Pass + " and WFStatus='Y'", con1);

SqlDataAdapter ad = new SqlDataAdapter(cmdchkpass); con1.Close();

DataSet ds = new DataSet(); ad.Fill(ds);

if (ds.Tables[0].Rows.Count != 0)

{

con2.Open();

SqlCommand cmdtrns = new SqlCommand("insert into WorkforceAtten(WorkforceID,AttendanceDate,CreateDate) values ((select WorkforceId from mstr_workforce where BarcodeId='" + Pass + "' ),'" + fullDate + "','" + Datetime + "') ", con2);

int res = cmdtrns.ExecuteNonQuery(); con2.Close();

if (res > 0)

{

list.Clear();

}

list.Clear();

}

else

{ list.Clear(); }

}

j++;

}

short delete = objSmart.DeleteMultiTransaction(cnt, trcnt, ref rOut, b);

}

}

}

}

}

catch (Exception ex)

{ }

if (con.State == ConnectionState.Open)

con.Close();

}