Lesson 1: Overview of ADO.NET

Lesson 1: Overview of ADO.NET

Most applications require some kind of data access. Desktop applications need to integrate with central databases, Extensible Markup Language (XML) data stores, or local desktop databases. ADO.NET data-access technology allows simple, powerful data access while maximizing system resource usage.

After this lesson, you will be able to

• Describe the major components of ADO.NET data access

• Explain the role of each data-access component

• Describe in general terms how ADO.NET data access is facilitated

Estimated lesson time: 30 minutes

Different applications have different requirements for data access. Whether your application simply displays the contents of a table, or processes and updates data to a central SQL server, ADO.NET provides the tools to implement data access easily and efficiently.

Disconnected Database Access

Previous data-access technologies provided continuously connected data access by default. In such a model, an application creates a connection to a database and keeps the connection open for the life of the application, or at least for the amount of time that data is required. However, as applications become more complex and databases serve more and more clients, connected data access is impractical for a variety of reasons, including the following:

• Open database connections are expensive in terms of system resources. The more open connections there are, the less efficient system performance becomes.

• Applications with connected data access are difficult to scale. An application that can comfortably maintain connections with two clients might do poorly with 10 and be completely unusable with 100.

• Open database connections can quickly consume all available database licenses, which can be a significant expense. In order to work within a limited set of client licenses, connections must be reused whenever possible.

ADO.NET addresses these issues by implementing a disconnected data access model by default. In this model, data connections are established and left open only long enough to perform the requisite action. For example, if an application requests data from a database, the connection opens just long enough to load the data into the application, and then it closes. Likewise, if a database is updated, the connection opens to execute the UPDATE command, and then closes again. By keeping connections open only for the minimum required time, ADO.NET conserves system resources and allows data access to scale up with a minimal impact on performance.

ADO.NET Data Architecture

Data access in ADO.NET relies on two entities: the DataSet, which stores data on the local machine, and the Data Provider, a set of components that mediates interaction between the program and the database.

The DataSet

The DataSet is a disconnected, in-memory representation of data. It can be thought of as a local copy of the relevant portions of a database. Data can be loaded into a DataSet from any valid data source, such as a SQL Server database, a Microsoft Access database, or an XML file. The DataSet persists in memory, and the data therein can be manipulated and updated independent of the database. When appropriate, the DataSet can then act as a template for updating the central database.

The DataSet object contains a collection of zero or more DataTable objects, each of which is an in-memory representation of a single table. The structure of a particular DataTable is defined by the DataColumns collection, which enumerates the columns in a particular table, and the Constraint collection, which enumerates any constraints on the table. Together, these two collections make up the table schema. A DataTable also contains a DataRows collection, which contains the actual data in the DataSet.

The DataSet contains a DataRelations collection. A DataRelation object allows you to create associations between rows in one table and rows in another table. The DataRelations collection enumerates a set of DataRelation objects that define the relationships between tables in the DataSet. For example, consider a DataSet that contains two related tables: an Employees table and a Projects table. In the Employees table, each employee is represented only once and is identified by a unique EmployeeID field. In the Projects table, an employee in charge of a project is identified by the EmployeeID field, but can appear more than once if that employee is in charge of multiple projects. This is an example of a one-to-many relationship; you would use a DataRelation object to define this relationship.

Additionally, a DataSet contains an ExtendedProperties collection, which is used to store custom information about the DataSet.

The Data Provider

The link to the database is created and maintained by a data provider. A data provider is not a single component, rather it is a set of related components that work together to provide data in an efficient, performance-driven manner. The first version of the Microsoft .NET Framework shipped with two data providers: the SQL Server .NET Data Provider, designed specifically to work with SQL Server 7 or later, and the OleDb .NET Data Provider, which connects with other types of databases. Microsoft Visual Studio .NET 2003 added two more data providers: the ODBC Data Provider and the Oracle Data Provider. Each data provider consists of versions of the following generic component classes:

• The Connection object provides the connection to the database.

