Opening and Closing Connections and Data Adapters

I use the Using structure:

Using con As New SQLConnection(YourConnectionString)
  con.Open
  Using cmd As New SQLCommand
    cmd.Connection= con
    cmd.CommandText = "blah blah"
    Using dt As New Datatable
      dt.Load(cmd.ExecuteReader)
      For each dtr As Datarow in dt.Rows
        <loop here
      Next
    End Using
  End Using
End Using

The Using structure will ensure your connection and command objects are disposed of properly.

        Using con As New SqlConnection(conString)
            Dim cmd As SqlCommand = New SqlCommand
            With cmd
                .CommandType = CommandType.StoredProcedure
                .CommandText = "Your stored procedure name here"
                .Connection = con
                .Connection.Open()
            End With

            Dim reader As SqlDataReader
            reader = cmd.ExecuteReader
            While reader.Read
                
                '  Do something with your data here...
                
            End While
            reader.Close()
        End Using

Select allOpen in new window

I generally use stored procedures when possible, rather than passing the query as a string. My understanding is that's less vulnerable to SQL Injection attacks... How you're using the data once you execute the query is up to you. You can fill an ADO table using the fill method you're using or you can use a SqlDataReader to iterate through the returned records like I'm showing above. Either way, if you use the "Using" method, that should automatically close the connections for you.

Ok, so based on the input is the below coding better?  If so, why is it better then my original code.  Is there an advantage to updating all of database calls this way (I have a lot of them)?  Will everything be disposed of properly this way -- even if there is a network interruption?  Thanks.

Imports System.Data.SqlClient

Public Class DB_SupplyReview_ContractSupplyHistoryAllItems


    Public Shared Function GetData(ByVal ContractID As Integer)
       

        Dim connection As String = Company_DB_Using.GetConnection
        Using con As New SqlConnection(connection)
            ''Create Select Statement
            Dim selectStatement As String _
            = "DECLARE @RC int " _
            & "DECLARE @ContractID int = " & ContractID & " " _
            & "EXECUTE @RC = cu_Todd_IMS_Supply_Orders_Contract_Profitability_SupplyHistoryAllItems @ContractID "

            Dim DataAdapter As New SqlDataAdapter(selectStatement, connection)

            Dim dt As New DataTable
            Try
                dt.Clear()
                DataAdapter.Fill(dt)

            Catch ex As Exception

                MessageBox.Show(ex.Message)
            End Try

            Return dt
        End Using



    End Function


End Class

In a nutshell, you can use the "Using" block with any object that implements IDisposable, and it should automatically dispose of the resources used by that object when you hit the End Using statement (when you're done 'using' it). More on that here. The SqlConnection class inherits DbConnection, which implements IDisposable. Likewise, the SqlCommand and DataTable classes inherit classes which implement IDisposable, so you can use Using blocks with them as well (as Scott showed in his post). The upside to using "Using" blocks is that you don't need to explicitly call "close" or "dispose", whereas you might otherwise have to do so in multiple places depending on the complexity of your code. It will automatically close connections and dispose of resources as soon as you're done using the object.

If your goal is to provide a ContactID and have your function return a Datatable, then this would probably be the "best" way:

 Public Shared Function GetData(ByVal ContractID As Integer) As Datatable
  Try
	Using con As New SqlConnection(Company_DB_Using.GetConnection)
        ''Create Select Statement
        Dim selectStatement As String _
        = "DECLARE @RC int " _
        & "DECLARE @ContractID int = " & ContractID & " " _
        & "EXECUTE @RC = cu_Todd_IMS_Supply_Orders_Contract_Profitability_SupplyHistoryAllItems @ContractID "
        con.Open
		Using cmd As New sqlCommand
			cmd.Connection = con
			Dim dt As New Datatable 
			dt.Load(cmd.ExecuteReader)
			If dt.Rows.Count>0 Then
				Return dt
			Else
				Return Nothing
			End If
		End Using
    End Using
  Catch ex At Exception
	Messagebox.Show("Error in GetData: " & ex.Tostring)
        Return Nothing
  End Try
End Function

Select allOpen in new window

That said, it looks like your Stored Procedure returns an Integer, which would result in the example code above erroring out.

I'm going to mark as complete, but it appears as if the consensus is that Using is the best and I should use ExecuteReader instead of DataAdapter.fill.  For my understanding can you please tell me why these are better?  Thanks

Thanks to both.

If u'r using datareader asynchroneously (ie. While Reader.Read ...) reader is faster and use less memory. If u'r using datatable.load method on datareader it's slower then DataAdapter.Fill(). See this.