Filling a DataSet Object with an Adapter
The disconnected nature of ADO.NET translates to literal code behavior. The DataSet object in ADO.NET replaces the Recordset object in ADO as the central object for managing data. The literal difference vis- -vis disconnectedness is that the DataSet object uses a connection (and an adapter) to get data but can work independently of the connection and adapter in your application. The only time the DataSet object needs the connection and adapter is when you are actually reading from or writing to the database. The rest of the time the DataSet object acts independently; think of a DataSet as a small subset of your database copied to the client.
DataSet objects are repositories for holding and managing data; however, they do not perform any of the tasks managed by a connection, nor do they perform the action of getting the data out of a DataSet and into the database through the connection. Moving the data out of a DataSet through the connection and into the database is the job of an adapter.
It is reasonable to wonder why we need all these classes. Quite simply, DataSet objects, adapters, and connections represent a separation of responsibilities. For example, by separating the DataSet behavior from adapter and connection behavior, programmers can create their own provider classes. If the job of the adapter were in the DataSet class, we would need both OleDbDataSet and SqlDataSet objects, and as a result, all the code shared between each version of the DataSet object would be repeated. By generalizing and finding useful subdivisions of labor, it is possible to reduce or eliminate redundant code. For this reason we have only one version each of DataSet , DataTable , DataView , and DataRelation , yet we have two versions each of adapters, connections, commands, command builders, and readers. This is really what object-oriented design and frameworks are about: converging general code and reducing the amount of specialized code. Listing 11.3 demonstrates how to use a connection and adapter to fill a DataSet . (Keep in mind that we are using an OleDb provider, Microsoft Access, but the code in the example will work with other databases simply by swapping the provider-specific classes. For example, swap an OleDbConnection class with a SqlConnection class to connect to Microsoft SQL Server 2000.)
Listing 11.3 Reading Data into a DataSet Object by Using an Adapter
Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Dim Connection As OleDbConnection = New _ OleDbConnection(Database.ConnectionString) Dim Adapter As OleDbDataAdapter = _ New OleDbDataAdapter("SELECT * FROM CUSTOMERS", Connection) Dim DataSet As DataSet = New DataSet("Customers") Adapter.Fill(DataSet) DataGrid1.DataSource = DataSet.Tables(0) DataGrid1.DataBind() End Sub
The code demonstrates how to create a connection and an adapter. The adapter uses the Connection object to open (and close) a connection, filling the DataSet object. The example reads all of the Northwind sample database's CUSTOMERS table into the DataSet . The last two statements assign the DataSet.Tables(0) property to the DataGrid1.DataSource property of a Web control and calls the grid's DataBind method. This is a straightforward way to get data onto a Web form.
There are a couple of points of interest here. One is that we are using Database.ConnectionString from Listing 11.2. Another is that OleDbDataAdapter is managing the state of the connection. A final point of interest is that a DataSet can contain many things, including more than one DataTable . When you read data into a DataSet object, the data returned by the queries are represented as DataTable objects, accessible through the DataSet.Tables property (as shown in Listing 11.3). You can also bind directly to the DataSet ; however, if you do, you will end up with links in the DataGrid that allow you to navigate to every table in the DataSet .
The fundamental premise behind the DataSet class is that you read the entire row from the various tables into a DataSet . This may be one or many tables; for example, you'd use many tables for master “detail relationships. In coordination with many tables you can also add one or more DataRelation objects to the DataSet to express relationships between the tables in the DataSet . The result is that the DataSet is analogous to a subset database all by itself. With ADO technologies you can read and update single tables with a Recordset object and only read heterogeneous tables. Because the ADO.NET DataSet contains separate tables and relationships, you can read and update tables even if you are doing so with what appears to be a heterogeneous single table. (Refer to the Updating a DataSet section later in this chapter for more information.)