|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Do I need to close DataReader explicitly?automatically closed without explicitly closing the reader? I have an ASP.NET app that on a daily basis generates 'there is already an open DataReader ...' exception. I search the code to see where I am calling a DataReader explicitly. I found this piece of code: using(SqlConnection conn = new SqlConnection...) { using(SqlCommand cmd = new SqlCommand...) { .... SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while(rdr.Read()) { ... } rdr.Close(); .... } } If an exception occurs after acquiring a reader but before closing the reader explicitly, the connection object should be properly closed and disposed since I am using 'using' statement. I am assuming that the active reader on the connection should be closed with the connection as well right? or Do I still need to catch the exception and close the reader explicitly by rdr.Close(); ? Thanks. CloseConnection doesn't close the connection automatically, it closes the
connection when the DataReader is closed. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommandbehaviorclasstopic.asp You're right about the connection and 'using' statement, however I would recommend explicitly closing the connection in a finally block (and yes, you will need to close your reader) just so that your intention is clear. This recommendation is solely for code readability and while many may argue this makes code less readable, it does show intent much better and for that reason, I recommend doing it. This of course is a matter of personal preference. Show quote "Matt" <M***@discussions.microsoft.com> wrote in message news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com... > If I close the connection, would the reader already open on the connection > be > automatically closed without explicitly closing the reader? I have an > ASP.NET app that on a daily basis generates 'there is already an open > DataReader ...' exception. I search the code to see where I am calling a > DataReader explicitly. I found this piece of code: > > using(SqlConnection conn = new SqlConnection...) > { > using(SqlCommand cmd = new SqlCommand...) > { > .... > SqlDataReader rdr = > cmd.ExecuteReader(CommandBehavior.CloseConnection); > while(rdr.Read()) > { > ... > } > rdr.Close(); > .... > } > } > > If an exception occurs after acquiring a reader but before closing the > reader explicitly, the connection object should be properly closed and > disposed since I am using 'using' statement. I am assuming that the active > reader on the connection should be closed with the connection as well > right? > or Do I still need to catch the exception and close the reader explicitly > by > rdr.Close(); > ? > > Thanks. Bill,
The last who would arguing in that recommendation from you is probably me. I find data retrieval statements without a catch a gruel. Just my 2 eurocents Cor Show quote "W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> schreef in bericht news:ObHaRW6kGHA.1244@TK2MSFTNGP05.phx.gbl... > CloseConnection doesn't close the connection automatically, it closes the > connection when the DataReader is closed. > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommandbehaviorclasstopic.asp > > You're right about the connection and 'using' statement, however I would > recommend explicitly closing the connection in a finally block (and yes, > you will need to close your reader) just so that your intention is clear. > This recommendation is solely for code readability and while many may > argue this makes code less readable, it does show intent much better and > for that reason, I recommend doing it. This of course is a matter of > personal preference. > "Matt" <M***@discussions.microsoft.com> wrote in message > news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com... >> If I close the connection, would the reader already open on the >> connection be >> automatically closed without explicitly closing the reader? I have an >> ASP.NET app that on a daily basis generates 'there is already an open >> DataReader ...' exception. I search the code to see where I am calling a >> DataReader explicitly. I found this piece of code: >> >> using(SqlConnection conn = new SqlConnection...) >> { >> using(SqlCommand cmd = new SqlCommand...) >> { >> .... >> SqlDataReader rdr = >> cmd.ExecuteReader(CommandBehavior.CloseConnection); >> while(rdr.Read()) >> { >> ... >> } >> rdr.Close(); >> .... >> } >> } >> >> If an exception occurs after acquiring a reader but before closing the >> reader explicitly, the connection object should be properly closed and >> disposed since I am using 'using' statement. I am assuming that the >> active >> reader on the connection should be closed with the connection as well >> right? >> or Do I still need to catch the exception and close the reader explicitly >> by >> rdr.Close(); >> ? >> >> Thanks. > > Code readibilty aside, from the operational point of view, if an exception
occurs in the code that I posted, the connection will be closed and along with it the reader. I do not need to explicitly close the reader, right? The connection, the command, and the reader objects should be properly closed and disposed of and should not have any ill effects. This would rule out the possibility of this piece of code returning connections back to the pool with an open reader which would later cause the ' already an open DataReader' exception. Show quote "W.G. Ryan - MVP" wrote: > CloseConnection doesn't close the connection automatically, it closes the > connection when the DataReader is closed. > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommandbehaviorclasstopic.asp > > You're right about the connection and 'using' statement, however I would > recommend explicitly closing the connection in a finally block (and yes, you > will need to close your reader) just so that your intention is clear. This > recommendation is solely for code readability and while many may argue this > makes code less readable, it does show intent much better and for that > reason, I recommend doing it. This of course is a matter of personal > preference. > "Matt" <M***@discussions.microsoft.com> wrote in message > news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com... > > If I close the connection, would the reader already open on the connection > > be > > automatically closed without explicitly closing the reader? I have an > > ASP.NET app that on a daily basis generates 'there is already an open > > DataReader ...' exception. I search the code to see where I am calling a > > DataReader explicitly. I found this piece of code: > > > > using(SqlConnection conn = new SqlConnection...) > > { > > using(SqlCommand cmd = new SqlCommand...) > > { > > .... > > SqlDataReader rdr = > > cmd.ExecuteReader(CommandBehavior.CloseConnection); > > while(rdr.Read()) > > { > > ... > > } > > rdr.Close(); > > .... > > } > > } > > > > If an exception occurs after acquiring a reader but before closing the > > reader explicitly, the connection object should be properly closed and > > disposed since I am using 'using' statement. I am assuming that the active > > reader on the connection should be closed with the connection as well > > right? > > or Do I still need to catch the exception and close the reader explicitly > > by > > rdr.Close(); > > ? > > > > Thanks. > > > I would put reader in a try/finally or using block, too.
-- Show quoteMiha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Matt" <M***@discussions.microsoft.com> wrote in message news:53CEA0AE-F1B5-4B5D-B069-A9933C909291@microsoft.com... > Code readibilty aside, from the operational point of view, if an exception > occurs in the code that I posted, the connection will be closed and along > with it the reader. I do not need to explicitly close the reader, right? > The > connection, the command, and the reader objects should be properly closed > and > disposed of and should not have any ill effects. > > This would rule out the possibility of this piece of code returning > connections back to the pool with an open reader which would later cause > the > ' already an open DataReader' exception. > > > "W.G. Ryan - MVP" wrote: > >> CloseConnection doesn't close the connection automatically, it closes the >> connection when the DataReader is closed. >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommandbehaviorclasstopic.asp >> >> You're right about the connection and 'using' statement, however I would >> recommend explicitly closing the connection in a finally block (and yes, >> you >> will need to close your reader) just so that your intention is clear. >> This >> recommendation is solely for code readability and while many may argue >> this >> makes code less readable, it does show intent much better and for that >> reason, I recommend doing it. This of course is a matter of personal >> preference. >> "Matt" <M***@discussions.microsoft.com> wrote in message >> news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com... >> > If I close the connection, would the reader already open on the >> > connection >> > be >> > automatically closed without explicitly closing the reader? I have an >> > ASP.NET app that on a daily basis generates 'there is already an open >> > DataReader ...' exception. I search the code to see where I am calling >> > a >> > DataReader explicitly. I found this piece of code: >> > >> > using(SqlConnection conn = new SqlConnection...) >> > { >> > using(SqlCommand cmd = new SqlCommand...) >> > { >> > .... >> > SqlDataReader rdr = >> > cmd.ExecuteReader(CommandBehavior.CloseConnection); >> > while(rdr.Read()) >> > { >> > ... >> > } >> > rdr.Close(); >> > .... >> > } >> > } >> > >> > If an exception occurs after acquiring a reader but before closing the >> > reader explicitly, the connection object should be properly closed and >> > disposed since I am using 'using' statement. I am assuming that the >> > active >> > reader on the connection should be closed with the connection as well >> > right? >> > or Do I still need to catch the exception and close the reader >> > explicitly >> > by >> > rdr.Close(); >> > ? >> > >> > Thanks. >> >> >>  |
|||||||||||||||||||||||
Other interesting topics