Oct 27, 2014 11:12 AM|A2H|LINK
IMHO I don't think there is anything wrong in checking the state prior to open the connection. However you need to ensure that your connection is always opened prior to executing the command.
If cnn.State = connectionState.Open Then cnn.Close()
Instead of closing connection like given above you can use the Using statement which will ensure that connection object will be automatically disposed.
'Get your connection string from config file
Dim strConn As String = ConfigurationManager.ConnectionStrings("Connection").ConnectionString.ToString()
'Create Connection object here
Using cnn As New SqlConnection(strConn)
If cnn.State <> ConnectionState.Open Then
'Open your connection
'Your Select Query
Dim selectString As String = "SELECT COUNT(*) FROM YourTable"
'Create COmmand object
Dim myCommand As New SqlCommand(selectString, cnn)
' Get the Result query
' You can do your task here
End Using 'You dont need to close your connection explicity it will be closed here
By making use of Using keyword You can automatically dispose objects that implement the IDisposable interface by using the using statement.when
you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method
on the object in the correct way.The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object.