The DataAdapter: Adapting to Your Environment

As you saw in Figure 4-1, a DataAdapter plays a vital role in the ADO.NET architecture. It sits between a data source and a DataSet, and it passes data from the data source to the DataSet, and vice versa, with or without using commands. Now you'll use disconnected classes such as DataSet, DataTable, DataView, and DataViewManager to write interactive database applications based on Windows Forms and Web Forms.

The DataAdapter enables you to connect to a database and specify SQL strings for retrieving data from or writing data to a DataSet. As you know, a DataSet represents in-memory cached data. An in-memory object frees you from the confines of the specifics of a database and allows you to deal with the data in memory. The DataAdapter serves as an intermediary between the database and the DataSet.

Constructing a DataAdapter Object

The DataAdapter constructor has many overloaded forms. You can create a DataAdapter using a constructor with no arguments, pass a Command object, pass a Command object with Connection object as arguments, or use a combination of these. You can also specify a SQL statement as a string for querying a particular table or more than one table. You can also specify the connection string or a Connection object to connect to the database. Listing 4-19 creates four SqlDataAdapter instances with different approaches. As you can see, the code first creates and opens SqlConnection and SqlCommand objects. Later you use these two objects to create DataAdapters. The first DataAdapter, da1, takes only one parameter of type SqlCommand. Because SqlCommand already has a SqlConnection, there's no need to specify a connection explicitly with the DataAdapter. The second DataAdapter, da2, is created using no argument, and later its SelectCommand property is set to a SqlCommand. The third DataAdapter, da3, takes a string argument as a SQL statement and a second argument as SqlConnection. The last DataAdapter takes both string arguments, one containing the SQL statement and the second containing the connection string.

Listing 4-19: Creating SqlDataAdapter Instances

 ' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _      "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) Dim SQL As String = "SELECT * FROM Customers" ' open a connection conn.Open() Dim cmd As SqlCommand = New SqlCommand(SQL, conn) ' Creating SqlDataAdapter using different constructors Dim da1 As SqlDataAdapter = New SqlDataAdapter(cmd) Dim da2 As SqlDataAdapter = New SqlDataAdapter() da2.SelectCommand = cmd Dim da3 As SqlDataAdapter = New SqlDataAdapter(SQL, conn) Dim da4 As SqlDataAdapter = New SqlDataAdapter(SQL, ConnectionString) 

As discussed, there's no difference between creating OleDb, Sql, and Odbc DataAdapters. The only difference is the connection string. For example, Listing 4-20 shows you how to create an OleDbDataAdapter object. Listing 4-20 uses the Access 2000 Northwind database and accesses all records of the Orders table by using a SELECT * SQL query.

Listing 4-20: Executing a SELECT * Statement Using OleDbDataAdapter

 ' Create a Connection Object Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\\Northwind.mdb" Dim SQL As String = "SELECT * FROM Orders" Dim conn As OleDbConnection = New OleDbConnection(ConnectionString) ' Open the connection conn.Open() ' Create an OleDbDataAdapter object Dim adapter As OleDbDataAdapter = New OleDbDataAdapter() adapter.SelectCommand = New OleDbCommand(SQL, conn) 

You can also use a DataAdapter's Command properties by using the Command object with OleDbDataAdaper. For example, the following code uses OleDbCommand to set the SelectCommand property of the DataAdapter. You can see that OleDbDataAdapter has no arguments as its constructor:

 ' Create an OleDbDataAdapter object Dim adapter As OleDbDataAdapter = New OleDbDataAdapter() adapter.SelectCommand = New OleDbCommand(SQL, conn) 

Understanding DataAdapter Properties

As you start working with DataAdapters, you need take a quick look at the DataAdapter properties and methods. The DataAdapter has four properties that are Command objects; they represent the ways it can query, insert, delete, and update the database.

Table 4-11 describes SqlDbDataAdapter class properties, and Table 4-12 shows the OleDbDataAdapter properties.

Table 4-11: The SqlDataAdpater Class Properties

