What's the best way to read a very large database table in VB.NET?

I'm developing a VB.NET application to obtain data from any data source (using an odbc connection string), and because this I can't use specific .net connectors like MySql.Net connector and I can't previously know if the file/DMBS supports LIMIT parameter. This app will read data from a table in the data source and use the information to make some files. At this point there was no problem with some tables because they are less than 3,000,000 records length, but there is a table that has 5,000,000+ rows length, and when I send the query the connection is lost. I'm working with OdbcDataReader, because I read on MSDN site that this is the best way to read a table one row at time and I just use each record once.

This is an example of my code:

Private Sub ReadData()
   dim cnn as odbc.odbcConnection
   dim coma as odbc.odbcCommand
   dim reg as odbc.odbcDataReader
   try
      cnn=new odbc.odbcConnection("Driver={MySQL ODBC 3.51 Driver}; server=localhost; Database=datos; User=usuario; Password=contrasenia; option=3;")
      cnn.open()
      coma=new odbc.odbcCommand("select * from tabla")
      reg=coma.ExecuteReader()'<- when this line is executed fails with the 5,000,000+ length table.
   catch ex as Exception
      MessageBox("Error: "+ex.Message,MsgBoxStyle.Critical,"Error")
   end try
   ... 'Do anything with the data
end sub

In VBA or VB6 I do something like this:

Private Sub ReadData()
   dim cnn as object
   dim tab as object
   set cnn = CreateObject("ADODB.Connection")
   set tab = CreateObject("ADODB.Recordset")
   cnn.cursorlocation=3
   cnn.open "Driver={MySQL ODBC 3.51 Driver}; server=localhost; Database=datos; User=usuario; Password=contrasenia; option=3;"
   tab.open "tabla", cnn,,2
   ...'Do anything with the data
end sub

And this code is executed without problem.

Any idea h开发者_开发技巧ow to retreive data in a more efficent way in VB.NET? Or there is any way to do like ADODB (just indicating the table name not the SQL sentence).

Sorry if anything is incomprehensible.

Perhaps try setting the ConnectionTimeout property? Try adding this line before your cnn.open() call:

cnn.ConnectionTimeout =  50000  ' Number of seconds before timeout

When I had similar problem, my solution was to add LimitQuery function that added keywords to query to limit the number of results depending on the provider.

Something like this:

Public Function LimitQuery(ByVal query As String, ByVal RowLimit As Integer) As String
    If RowLimit > 0 Then    
        Select Case m_DbType    
            Case DbType.Oracle    
            return "SELECT * FROM(" & query & ") WHERE ROWNUM<" & cstr(RowLimit + 1)
            Case DbType.SQLServer    
                return Replace(query, "SELECT", "SELECT TOP " & cstr(RowLimit), 1, 1)
            Case DbType.MySQL
                return query & " LIMIT " & cstr(RowLimit)
        End Select    
    Else
        return query    
    End If
End Function

This is jast a quick hack, if you want to use any data source, sooner or later, you'll need some database abstraction layer.