return datareader and close connection

[Craig]

I have some methods that open a database connection, get some data and then
return a datareader. How do I manage closing the connection to the database
then?

public OracleDataReader ExecuteCommand(string cmdStr) {
Connect();
OracleCommand cmd = new OracleCommand(cmdStr, this._conn);
OracleDataReader reader = cmd.ExecuteReader();
return reader;
}

What's the correct way to do this, even if it means totally changing this
method?

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You can't a DataReader is a server side , forward only cursor. The
connection will be open until you close the Reader.

Therefore you have to use it very carefully.
Tip:
You could use ExecuteReader( CommandBehaviour.CloseConnection)

In this case once you close the reader the connection will be close too.

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


"Craig" <Cr***@discussions.microsoft.com> wrote in message
news:76**********************************@microsof t.com...

I have some methods that open a database connection, get some data and then
return a datareader. How do I manage closing the connection to the
database
then?

public OracleDataReader ExecuteCommand(string cmdStr) {
Connect();
OracleCommand cmd = new OracleCommand(cmdStr, this._conn);
OracleDataReader reader = cmd.ExecuteReader();
return reader;
}

What's the correct way to do this, even if it means totally changing this
method?

[Christof Nordiek]

"Craig" <Cr***@discussions.microsoft.com> schrieb im Newsbeitrag
news:76**********************************@microsof t.com...

I have some methods that open a database connection, get some data and then
return a datareader. How do I manage closing the connection to the
database
then?

public OracleDataReader ExecuteCommand(string cmdStr) {
Connect();
OracleCommand cmd = new OracleCommand(cmdStr, this._conn);
OracleDataReader reader = cmd.ExecuteReader();
return reader;
}

What's the correct way to do this, even if it means totally changing this
method?

Since the datareader has to remain open while the datareader is open, you
have to
wait for the caller using the datareader to close it.
You should call ExecuteReader(CommandBehavior.CloseConnection)
this will close the Connection when the reader is closed.
Still the caller is responsable for closing the reader.

[Nicholas Paldino [.NET/C# MVP]]

Craig,

You can definitely follow Christof's and Ignatio's advice. However, if
you don't trust the code that is calling this method (because you don't want
it to be able to access your database through the connection), their
recommendations won't work. Rather, you will have to encapsulate your
operations in the method, and not return the data reader.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Craig" <Cr***@discussions.microsoft.com> wrote in message
news:76**********************************@microsof t.com...

I have some methods that open a database connection, get some data and then
return a datareader. How do I manage closing the connection to the
database
then?

public OracleDataReader ExecuteCommand(string cmdStr) {
Connect();
OracleCommand cmd = new OracleCommand(cmdStr, this._conn);
OracleDataReader reader = cmd.ExecuteReader();
return reader;
}

What's the correct way to do this, even if it means totally changing this
method?