vb.net cycle through query results

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

From stackoverflow

• 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.
  Jeff Davis : gconIntegration is a global connection. So you are saying a single connection for all my reading and writing is not optimum way to go?

  Jeff Davis : SQL Injection is not really a concern when it is not a web application (this is actually a compiled exe)

  Joel Coehoorn : Correct- that design forces you to do all your database work in serial. This is especially bad for a web site, where all the requests are in the same application. .Net pools connections for you in the background, so there's no need to keep on global connection.

  Joel Coehoorn : Yes, injection is a concern in a desktop application. Users can still enter bad things into text fields.

  Joel Coehoorn : Even with desktop apps, you still don't want a global connection object. Retrieving data from the database can be very slow, so it's something you want to be able to do in parallel.

  Joel Coehoorn : Also: a parameterized query generally performs a little better, because the server will cache the execution plan and save a compile step.

  Jeff Davis : Very interesting. Thank you for the detailed answer.