ADO.NET Data Adapters

Summary

Prerequisites

To properly understand the definition of a DataAdapter object and its position within ADO.NET, consider the following diagram which represents the overall basic architecture of ADO.NET:

Definitions

Mechanisms

In general, a data adapter exchanges data between a single data-source table and a single DataTable within a DataSet. If a DataSet contains multiple tables, the usual strategy is to have multiple data adapters with each data adapter exchanging data between a single DataTable and its corresponding data-source table.

Connection Objects

A data adapter needs an open connection to a data source to read and write data. In other words, a data adapter needs an IDBConnection-based object (SqlConnection, OdbcConenction, etc.) to communicate with a database. Note that a data adapter can contain up to four (4) connection references, one for each type of action it can perform - select, update, insert, and delete.

Note: You must always close the connection when you are done using it. This can be done using either Close or Dispose methods of the Connection object. Connections are not implicitly released when the Connection object falls out of scope or is reclaimed by the garbage collector.

Connection Pooling

Pooling data base connections can significantly improve application performance and scalability. When a connection is first opened (this is not the same as creating a Connection object), a connection pool is created based on an exact matching algorithm that associates the pool with the connection string used to open the connection. When a new connection is opened, if the connection string is not an exact match to that of an existing pool, a new pool is created (even if the two connection strings were different by a white space, they are considered two different connection strings from pooling perspective, and hence each opens a connection in a different pool). Once created, connection pools are not destroyed until the active process terminates.

// Set up a connection string
string strConn = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind";

// Create two new connection object. Because both use the same connection string, both will
// be managed by the same connection pool

SqlConnection nwindConn1 = new SqlConnection( strConn );
nwindConn.Open();                                         // Connection added to pool here!

SqlConnection nwindConn1 = new SqlConnection( strConn );
nwindConn.Open();                                         // Connection added to pool here!

When a connection pool is first created, multiple connection objects are automatically created by the pool to satisfy the minimum pool size requirement. Connections will be added to the pool as needed up to the maximum pool size. For example, when a SqlConnection object is opened, it will be obtained from the pool if a usable connection is available. To be usable, the connection must be:

If the maximum pool size has been reached and there are no more usable connection objects, the request to open a connection is queued. If the timeout period elapses before a connection object is made available in the pool, an error occurs.

Note: You must always close the connection when you are done using it. This can be done using either Close or Dispose methods of the Connection object. Connections that are not explicitly closed are not added or returned to the pool.

Connections are drawn from the pool and assigned based on transaction context. The context of the requesting thread and the assigned connection object must match. Therefore, each connection pool is actually divided into connections with no transaction context associated with them, and N subdivisions that each contain connections with a particular transaction context. When a connection is closed, it is returned back to the pool and to its particular subdivision based on its transaction context. Therefore, you can close a connection without generating an error even though a distributed transaction is still pending. This allows you to commit or abort the transaction at a later time.

You can control connection pooling using key/value pairs that are specific to each provider. For example, for SQL Server .NET Data Provider the following keywords can be used in the ConnectionString property of the SqlConnection object:

Note: You could monitor your connection pool using Performance Monitor (PerfMon) utility.

Connection Events

The Connection object has two events as shown below:

void foo()
{
    // Create a connection and add an event handler for the InfoMessage event 
    SqlConnection conn = new SqlConnection( "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind" );
    conn.InfoMessage += new SqlInfoMessageEventHandler( OnInfoMessage );

    ...
}

// Handler for the InfoMessage connection event
private void OnInfoMessage( object o, SqlInfoMessageEventArgs e )
{
    ...
}

Command Objects

With an adapter you can read, add, update and delete records in a data source. You can specify how each of these operations should occur by specifying the appropriate command object - implemented as a property on the data adapter:

Read only data

If you need to perform a sequential read-only only pass through a query result, then using a data reader is preferred to using a data set. Data readers are therefore used for forward-only, read-only access to query results. A data reader fetches the data form the data source and passes it directly to the application. A typical usage is in Web applications where the ASP.NET executes on the server populating a grid with the results of the query and then returning the page (with the data) to the client.

