Monitoring SQL Connectivity

[avgVBUser]

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.

[TomW]

You should always open & close the connects as needed and not leave it open for the life of the program. Otherwise you are not optimizing connection pooling.

You call to your stored procedure was made with a dataadapter. When you use a dataadapter, it will actually open the connection to make the call and close it afterwards.

For example, you could execute commands directly to the database from the command object, without ever using a dataadapter but with the command object you will need to explicitly tell it to open & close.

[avgVBUser]

You should always open & close the connects as needed and not leave it open for the life of the program. Otherwise you are not optimizing connection pooling.

You call to your stored procedure was made with a dataadapter. When you use a dataadapter, it will actually open the connection to make the call and close it afterwards.

For example, you could execute commands directly to the database from the command object, without ever using a dataadapter but with the command object you will need to explicitly tell it to open & close.

Thanks Tom. That's exactly the info I was looking for.

[sknake]

You should also consider wrapping your SqlCommand, SqlConnection, etc members in a using() clause to ensure IDisposable.Dispose() is called. These data accessors use unmanaged resources so you should ensure that clean up takes place as soon as possible.

Please mark this thread as solved since TomW did an excellent job answering your question ;) and good luck!

[TomW]

Excellent advice about the Using clause(s). I never (at least since Ive learned better) define an sql connection outside of the individual sub that it is being used, and that within a using clause. I store the connection string in a global variable.

Below is an example of a call I just finished for my own program. It does the same as yours, calls a stored procedure (SP) to return a result set. Well Im using the single SP and call to return multiple result sets.

The results will fill two different tables within my dataset appropiatley. The only difference in the coding, since multiple results are being returned is that you have to tell it exactly which result set goes to which dataset table. This can be done thru the DataAdapters TableMappings method.

The SP returns the results in the order the queries were written and assigns the name "Table" to the first result set, name "Table1" to the second, "Table2" to the third etc...

Private Sub cmdExecuteSproc_Click()

        Using con As New SqlConnection(g_strDbConnection)
            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter

            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spOrdersCustomersSelect"
            cmd.Parameters.AddWithValue("@PkId", txtPkId.Text)
            cmd.Parameters.AddWithValue("@CustKey", CInt(txtCust.Text))

            da.TableMappings.Add("Table", "tblCustomers")
            da.TableMappings.Add("Table1", "tblOrders")
            da.SelectCommand = cmd
            da.Fill(m_ds)

            da.Dispose()
            cmd.Dispose()
        End Using 

End Sub

As mentioned by Sknake there are benefits to the using block, if your db connection is closed it will open it for you (similar to the DataAdapter). At the end of the block, it will automatically close the connection and dispose of it. Likewise since my DA & CMD were both declared within the block, there life cycles only last the length of the block itself. As a side note use of the USING block is not limited to database connections, I use them for all sorts of things, file reads, objects etc.

Previously I used to use a single global db connection in all my projects, even if I open & closed them properly. It just didnt make sense to me (still doesnt really) that creating so many seperate connection objects can be more efficient and faster then a single object that is already in memory. So I made a seperate test project that compared large loops, each making large db transactions, the one with the single DB connection and the other creating with each itteration of the loop. The results suprised me, creating thousands of individual connection objects actually proved to be faster then the single global connection.

[avgVBUser]

Thanks to both of you. Very helpful and useful information.