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.

The output generated by the code in Example 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 ...

Get Programming Visual Basic .NET now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.