A DataAdapter can be considered as a bridge between the actual data source to your application. It is commonly used together with a DataSet. Using DataAdapter and DataSet is the disconnected way of retrieving data from the data source. It means you don’t need an open connection for them to work. A DataAdapter allows you to fill a DataSet with values from the data source or execute different commands to the data source. A DataAdapter class inherits from the System.Data.Common.DbDataAdapter base class. Each data provider has its own version of DataAdapter.

Provider DataAdapter Class
SQL Server SqlDataAdapter
OLE DB OleDbDataAdapter
ODBC OdbcDataAdapter

Figure 1 – DataAdapter Classes

The DbDataAdapter class provides the following properties and methods.

Property Description
DeleteCommand Specifies the Command object with the SQL command to be used for deleting a record.
FillCommandBehavior Specifies the behavior of the command used to fill the data adapter.
InsertCommand Specifies the Command object with the SQL command used for inserting a record.
SelectCommand Specifies the Command object with the SQL command to be used for retrieving records.
UpdateBatchSize Specifies the number of commands that can be executed as a batch.
UpdateCommand Specifies the Command object with the SQL command to be used for updating a record.

Figure 2 – DbDataAdapter Properties

Method Description
AddToBatch Adds a Command object to a batch that will be executed.
ClearBatch Removes all the Command objects from the batch.
ExecuteBatch Executes the batch of commands.
Fill Executes the SelectCommand property and fills a DataSet that is provided.
InitializeBatching Initialize the batching process.
GetFillParameters Gets the parameters of the SelectCommand property.
TerminateBatching Terminates the batching process.
Update Calls the corresponding INSERT, DELETE, or UPDATE command of this DataAdapter and updates the data source using the specified commands.

Figure 3 – DbDataAdapter Methods

We will use the SQL Server provider for the following examples. To create a DataAdapter, you can use its parameterless constructor.

SqlDataAdapter adapter = new SqlDataAdapter();

The DataAdapter is the one that actually executes the commands to the data source. It has a SelectCommand property which accepts a DbCommand object that specifies the SELECT statement used to retrieved data from the data source. The following shows you an example of assigning a SelectCommand.

SqlCommand selectCommand = new SqlCommand("SELECT * FROM Students", connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;

To execute the command specified by the SelectCommand property, we can use the Fill() method of the DbDataAdapter class. The Fill() method requires an instance of the DataSet or DataTable classes. The following shows an example of filling a DataTable instance with values retrieved from the database.

DataTable myTable = new DataTable("TableName");

adapter.Fill(myTable);

This will execute the command indicated in the SelectCommand property and the results of the query will be stored in the DataTable. The following is another form of the Fill command which accepts a DataSet and the name of the DataTable to be created.

DataSet myDataSet = new DataSet();

adapter.Fill(myDataSet, "TableName");

After execution of the above command, a new DataTable with the name TableName will be added to the Tables property of the DataSet. You can then access the data using the Rows property of the DataTable.

The DbDataAdapter also allows you to specify commands for its UpdateCommand, InsertCommand, and DeleteCommand properties. Note that these commands will be autogenerated by a CommandBuilder as we will see in a separate lesson. You can then use the Update()method of the DbDataAdapter which executes these commands depending on the modification to the DataSet. You will see how to update, insert, and delete records using the Update() method in an upcoming lesson.