none
'Using' statement - does it close DB2 connection as well? RRS feed

  • Question

  • Hi All,

    The following link shows usage of ‘Using’ statement to declare SQLConnection.

    http://msdn.microsoft.com/en-us/library/wydd5hkd.aspx

    Can we use also use ‘Using’ statement to declare and dispose a DB2 Connection as well? We have a web application with huge number of users access it at the same time. The application brought down the database during its peak usage and we found that there we lot of open connections.

    We are using IBM.Data.DB2.DB2Connection class to connect to DB2 database. Can ‘Using’ handle this DB2 Connection in the same way as SQLConnection as explained in the link above. Do we explicitly need to dispose the connection as we used to perform with try..catch..finally. Can we rely on ‘Using’ statement when we have large set of users requesting for connection simultaneously?

    Please clarify.

    Thanks,

    Ramya


    • Edited by RamyaNayak Tuesday, August 14, 2012 8:29 AM
    Tuesday, August 14, 2012 5:04 AM

Answers

  • Yes, it is disposing afterwards. Please see http://msdn.microsoft.com/en-us/library/htd05whh.aspx for a description of the using block in Visual Basic.

    Extract:
    A Using block has three parts: acquisition, usage, and disposal.
    ...
    Disposal means calling the Dispose method on the object in resourcename. This allows the object to cleanly terminate its resources. The End Using statement disposes of the resources under the Using block's control.

    With kind regards,

    Konrad

    • Marked as answer by RamyaNayak Tuesday, August 14, 2012 11:17 AM
    Tuesday, August 14, 2012 10:50 AM

All replies

  • Dispose does not automaticlly close a connection unless close is implimented in the dispose function.

    The using statement does two things

    1) Creates a local variable which is a pointer to the original object (not a copy of the object). so when you change the variable in the using structure you also change the original object

    2) The using variable is like a short cut so instead of typing a long variable name you can type a much shorter string.  It is equivalent to using a "var".

    When a function/method executes the "local" variables that are used are placed on the execution stack.   When the function/method finishes executing the stack is automatically restored to the same location where the stack was located before the function/method started which automatically disposes the local variables.

    Static variables are different from local variables because they maintain there values forever until the static variable are deallocated.  The static variables are in global memory space (not on execution stack.  The dispose method deallocates the static variables associated with a class.

    A connection creates events that are linked onto the ethernet service routine.  When a connection is closed the the events are removed from the ethernet service routine.  The dispose method only removes the static variables and not necessarily the events (unless the close connection is in the dispose method).  The dispose also will not close the far end of the connection.


    jdweng

    Tuesday, August 14, 2012 9:13 AM
  • Thanks for the response Joel.

    http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.dndp.doc/htm/frlrfIBMDataDB2DB2ConnectionClassTopic.htm

    The link above from IBM says calling .dispose also calls DB2Connection.Close. So this means that .dispose has .close implemeted with it. Which probably means that if is safe to use 'Using' with DB2Connection class as well.


    Ramya


    • Edited by RamyaNayak Tuesday, August 14, 2012 9:55 AM
    Tuesday, August 14, 2012 9:43 AM
  • No! The Using statement is not calling Dispose.  the code below will close the connection.

    Imports System.Data.SqlClient
    Module Module1
        Sub Main()
            Dim abc As class1 = New class1
            abc.AccessSql("xyz")
            abc.class1_dispose()
        End Sub
        Class class1
            Public sqc As SqlConnection = New System.Data.SqlClient.SqlConnection(s)
            Public Sub AccessSql(ByVal s As String)
                Using sqc
                    MsgBox("Connected with string """ & sqc.ConnectionString & """")
                End Using
            End Sub
            Public Sub class1_dispose()
                sqc.Close()
            End Sub
        End Class
    End Module


    jdweng

    Tuesday, August 14, 2012 10:13 AM
  • Hi Joel,

    I think he does not meant the using on top of the file to get namespaces in or define an alias for a namespace.

    Hi Ramya,

    the using statement is exactly what you are looking for! It will make sure that Dispose gets called.

    But regarding the Database Connection: It does not always means that a connection is closed. There is a pattern called Connection Pooling. If that is enabled, the connections are maintained and reused. For SQL Server that is a default and could be changed or switched off inside the connection string. But that depends on the implementation and please do not get confused by this information. If the vendor told you to dispose: Just use the using statement.

    With kind regards,

    Konrad

    Tuesday, August 14, 2012 10:48 AM
  • Yes, it is disposing afterwards. Please see http://msdn.microsoft.com/en-us/library/htd05whh.aspx for a description of the using block in Visual Basic.

    Extract:
    A Using block has three parts: acquisition, usage, and disposal.
    ...
    Disposal means calling the Dispose method on the object in resourcename. This allows the object to cleanly terminate its resources. The End Using statement disposes of the resources under the Using block's control.

    With kind regards,

    Konrad

    • Marked as answer by RamyaNayak Tuesday, August 14, 2012 11:17 AM
    Tuesday, August 14, 2012 10:50 AM
  • I agree but you have to go to a few different webpages to figure all this out.

    First you have to know that the "Using" statment automatically called the dispose method.  I don't think most people know this fact.

    Second you have to know that the dispose is euqivalent to the close method.  Again not obvious as it says below (taken from a webpage).

    Third the dispose/close it is not automatic as stated below.

    If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is actually closed.


    jdweng

    Tuesday, August 14, 2012 11:37 AM
  • Hi Joel,

    you might be correct: A lot of people do not know about the Disposing stuff. It is a big problem in my eyes because it is a very important thing. Small applications might work without it because they might get closed quite often but as soon as you build long running processes you quickly end up with a memory leak.

    So whenever I come over this point I direcly point out that people should read about it. And I even set it up as a rule: "Always dispose IDisposable objects!". I might be to harsh with this point sometimes but in my eyes the importantce is that high that it is really required.

    And yes: Software Development is something that requires a lot of knowledge! you simply need to look up carefully what you need and what you are doing. Repairing a car is very complex. You cannot do that at all without deep knowledge about how to repair your car. You might start slowly (Check the oil and refill it if required) but simply trying to work on the motor - I think nobody would do that :)

    But I am quite sure: People with experience in software development (who maybe work as "professional" software engineer) simply know these things. It is something that they came over and then simply remembered it and where it is a basic thing in their daily work (Database connections with their behaviour and disposable objects are some basic things that you need quite often if you develop enterprise applications). At least I expect that people know such stuff. But of course: Some does not know it and whenever we had an interview with an applicant we asked about such basics, too.

    With kind regards,

    Konrad

    Tuesday, August 14, 2012 12:05 PM