Doing some Winders development again.

I've been writing a C# app that is, among other things, a front-end to a few PostgreSQL databases. I'm using System.Data.Odbc and the using( OdbcConnection cn = new ...){ ... } syntax which properly automatically closes the connection.

I know my question sounds like something a lazy, careless developer might ask, but that's not really it. To be sure I'm the first to admit that *I* introduced a bug that resulted from poor attention to detail and fixed it. But it got me to thinking about how the using( OdbcConnection cn = new ...) { ... } is implemented and there is something about it I don't like that reminds me of the (imo) extreme care one must take doing XL automation coding so as to avoid Excel continuing to run in the background after your automation app is closed. The bug I introduced is analogous, imo, to the issues with XL automation.

Inside the using( OdbcConnection cn ...) fragment, I open a cursor and then use an OdbcDataReader object to iterate through the cursor. In one case, I didn't explicitly close the reader. What happens when you do that is that the containing connection is indeed closed and your object variable is set to null as expected, but the database connection remains open on the database server. This leads me to my issue with how using ( OdbcConnection cn = new ... ){} is implemented. If there are dependent objects on the OdbcConnection that are still instantiated and open when the "}" is encountered, shouldn't those dependent objects be closed and set to null before the connection object is closed and set to null?

From my POV, I don't think I'm being pedantic (or lazy, careless, stupid, etc.), it just seems to me that if object variables are declared in a block dependent upon another object that defines that block, before the container object is closed and goes out of scope, dependent objects *should* close (a sort of "Delete Cascade ..." if you will). I've already conceded it was sloppy of me not to explicitly Close() my reader object, but because I'd done that, Idle connections were left open on the database server and that happened silently.

In short, I don't think writing the code below...

1 using( OdbcConnection cn = new OdbcConnection(connString)
2 {
3 cn.Open();
4
5 using( OdbcCommand cmd = new OdbcCommand( SqlQueryString, cn )
6 {
7 OdbcDataReader reader = cmd.ExecuteReader();
8
9 while( reader.Read() )
10 {
11 --- Do something with the data
12 }
13
14 }
15
16 }

Should leave you with open connections on a database server that you cannot close because you no longer have a way to reference those connections simply because you did not include the code "reader.Close()" on line 13. Is it sloppy not to include that? Of course. But it is no LESS sloppy imo to close the OdbcCommand Object and the OdbcConnection Object and free them without closing and freeing the OdbcDataReader object.

Anybody have an opinion that?

https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcconnection?view=netframework-4.7.2

You should always explicitly close any open OdbcConnection objects by calling Close or Dispose before the OdbcConnection object goes out of scope, or by placing the connection within a Using statement.

So the Using construct on the connection object takes care of the close call when it goes out of scope.

But
https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcdatareader.close?view=netframework-4.7.2#System_Data_Odbc_OdbcDataReader_Close

You must explicitly call the Close method when you are finished using the OdbcDataReader to use the associated OdbcConnection for any other purpose.

So I guess, closing the connection falls under "using it for any other purpose" :-/

Post #425,927 by mmoffitt 10/18/18 9:20:56 AM 10/18/18 9:20:56 AM Reply	Doing some Winders development again. I've been writing a C# app that is, among other things, a front-end to a few PostgreSQL databases. I'm using System.Data.Odbc and the using( OdbcConnection cn = new ...){ ... } syntax which properly automatically closes the connection. I know my question sounds like something a lazy, careless developer might ask, but that's not really it. To be sure I'm the first to admit that I introduced a bug that resulted from poor attention to detail and fixed it. But it got me to thinking about how the using( OdbcConnection cn = new ...) { ... } is implemented and there is something about it I don't like that reminds me of the (imo) extreme care one must take doing XL automation coding so as to avoid Excel continuing to run in the background after your automation app is closed. The bug I introduced is analogous, imo, to the issues with XL automation. Inside the using( OdbcConnection cn ...) fragment, I open a cursor and then use an OdbcDataReader object to iterate through the cursor. In one case, I didn't explicitly close the reader. What happens when you do that is that the containing connection is indeed closed and your object variable is set to null as expected, but the database connection remains open on the database server. This leads me to my issue with how using ( OdbcConnection cn = new ... ){} is implemented. If there are dependent objects on the OdbcConnection that are still instantiated and open when the "}" is encountered, shouldn't those dependent objects be closed and set to null before the connection object is closed and set to null? From my POV, I don't think I'm being pedantic (or lazy, careless, stupid, etc.), it just seems to me that if object variables are declared in a block dependent upon another object that defines that block, before the container object is closed and goes out of scope, dependent objects should close (a sort of "Delete Cascade ..." if you will). I've already conceded it was sloppy of me not to explicitly Close() my reader object, but because I'd done that, Idle connections were left open on the database server and that happened silently. In short, I don't think writing the code below... 1 using( OdbcConnection cn = new OdbcConnection(connString) 2 { 3 cn.Open(); 4 5 using( OdbcCommand cmd = new OdbcCommand( SqlQueryString, cn ) 6 { 7 OdbcDataReader reader = cmd.ExecuteReader(); 8 9 while( reader.Read() ) 10 { 11 --- Do something with the data 12 } 13 14 } 15 16 } Should leave you with open connections on a database server that you cannot close because you no longer have a way to reference those connections simply because you did not include the code "reader.Close()" on line 13. Is it sloppy not to include that? Of course. But it is no LESS sloppy imo to close the OdbcCommand Object and the OdbcConnection Object and free them without closing and freeing the OdbcDataReader object. Anybody have an opinion that? bcnu, Mikem It's mourning in America again.
Post #425,928 by drook 10/18/18 9:37:39 AM 10/18/18 9:37:39 AM Reply	Should be both or neither If you need reader.close() on line 13, you should need cn.close() on 15. If cn is closed when it goes out of scope at 16, same should be true of reader at 14. Style and consistency aside, it shouldn't leave a connection around with no accessible reference to close it. -- Drew
Post #425,929 by mmoffitt 10/18/18 9:43:54 AM 10/18/18 9:43:54 AM Reply	Thanks. Another "mark the calendar day" - we're on the same page. ;0) bcnu, Mikem It's mourning in America again.
Post #425,932 by drook 10/18/18 11:32:37 AM 10/18/18 11:32:37 AM Reply	Of course we are, this time you're right :-P -- Drew
Post #425,940 by scoenye 10/18/18 7:47:47 PM 10/18/18 7:47:47 PM Reply	Ah, Microsoft... https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcconnection?view=netframework-4.7.2 You should always explicitly close any open OdbcConnection objects by calling Close or Dispose before the OdbcConnection object goes out of scope, or by placing the connection within a Using statement. So the Using construct on the connection object takes care of the close call when it goes out of scope. But https://docs.microsoft.com/en-us/dotnet/api/system.data.odbc.odbcdatareader.close?view=netframework-4.7.2#System_Data_Odbc_OdbcDataReader_Close You must explicitly call the Close method when you are finished using the OdbcDataReader to use the associated OdbcConnection for any other purpose. So I guess, closing the connection falls under "using it for any other purpose" :-/
Post #425,944 by static 10/18/18 8:50:41 PM 10/18/18 8:50:41 PM Reply	I was going to ask what the documentation said. The inconsistency of closing the reader but leaving the scoped construct close the connection seems very Microsoft-ish to me. Wade

Welcome to IWETHEY!