Understanding the ADO.NET Architecture


ADO.NET has been designed specifically for multitier enterprise development. In other words, an enterprise application can scale with support for thousands of connecting users without slowing down significantly because its major elements run on different servers. ADO.NET provides flexibility to multitier environments by providing both direct and disconnected data access.

Direct data access and disconnected data access offer different advantages to an enterprise application. Direct data access allows an application to read from and write to a database directly and immediately. This offers the fastest response time to an application. Disconnected data access allows an application to read from and write to a memory image of the database, not the database itself. The memory image of the database is complete with separate tables, columns , and relationships between the tables. This offers the advantage of freeing valuable database connections while performing extensive processing on data or while binding user interface elements directly to values within the database. The application later commits the changes in memory back to the database.

ADO.NET abstracts direct and disconnected data access with two foundation objects in the System.Data namespace: the DataProvider object and the DataSet object. Both objects contain additional supporting objects that help round out their functionality. A DataProvider object abstracts direct data access and contains the Connection, Command, DataReader, and DataAdapter objects. The DataSet object abstracts disconnected data access and contains DataTables and DataRelations. A DataTable object contains the DataColumn, DataRow, and Constraint objects. Figure 2-2 illustrates these object relationships as defined by the ADO.NET framework.

click to expand
Figure 2-2: ADO.NET data access objects and their relationships

Using the DataProvider Object

The ADO.NET DataProvider object establishes direct database connections and executes commands that store and retrieve application data. You can build and optimize data providers, also known as managed providers , for maximum performance on a specific database platform. ADO.NET provides two managed providers, one optimized for SQL Server database connectivity and another that connects to Object Linking and Embedding (OLE) databases, such as Microsoft Access. Vendors, such as DB/2 and Sybase, can create and distribute customized DataProvider objects that optimize performance for their database engines. As shown in Figure 2-2, the DataProvider object comprises four principal objects: Connection, Command, DataReader, and DataAdapter. Each object within a DataProvider has been optimized for performance with a specific database platform. You can download an Oracle DataProvider directly from the Oracle Technology Network site at http://otn.oracle.com/tech/ windows /odpnet/content.html . You can download data providers for other database solutions from the respective vendor Web sites.

Using the Connection Object

The Connection object provides the basic connectivity to a database. When an application establishes a direct database connection, this is where the application begins. The three most significant methods this object offers are its constructor, the Open method, and the Close method. The constructor initializes the Connection object with connection details passed as an argument, known as a connection string . The connection string includes a username, password, database location, and default table name . The Open method establishes the connection to the database and manages a handle to that connection. Subsequent database access methods use that handle, which is eventually released when the Close method disconnects the database connection.

Using the Command Object

The Command object invokes database commands and stored procedures that store, retrieve, and modify data residing within the database. Database commands, known as query statements , follow standard SQL syntax such as SELECT, INSERT, UPDATE, and DELETE to retrieve, insert, modify, or delete records (respectively). The query string passes to the Command object along with a reference to the database connection.

The SqlCommand object contains four methods that execute commands to the database: ExecuteNonQuery, ExecuteReader, ExecuteScalar, and ExecuteXmlReader. Each serves a different purpose depending upon the expected response from the database.

The ExecuteNonQuery method is for executing commands that do not return any rows of data but, rather, that return the number of records affected by the command. Typically, commands that modify the database, such as INSERT, UPDATE, or DELETE commands, use the ExecuteNonQuery method.

The ExecuteReader method is for executing commands that return multiple rows of data. Typically, commands that retrieve data, such as the SELECT command, use the ExecuteReader method. This method returns a SqlDataReader object that provides forward-only and read-only access to the command results. An application can traverse the records by retrieving them one at a time but cannot view previous records already read.

The ExecuteScalar method is for executing commands that return only a single column of data from a single row. Typically, this method is for executing an aggregate function, such as COUNT, SUM, or MAX.

The ExecuteXmlReader method is for executing commands that return data intended for Extensible Markup Language (XML) document formatting. Typically, commands that retrieve data and specify FOR XML AUTO in their command clauses use the ExecuteXmlReader method.

Using the DataReader Object

The DataReader object provides the fastest means of data access by sending forward-only and read-only data access to an application. The DataReader object works closely with the ExecuteReader method to provide an application with the results of a data retrieval command. There is cost associated with its fast data access, however. Specifically, the DataReader object is resource intensive in that it maintains an open connection to the database. This can adversely affect the scalability of an application because multiple data requests by multiple users force the database to run out of available connections. Using the DataReader object should be limited to fast-executing queries that do not tie up valuable connections for any length of time.

A number of data access methods are available to retrieve record values based on the different column types. The most common methods used include GetInt16, GetInt32, GetString, and GetBoolean. Each method requires a zero-based column identifier. Each time you call the Read method, the application returns the next row of data. When a database command first initializes a DataReader object, the DataReader always points just before the first row. To access the first row of data, you must invoke the Read method.

Because the DataReader object is resource intensive, it is also often useful to construct batch queries and use multiple result sets. This allows an application to retrieve the results of multiple queries with a single request to the database. You can accomplish this by assigning the SqlCommand object to multiple SQL queries by a semicolon or to a Transact-SQL stored procedure. After invoking the ExecuteReader method, you can retrieve data using the data access methods previously described. Finally, to point to the next batched result set, invoke the DataReader object's NextResult method.

Using the DataAdapter Object

Although the Connection, Command, and DataReader objects provide applications with all that they need to accomplish direct database connectivity and interactivity, the DataAdapter object rounds out the DataProvider object's functionality by providing a bridge to the memory image of the database. That memory image is the DataSet object, as shown in Figure 2-2. The DataAdapter fills the DataSet object with records from the database and later commits changes in the DataSet back to the database.

Using the DataSet Object

The ADO.NET DataSet object manages a disconnected memory representation of the database. Applications can quickly execute commands that store and retrieve data from this memory image, which is periodically synchronized with the actual database. See the "Understanding the DataSet Object" section to see how the IssueTracker application uses it to manage the cached reference data.




Developing. NET Enterprise Applications
Developing .NET Enterprise Applications
ISBN: 1590590465
EAN: 2147483647
Year: 2005
Pages: 119

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net