PROPERTY	DESCRIPTION
AcceptChangesDuringFill	The AcceptChanges method of a DataRow saves the changes made to a DataRow. The True value of this property makes a call to the DataRow's AcceptChanges property after it has been added to the DataTable. The False value doesn't. The default value is True.
ContinueUpdateOnError	The True value of this property keeps the update process continuing even if an error occurred in a DataRow. The False value generates an exception. The default value is False.
DeleteCommand	Represents a DELETE statement or stored procedure for deleting records from the data source.
InsertCommand	Represents an INSERT statement or stored procedure for inserting a new record to the data source.
MissingSchemaAction	Determines the action to be taken when incoming data doesn't have a matching schema. This is a type of MissingMappingAction enumeration, which has three values: Error, Ignore, and Passthrough. The Error value generates an exception, the Ignore value maps the unmatched columns or tables, and the Passthrough value adds the source column or source table created to the DataSet using its original name.
MissingActionSchema	Determines the action to be taken when a DataSet schema is missing. This is a type of MissingSchemaAction enumeration, which has three values: AddWithKey, Error, and Ignore. AddWithKey adds the necessary columns and primary key information, Error generates an exception, and Ignore ignores the extra columns.
SelectCommand	Represents a SELECT statement or stored procedure for selecting records from a data source.
UpdateCommand	Represents an UPDATE statement or stored procedure for updating records in a data source.
TableMappings	Represents a collection of mappings between an actual data source table and a DataTable object.

Table 4-12: OleDbDataAdapter Command Properties with Examples

PROPERTY	EXAMPLE
SelectCommand	cmd.SelectCommand.CommandText = "SELECT * FROM Orders ORDER BY Price";
DeleteCommand	cmd.DeleteCommand.CommandText = "DELETE FROM Orders WHERE LastName ='Smith'";
InsertCommand	cmd.InsertCommand.CommandText = "INSERT INTO Orders VALUES (25,'Widget1','Smith')";
UpdateCommand	cmd.UpdateCommand.CommandText = "UPDATE Orders SET ZipCode='34956' WHERE OrderNum =14";

Listing 4-21 sets some of the SqlDataAdapter properties.

Listing 4-21: Using SqlDataAdapter Properties

  ' Create an OleDbDataAdapter object    Dim adapter As SqlDataAdapter = New SqlDataAdapter()    adapter.SelectCommand = New SqlCommand(SQL, conn)    adapter.AcceptChangesDuringFill = True    adapter.ContinueUpdateOnError = True    adapter.MissingSchemaAction = MissingSchemaAction.Error adapter.MissingMappingAction = MissingMappingAction.Error 

Understanding DataAdapter Methods

The DataAdapter class provides many useful methods. For instance, the Fill method of the DataAdapter fills data from a DataAdapter to the DataSet object, and the Update method stores data from a DataSet object to the data source.

Table 4-13 describes some of the OleDbDataAdapter methods.

Table 4-13: The DataAdapter Methods

METHOD	DESCRIPTION
Fill	Fills data records from a DataAdapter to a DataSet object depending on the selection query.
FillSchema	This method adds a DataTable to a DataSet and configures the schema to match that in the data source.
GetFillParameters	This method retrieves parameters that are used when a SELECT statement is executed.
Update	This method stores data from a DataSet to the data source.

Filling the DataSet

The Fill method is the primary method for bringing data into a DataSet from a data source. This command uses the SelectCommand SQL statement to fill a DataSet memory structure consisting of DataTables, DataRows, DataColumns, and DataRelations. The Fill method has eight overloaded forms. Using these methods, you can fill a DataSet from a DataAdapter by selecting a particular table, selecting a range of rows, and selecting rows based on the CommandBehavior. The following shows different overloaded forms of the Fill method defined in the DbDataAdapter class:

 Overloads Overrides Public Function Fill(DataSet) As Integer _ Implements IDataAdapter.Fill Overloads Public Function Fill(DataTable) As Integer Overloads Public Function Fill(DataSet, String) As Integer Overloads Overridable Protected Function Fill(DataTable, IDataReader) _  As Integer Overloads Overridable Protected Function Fill(DataTable, _ IDbCommand, CommandBehavior) As Integer Overloads Public Function Fill(DataSet, Integer, Integer, String) _  As IntegerOverloads Overridable Protected Function Fill(DataSet, String, IDataReader, _  Integer, Integer) As IntegerOverloads Overridable Protected Function Fill(DataSet, Integer, Integer, _  String, IDbCommand, CommandBehavior) As Integer 

