Re: How to close connection?

"aspfun via DotNetMonster.com" <u53138@uwe> wrote in message
news:9e871b9bd80e9@uwe...
>I used code below to close connection, is it right?
>
> Try
> myConn.Open()
> mySQLCommand.ExecuteNonQuery()
> Catch ex As Exception
> 'code to display error message
> Finally
> myConn.Close()
> mySQLCommand.Dispose()
> myConn.Dispose()
> End Try
>
> Does it need " mySQLCommand.Dispose()"?
>
> --
> Message posted via http://www.dotnetmonster.com

If you are going to dispose the connection, then that will cause it to be
closed if needed, so closing and disposing the connection, while not
incorrect, is overkill.

Also, since your command is tied to an explicit connection, there isn't a
need to dispose of the command. Again, it's not wrong, just overkill in
this case.

You could avoid the dispose calls completely, by declaring your connection
in a Using block as in:

Using myConn As New SqlConnection(.....)

Try
myConn.Open()
mySQLCommand.ExecuteNonQuery()
myConn.Close()
Catch ex As Exception
'code to display error message
Finally
myConn.Close()
End Try

End Using


The reason for still calling Close in the Try and the Finally is that you
want to close your connections as soon as you possibly can. So, if all goes
well, your connection will be closeed at the end of the Try, but if there is
an exception before the Try block's Close call, then the connection will
still be closed in the Finally, which might still be several lines of code
away from the End Using block.

-Scott

Scott M.

"aspfun via DotNetMonster.com" <u53138@uwe> wrote in message
news:9e8785c960052@uwe...
> Scott M. wrote:
>>>I used code below to close connection, is it right?
>>>
>>[quoted text clipped - 10 lines]
>>>
>>> Does it need " mySQLCommand.Dispose()"?
>>
>>If you are going to dispose the connection, then that will cause it to be
>>closed if needed, so closing and disposing the connection, while not
>>incorrect, is overkill.
>>
>>Also, since your command is tied to an explicit connection, there isn't a
>>need to dispose of the command. Again, it's not wrong, just overkill in
>>this case.
>>
>>You could avoid the dispose calls completely, by declaring your connection
>>in a Using block as in:
>>
>>Using myConn As New SqlConnection(.....)
>>
>> Try
>> myConn.Open()
>> mySQLCommand.ExecuteNonQuery()
>> myConn.Close()
>> Catch ex As Exception
>> 'code to display error message
>> Finally
>> myConn.Close()
>> End Try
>>
>>End Using
>>
>>The reason for still calling Close in the Try and the Finally is that you
>>want to close your connections as soon as you possibly can. So, if all
>>goes
>>well, your connection will be closeed at the end of the Try, but if there
>>is
>>an exception before the Try block's Close call, then the connection will
>>still be closed in the Finally, which might still be several lines of code
>>away from the End Using block.
>>
>>-Scott
>
>
> Thank you, Scott.
> I am confused how to add "using..."
> Before "try...finally", I have code like below:
>
> Dim myDSN As String =
> ConfigurationManager.AppSettings("ConnectionString ")
> Dim myConn As New SqlConnection(myDSN)
> Dim mySQLCommand As New SqlCommand("sUM_updateauthmasters", myConn)
> mySQLCommand.CommandType = CommandType.StoredProcedure
> ...code to add parameters...
>
> If I want to add "using...." block as you suggested, where to add it?
>
> --
> Message posted via DotNetMonster.com
> http://www.dotnetmonster.com/Uwe/For...p-net/200911/1

Using is an alternative way to either declare a new object variable or
indicate the scope that an existing one should be used, so you could do
either of these:

Dim myDSN As String =
ConfigurationManager.AppSettings("ConnectionString ")

'Here the keyword Using replaces the keyword Dim
'so the connection is not only declared, but the scope of its inteneded
'use is also declared. When the End Using statement is reached
'the connection's dispose method will automatically be called.

Using myConn As New SqlConnection(myDSN)
Dim mySQLCommand As New SqlCommand("sUM_updateauthmasters", myConn)
mySQLCommand.CommandType = CommandType.StoredProcedure

Try
myConn.Open()
mySQLCommand.ExecuteNonQuery()
myConn.Close()
Catch ex As Exception
'code to display error message
Finally
myConn.Close()
End Try
End Using

************************************************** **********************

Dim myDSN As String =
ConfigurationManager.AppSettings("ConnectionString ")
Dim myConn As New SqlConnection(myDSN)
Dim mySQLCommand As New SqlCommand("sUM_updateauthmasters", myConn)
mySQLCommand.CommandType = CommandType.StoredProcedure

'Here, the variable was declared normally, but Using is used to
'denote the scope with with the connection will be used.
Using myConn
Try
myConn.Open()
mySQLCommand.ExecuteNonQuery()
myConn.Close()
Catch ex As Exception
'code to display error message
Finally
myConn.Close()
End Try
End Using


-Scott

Scott M.