Vb.net pull in a SQL table row by row

Question

I am a little new to using vb.net and SQL so I figured I would check with you guys to see if what I am doing makes sense, or if there is a better way. For the first step I need to read in all the rows from a couple of tables and store the data in the way the code needs to see it. First I get a count:

mysqlCommand = New SQLCommand("SELECT COUNT(*) From TableName")
Try
    SQLConnection.Open()
    count = myCommand.ExecuteScalar()
Catch ex As SqlException

Finally
    SQLConnection.Close()
End Try

Next

Now I just want to iterate through the rows, but I am having a hard time with two parts, First, I cannot figure out the SELECT statement that will jet me grab a particular row of the table. I saw the example here, How to select the nth row in a SQL database table?. However, this was how to do it in SQL only, but I was not sure how well that would translate over to a vb.net call.

Second, in the above mycommand.ExecuteScalar() tell VB that we expect a number back from this. I believe the select statement will return a DataRow, but I do not know which Execute() statement tells the script to expect that.

Thank you in advance.

There is a better way. Select the data from the database and iterate through the DataTable. — Dan Bracuk, Jan 20 '14 at 23:02
Why get a count? If the tables change in between the requests it's liable to be wrong. Mr Schmelter has given you one way. Another would be using SqlDataReader, and another an ORM. Hard to say more without knowing what you are doing with the data. — Tony Hopkinson, Jan 20 '14 at 23:10

score 1 · Accepted Answer · answered Jan 20 '14 at 23:06

A simple approach is using a DataTable which you iterate row by row. You can use a DataAdapter to fill it. Use the Using-statement to dispose/close objects property that implement IDisposable like the connection:

Dim table = New DataTable
Using sqlConnection = New SqlConnection("ConnectionString")
    Using da = New SqlDataAdapter("SELECT Column1, Column2, ColumnX FROM TableName ORDER By Column1", sqlConnection)
        ' you dont need to open/close the connection with a DataAdapter '
        da.Fill(table)
    End Using
End Using

Now you can iterate all rows with a loop:

For Each row As DataRow In table.Rows
    Dim col1 As Int32 = row.Field(Of Int32)(0)
    Dim col2 As String = row.Field(Of String)("Column1")
    ' ...' 
Next

or use the table as DataSource for a databound control.

Vb.net pull in a SQL table row by row

1 Answers1