Programming Visual Basic .NET

Reading Data into a DataSet

The DataSet class is ADO.NET’s highly flexible, general-purpose mechanism for reading and updating data. Example 8-1 shows how to issue a SQL SELECT statement against the SQL Server Northwind sample database to retrieve and display the names of companies located in London. The resulting display is shown in Figure 8-1.

Figure 8-1. The output generated by the code in Example 8-1

Example 8-1. Retrieving data from SQL Server using a SQL SELECT statement

' Open a connection to the database.
Dim strConnection As String = _
   "Data Source=localhost; Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )

' Set up a data set command object.
Dim strSelect As String = "SELECT * FROM Customers WHERE City = 'London'"
Dim dscmd As New SqlDataAdapter(strSelect, cn)

' Load a data set.
Dim ds As New DataSet(  )
dscmd.Fill(ds, "LondonCustomers")

' Close the connection.
cn.Close(  )

' Do something with the data set.
Dim dt As DataTable = ds.Tables.Item("LondonCustomers")
Dim rowCustomer As DataRow
For Each rowCustomer In dt.Rows
   Console.WriteLine(rowCustomer.Item("CompanyName"))
Next

The code in Example 8-1 performs the following steps to obtain data from the database:

1. Opens a connection to the database using a SqlConnection object.

2. Instantiates an object of type SqlDataAdapter in preparation for filling ...