The following form of the Fill method selects or refreshes rows in a specified range in the DataSet:

 Overloads Public Function Fill(_    ByVal dataSet As DataSet, _    ByVal startRecord As Integer, _    ByVal maxRecords As Integer, _    ByVal srcTable As String _ ) As Integer 

In this example, dataSet is a DataSet, startRecord is the zero-based record number to start with, maxRecords is the maximum number of records to retrieve, and srcTable is the name of the source table to use for table mapping. The following code calls the same overloaded method to get or refresh rows from the 9^th position to the 15^th position of the Orders table. In other words, it fills the DataSet with seven rows if found in the table:

 adapter.Fill(ds, 9, 15, " Orders") 

You can use a DataSet directly or a DataTable to fill or refresh the data of a DataSet. Listing 4-22 calls the Fill method to fill data to a DataSet, which later can bind with data-bound controls.

Listing 4-22: Calling a DataAdapter's Fill Method

 ' Create an OleDbDataAdapter object   Dim adapter As SqlDataAdapter = New SqlDataAdapter()   adapter.SelectCommand = New SqlCommand(SQL, conn)   ' Create DataSet Object   Dim ds As DataSet = New DataSet("Orders")   ' Call DataAdapter's Fill method to fill data from the   ' DataAdapter to the DataSet   adapter.Fill(ds) 

Adding a DataTable to a DataSet

The FillSchema method adds a DataTable to a DataSet. The FillSchema method has two arguments; the first is DataTable, and the second is SchemaType. The SchemaType argument defines the handling of existing schema. It has two values: Mapped and Source. The SchemaType.Mapped value means you can apply any existing table mapping to the incoming schema and configure the DataSet with the transformed schema. The SchemaType.Source value means you can ignore any table mappings on the DataAdapter and configure the DataSet using the incoming schema without applying any transformations.

Listing 4-23 uses the FillSchema method to add a DataTable to a DataSet.

Listing 4-23: Using the FillSchema Method of SqlDataAdapter

 ' Create an OleDbDataAdapter object Dim adapter As SqlDataAdapter = New SqlDataAdapter() adapter.SelectCommand = New SqlCommand(SQL, conn) ' Create DataSet Object Dim ds As DataSet = New DataSet("Orders") ' Call DataAdapter's Fill method to fill data from the ' DataAdapter to the DataSet adapter.Fill(ds) ' Adding a DataTable to the DataSet Dim table As DataTable() = _ adapter.FillSchema(ds, SchemaType.Mapped, "Categories") 

Looking at a DataAdapter Example

Now you'll create your first sample using DataAdapters. In this example, you'll learn how to create DataAdapters using the Sql and OleDb data providers and fill data from a DataAdapter to a DataGrid control.

First, create a Windows application using Visual Basic Projects and add two Button controls and a DataGrid control to the form by dragging the controls from the Toolbox. Second, set both buttons' Name property; use OleDbDataAdapter and SqlDataAdapter. Next, set the Text properties to OleDbDataAdapter and SqlDataAdapter. After setting these properties, the form will look like Figure 4-3. As you can see, there are two buttons, shown as OleDbDataAdapter and SqlDataAdapter.

Figure 4-3: Creating a Windows Forms application and adding controls to the form

Now add button click event handlers for both the OleDbDataAdapter and SqlDataAdapter buttons. You can add a button click event handler either by double-clicking the button or by using the Events tab of the Properties window. On the OleDbDataAdapter button click event handler, you'll write code to read data from an OleDb data source and fill data to the DataGrid control. On the SqlDataAdapter button click event handler, you'll write code to read data from a SQL Server data source and fill data to the DataGrid.

Listing 4-24 shows the source code for the OleDbDataAdapter button click, and Listing 4-25 shows the source code for the SqlDataAdapter button click. As you can see, you follow the same steps as before. Open a connection, create a DataAdapter object with a SELECT string, create a DataSet object, call a DataAdapter's Fill method to fill the DataSet, and bind the DataSet to the DataGrid control using the DataGrid.DataSouce property of DataSet.DefaultViewManager, which represents the default view of a DataSet object.

