Command object
The Command object can execute SQL queries and stored procedures. You can execute SQL queries to return data in a DataSet or a DataReader object. To retrieve add, update and delete data you use SELECT, INSERT, UPDATE, and DELETE SQL queries. A DataAdapter generated using the VS .NET Integrated development Environment (IDE) has these queries.
Figure 1 shows the relationship between a DataAdapter and a Command object.
Figure 1: The relationship between data Adapter and command
Similar to the Connection class each data provider has its own Command class. Table 1 describes command classes for .NET data providers.
Table 1: Data provider command classes
| DATA PROVIDER |
COMMAND CLASS |
| OleDb |
OleDbCommand |
| Sql |
SqlCommand |
| ODBC |
OdbcCommand |
You call the ExecuteReader method of a Command object that executes the query and returns data in a DataReader object (see figure 2).
Figure 2: Creating a data reader from a command object
The Command Builder
The SQL SELECT command is a fairly easy one to construct. Even if you don't know how to construct a SQL SELECT command, the Query builder in visual studio helps you. But notice there are three other commands in figure 1 to construct: InsertCommand, UpdateCommand, and DeleteCommand. These commands can get quite complicated in .NET because they require complex parameter objects and often involve large lists of columns ADO.NET provides a nice utility know as the CommandBuilder that automatically builds these commands for you. Figure 1 describes the relationship between CommandBuilder and DataAdapter. CommandBuilder is constructed with DataAdapter and immediately generates the remaining Command objects upon construction.
Figure 3: The relationship between Data Adapter and command Builder objects.
The Data Adapter Object
The DataAdapter object serves as a conduit between the data source and the Dataset. The DataAdapter knows about the DataSet and it knows how to populate it. The Adapter also knows about the connection to the data source. Figure 4 is a model that shows the simple relationship between the DataAdapter and the data source.
Figure 4: The relationship between DataAdapter and DataSet
As you can see from Figure 4, the Fill method of a DataAdapter fills data from a DataAdapter to the DataSet, and the UPDATE method makes DataSet changes to the final data source.
There is a DataAdapter available for each data provider. Table 3-3 describes data adapter classes for .NET data providers.
Table 1: Data provider Data Adapter classes
| DATA PROVIDER |
COMMAND CLASS |
| OleDb |
OleDbCommand |
| Sql |
SqlCommand |
| ODBC |
OdbcCommand |
Conclusion
Hope this article would have helped you in understanding Command object, Command Builder and Data Adapter object in ADO.NET.. See my other articles on the website on ADO.NET.