Data Adapter Basics

When to Use a Data Adapter

In general, use a data adapter when you need to perform one of the following:

Table Mappings

When using a data adapter to generate a data set with one or more data tables, the names of tables and columns in the data source will be reflected back in the name of data tables and their associated columns. However, a data set is flexible so that table and column names used in the data source do not necessarily have to match those in the data set. The data set allows you to create new table and column names in the data set, and then map these back to the names used on the data base. Adapters use a TableMapping property to maintain the correspondence between names on the data set (table and column names) and names on the data source (table and column names). See Table Mapping in Data Adapters for more details.

Data Adapter Events

The DataAdapter object exposed three events that you can use to respond to changes make to data at the data source. These events are:

When using Update, there are two events that occur per data row updated. The order of execution is as follows: 

  1. The values in the DataRow are moved to the parameter values. 
  2. The RowUpdating event is raised. 
  3. The command executes. 
  4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow
  5. If there are output parameters, they are placed in the DataRow
  6. The RowUpdated event is raised. 
  7. AcceptChanges is called. 

Data adapter commands and parameters

The data adapter command objects (SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand) perform the required action (select, insert, update, and delete) via SQL or stored procedure call in each object's CommandText property. The SQL or stored procedure associated with each command object can often include parameters that are passed to the data source at runtime. These parameters can be used in two contexts: selection parameters and update parameters.

Selection Parameters

Selection parameters allow you to address the following situation in SQL code:

-- Command text for a SelectCommand object using a ? place holder
select BookID, Title, Author from Books where Title like ?

-- Command text for a SelectCommand object using a named parameter
select BookID, Title, Author from Books where Title like @title

Parameters are indicated using either a ? place-holder (OleDBCommand and ODBCCommand objects) or a named parameter variable (SqlCommand and OracleCommand objects). If you need to execute code like the above against a data source, or if your command text is a stored procedure that expects parameters, then you will need to specify command parameters to the data adapter using the appropriate IDbCommand-derived object. See Establishing Parameter Values.

Update Parameters

The  command text for the InsertCommand, UpdateCommand, and DeleteCommand command objects always contain parameters. For example, consider the following insert, update, and delete SQL codes which all use a ? place holder to signify a required parameter ( a named parameter could also have been used):

-- Command text for an InsertCommand object
insert into Cart (BookID, CustomerID, Price) values(?, ?, ?)

-- Command text for an UpdateCommand object
update Cart set BookID = ?, Quantity = ? where UserName = ?

-- Command text for an DeleteCommand object
delete from Cart where UserName = ?

The same logic applies if we had a stored procedure for each of the above three statements where the stored proc expects the given parameters.

The Parameters Collection

To allow passing selection/update parameter values at run time, each of the four command objects contains a Parameters collection. This property is a collection of individual parameter objects that correspond one-to-one with place holders (or named parameters) in a statement. For example, when using SQLDataAdapter, the parameter collection is of type SQLParameterCollection and each parameter is of type SQLParamter.

Obviously, by using parameters you save yourself the effort of creating a SQL command (SQL Text or stored procedure call) as a string with run-time values. Using parameters also allows you to call parameterized stored procedures more easily.

Structure of the Parameters Collection

The items in the Parameters collection of a SQL command object correspond one-to-one to parameters required for the corresponding command object. In general, depending on the type of the adapter that you use, the order of the parameters may or may not be important. In general, if the adapter requires ? place holders for parameters, then the order is important. Likewise, if the adapter requires named parameters, then order is not important. The following table summarizes this requirement for some of the more common adapters

