| As we mentioned in the first chapter of this book, a good design is the foundation of a successful database project. ADO.NET, Microsoft's latest entry into the data access arena, represents a shift in thinking from previous data access technologies and is clearly geared toward Internet applications. In this section, we will provide a brief overview of the motivation behind ADO.NET and introduce the major ADO.NET classes. In the sections that follow, we will explore ADO.NET in more detail. Understanding Disconnected Data Access One important component of ADO.NET is the ability to provide disconnected data access to an application. To understand why this is important, we need to first describe some application design strategies. Consider the traditional client-server architecture, an example of which is pictured in Figure 22.1. Figure 22.1. The client-server model, which has passed the peak of its popularity, requires a database connection for each active user.  In the client-server model, each client connects to a database server and maintains the connection as long as needed. The client application, usually a Visual Basic executable installed on the user's desktop, contains all the application logic. Database operations are performed from the application directly against the connected database server. The client-server model works well, but the advance of the World Wide Web brought about another model, known as the multi-tier (or n-tier) architecture. Figure 22.2 shows a type of multi-tier architecture. Figure 22.2. A multi-tier application is divided into layers that clearly separate the user interface, business, and database logic.  With an application that has been segmented in this manner, maintaining a database connection for every user is not practical or even desired. Instead, data is passed around from tier-to-tier while disconnected from the database. Connections are made from the data layer to the database server for only the brief moment when data needs to be retrieved or updated, then the connection is immediately dropped. Consider some important advantages provided by this new architecture: Flexibility. Because the application is in logical parts, layers can be interchanged with minimal effort. For example, a database layer that communicates with Sybase can be replaced with another database layer that communicates with a Microsoft SQL Server without changing the business layer. Reusability. Multiple user interfaces, such as a telephone system and a Web site, can use the same business layer. Because the layers are usually implemented as classes, they can be easily used in new applications or shared as Web services. Scalability. This often-thrown-around buzzword is precisely the reason why the n-tier model fits a Web site much better than the client-server model. If something "scales" well, it handles an increase in simultaneous users without severe performance loss. Because the user is not continuously connected to the database, the database server can handle more users. In addition, the layers of an n-tier application are usually written to be stateless, which means you do not need to access the same object instance when communicating with a particular layer. The layers of such an application can be easily split across multiple physical computers, creating a "server farm" to further increase scalability. Contrast this approach to the older client-server model, where the only thing you can do to increase scalability is buy a more powerful database server and add additional connection licenses.
In short, Microsoft has designed ADO.NET to make it the ideal choice for an n-tier application. Understanding the ADO.NET Classes At the core of any data access technology are the classes you use to actually perform operations on the database. One change in the .NET environment from previous ADO versions is the addition of SQL Server specific data access classes. In previous ADO versions, when you wanted to execute a command, you used a Command object whether you were connecting to SQL Server, Oracle, Access, and so on. ADO.NET, however, includes both a SQLCommand object and an OleDbCommand object. The SQLCommand object talks to SQL Server using API routines specifically optimized for SQL Server, whereas the OleDbCommand object accesses a database through an OLEDB provider. Understandably, the SQL-specific classes will provide better performance than the OLEDB provider classes when accessing SQL server databases. Note When accessing a Microsoft SQL Server database, use the ADO.NET classes with the SQL prefix. For other types of databases, use the OLEDB-prefixed classes.
Table 22.1 provides a list of the most important ADO.NET classes that we will be covering in this chapter and lists the classes that have multiple SQL and OLE-DB versions. Table 22.1. Important ADO.NET Classes| Class Name | Purpose |
|---|
| SQLConnection, OLEDBConnection | Used for establishing a connection to a data source. | | SQLDataReader, OLEDBDataReader | Used for read-only, forward-only processing of records while connected to a database. | | DataSet | Disconnected store for data. Can contain multiple tables and relationships. | | SQLCommand, OLEDBCommand | Represents a database command, such as a stored procedure or other SQL statement. | | SQLParameter, OLEDBParameter | A parameter used in a database command. | | SQLDataAdapter, OleDbDataAdapter | Used for synchronizing records in a data source to those in a DataSet. | | DataTable | Represents a single table of data, containing columns and rows of data. |
The classes listed in Table 22.1 are available in the System.Data.SQLClient and System.Data.OleDb namespaces. To use ADO.NET, make sure you have a reference to System.Data.DLL (usually included automatically) and add an Imports statement for the appropriate namespace.  For more on namespaces and Imports, p.113
Our discussion of ADO.NET will apply to both the SQL and OLEDB data access classes, with differences noted where applicable. In addition, several ADO.NET classes (such as the DataSet) are the same, no matter what type of database you are using on the back end. |
