Getting Started with ADO.NET

Now that you've seen the objects provided by the ADO.NET namespaces and classes, you might want to see some simple code that uses these objects. The next two subsections introduce two examples that take advantage of ADO.NET. Don't worry about the details at this point—you'll build several pages using ADO.NET and data access throughout the rest of this book. For now, just take a look at how the various ADO.NET objects work together to accomplish data-access goals.

Using a DataAdapter to Fill a DataSet

If you need to get data into a DataSet, you'll most likely use a DataAdapter object to do the work. You supply the DataAdapter object with the SQL string that describes the data you need, as well as a connection string that describes the connection to the data source. The DataAdapter object provides its Fill method, which you can call in order to add a DataTable to the DataSet, using the SQL string to retrieve the data you need. Listing 10.1 shows how to load a DataSet using the OleDbDataAdapter object.

Listing 10.1. You Can Fill a DataSet Using a DataAdapter

Private Sub DataSetLoad()
  Dim da As OleDb.OleDbDataAdapter
  Dim ds As New DataSet()

  Dim strSQL As String = _
   "SELECT * FROM Products"
  Dim strConn As String = _
   "Provider=sqloledb;Data Source=(local);" & _
   "Initial Catalog=Northwind;User ID=sa"

  ' Create DataAdapter.
  da = New OleDb.OleDbDataAdapter(strSQL, strConn)

  ' Fill the DataSet
  da.Fill(ds, "Products")
  ' Now use the DataSet...
End Sub

The code in Listing 10.1 fills a DataSet with all the rows and columns from the Products table in the Northwind sample database from your local SQL Server installation. Once you've filled the DataSet, you could use it in lots of different ways. You could modify its contents or bind it to a DataGrid control, for example. Later chapters show several examples working with data in DataSets.

Using a DataReader Object

If you simply want to iterate through all the rows in some set of data, you're likely to use the DataReader object to retrieve the data for you. In order to create the DataReader, you'll need to first create Connection and Command objects. The Connection object provides properties and methods that allow you to connect to a data source, and the Command object does the work of retrieving the data for you. Before you can retrieve data, you'll need to open the connection and then set up the Command object to use the open connection.

Once you've opened the connection and created a Command object, you can use the ExecuteReader method of the Command object to submit the instructions for retrieving data (the SQL string you've supplied, in this example) on the open connection. This retrieves a server-side, forward-only, read-only cursor. When you first retrieve the DataReader, the current row is a “phantom” row just prior to the first row in the result set. You can use the Read method of the DataReader to move to the next row (the first row), and you can use the Item method to retrieve any column (or columns) you need within the current row. As long as the Read method returns True, you can loop through all the rows in a forward-only fashion. (After you move to a new row, the previous row is no longer available to your code.) Once the Read method returns False, you know there aren't any more rows, and your loop ends. The example in Listing 10.2 loops through a DataReader object and adds the values in the ProductName column from the Northwind Products table to a ListBox control on a page. (There are better ways to accomplish this goal, as you'll see in later chapters.)

Listing 10.2. You Use Command and Connection Objects to Create a DataReader Object

Private Sub DataReaderList()
  Dim cmd As OleDb.OleDbCommand
  Dim dr As OleDb.OleDbDataReader
  Dim cnn As OleDb.OleDbConnection
  Dim strSQL As String = _
   "SELECT ProductName FROM Products"
  Dim strConn As String = _
   "Provider=sqloledb;Data Source=(local);" & _
   "Initial Catalog=Northwind;User ID=sa"

  ' Create Command and Connection Objects
  cnn = New OleDb.OleDbConnection(strConn)
  cnn.Open()

  ' Create the Command, and retrieve the DataReader.
  cmd = New OleDb.OleDbCommand(strSQL, cnn)
  dr = cmd.ExecuteReader()

  ' Loop through DataReader
  Do While dr.Read
    lstProducts.Items.Add(dr.Item("ProductName"))
  Loop

  ' Close DataReader and Connection.
  dr.Close()
  cnn.Close()
End Sub