How do you read a full table from a SQL database in VB.NET?

Question

I hate to ask this question on this forum, but I'm having a lot of trouble finding it on these online tutorials. Basically, I'm learning about SQL, VB.NET, and how to glue the two together, and I've learned about using SqlCommand.executeScalar() to get the first entry in a table. But that's all it's really good for, right? Or is there something more to the story here?

Basically, I'm wanting to ask about how to pull in a FULL table from a SQL database while using VB.NET, as well as how to pry substrings and stuff from it. Again I'm having to ask this on this forum because all these online tutorials seem to ignore the subject completely. Thank you for you help!

Answer 1

You have two options:

• Use a SqlDataAdapter object to fill a DataTable from a SELECT command.
  This is the more flexible method; you can loop or query the DataTable at will, or bind it to a grid control.

• Call ExecuteReader on a SqlCommand to get a SqlDataReader object that reads the rows in a SELECT statement one at a time.

Answer 2

Here's an example (a little more verbose than I like my code, but perhaps will help):

---

    Dim conString As String = "data source=ServerName;" & _
                                "initial catalog=DBName;" & _
                                "integrated security=SSPI;" & _
                                "persist security info=false;"

    Dim conSQL As New SqlConnection(conString)

    conSQL.Open()

    Dim cmdSQL As New SqlCommand()
    cmdSQL.CommandType = Data.CommandType.Text
    cmdSQL.CommandText = "SELECT FieldName1, FieldName2 FROM MyTable"

    Dim adptSQL As New SqlClient.SqlDataAdapter(cmdSQL)
    Dim myDataSet As New DataSet()
    adptSQL.Fill(myDataSet)

    conSQL.Close()

    With myDataSet.Tables(0)
        For rowNumber As Integer = 0 To .Rows.Count - 1
            With .Rows(rowNumber)
                Console.WriteLine(String.Format("Field1: {0}, Field2: {1}", _
                                                .Item(0).Value.ToString, _
                                                .Item(1).Value.ToString))
            End With
        Next
    End With

---

Basically, one of the options SLaks mentioned above.