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.
Figure 1 – DataAdapter Classes
The DbDataAdapter class provides the following properties and methods.
|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
|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.