I'm trying to understand the sql connection a little better. I have some code that returns data via a stored procedure on the sql server. Here is the code to execute the procedure and put the data in a data grid:
Dim tblSteps As DataTable = New DataTable Dim cnPMSQL As New SqlConnection("Data Source=source;" _ + "Initial Catalog=db;" _ + "Persist Security Info=True;" _ + "User ID=username;" _ + "Password=password") Private Sub cmdExecuteSproc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecuteSproc.Click Dim spSteps As New SqlCommand("a_stored_procedure", cnPMSQL) spSteps.Parameters.AddWithValue("@data_required", txtShopOrder.Text) spSteps.CommandType = CommandType.StoredProcedure Dim adapter As SqlDataAdapter = New SqlDataAdapter(spSteps) tblSteps.Clear() adapter.Fill(tblSteps) dgSteps.DataSource = tblSteps End Sub
This code works fine but, I also would like to indicate the status of a sql connection using the connections ConnectionState property. So here's the part I don't understand. If I specifically use a cnPMSQL.Open() statement the ConnectionState returns ConnectionState.Open. If I don't the data is still retrieved from the server as expected but the ConnectionState never shows open.
So, my first question is do what purpose the open and close methods serve and the second is how can I include an sql connection status message for my program.