Introducing ADO.NET

DataDriven

Languages: ADO.NET

IntroducingADO.NET

A NewWay to Access Your Database

By Wayne S.Freeze

Welcome to the first installment of a column dedicated todatabase programming with ASP.NET and related issues. This column is intendedfor professional programmers who use database servers such as SQL Server,Oracle9i, and DB2 in their Web applications. Because the best way tolearn about database programming is by studying code, this column will includelots of code examples written in Visual Basic. These samples will be availablefor download along with the equivalent code written in C# and JScript. Becausethese sample programs require a database, I'll also provide a sample databaseyou can download. With that said, let me add that this first column will besomewhat atypical without any code samples.

ADO.NET represents a completely new object model tailoredfor the .NET programming environment. It makes extensive use of inheritance andnamespaces to provide a more general solution for data access and manipulationthan existed in any of the previous object models. In this article, I want tointroduce the ADO.NET object model and discuss some of its key features.

BeforeADO.NET

Before .NET, Microsoft offered three different databaseobject models. The original object model, Data Access Objects (DAO) wasdeveloped to provide a way for Visual Basic programmers to use the Accessdatabase (also known as the Jet engine). However, using DAO for otherdatabases, such as SQL Server and Oracle, was slow and painful. To address thisproblem, Microsoft developed another object model called Remote Data Objects(RDO), which provided a high-performance way to access relational databaseslike SQL Server and Oracle.

Having two object models caused some problems because youcouldn't build a single application that worked for all database servers. So,Microsoft went back to the drawing board to develop another database objectmodel called ActiveX Data Objects (ADO). Although ADO was developed withrelational databases in mind, it represented the first step toward universaldata access. By using ADO, you could update non-database data sources, such asExchange Server and Excel worksheets, as if they were relational databases.

DAO, RDO, and ADO also reflect the low-level application-programminginterface (API) programmers use to access the database. Both DAO and RDO relyon the Open Database Connectivity (ODBC) API. While ADO relies on the OLE DBAPI, ADO also can communicate with ODBC databases if necessary.

The ADO.NETObject Model

As you might expect, ADO.NET offers a rich collection oftools for manipulating data in the .NET environment. It expands on ADO's goalof 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). Theprimary namespace is the System.Datanamespace, which contains a collection of classes and interfaces that providethe framework for ADO.NET. The System.Data.Commonnamespace contains tools that allow you to build data adapters for specificdata providers. The System.Data.OleDband the System.Data.SqlClientnamespaces contain classes that use the OLE DB data provider and the SQL Serverdata provider. Finally, the System.Data.SqlTypes contains information about the data typesavailable in SQL server.

FIGURE 1: The ADO.NET objectmodel.

ADO.NET is more complex than ADO for several reasons.First, the facilities to create other data providers are included in the objectmodel even though most programmers will never use them. Second, rather thanusing a small collection of general-purpose objects, ADO.NET uses a largercollection of focused objects, which provide better performance and increasedfunctionality. Finally, the object-oriented nature of the .NET Frameworkencourages the use of lots of classes.

ExploringSystem.Data

The System.Datanamespace serves two main purposes. It houses the classes necessary toimplement the DataSet class andincludes a number of interfaces the other ADO.NET classes use.

The DataSetclass (see FIGURE 2) is the cornerstone of ADO.NET. The purpose of the DataSet class is to provide you with anobject that can hold a collection of data in memory that is independent of thedata source. This is similar to the concept of a disconnected Recordset object in ADO. Unlike ADO,however, the DataSet class permitsyou to store multiple sets of information. You can even create hierarchicalrelationships using this data.

FIGURE 2: The DataSet class hierarchy in System.Data.

The DataTableclass is similar to a relational database table in that it stores data as acollection of rows and columns. You can retrieve the name using the TableName property and access theschema information for the table through the Columns property. The Rowsproperty contains an object reference to the collection of rows in the DataTable.

The Columnsproperty contains a reference to the DataColumnCollectionobject, which in turn contains a set of DataColumnobjects that describe each column in the table. The ColumnName property contains the name of the column as it's knownin the database. The DataTypeproperty contains an object reference to the Type object associated with the column in the table. A number ofother properties are available in the DataColumnobject, including DefaultValue,which contains the default value for the column; AutoIncrement, which means the value of the column will beincremented automatically when the row is saved to the database; and Caption, which suggests a text stringthat can be used to describe the column.

Each row in the DataTableis stored in a DataRow object. Theset of rows is stored in a DataRowCollectionand can be referenced through the Rowsproperty of the DataTable. The individualcolumns in the row can be referenced through the Item property.

The DataRelationclass allows you to define a parent-child relationship between two DataTables. This allows you to build ahierarchical representation of data, such as a customer-order relationship or asupplier-parts relationship, which can be useful when displaying information ona Web page.

The DataView class represents another way you canaccess data from a DataTable. You can filter and sort rows locallywithout interacting with the database server. You can also insert, update, ordelete rows through a DataView. However, the primary purpose of a DataViewis to allow you to bind controls on a Web form to the data in a DataTable.To make data binding simpler, each DataTablehas a property, DefaultView, whichreturns a DataView representing allof the rows and columns of the DataTable.

System.Data.SqlClientvs. System.Data.OleDb

The System.Data.SqlClientand System.Data.OleDb namespaces arefairly similar (see FIGURE 3). They provide the classes that are used tocommunicate with and execute commands on a database server. The main differencebetween the two namespaces is the provider used to retrieve data from thedatabase. System.Data.SqlClientcommunicates with a SQL Server database using the database's internal API,while System.Data.OleDb communicateswith any database that supports the OLE DB standard.

