CODEWITHSUNDEEP
c#oledboledbconnection
proud
proud

Reputation: 53

OleDbConnection.Close() does not work

I have a method where I insert some data in a local MDB-Acces file. With this code it works fine:

try
{
   foreach (DataRow rowS99 in dtS99.Rows)
   {
      con.Open();
      da.InsertCommand = con.CreateCommand();
      da.InsertCommand.Parameters.AddWithValue("@wertListName", rowS99["WertListName"]);
      da.InsertCommand.Parameters.AddWithValue("@key", rowS99["Key"]);
      da.InsertCommand.Parameters.AddWithValue("@bezeichner", rowS99["Bezeichner"]);
      da.InsertCommand.Parameters.AddWithValue("@keyAufbereitet", rowS99["KeyAufbereitet"]);
      da.InsertCommand.CommandText = sql;
      da.InsertCommand.ExecuteNonQuery();
      con.Close();
   }
   check = true;
}

When i put the Open and Close method outside the foreach loop the code works, but the local file, which was used with the connection, is already opened with Visual Studio so that i can't delete or move it. The Status of the connection says after con.Close() that it is closed.

What is the problem here?

Upvotes: 0

Views: 3233

Answers (4)

RedCNC
RedCNC

Reputation: 1

try
{
 con.Open();
 foreach (DataRow rowS99 in dtS99.Rows)
 {
  da.InsertCommand = con.CreateCommand();
  da.InsertCommand.Parameters.AddWithValue("@wertListName", rowS99["WertListName"]);
  da.InsertCommand.Parameters.AddWithValue("@key", rowS99["Key"]);
  da.InsertCommand.Parameters.AddWithValue("@bezeichner", rowS99["Bezeichner"]);
  da.InsertCommand.Parameters.AddWithValue("@keyAufbereitet", rowS99["KeyAufbereitet"]);
  da.InsertCommand.CommandText = sql;
  da.InsertCommand.ExecuteNonQuery();
 }
 con.Close();
 check = true;
 OleDb.OleDbConnection.ReleaseObjectPool();
 GC.Collect();  // I know attation
}

Works for me

Upvotes: 0

Hardik Fefar
Hardik Fefar

Reputation: 321

Try to put con.close in finally().

Upvotes: 0

Derek
Derek

Reputation: 8640

You don't need to have the Connection Opened and Closed within the loop. If there are 500 rows in your table then you will be opening & closing the connection 500 times, its not best practice..

Your connection should open in the Try statement before the loop and close once all Rows have been processed.

I think you would be best using the Update() function rather than the method your doing above.

You can take out the need to loop through each and every row.

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx

Using this functionality allows you to add, edit and delete rows simply by calling the Update() method. You will need all 3 Commands setting up, UPDATE, INSERT, DELETE.

Hope this helps

Upvotes: 1

Dennis
Dennis

Reputation: 37800

IDbConnection.Close method by default just returns connection back to the connection pool. It doesn't close the physical connection (either network connection or file). You may disable connection pooling (but this will decrease productivity of your application), or clear connection pool (see OleDbConnection.ReleaseObjectPool).

Upvotes: 0

Related Questions