Close question

[Woody Splawn]

I have some code which I will show below that opens a SQLDataReader, gets
some information and then closes. My question is, what is the proper way to
close? Do I close the reader first and then attempt to close SQLCommand and
the SQLConnection? Do I close the SQLCommand and the SQLConnection and then
the SQLDataReader? Do I need to check the conneciton state for each?

What is the proper way?

mySqlConnection = New SqlConnection(myConnectionString)
Dim sql_Command As New SqlCommand( _
"Select * from VSPW", _
mySqlConnection)

Try
mySqlConnection.Open()
Dim myReader As SqlDataReader =
sql_Command.ExecuteReader(CommandBehavior.CloseConnection)

While myReader.Read()
psMasterPW = myReader.GetString(0) ' The first field in the
answer set
psEditARPW = myReader.GetString(1) 'The second field in the
answer set
psPrintPW = myReader.GetString(2) 'The third field in the
answer set
End While
'myReader.Close()
'sql_Command.Connection.Close()
'If mySqlConnection.State = ConnectionState.Open Then
' mySqlConnection.Close()
'End If

Catch ex As SqlException
MessageBox.Show(ex.ToString, "Exception Error")
Finally
If sql_Command.Connection.State = ConnectionState.Open Then
sql_Command.Connection.Close()
End If
If mySqlConnection.State = ConnectionState.Open Then
mySqlConnection.Close()
End If
sql_Command.Connection.Close()
End Try

 

[William Ryan eMVP]

Fire your reader specifying the CommandBehavior.CloseConnection enum
http://www.knowdotnet.com/articles/schemas2.html
And then just close your connection afterward. The only thing you'll
actually close is the connection and datareader not the command.

HTH,

Bill

 

[Peter Huang]

Hi Woody

Thanks for posting in the community.

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you wants to know close the
datareader and its according resource.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

I agree with Bill's suggestion, you do not need to close the SQLCommand.
Its Connection is the same one the sqlcommand are using.

SqlCommand.Connection Property .
Gets or sets the SqlConnection used by this instance of the SqlCommand.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlcommandclassconnectiontopic.asp

Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpcontheadonetdatareader.asp

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang]

Hi Woody

Thanks for posting in the community.

Did my suggestion help you?
If you have any concern on this issue, please post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.