FIGURE 3: A comparison of thekey classes in System.Data.SqlClientand System.Data.OleDb.

Because the SQL Server internal API is more efficient thanis the OLE DB provider, you should use the classes in the System.Data.SqlClient when communicating with a SQL Serverdatabase. If you are still using SQL Server 6.5 or older, you must stick withthe System.Data.OleDb namespacebecause these databases do not support the internal SQL Server API. Of course,by using the System.Data.SqlClientnamespace, you will have to modify your application if you ever choose to moveit to another database server.

Over time, I believe other database vendors will developother System.Data namespaces thattake advantage of the database's native API. The basic tools needed toconstruct these namespaces reside in the System.Datanames as a collection of interface objects.

Digginginto System.Data.SqlClient

Because the classes in the System.Data.SqlClient and System.Data.OleDbnamespaces are so similar, I'm only going to discuss the classes in the System.Data.SqlClient namespace. Youcan find the equivalent classes in the System.Data.OleDbnamespace by changing SqlClient to OleDb in the class name.

The SqlConnectionclass contains the information necessary to connect to a database server. The Openmethod creates a session with the database server, while the Close method terminates the sessionwith 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 finishedusing it. It is far more efficient to open and close a connection many timesthan to leave it open for a long period of time.

Note that even though .NET will automatically close aconnection when the connection object is destroyed, you shouldn't rely on .NETto close the connection in a timely fashion. Garbage collection runsasynchronously, and it may be a long time before the connection actuallycloses.

The SqlCommandclass corresponds to the old ADO Commandobject. This class contains the information necessary to execute a SQLstatement or stored procedure. To execute the SQL statement or storedprocedure, SqlCommand requires analready opened SqlConnection object.

There are four ways to execute a command object, dependingon the results you expect the command to return. You can use the ExecuteNonQuery method to execute thecommand and return the number of rows affected. You should use this method ifyou are running any statements, such as Insertor Update, or a stored procedurethat doesn't return any rows.

If your command object contains a Select statement or runs a stored procedure that returns a set ofrows, you can use the ExecuteScalar,ExecuteReader, or ExecuteXmlReadermethods to run the query. The ExecuteScalarmethod 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 SqlDataReaderobject that contains the rows and columns generated by the query. The ExecuteXmlReader returns an XmlReader object in which the resultsof the query are returned as an XML document.

You can define a set of parameters for the SqlCommand through the Parameters property. This propertypoints to a SqlParameterCollectionobject, which contains a set of SqlParameterobjects. The SqlParameter objectcontains the name of the parameter, its data type, and value. If the SqlCommand object refers to a storedprocedure, you can use the Directionproperty to specify a parameter as Input,Output, InputOutput, or ReturnValue.Thus, you can create a stored procedure that returns data through theparameters, which, for a single row, is even more efficient than returning areader.

Readers,Adapters, and DataSets

The SqlReaderclass is similar to a read-only, forward-only ADO Recordset. SqlReader isoptimized for fast performance but is also limited in its capabilities. Youcan't update data directly through a reader object, you can't rewind to thebeginning, and you can only sequentially process the rows that are returned.However, the SqlReader class isideal because it offers ASP.NET developers a fast, efficient way to retrievedisplay-only data from the database.

Just because you can't update data directly using the SqlReader doesn't mean you can't updatethe database. In fact, you'll find many high-performance ASP.NET applicationsuse a SqlCommand object to return a SqlDataReader object with the data andthen use another SqlCommand objectto explicitly update the database. In both cases, the SqlCommand object would refer to a stored procedure that alreadyexists in the database rather than specifying a SQL statement.

The SqlDataAdaptertool bridges the gap between the DataSetin-memory, data-storage class, and the database server. It contains referencesto a SqlConnection object, as wellas references to the SqlCommandobject for selecting, inserting, updating, and deleting database information.The Fill method will populate theinformation in the DataSet using theSelect command specified. The Update method scans the DataSet for changes and uses theappropriate Input, Update, or Delete command to apply the changes to the database.

To make using the SqlDataAdaptereasier, Microsoft also makes available a class called the SqlCommandBuilder, which creates the SqlCommand objects used by the SqlDataAdapterbased on a Select statement youspecified for the SqlDataAdapter.Note that this tool is limited to creating statements for a single databasetable only. If you have a more complex data structure, you can create your own SqlCommand objects for the dataadapter.

Conclusion

In this article, I presented a brief overview of theADO.NET object model. If you look closely, many of the programming conceptsfrom ADO continue to exist in ADO.NET, including Commands and Connections,while the complex Recordset objectin ADO has been split into two new classes, DataSets and Readers. DataSets are similar to disconnected Recordsets with many new features thatmake it easy to model complex data relations. Readers, on the other hand, are a simpler version offorward-and-read-only Recordsets.

In future columns, I plan to focus on various aspects,including binding data to controls, paging though a table, managing sessionstate using a database, and creating middle-tier business objects. Becausestored procedures are an important technique for ASP.NET database programmers,in a future column I'll write about how to incorporate stored procedures intoyour application. I'll include some tricks that allow you to reduce the amountof code you have to write. If you'd like to see any specific topics addressedin this column, or if you have questions about the material I cover, pleasedrop 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 hiscredit, including ASP.NET Database ProgrammingBible (Hungry Minds2001),Windows Game Programming with Visual Basic andDirectX (QUE, 2001), and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds, 2000). He hasnearly 25 years of experience using all types of computers, from small,embedded microprocessor control systems to large-scale IBM mainframes. Waynehas a Master's degree in management information systems as well as degrees incomputer science and engineering. You can visit his Web site at http://www.JustPC.comand send him e-mail at mailto:[email protected].