Listing 4-24: Displaying the Orders Table Data in a DataGrid Using OleDbDataAdapter

 Private Sub OleDbDataAdapter_Click(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles OleDbDataAdapter.Click    ' Create a Connection Object    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _    "Data Source=c:\\Northwind.mdb"    Dim SQL As String = "SELECT * FROM Orders"    Dim conn As OleDbConnection = New OleDbConnection(ConnectionString)    ' Open the connection    conn.Open()    ' Create an OleDbDataAdapter object    Dim adapter As OleDbDataAdapter = New OleDbDataAdapter()    adapter.SelectCommand = New OleDbCommand(SQL, conn)    ' Create DataSet Object    Dim ds As DataSet = New DataSet("Orders")    ' Call DataAdapter's Fill method to fill data from the    ' DataAdapter to the DataSet    adapter.Fill(ds)    ' Bind data set to a DataGrid control    DataGrid1.DataSource = ds.DefaultViewManager End Sub 

Listing 4-25: Displaying the Customers Table's Data in a DataGrid Using SqlDataAdapter

 Private Sub SqlDataAdapter_Click(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles SqlDataAdapter.Click    ' Create a Connection Object    Dim ConnectionString As String = "Integrated Security=SSPI;" & _         "Initial Catalog=Northwind;Data Source=MCB;"    Dim conn As SqlConnection = New SqlConnection(ConnectionString)    Dim SQL As String = "SELECT * FROM Customers"    ' Open the connection    conn.Open()    ' Create an OleDbDataAdapter object    Dim adapter As SqlDataAdapter = New SqlDataAdapter()    adapter.SelectCommand = New SqlCommand(SQL, conn)    ' Create DataSet Object    Dim ds As DataSet = New DataSet("Orders")    ' Call DataAdapter's Fill method to fill data from the    ' DataAdapter to the DataSet    adapter.Fill(ds)    ' Adding a DataTable to the DataSet    Dim table As DataTable() = _     adapter.FillSchema(ds, SchemaType.Mapped, "Categories")    ' Bind data set to a DataGrid control    DataGrid1.DataSource = ds.DefaultViewManager End Sub 

The output of Listing 4-24 looks like Figure 4-4.

Figure 4-4: Filling data from an Access database to a DataGrid control using OleDbDataAdapter

The output of Listing 4-25 looks like Figure 4-5.

Figure 4-5: Filling data from a SQL Server database to a DataGrid control using SqlDataAdapter

Filling the DataAdapter from a Recordset

Because Microsoft realized it would be difficult for some developers to let go of their ADO recordsets (or perhaps they have some legacy applications or components that would be difficult to convert), the DataAdapter's Fill method also allows you to fill a DataSet with an ADO recordset. You can't, however, go the other way and fill a recordset with a Dataset. The Fill method appends rows from the recordset to the existing DataSet's DataTable. If a primary key exists for the DataRows in the DataSet, then the Fill method will attempt to update rows from the recordset with a matching primary key. You can call the recordset with the |following code:

 OleDbDataAdapter1.Fill(ds,  anADORecordset, SourceTableName) 

Updating the Database Using the Update Method

The architecture of a DataAdapter has been designed so that you can make any changes you want to the data in a filled DataSet, without affecting the database until you call the Update method. When Update is called, the DataAdapter will attempt to execute each query (UPDATE, INSERT, DELETE) on every row of the DataSet that has been updated, inserted, and deleted. For example, if you call Delete on a row in the DataSet, then when Update is called on the DataAdapter, the DeleteCommand of the DataAdapter will be called using the particular row in the DataSet.

Note

Keep in mind that this Update is different from a SQL UPDATE statement.

To delete a row, you create an SqlCommand object with a DELETE statement and set the DeleteCommand property of the DataAdapter. Listing 4-26 creates a SqlCommand object with a DELETE statement where EmployeeId = 10.

Listing 4-26: Setting the DeleteCommand Property of DataAdapter

 ' Set DeleteCommand property adapter.DeleteCommand = New SqlCommand(    "DELETE from Employees where EmployeeID = 10", conn) 

Also, you need to create a parameter for the command that maps to the EmployeeID in the database. (We discuss parameters in more depth in the "Staying within the Parameters" section of this chapter.)

Listing 4-27 shows an example that creates and sets parameter properties for the Sql data provider.

Listing 4-27: Creating a SqlParameter

 Dim workParam As SqlParameter = Nothing adapter.DeleteCommand = New SqlCommand(_ "DELETE from Employees where EmployeeID = 10", conn)  workParam = adapter.DeleteCommand.Parameters.Add(_ "@EmployeeID", OleDbType.Integer) workParam.SourceColumn = "EmployeeID" workParam.SourceVersion = DataRowVersion.Original 

Now you create a DataSet and fill it with the employees from the Employees table in the Northwind database, as shown in Listing 4-28.

Listing 4-28: Calling the Fill Method of a DataAdapter

 Dim ds As DataSet = New DataSet("EmployeeSet") adapter.Fill(ds, "Employees") 

Now you're prepared to delete data from a table using a DataTable. By calling Delete on the last row in the Rows collection, you mark the last row as deleted. To cause the actual deletion to take place in the Northwind database, you need to call Update on the adapter. This causes the adapter to go through each changed row of the DataSet and see which command needs to be called on that row. In the case of the last row in the Employees table, you call the DeleteCommand (see Listing 4-29).

Listing 4-29: Removing a Row and Calling the Update Method of a DataAdapter

 Dim Dt As DataTable = ds.Tables("Employees") Dim lastRow As Integer = Dt.Rows.Count - 1 Dim firstName As String = Dt.Rows(lastRow)("FirstName").ToString() Dim lastName As String = Dt.Rows(lastRow)("LastName").ToString() Dt.Rows(Dt.Rows.Count - 1).Delete() adapter.Update(ds, "Employees") 

Listing 4-30 shows the code that calls the Update method to delete a row from a DataSet and updates it back to the database using DataApdater's Update method.

Listing 4-30: Calling a DataAdapter's Update Method

 Dim ConnectionString As String = "Integrated Security=SSPI;" & _          "Initial Catalog=Northwind;Data Source=MCB;"    Dim conn As SqlConnection = New SqlConnection(ConnectionString)    Dim adapter As SqlDataAdapter = New SqlDataAdapter()    Dim workParam As SqlParameter = Nothing    adapter.DeleteCommand = New SqlCommand(_    "DELETE from Employees where EmployeeID = 10", conn)    workParam = adapter.DeleteCommand.Parameters.Add(_ "@EmployeeID", OleDbType.Integer)    workParam.SourceColumn = "EmployeeID"    workParam.SourceVersion = DataRowVersion.Original    adapter.SelectCommand = New SqlCommand("SELECT * FROM Employees", conn)    Dim ds As DataSet = New DataSet("EmployeeSet")    adapter.Fill(ds, "Employees")    Dim Dt As DataTable = ds.Tables("Employees")    Dim lastRow As Integer = Dt.Rows.Count - 1    Dim firstName As String = Dt.Rows(lastRow)("FirstName").ToString()    Dim lastName As String = Dt.Rows(lastRow)("LastName").ToString()    Dt.Rows(Dt.Rows.Count - 1).Delete()    adapter.Update(ds, "Employees")    DataGrid1.DataSource = ds.DefaultViewManager    ' release objects    conn.Close()    conn.Dispose() 

Table and Column Mapping

One of the important properties of the DataAdapter is the TableMappings property. This property contains a collection of DataTableMapping objects (from the System.Data.Common namespace). The DataAdapter uses the DataTableMapping object to map the table name of the data source to the DataTable name of the DataSet. In general, the names for both sources can be the same.

For example, Listing 4-31 constructs the Northwind database's Order Table Mapping and adds it to the DataAdapter.

Listing 4-31: Using DataTableMapping to Map the Orders Table of Northwind

 ' Create a Connection Object     Dim ConnectionString As String = "Integrated Security=SSPI;" & _     "Initial Catalog=Northwind; Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' Open the connection     conn.Open()     ' Create a DataTableMapping object     Dim dtMapping As DataTableMapping =        New DataTableMapping("Orders", "mapOrders")     Dim adapter As SqlDataAdapter =         New SqlDataAdapter("Select * From Orders", conn)     ' Call DataAdapter's TableMappings.Add method     adapter.TableMappings.Add(dtMapping)     ' Create a DataSet Object and call DataAdapter's Fill method     ' Make sure you use new name od DataTableMapping i.e., MayOrders     Dim ds As DataSet = New DataSet()     adapter.Fill(ds, "mapOrders")     DataGrid1.DataSource = ds.DefaultViewManager     'Dispose     conn.Close()     conn.Dispose() 

The default mapping for a DataTable is the Table alias. If you use this mapping name, then you don't need to mention the table in the Fill method. Listing 4-32 shows an example using DataTableMapping with the Table option.

Listing 4-32: Using DataTableMapping with the Table Option

 ' Create a Connection Object     Dim ConnectionString As String = "Integrated Security=SSPI;" & _     "Initial Catalog=Northwind; Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' Open the connection     conn.Open()     ' Create a DataTableMapping object Dim dtMapping As DataTableMapping =         New DataTableMapping("Table", "mapOrders")     Dim adapter As SqlDataAdapter =         New SqlDataAdapter("Select * From Orders", conn)     ' Call DataAdapter's TableMappings.Add method     adapter.TableMappings.Add(dtMapping)     ' Create a DataSet Object and call DataAdapter's Fill method     ' Make sure you use new name od DataTableMapping i.e., MayOrders     Dim ds As DataSet = New DataSet()     adapter.Fill(ds)     DataGrid1.DataSource = ds.DefaultViewManager     'Dispose     conn.Close()     conn.Dispose() 

DataTables are not the only things aliased in ADO.NET. You can also alias the DataColumns using DataColumnMapping objects. Why do you want column mapping? Let's say you have a table that has column names such as a1, a2, and so on, but when a user views the data in data-bound controls, you want to display some meaningful names such as CustomerName and so on.

Data column mapping allows you to customize a DataSet's column names, which internally are mapped with a table columns. You can achieve this by using DataAdapter.DataTableMapping.ColumnMapping. The DataColumnMapping property of DataTableMapping is a collection of column mappings. You use the Add method of DataColumnCollection, which adds a column mapping. The Add method takes a column name of the source table and a DataSet column name. Listing 4-33 maps the OrderId, ShipName, ShipCity, and OrderDate columns of the Orders table to mapID, mapName, mapCity, and mapDate, respectively.

Listing 4-33: Using DataColumnMapping

 ' Create a Connection Object     Dim ConnectionString As String = "Integrated Security=SSPI;" & _     "Initial Catalog=Northwind; Data Source=MCB;"     Dim conn As SqlConnection = New SqlConnection(ConnectionString)     ' Open the connection     conn.Open()     ' Create a DataTableMapping object     Dim dtMapping As DataTableMapping = New DataTableMapping("Table", "Orders")     Dim adapter As SqlDataAdapter = New SqlDataAdapter _     ("SELECT OrderID, ShipName, ShipCity, OrderDate FROM Orders", conn)     ' Call DataAdapter's TableMappings.Add method     adapter.TableMappings.Add(dtMapping)     dtMapping.ColumnMappings.Add(New DataColumnMapping("OrderID", "mapID"))     dtMapping.ColumnMappings.Add(New DataColumnMapping("ShipName", "mapName"))     dtMapping.ColumnMappings.Add(New DataColumnMapping("ShipCity", "mapCity"))     dtMapping.ColumnMappings.Add(New DataColumnMapping("OrderDate", "mapDate"))     ' Create a DataSet Object and call DataAdapter's Fill method     ' Make sure you use new name od DataTableMapping i.e., MayOrders     Dim ds As DataSet = New DataSet()     adapter.Fill(ds)     DataGrid1.DataSource = ds.DefaultViewManager     'Dispose     conn.Close()     conn.Dispose() 

Figure 4-6 displays the results of Listing 4-33.

Figure 4-6: Column mapping

The IDE automatically generates much of the mappings, so you don't have to worry about them. But, occasionally, you may want to choose your own schema names for your DataSet that map back to the data source.