Understanding ADO.NET

There are three layers to data access in ADO.NET:

• The physical data store. This can be an OLE database, a SQL database, or an XML file.
• The data provider. This consists of the Connection object and command objects that create the in-memory representation of the data.
• The data set. This is the in-memory representation of the tables and relationships that you work with in your application.

The data provider layer provides abstraction between the physical data store and the data set you work with in code. After you' ve created the data set, it doesn' t matter where it comes from or where it is stored. This architecture is referred to as disconnected because the data set is independent of the data store.

Figure 5-1 shows the ADO.NET object model in action.

Figure 5-1 The Connection and command objects

There are two types of database connection in ADO.NET:

• Use an OleDbConnection object to connect to a local database. OLE database connections use the OleDbDataAdapter object to perform commands and return data.
• Use a SqlDbConnection object to connect to a server database. SQL database connections use the SqlDbDataAdapter object to perform commands and return data.

In addition to these database connections, you can access XML files directly from data sets using the DataSet object' s ReadXML and WriteXML methods. XML files are static representations of data sets. ADO.NET uses XML for all data transfer across the Internet.

ADO.NET provides its objects, properties, and methods through the three name-spaces described in Table 5-1. The System.Data.SqlClient and System.Data.OleDb namespaces listed in the table provide equivalent features for SQL and OLE databases, respectively.

To access a database through ADO.NET, follow these steps:

• Create a connection to the database using a connection object.
• Invoke a command to create a DataSet using an adapter object.
• Use the DataSet object in code to display data or to change items in the database.
• Invoke a command to update the database from the DataSet using an adapter object.
• Close the database connection if you explicitly opened it in step 2 using the Open method. Invoking commands without first invoking the Open method implicitly opens and closes the connection with each request.

The following sections discuss each of these steps in more detail.