How can I close my connections after the subscriber finishes them?

Question

---

I created the following method:

public System.Data.OleDb.OleDbDataReader GetReader(string sqlQuery) { System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(); LoadConnectionStrings(); myConnection.ConnectionString = ConnectionString; myConnection.Open(); System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(sqlQuery, myConnection); System.Data.OleDb.OleDbDataReader myReader = null; myReader = myCommand.ExecuteReader(); return myReader; } 

---

Several thousand lines of code rely on it, and I think I didn’t think when I implemented it ...

Anyway,. If I get the reader this way, I have no way to close the connection, and if I close the connection before the reader calls the read () method, it will explode when it goes on reading and says that the database connection is necessary for opening.

The question is, how can I close the connection with the previous code bodies? Or all connections are generally possible ..

From what I read here , unless you specifically name 'close ()', it does not close, and if you use file access in 2003, it leaves you in a world of pain.

Answer 1

In this scenario, the connection should remain open for reading to the reader, however, there is an overload in ExecuteReader that allows you to specify a flag for reading to close the connection when the reader is closed:

 myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); 

This allows the caller to use:

 using(var reader = GetReader(query)) { //... } 

And the connection will close cleanly when the caller is done with the reader.

Answer 2

Not sure if it completely solves your problem, but you can use the IDBcommand.ExecuteReader (CommandBehavior) overload.

If you pass CommandBehavior.CloseConnection to this, the database connection will be closed when your reader is closed.

Answer 3

I agree with Mark, but I want to point out the drawback of your approach.

This mechanism hides the connection from the caller, so the caller cannot use it for other database operations. It also connects the relationship with the reader’s lifetime, so that if any other actions need to be taken, they will have to happen before deleting the reader.

Perhaps it would be better if the caller manages the connection and simply passes the connection to the GetReader method.

Answer 4

I am using something like:

 IDataReader GetReader(...) { IDbConnection connection = ...; try { IDbCommand command = ...; command.Connection = connection; connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } catch { connection.Close(); throw; } } 

This does not close IDbCommand , but in my experience this is normal as long as the connection is closed.