ADO.NET Managed Adapter ? Place holder or Named Parameter Parameter order In Parameters collection important?
SQL Server Named Parameter No
Oracle Named Parameter No
ODBC ? Placeholder Yes
Ole DB ? Placeholder Yes

 If you are passing parameters to a stored procedure, be aware that some stored procedures return a value. If so, then the value is returned back to your application in the Parameters collection, and your code must account for that by adding an extra parameter (usually this should be the first parameter).

Just like any collection in .NET, you can access individual items within a collection either by using an index or by using the name of the parameter. Using a parameter name is generally better because it reduces the need for maintenance and frees you from having to remember when a stored procedure returns a value.

Establishing Parameter Values

There are two ways to establish the valuefor any given parameter:

SQLataAdapter da = new SQLDataAdapter();
da.SelectCommand = new SQLCommand( "select * from T1 where F1 = @F1", obConn );
da.SelectCommand.Parameters.Add( "@F1", SqlDbType.Int ).Value = 10;

Parameter Type

The type of a parameter is specific to a .NET data adapter. Specifying a type converts the value of the parameter to the .NET data provider type before passing the value to the data source. If a type was not specified, ADO.NET will infer the .NET data provider type of the parameter from the .NET Framework type of the value given to the parameter or from the DbType property of the parameter object.

In general, the .NET data provider type of any given parameter is inferred either from the .NET Framework type of the value given to the parameter, or from the DbType property of the parameter object.

Parameter source column and source version

The Parameter.SourceColumn and Parameter.SourceVersion properties are important when updating a DataSet object against a data source. Parameter.SourceColumn is the name of the DataColumn where the value of the parameter will be retrieved. Parameter.SourceVersion specifies which version of the value to retrieve (current, default, original, or proposed). See Updating the database using a DataAdapter example for a full example.

Table Mapping in Data Adapters

Table mapping in a data adapter links the names of columns and tables in the data source with those in the data set's data table For example, information for a column called xref in the data source might belong to column called External Reference in the data set DataTable. By default, data set elements (DataTable and DataColumn) have the same names as in the data source. However, there can be times when the names in the data set are different from those in the data source. For example:

The above mapping between data source names and data element names in a data set is accomplished using the TableMappings property of the data adapter. The TableMappings property is actually a collection of items whose type is DataTableMapping.  Each DataTableMapping item in turn contains properties to identify the data source table, the data set table and a ColumnManppings property that contains items that represent the actual mappings. A DataTableMapping name can be passed instead of a DataTable name to DataAdapter.Fill method in order to use column names in a DataTable that are different than those in the data source. The following example creates  a DataTableMapping object called AuthorsMapping for the Authors table:

MyAdapter.TableMappings.Add( "AuthorsMapping", "Authors" );

Usually, when calling DataAdapter.Fill or DataAdapter.Update, you can specify either a table name or an object of type DataTableMapping to handle mapping column names. The following code creates a custom DataTableMapping object and then maps columns from the first table in the query to a set of more user friendly names:

// Set up custom mappings in a DataTableMapping object
DataTableMapping dtmCustom = MyAdapter.TableMappings.Add("MappedCustomers", "Customers" );
dtmCustom.ColumnMappings.Add( "CustomerID","Client ID" );
dtmCustom.ColumnMappings.Add( "CompanyName","Client Name" );
dtmCustom.ColumnMappings.Add( "ContactName","Contact Name" );

// Fill the data set using mappings in the DataTableMapping named "MappedCustomers"
MyAdapter.Fill( MyDataSet, "MappedCustomers" );

However, when calling DataAdapter.Fill or DataAdapter.Update without specifying a string for the table name or an object of type DataTableMapping,  the DataAdapter will look for a DataTableMapping object called "Table":

The following code creates a DataTableMapping object and makes it the default mapping for the specified data adapter by naming it "Table". The example then maps columns from the first table in the query to a set of more user friendly names. For unmapped columns, the name will be that of the data source column:

// Set up custom mappings in the default DataTableMapping object
DataTableMapping dtmCustom = MyAdapter.TableMappings.Add("Table", "Customers" );
dtmCustom.ColumnMappings.Add( "CustomerID","Client ID" );
dtmCustom.ColumnMappings.Add( "CompanyName","Client Name" );
dtmCustom.ColumnMappings.Add( "ContactName","Contact Name" );

