ODBC Query question

[D. Yates]

Is there anything wrong with immediately disposing of an OdbcCommand object
after calling its ExecuteReader method?
Yes, this example runs without error, but I'm concerned about a possible
timing issue in which garbage collection might destroy the OdbCommand object
when in fact it is still being referenced by the OdbcDataReader object (if
that's the case).

For example:

public static OdbcDataReader ExecuteReader(OdbcConnection aConnection,
string sSQL, UlSimpleMsgDelegate errorCallBack)
{
OdbcDataReader result;

try
{
OdbcCommand cmd = aConnection.CreateCommand();
try
{
cmd.CommandText = sSQL;
result = cmd.ExecuteReader();
}
finally
{
cmd.Dispose();
cmd = null;
}
}
catch(Exception e)
{
if (errorCallBack != null)
{
errorCallBack("Error Message : " + e.Message);
errorCallBack("SQL : " + sSQL);
errorCallBack("Stack Trace: " + e.StackTrace);
}
throw e;
}

return result;
}
Thanks,
Dave

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

Dave,

I don't believe so. When you call Dispose on the command, it should not
dispose of the underlying connection. Once you obtain the reader, there is
little use for the command anymore, since the reader is just dealing with
streaming the result.

The only thing that I would be worried about is the connection state of
the connection, making sure it is closed properly.

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

"D. Yates" <fo****@hotmail.com> wrote in message
news:OV**************@TK2MSFTNGP14.phx.gbl...

Is there anything wrong with immediately disposing of an OdbcCommand
object
after calling its ExecuteReader method?
Yes, this example runs without error, but I'm concerned about a possible
timing issue in which garbage collection might destroy the OdbCommand
object
when in fact it is still being referenced by the OdbcDataReader object (if
that's the case).

For example:

public static OdbcDataReader ExecuteReader(OdbcConnection
aConnection,
string sSQL, UlSimpleMsgDelegate errorCallBack)
{
OdbcDataReader result;

try
{
OdbcCommand cmd = aConnection.CreateCommand();
try
{
cmd.CommandText = sSQL;
result = cmd.ExecuteReader();
}
finally
{
cmd.Dispose();
cmd = null;
}
}
catch(Exception e)
{
if (errorCallBack != null)
{
errorCallBack("Error Message : " + e.Message);
errorCallBack("SQL : " + sSQL);
errorCallBack("Stack Trace: " + e.StackTrace);
}
throw e;
}

return result;
}
Thanks,
Dave

[D. Yates]

Thanks for your replay, Nicholas.

Yes, we do dispose of the OdbcConnection properly before exiting the
application.

We were just looking to create a generic ExecuteReader method that would log
any errors, the stack trace and the SQL that caused the problem without
repeating the same code all over the place.

Thanks again.

Dave
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:OG**************@TK2MSFTNGP11.phx.gbl...

Dave,

I don't believe so. When you call Dispose on the command, it should not dispose of the underlying connection. Once you obtain the reader, there is little use for the command anymore, since the reader is just dealing with
streaming the result.

The only thing that I would be worried about is the connection state of the connection, making sure it is closed properly.

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

"D. Yates" <fo****@hotmail.com> wrote in message
news:OV**************@TK2MSFTNGP14.phx.gbl...

Is there anything wrong with immediately disposing of an OdbcCommand
object
after calling its ExecuteReader method?
Yes, this example runs without error, but I'm concerned about a possible
timing issue in which garbage collection might destroy the OdbCommand
object
when in fact it is still being referenced by the OdbcDataReader object (if that's the case).

For example:

public static OdbcDataReader ExecuteReader(OdbcConnection
aConnection,
string sSQL, UlSimpleMsgDelegate errorCallBack)
{
OdbcDataReader result;

try
{
OdbcCommand cmd = aConnection.CreateCommand();
try
{
cmd.CommandText = sSQL;
result = cmd.ExecuteReader();
}
finally
{
cmd.Dispose();
cmd = null;
}
}
catch(Exception e)
{
if (errorCallBack != null)
{
errorCallBack("Error Message : " + e.Message);
errorCallBack("SQL : " + sSQL);
errorCallBack("Stack Trace: " + e.StackTrace);
}
throw e;
}

return result;
}
Thanks,
Dave

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

Dave,

That should be fine, but I would favor the use of the "using" statement
over generating your own try/finally clause.

You can replace:

OdbcCommand cmd = aConnection.CreateCommand();
try
{
cmd.CommandText = sSQL;
result = cmd.ExecuteReader();
}
finally
{
cmd.Dispose();
cmd = null;
}

With:

using (OdbcCommand cmd = aConnection.CreateCommand())
{
cmd.CommandText = sSQL;
result = cmd.ExecuteReader();
}

The second looks MUCH better to me.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"D. Yates" <fo****@hotmail.com> wrote in message
news:uF*************@TK2MSFTNGP12.phx.gbl...

Thanks for your replay, Nicholas.

Yes, we do dispose of the OdbcConnection properly before exiting the
application.

We were just looking to create a generic ExecuteReader method that would
log
any errors, the stack trace and the SQL that caused the problem without
repeating the same code all over the place.

Thanks again.

Dave
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:OG**************@TK2MSFTNGP11.phx.gbl...

Dave,

I don't believe so. When you call Dispose on the command, it should

not

dispose of the underlying connection. Once you obtain the reader, there

is

little use for the command anymore, since the reader is just dealing with
streaming the result.

The only thing that I would be worried about is the connection state

of

the connection, making sure it is closed properly.

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

"D. Yates" <fo****@hotmail.com> wrote in message
news:OV**************@TK2MSFTNGP14.phx.gbl...

> Is there anything wrong with immediately disposing of an OdbcCommand
> object
> after calling its ExecuteReader method?
> Yes, this example runs without error, but I'm concerned about a
> possible
> timing issue in which garbage collection might destroy the OdbCommand
> object
> when in fact it is still being referenced by the OdbcDataReader object (if > that's the case).
>
> For example:
>
> public static OdbcDataReader ExecuteReader(OdbcConnection
> aConnection,
> string sSQL, UlSimpleMsgDelegate errorCallBack)
> {
> OdbcDataReader result;
>
> try
> {
> OdbcCommand cmd = aConnection.CreateCommand();
> try
> {
> cmd.CommandText = sSQL;
> result = cmd.ExecuteReader();
> }
> finally
> {
> cmd.Dispose();
> cmd = null;
> }
> }
> catch(Exception e)
> {
> if (errorCallBack != null)
> {
> errorCallBack("Error Message : " + e.Message);
> errorCallBack("SQL : " + sSQL);
> errorCallBack("Stack Trace: " + e.StackTrace);
> }
> throw e;
> }
>
> return result;
> }
>
>
> Thanks,
> Dave
>
>