Data Driven
Languages: ADO.NET
Introducing ADO.NET
A New Way to Access Your Database
By Wayne S. Freeze
Welcome to the first installment of a column dedicated to database programming with ASP.NET and related issues. This column is intended for professional programmers who use database servers such as SQL Server, Oracle9i, and DB2 in their Web applications. Because the best way to learn about database programming is by studying code, this column will include lots of code examples written in Visual Basic. These samples will be available for download along with the equivalent code written in C# and JScript. Because these sample programs require a database, I'll also provide a sample database you can download. With that said, let me add that this first column will be somewhat atypical without any code samples.
ADO.NET represents a completely new object model tailored for the .NET programming environment. It makes extensive use of inheritance and namespaces to provide a more general solution for data access and manipulation than existed in any of the previous object models. In this article, I want to introduce the ADO.NET object model and discuss some of its key features.
Before ADO.NET
Before .NET, Microsoft offered three different database object models. The original object model, Data Access Objects (DAO) was developed to provide a way for Visual Basic programmers to use the Access database (also known as the Jet engine). However, using DAO for other databases, such as SQL Server and Oracle, was slow and painful. To address this problem, Microsoft developed another object model called Remote Data Objects (RDO), which provided a high-performance way to access relational databases like SQL Server and Oracle.
Having two object models caused some problems because you couldn't build a single application that worked for all database servers. So, Microsoft went back to the drawing board to develop another database object model called ActiveX Data Objects (ADO). Although ADO was developed with relational databases in mind, it represented the first step toward universal data access. By using ADO, you could update non-database data sources, such as Exchange Server and Excel worksheets, as if they were relational databases.
DAO, RDO, and ADO also reflect the low-level application-programming interface (API) programmers use to access the database. Both DAO and RDO rely on the Open Database Connectivity (ODBC) API. While ADO relies on the OLE DB API, ADO also can communicate with ODBC databases if necessary.
The ADO.NET Object Model
As you might expect, ADO.NET offers a rich collection of tools for manipulating data in the .NET environment. It expands on ADO's goal of universal data access by generalizing the way data is stored in the objects. This makes it easier to use with non-relational database data sources.
There are five namespaces in ADO.NET (see FIGURE 1). The primary namespace is the System.Data namespace, which contains a collection of classes and interfaces that provide the framework for ADO.NET. The System.Data.Common namespace contains tools that allow you to build data adapters for specific data providers. The System.Data.OleDb and the System.Data.SqlClient namespaces contain classes that use the OLE DB data provider and the SQL Server data provider. Finally, the System.Data.SqlTypes contains information about the data types available in SQL server.
FIGURE 1: The ADO.NET object
model.
ADO.NET is more complex than ADO for several reasons. First, the facilities to create other data providers are included in the object model even though most programmers will never use them. Second, rather than using a small collection of general-purpose objects, ADO.NET uses a larger collection of focused objects, which provide better performance and increased functionality. Finally, the object-oriented nature of the .NET Framework encourages the use of lots of classes.
Exploring System.Data
The System.Data namespace serves two main purposes. It houses the classes necessary to implement the DataSet class and includes a number of interfaces the other ADO.NET classes use.
The DataSet class (see FIGURE 2) is the cornerstone of ADO.NET. The purpose of the DataSet class is to provide you with an object that can hold a collection of data in memory that is independent of the data source. This is similar to the concept of a disconnected Recordset object in ADO. Unlike ADO, however, the DataSet class permits you to store multiple sets of information. You can even create hierarchical relationships using this data.
FIGURE 2: The DataSet class hierarchy in System.Data.
The DataTable class is similar to a relational database table in that it stores data as a collection of rows and columns. You can retrieve the name using the TableName property and access the schema information for the table through the Columns property. The Rows property contains an object reference to the collection of rows in the DataTable.
The Columns property contains a reference to the DataColumnCollection object, which in turn contains a set of DataColumn objects that describe each column in the table. The ColumnName property contains the name of the column as it's known in the database. The DataType property contains an object reference to the Type object associated with the column in the table. A number of other properties are available in the DataColumn object, including DefaultValue, which contains the default value for the column; AutoIncrement, which means the value of the column will be incremented automatically when the row is saved to the database; and Caption, which suggests a text string that can be used to describe the column.
Each row in the DataTable is stored in a DataRow object. The set of rows is stored in a DataRowCollection and can be referenced through the Rows property of the DataTable. The individual columns in the row can be referenced through the Item property.
The DataRelation class allows you to define a parent-child relationship between two DataTables. This allows you to build a hierarchical representation of data, such as a customer-order relationship or a supplier-parts relationship, which can be useful when displaying information on a Web page.
The DataView class represents another way you can access data from a DataTable. You can filter and sort rows locally without interacting with the database server. You can also insert, update, or delete rows through a DataView. However, the primary purpose of a DataView is to allow you to bind controls on a Web form to the data in a DataTable. To make data binding simpler, each DataTable has a property, DefaultView, which returns a DataView representing all of the rows and columns of the DataTable.
System.Data.SqlClient vs. System.Data.OleDb
The System.Data.SqlClient and System.Data.OleDb namespaces are fairly similar (see FIGURE 3). They provide the classes that are used to communicate with and execute commands on a database server. The main difference between the two namespaces is the provider used to retrieve data from the database. System.Data.SqlClient communicates with a SQL Server database using the database's internal API, while System.Data.OleDb communicates with any database that supports the OLE DB standard.
FIGURE 3: A comparison of the
key classes in System.Data.SqlClient
and System.Data.OleDb.
Because the SQL Server internal API is more efficient than is the OLE DB provider, you should use the classes in the System.Data.SqlClient when communicating with a SQL Server database. If you are still using SQL Server 6.5 or older, you must stick with the System.Data.OleDb namespace because these databases do not support the internal SQL Server API. Of course, by using the System.Data.SqlClient namespace, you will have to modify your application if you ever choose to move it to another database server.
Over time, I believe other database vendors will develop other System.Data namespaces that take advantage of the database's native API. The basic tools needed to construct these namespaces reside in the System.Data names as a collection of interface objects.
Digging into System.Data.SqlClient
Because the classes in the System.Data.SqlClient and System.Data.OleDb namespaces are so similar, I'm only going to discuss the classes in the System.Data.SqlClient namespace. You can find the equivalent classes in the System.Data.OleDb namespace by changing SqlClient to OleDb in the class name.
The SqlConnection class contains the information necessary to connect to a database server. The Open method creates a session with the database server, while the Close method terminates the session with the database server. You should never keep a session open for a long time. Open it just before you need it and close it immediately after you've finished using it. It is far more efficient to open and close a connection many times than to leave it open for a long period of time.
Note that even though .NET will automatically close a connection when the connection object is destroyed, you shouldn't rely on .NET to close the connection in a timely fashion. Garbage collection runs asynchronously, and it may be a long time before the connection actually closes.
The SqlCommand class corresponds to the old ADO Command object. This class contains the information necessary to execute a SQL statement or stored procedure. To execute the SQL statement or stored procedure, SqlCommand requires an already opened SqlConnection object.
There are four ways to execute a command object, depending on the results you expect the command to return. You can use the ExecuteNonQuery method to execute the command and return the number of rows affected. You should use this method if you are running any statements, such as Insert or Update, or a stored procedure that doesn't return any rows.
If your command object contains a Select statement or runs a stored procedure that returns a set of rows, you can use the ExecuteScalar, ExecuteReader, or ExecuteXmlReader methods to run the query. The ExecuteScalar method returns the value from the first row and first column of the result, while ignoring any other columns and rows. The ExecuteReader method returns a SqlDataReader object that contains the rows and columns generated by the query. The ExecuteXmlReader returns an XmlReader object in which the results of the query are returned as an XML document.
You can define a set of parameters for the SqlCommand through the Parameters property. This property points to a SqlParameterCollection object, which contains a set of SqlParameter objects. The SqlParameter object contains the name of the parameter, its data type, and value. If the SqlCommand object refers to a stored procedure, you can use the Direction property to specify a parameter as Input, Output, InputOutput, or ReturnValue. Thus, you can create a stored procedure that returns data through the parameters, which, for a single row, is even more efficient than returning a reader.
Readers, Adapters, and DataSets
The SqlReader class is similar to a read-only, forward-only ADO Recordset. SqlReader is optimized for fast performance but is also limited in its capabilities. You can't update data directly through a reader object, you can't rewind to the beginning, and you can only sequentially process the rows that are returned. However, the SqlReader class is ideal because it offers ASP.NET developers a fast, efficient way to retrieve display-only data from the database.
Just because you can't update data directly using the SqlReader doesn't mean you can't update the database. In fact, you'll find many high-performance ASP.NET applications use a SqlCommand object to return a SqlDataReader object with the data and then use another SqlCommand object to explicitly update the database. In both cases, the SqlCommand object would refer to a stored procedure that already exists in the database rather than specifying a SQL statement.
The SqlDataAdapter tool bridges the gap between the DataSet in-memory, data-storage class, and the database server. It contains references to a SqlConnection object, as well as references to the SqlCommand object for selecting, inserting, updating, and deleting database information. The Fill method will populate the information in the DataSet using the Select command specified. The Update method scans the DataSet for changes and uses the appropriate Input, Update, or Delete command to apply the changes to the database.
To make using the SqlDataAdapter easier, Microsoft also makes available a class called the SqlCommandBuilder, which creates the SqlCommand objects used by the SqlDataAdapter based on a Select statement you specified for the SqlDataAdapter. Note that this tool is limited to creating statements for a single database table only. If you have a more complex data structure, you can create your own SqlCommand objects for the data adapter.
Conclusion
In this article, I presented a brief overview of the ADO.NET object model. If you look closely, many of the programming concepts from ADO continue to exist in ADO.NET, including Commands and Connections, while the complex Recordset object in ADO has been split into two new classes, DataSets and Readers. DataSets are similar to disconnected Recordsets with many new features that make it easy to model complex data relations. Readers, on the other hand, are a simpler version of forward-and-read-only Recordsets.
In future columns, I plan to focus on various aspects, including binding data to controls, paging though a table, managing session state using a database, and creating middle-tier business objects. Because stored procedures are an important technique for ASP.NET database programmers, in a future column I'll write about how to incorporate stored procedures into your application. I'll include some tricks that allow you to reduce the amount of code you have to write. If you'd like to see any specific topics addressed in this column, or if you have questions about the material I cover, please drop me an e-mail at mailto:[email protected] and let me know. I'd love to hear from you.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including ASP.NET Database Programming Bible (Hungry Minds 2001), Windows Game Programming with Visual Basic and DirectX (QUE, 2001), and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds, 2000). He has nearly 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Wayne has a Master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected].