// Fill the data set using mappings in the default DataTableMapping
MyAdapter.Fill( MyDataSet );        // uses the DataTableMapping named "Table"

In general, when a data adapter uses the Fill method to populate a data set, it transparently uses the TableMappings property to look up source column names and get the matching column name in the data set data table. Using this name, the adapter writes data from the source to the corresponding data table column. However, there are couple of conditions that could prevent this process from completing:

When either of these situation happen, the data adapter has two properties that allow you to specify what should happen:

MissingMappingAction Property

This property allows you to specify what action to take if a mapping is missing:

MissingSchemaAction Property

This property allows to specify what action to take what the adapter tries to write data to a column that is not defined in the data set's schema:

In general, both properties are set to accommodate a project's specific needs. For example, setting MissingMappingAction to Passthrough and MissingSchemaAction to Add has the effect of automatically duplicating table and column names in the data set.

Creating Data Adapters

Recall that data adapters define the means by which you get information from one or more tables in a data source into a data set and back again. Visual Studio.NET has four ways that allow you to create and configure data adapters:

Using Data Adapters

Retrieving data from the database using a DataAdapter

In general, DataAdapter.Fill method is used to retrieve rows from a data source into a target data set object. This method retrieves data from a data source using the SELECT statement associated with the SelectCommand property. The Fill operation creates a DataTable object if one does not exist and then adds rows to it. When multiple results are fetched, the Fill method creates multiple DataTables in the DataSet. The following example illustrates:

// Create a new connection object
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
nwindConn.Open();

// Specify the command used to retrieve data
SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);

// Set up the data adapter to use the select command
SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = selectCMD;

// Now retrieve data into a data set
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");

// Clean up
nwindConn.Close();

Updating the database using a DataAdapter

The Update method of the DataAdapter is used to resolve changes in the DataSet back to the data source. When the Update method is called, the DataAdapter analyzes the changes that have been made in each row and executes the appropriate command object (UpdateCommand, DeleteCommand, and InsertCommand).  These commands must be explicitly set before calling the Update method, else an exception will be thrown. Note that command object Parameters can be used to specify input and output values for any SQL statement or stored procedure associated with a command object (UpdateCommand, DeleteCommand, and InsertCommand).

Note that the Update method will attempt to resolve your changes back to the data source, however, other clients may have modified at the data source since the last time the DataSet has been filled. To refresh the DataSet with current data, call the Fill method on the DataAdapter again. New rows will be added to the table, and updated information will be incorporated into existing rows.

To handle exceptions that may occurs during an Update, you can use the RowUpdated event to respond to row update errors when they occur, or you can set DataAdapter.ContinueUpdateOnError to true before calling Update and respond to the error information stored in the RowError property on a particular error when Update is completed.

The following example shows how to update rows in a DataSet by explicitly setting the UpdateCommand on a DataAdapter. Note that the parameter specified in the WHERE clause of the UPDATE statement is set to use the Original value of the source column (SourceColumn). This is important because the current value may have been modified and may not match the value in the data source. The original value was the value used to populate a DataTable from a data source.

