locked
Do I need disconnect the database connection when using DAAB RRS feed

  • Question

  • User-548594559 posted
    Hello List, My question is in .net, do I need disconnect the database connection when I connect database through Data Access Application Block. If the anwser is yes, how can I do it? Thanks in advance, Peter
    Thursday, October 9, 2003 9:45 AM

All replies

  • User493446439 posted
    pxw, You need to .Close (a.k.a. disconnect) the connection whether your in the data access app block or not. I think the only time any auto disconnects of a connection happen when your using the datareader with something?? set to automatic, then when you close your datareader the connection is also closed. jc
    Thursday, October 9, 2003 10:18 AM
  • User-2044413392 posted
    It depends on where the connection is opened. If you call one of the DAAB methods with a connection string, the DAAB will manage the connection (with one exception relating to Data Readers, see below). If you pass a connection object instead of a connection string, then you are responsible for closing the connection. Even if you call SqlHelper.ExecuteReader with one of the connection string parameters, you will have to close the Data Reader in order to close the associated connection. -Weston
    Thursday, October 9, 2003 1:11 PM
  • User-548594559 posted
    Thanks Weston!
    Friday, October 10, 2003 3:51 PM
  • User-308062919 posted
    Hi I have a few things I need to make sure I am 100% about: 1) Is this the only time I need to close the connection then? And is this the correct way to do it? Dim dr as sqlDataReader dr= sqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("sqlconnect"), "usp_Segment_Get_Text", request.querystring("nav")) rptResults.DataSource = dr rptResults.DataBind() dr.close 2) I'm also using 'SQLHelper.ExecuteNonQuery' and 'SQLHelper.ExecuteScalar' do I need to close the connections here or are they looked after automatically? 3) In some cases I am using two datareaders. Should I use 'dr.close' in between the two datareaders and at the end or should I just call it once at the end? Dim dr as sqlDataReader dr= sqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("sqlconnect"), "usp_Segment_Get_Text", request.querystring("nav")) rptResults.DataSource = dr rptResults.DataBind() dr= sqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("sqlconnect"), "usp_Segment_Get_Image", request.querystring("nav")) rptImages.DataSource = dr rptImages.DataBind() I have been getting max pool size reached errors. PLEASE HELP! Richard
    Thursday, November 13, 2003 7:03 AM
  • User-308062919 posted
    Also sometimes this causes an error: dr= sqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("sqlconnect"), "usp_Results_Category_ALL_1",request.querystring("catID"),cint(sProductHeader),cint(sProductFamily),sRegion,strCountry,sIndustryHeader,sIndustry,cint(pageNumber),recordsPerPage,sortBy) rptResults.DataSource = dr rptResults.DataBind() dr.close() errors occur on dr.close() Do I need to close the datareader when I have called DataBind?
    Thursday, November 13, 2003 11:23 AM
  • User-2044413392 posted
    The DAAB will not automatically close your DataReader. It will associate the connection with the DataReader that it returns if you pass a connection string instead of a connection object. It uses the CommandBehavior.CloseConnection parameter when creating the DataReader to effect this behavior. You still have to close the DataReader to close the connection. I think you are getting max pool size reached errors because you are not closing your DataReaders and leaking connections. The issue is complicated by the fact that it appears that sometimes DataBind() will close your DataReader. See this thread for more information. I have experienced a situation where the DataBind() closed my DataReader using OracleClient and I have seen posts (including yours in this thread) where others have experienced it with SqlClient. However, I can not find anything in the documentation about this "feature" and others have said that they have not experienced it. So, the DataReader may be closed inconsistently in some DataBind() methods, but not others. The ExecuteScalar and ExecuteReader methods close the connection if you pass a connection string instead of a connection object. In the C# implementation, these methods use the using construct which closes (actually, disposes of) the connection when it goes out of scope. In the VB implementation, these methods use a try catch finally block to close the connection. -Weston
    Thursday, November 13, 2003 2:10 PM
  • User-2044413392 posted
    There seems to be some debate on this issue. See this thread. -Weston
    Thursday, November 13, 2003 2:12 PM