• The Command object executes a command against a data source. It can execute non-query commands, such as INSERT, UPDATE, or DELETE, or return a DataReader with the results of a SELECT command.

• The DataReader object provides a forward-only, read-only, connected recordset.

• The DataAdapter object populates a disconnected DataSet or DataTable with data and performs updates.

NOTE
Throughout this chapter, whenever information is applicable to member classes of any data provider, the classes are referred to by the generic name. For example, Command can mean OleDbCommand or SqlCommand.

Data access in ADO.NET is facilitated as follows: a Connection object establishes a connection between the application and the database. This connection can be accessed directly by a Command object or by a DataAdapter object. The Command object provides direct execution of a command to the database. If the command returns more than a single value, the Command object returns a DataReader to provide the data. This data can be directly processed by application logic. Alternatively, you can use the DataAdapter to fill a DataSet object. Updates to the database can be achieved through the Command object or through the DataAdapter.

The generic classes that make up the data providers are summarized in the following sections.

The Connection Object

The Connection object represents the actual connection to the database. Visual Studio .NET 2003 supplies two types of Connection classes: the SqlConnection object, which is designed specifically to connect to SQL Server 7 or later, and the OleDbConnection object, which can provide connections to a wide range of database types. Visual Studio .NET 2003 further provides a multipurpose ODBCConnection class, as well as an OracleConnection class optimized for connecting to Oracle databases. The Connection object contains all of the information required to open a channel to the database in the ConnectionString property. The Connection object also incorporates methods that facilitate data transactions.

The Command Object

The Command object is represented by two corresponding classes, SqlCommand and OleDbCommand. You can use Command objects to execute commands to a database across a data connection. Command objects can be used to execute stored procedures on the database and SQL commands, or return complete tables. Command objects provide three methods that are used to execute commands on the database:

• ExecuteNonQuery.

  Executes commands that return no records, such as INSERT, UPDATE, or DELETE

• ExecuteScalar.

  Returns a single value from a database query

• ExecuteReader.

  Returns a result set by way of a DataReader object

The DataReader Object

The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of a data provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of a Command object s ExecuteReader method. The SqlCommand.Execute Reader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. Likewise, the ODBC and Oracle Command.ExecuteReader methods return a DataReader specific to the ODBC and Oracle Data Providers respectively. The DataReader can supply rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance, but it requires exclusive use of an open Connection object for the lifetime of the DataReader.

The DataAdapter Object

The DataAdapter is the class at the core of ADO.NET disconnected data access. It is essentially the middleman, facilitating all communication between the database and a DataSet. The DataAdapter fills a DataTable or DataSet with data from the database whenever the Fill method is called. After the memory-resident data has been manipulated, the DataAdapter can transmit changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands. The four properties are:

• SelectCommand.

  Contains the command text or object that selects the data from the database. This command is executed when the Fill method is called and fills a DataTable or a DataSet.

• InsertCommand.

  Contains the command text or object that inserts a row into a table.

• DeleteCommand.

  Contains the command text or object that deletes a row from a table.

• UpdateCommand.

  Contains the command text or object that updates the values of a database.

When the Update method is called, changes in the DataSet are copied back to the database, and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.

Lesson Summary

• ADO.NET is a data-access technology that is primarily disconnected and designed to provide efficient, scalable data access.

• Data is represented within a DataSet object, which is a disconnected, in-memory copy of part or all of a database.

• A data provider is a set of classes that provide access to databases. The main components of data providers are:

  • Connection

  • Command

  • DataReader

  • DataAdapter

• Visual Studio .NET includes two data providers:

  • The SQL Data Provider, which contains classes optimized for accessing SQL Server 7 or later

  • The OleDb Data Provider, which contains classes that provide access to a broad range of database formats

• Two additional data providers are included in Visual Studio .NET 2003:

  • The ODBC Data Provider, which facilitates access to many different database formats

  • The Oracle Data Provider, which contains classes optimized for accessing Oracle databases