SqlConnection conn = null;
try
{
    // Create a new connection and a new data adapter
    conn = new SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI");
    SqlDataAdapter da = new SqlDataAdapter();

    // Create select and update commands
    da.SelectCommand = new SqlCommand("Select CategoryID, CategoryName from Categories");
    da.SelectCommand.Connection = conn;
    da.UpdateCommand = new SqlCommand("update Categories set CategoryName = @CatName where CategoryID = @CatID");
    da.UpdateCommand.Connection = conn;

    // Now wire up evnts
    da.RowUpdated += new SqlRowUpdatedEventHandler( OnRowUpdated );
    da.RowUpdating += new SqlRowUpdatingEventHandler( OnRowUpdating );
    da.FillError += new FillErrorEventHandler( OnFillError );

    // Add parameters for the update command (it has two parameters identified by '@')
    SqlParameter param1 = da.UpdateCommand.Parameters.Add("@CatName", SqlDbType.NVarChar, 12, "CategoryName" );
    SqlParameter param2 = da.UpdateCommand.Parameters.Add("@CatID", SqlDbType.Int );
    param2.SourceColumn = "CategoryID";
    param2.SourceVersion = DataRowVersion.Original;

    // Open connection as late as possible to get data
    conn.Open();
    DataSet dsData = new DataSet();
    da.Fill( dsData);

    // Make a change to some row then update it
    dsData.Tables[0].Rows[0]["CategoryName"] = "Some new category";
    da.Update( dsData );
    }
    catch( Exception ex )
    {
        MessageBox.Show( ex.Message );
    }
    finally
    {
        // Close connection
        conn.Close();
    }
}

private void OnRowUpdating(object o, SqlRowUpdatingEventArgs e)
{
    DataRow rowUpdated = e.Row;;
    string strCommandText = e.Command.CommandText;
    UpdateStatus upStatus = e.Status;
    if (e.StatementType == StatementType.Update )
    {
        Trace.WriteLine( "Attempting to update row with the following details:" );

        // Add details from the above information ...

    }
    if (e.StatementType == StatementType.Delete )
    {
        Trace.WriteLine( "Attempting to delete row with the following details:" );

        // Add details from the above information ...

    }
}

private void OnRowUpdated(object o, SqlRowUpdatedEventArgs e)
{
    if (e.Status == UpdateStatus.ErrorsOccurred )
    {
        string strSQLAction = e.StatementType.ToString();
        string strError = e.Errors.Message;
        Trace.WriteLine( "Unable to update data set against the data source" );
        Trace.WriteLine( "SQL action: " + strSQLAction + " Error: " + strError );

        // Finally update the state of the row
        e.Row.RowError = strError;
    }
}

private void OnFillError( object o, FillErrorEventArgs e )
{
    if (e.Errors.GetType() == typeof( System.OverflowException ) )
    {
        // Code to handle precision loss. Perhaps add a row to the DataTable using e.DataTable

        // Continue filling the data adapter
        e.Continue = true;
    }

    // Perhaps check for other kinds of exceptions
    if (e.Errors.GetType() == typeof( System.ArgumentOutOfRangeException) )
    {
        // Do some required processing...

        // Stop filling the data adapter
        e.Continue = false
    }
}

Populating a DataSet from multiple DataAdapter objects

Any number of DataAdapter objects can be used with a single DataSet. Each DataAdapter can be used to fill one or more DataTables in the DataSet and then resolve updates back to the relevant data source. DataRelation and Constraint objects can also be added to the DataSet locally and thus enabling you to relate data from different data sources. For example, a DataSet can contain a table form SQL Server, another from Oracle, and another form an XML data source, with each table obtained by a separate DataAdapter object..

private void button1_Click(object sender, System.EventArgs e)
{
    // This DataSet object will be filled using two different adapters
    DataSet ds = new DataSet();

    // Use two different DataAdapter objects to fill the same DataSet. Note how a connection
    // is opened as late as possible and closed as early as possible
    SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=northwind;Integrated Security=SSPI");
    SqlDataAdapter da1 = new SqlDataAdapter( "select * from Customers", conn );
    conn.Open();                 // open as late as possible
    da1.Fill( ds, "Customers" );

    SqlDataAdapter da2 = new SqlDataAdapter( "select * from Orders", conn );
    da2.Fill( ds, "Orders" );
    conn.Close();                // close as early as possible

    // Now establish a relation between the Customers and Orders DataTables
    ds.Relations.Add( "CustomersToOrders",
    ds.Tables["Customers"].Columns["CustomerID"],
    ds.Tables["Orders"].Columns["CustomerID"] );

    // ...
}