vb.net cycle through query results

Question

I am familiar with the VB6 ADO way of dealing with SQL queries and looping through the record set results.

However, what is the correct way to query a server, cycle through the results, and dispose of my query in VB.Net? All the ways I have been using seem to be unstable and crash randomly.

I have been using the following code:

Public Function GetSQLTable(ByVal strSQL As String) As DataTable
    Dim table As New DataTable
    Dim adapt As SqlDataAdapter

    Try
        adapt = New SqlDataAdapter(strSQL, gconIntegration)
        adapt.Fill(table)
    Catch ex As Exception
        LogError("GetSQLTable: " & ex.ToString(), "SQL: " & strSQL)
    End Try

    Return table
End Function

And using it like this:

 Dim dt As DataTable
 Dim lngRow As Long
 Dim current As DataRow
 Dim lngContact As long

 Try
        dt = GetSQLTable(strSQL)
        For lngRow = 0 To dt.Rows.Count - 1
            current = dt.Rows.Item(lngRow)
            lngContact = current.Item("indvid") 
            DoSomething(lngContact)
        Next
Catch ex As Exception
    LogError("FindContact: " & ex.ToString(), "SQL: " & strSQL)
    lngContact = -1     
 Finally
    current = nothing
    dt = nothing

Answer 1

I suspect the problem has to do with how you manage your gconIntegration connection. You're trying too hard to keep using that same connection. It would be helpful to see where it lives.

Better to get "new" connections from the pool and let .Net worry about it for you.

Also, your generic "GetSQLTable" code is missing an important part: it makes no allowance for setting parameters, which tells me you're building them directly into your query strings. That's a recipe for disaster: it will lead to Sql injection security holes.

One more thing: don't set objects to Nothing in .Net. Either dispose them if needed or let them fall out of scope on their own.

Here's my normal method for pulling back a datatable from a datatable:

Function GetSomeData(ByVal Table2ID As Integer)
    Dim result As New DataTable

    Dim sql As String = "SELECT Column1,Column2 FROM [Table1] WHERE Table2ID= @Table2ID"

    Using cn As New SqlConnection( GetConnectionString() ), _
    Using cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@Table2ID", SqlDbType.Int).Value = Table2ID

        Using rdr As SqlDataReader = cmd.ExecuteReader()
           result.Load(rdr)
        End Using
    End Using
    return result
End Function

Some notes on that code:

• The Using statement will guarantee that the associated object is disposed at the corresponding End Using.
• The query parameters are kept strongly typed, and never substituted directly into the query string, even when they are transmitted to the server. Sql Data and Sql Code never mix.
• You do need a separate function for each query you need to send. This is really a good thing, as it leads into building a strongly-typed interface for your database. Ideally all of these functions are in the same class, and the GetConnectionString function is private to that class. No database access happens outside of this data layer.