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

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 list = values.ToList();

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();

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vivek Kumar10y ago
Hi Venkat,
Before opening the connection, please check connection is already opened or not as below

if (con.State == ConnectionState.Closed)
{
con.Open();
}
Abubakar Dandoti10y ago
do this befor open cnnection
if (con.State == ConnectionState.Closed)
{
con.Open();
}

or do this after update is completed
con=null;
Abhay Shanker10y ago
below link can be also helpful to you

http://stackoverflow.com/questions/13343236/the-connection-was-not-closed-the-connections-current-state-is-open
Vipan Sharma10y ago
Add

if (con.State == ConnectionState.Closed)
{
con.Open();
}

where you open the connection.

Shubham Kumar10y ago

Hi its a normal problem its happen due to not manage connection correctly

use

using (SqlConnection con = new SqlConnection(connectionString))

{ 	 
   // 	  
  // Open the SqlConnection. 	    // 	 
   con.Open(); 	  
  // 	   
 // The following code uses an SqlCommand based on the SqlConnection. 	    // 
  using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM tbl", con)) 
   using (SqlDataReader reader = command.ExecuteReader())
 { 	
   while (reader.Read()) 		
    {}
  }
}