4GuysFromRolla.com - Data Manipulation with ADO.NET

Distributed Applications Developer
IT Search
US-TX-Dallas

Published: Friday, September 07, 2001

ASP.NET: Tips, Tutorials, and Code
Chapter 6: Data Manipulation with ADO.NET

by Steve Walther

In this chapter you will learn the following:

How to open a database connection and perform common database operations such as inserting, updating, and deleting database records.

How to use a DataReader to represent records retrieved from a database table.

How to use parameters and execute stored procedures from an ASP.NET page.

How to create a memory resident database using DataSets and DataTables.

atabase access is a crucial component of almost any ASP.NET application. Fortunately, the .NET framework contains a rich set of classes and controls for working with database data in your ASP.NET pages.

We'll approach the subject of database access by dividing the topic into two chapters. In this chapter, you'll be given an overview of ADO.NET, the data access technology built into the .NET framework. You'll learn how to use the ADO.NET classes to perform standard database tasks such as modifying and accessing database data. We'll also cover some of the more advanced features of ADO.NET, such as stored procedure support and filtering and sorting data.

In the next chapter, "Data Presentation," you'll learn how to use ADO.NET with ASP.NET controls. You'll learn how to use ADO.NET to bind database data to standard Web Controls, such as the DropDownList and RadioButtonList controls. You'll also learn how to display and edit database data using the more specialized data controls such as the Repeater, DataList, and DataGrid controls.

1. Overview of ADO.NET

Let's begin with a quick tour of ADO.NET. The .NET Framework contains several namespaces with dozens of classes devoted to database access. However, for the purposes of explaining ADO.NET in this chapter, I'll make a rough division of these classes into three groups.

The first group consists of the following three classes:

SqlConnection
SqlCommand
SqlDataReader

If you plan to build your ASP.NET application with Microsoft SQL Server (version 7.0 or greater), these are the classes you'll use most often. These classes enable you to execute SQL statements and quickly retrieve data from a database query.

The SqlConnection class represents an open connection to a Microsoft SQL Server database. The SqlCommand class represents a SQL statement or stored procedure. Finally, the SqlDataReader class represents the results from a database query. We'll go into the details of using each of these classes in the next section of this chapter.

Note

If you have used the ActiveX Data Objects (ADO), these three classes should be very familiar. The SqlConnection and SqlCommand classes are similar to the ADO Connection and Command objects, with the important exception that they work only with Microsoft SQL Server.

The SqlDataReader class is similar to an ADO Recordset object opened with a fast, forward-only cursor. However, unlike a Recordset object, the SqlDataReader class does not support alternative cursor types and it only works with Microsoft SQL Server.

These classes work only with Microsoft SQL Server. If you need to work with another type of database, such as an Access or Oracle database, you will need to use the following classes:

OleDbConnection
OleDbCommand
OleDbDataReader

Notice that these classes have the same names as the ones in the previous group, except these classes start with OleDb rather than Sql.

Why did Microsoft duplicate these classes, creating one version specifically for SQL Server and one version for non–SQL Server databases? By creating two sets of classes, Microsoft was able to optimize the performance of the first set of classes specifically for SQL Server.

The OleDb classes use OLEDB providers to connect to a database. The Sql classes, on the other hand, communicate with Microsoft SQL Server directly on the level of the Tabular Data Stream (TDS) protocol. TDS is the low-level proprietary protocol used by SQL Server to handle client and server communication. By bypassing OLEDB and ODBC and working directly with TDS, you get dramatic performance benefits.

There's one last group of classes that we'll work with in this chapter. This group contains the following classes:

SqlDataAdapter
OleDbDataAdapter
DataSet
DataTable
DataRelation
DataView

You can use the classes in this group to build a memory-resident representation of a database (an in-memory database). A DataSet represents the in-memory database itself. Once you create a DataSet, you can populate it with one or more DataTables that represent database tables. You create the DataTables with the help of either the SqlDataAdapter or OleDbDataAdapter class. You can then define various relationships between the tables with the DataRelation class and create filtered or sorted views on the DataTables with DataViews.

Why would you want to build an in-memory database? In certain situations, it is useful to have all the data from a database table available to your ASP.NET application in such a way that it is disconnected from the underlying database. For example, using the classes from this group, you can cache one or more database tables in your server's memory and use the same data in multiple ASP.NET pages. You'll see some other applications of these classes in the final section of this chapter.

Note
If you are an experienced ADO developer, it might be helpful to think of a DataTable as a disconnected, client-side, static Recordset.

Read Part 2