Understanding ADO.NET Components

You can categorize ADO.NET components into three categories: disconnected, common or shared, and the .NET data providers. The disconnected components build the basic ADO.NET architecture. You can use these components (or classes) with or without data providers. For example, you can use a DataTable object with or without data providers. Shared or common components are the base classes for data providers and are shared by all data providers. The data provider components are specifically designed to work with different kinds of data sources. For example, Odbc data providers work with ODBC data sources, and OleDb data providers work with OLE-DB data sources.

Figure 1-4 represents the ADO.NET components model and shows how the components work together.

Figure 1-4: The ADO.NET components model

A data provider is a set of components, such as Connection, Command, DataAdapter, and DataReader. The Connection is the first component that talks to a data source. The Connection object establishes a connection to a data source and is used in Command and DataAdapter objects as a connection reference. A Command object executes a SQL query and stored procedures to read, add, update, and delete data in a data source via a DataAdapter. The DataAdapter is a bridge between a DataSet and the connection. It uses Command objects to execute SQL queries and stored procedures.

All data providers share the ADO.NET common components. These components represent the data. Some of the common components are DataSet, DataView, and DataViewManager. The DataSet uses XML to store and transfer data between the applications and the data provider. A DataSet is a set of DataTable objects. A DataTable represents a database table. The DataView and DataViewManager objects provide single or multiple views of a DataSet. You can attach a DataView or a DataViewManager directly to data-bound controls such as a DataGrid or a DataList. Other common components are DataTable, DataRow, DataColumn, and so on. Now let's break down the ADO.NET model to see how it works.

The Connection Object

The Connection object is the first component of ADO.NET that we should talk about. A connection sets a link between a data source and ADO.NET. A Connection object sits between a data source and a DataAdapter (via Command). You need to define a data provider and a data source when you create a connection. With these two, you can also specify the user ID and password depending on the type of data source. Figure 1-5 shows the relationship between a connection, a data source, and a DataAdapter.

Figure 1-5: The relationship between Connection, DataAdpater, and a data source

You can also connect a Connection object to a Command object to execute SQL queries, which you can use to retrieve, add, update, and delete data in a data source. Figure 1-6 shows the relationship between the Command and Connection objects.

Figure 1-6: The relationship between the Command object and the Connection object

The Connection object also plays a useful role in creating a transaction. Transactions are stored in Transaction objects, and Transaction classes have all those nice features for dealing with transactions such as commit and rollback. Figure 1-7 shows the relationship between the Connection object and a transaction.

Figure 1-7: Creating a transaction from a Connection object

Each data provider has a Connection class. The OleDbConnection, SqlConnection, and OdbcConnection classes represent the Connection object of the OleDb, Sql, and Odbc data providers, respectively.

Note

ADO.NET connections are discussed in more detail in "The Connection: Connecting to a Data Source" section of Chapter 4.

The Command Object

You will usually use the Command object to 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-8 shows the relationship between a DataAdaper and a Command object.

Figure 1-8: The relationship between DataAdapter and Command

Similar to the Connection class, each data provider has its own Command class. Each data provider has a Connection class. The OleDbCommand, SqlCommand, and OdbcCommand classes represent the Command object of the OleDb, Sql, and Odbc data providers, respectively.

You call the ExecuteReader method of a Command object, which executes the query and returns data in a DataReader object (see Figure 1-9).

Figure 1-9: Creating a DataReader from a Command object

Note

ADO.NET commands are discussed in more detail in "The Command: Executing SQL Statements" section of Chapter 4.

The Command Builder

The SQL SELECT command is fairly easy to construct. Even if you don't know how to construct a SQL SELECT command, the Query Builder in VS helps you. But notice there are three other commands in Figure 1-8 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 known as the CommandBuilder that automatically builds these commands for you. Figure 1-10 describes the relationship between CommandBuilder and DataAdapter. CommandBuilder is constructed with DataAdapter and immediately generates the remaining Command objects.

Figure 1-10: The relationship between DataAdapter and CommandBuilder

Note

The CommandBuilder is discussed in more detail in "Command-Builder: Easing the Work of Programmers" section of Chapter 4.

The DataAdapter Object

The DataAdapter object serves as a conduit between the data source and the DataSet. The DataAdapter knows about the DataSet, and the DataAdapter knows how to populate the DataSet. The DataAdapter also knows about the connection to the data source. Figure 1-11 is a model that shows the simple relationship between the DataAdapter, a DataSet, and a data source.

Figure 1-11: The relationship between DataAdapter and DataSet

As you can see from Figure 1-11, 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's a DataAdapter available for each data provider. OleDbDataAdapter, SqlDataAdapter, OdbcDataAdapter represent the DataAdapter classes for OleDb, Sql, and Odbc data providers, respectively.

Note

ADO.NET DataAdapters are discussed in more detail in "The DataAdapter: Adapting to Your Environment" section of Chapter 4.

DataSet Structure

A DataSet object falls into the disconnected components category. You can use it with or without data providers. The DataSet consists of a collection of tables, rows, columns, and relationships. Figure 1-12 illustrates the relationship between a DataSet and its contents.

Figure 1-12: The relationship between classes in a DataSet

As you can see from Figure 1-12, a DataSet is a collection of DataTables and DataRelations. All DataTable objects of a DataSet are accessed through the Tables property, which represents a collection of DataTable objects. The Relations property of a DataSet represents the collection of DataRelation objects associated with a DataSet.

A DataTable is a collection of DataRow, DataColumn, and Constraint objects. The Rows, Columns, and Constraints properties of a DataTable represent a collection of DataRow, DataColumn, and Constraint objects. DataRow, DataColumn, and Constraint are the smallest units of a DataSet, and they represent a table row, column, and constraint, respectively.

Note

The DataSet object is discussed in more detail in the "The DataSet: The Heart of ADO.NET" section of Chapter 3 and the "The DataSet in Connected Environments" section of Chapter 4.

DataSets in DataViews

Another thing you can do with the contents of DataSets is sort and filter them using DataViews. You can have multiple views of a single DataSet. A DataView is a view of your data created according to certain criteria. Each DataView has a one-to-one mapping to a DataTable in a DataSet. For example, say you have three tables in a DataSet: table1, table2, and table3. Using three different data tables and data views, you can represent this DataSet in three different views. Using sort and filters, you can even sort and filter the data based on some criteria. Figure 1-13 shows three different views of a DataSet in the form of three different data views.

Figure 1-13: The relationship between DataSet and DataView objects

A DataView can directly attach to data-bound controls such as a DataGrid, DataList, or a combo box. We cover this data binding in Chapter 2 and Chapter 7 in more detail.

OK, enough theory. Let's see how to write your first database application and get some hands-on experience with ADO.NET.