Microsoft ASP.NET Programming with Microsoft Visual Basic .NET Version 2003 Step by Step [Electronic resources] نسخه متنی

Understanding ADO.NET

In classic ASP, the most common way to access data was through ADO. Developers used ADO Connection objects to connect to a database, and then used ADO Command and Recordset objects to retrieve, manipulate, and update data. When designing applications, particularly those with high scalability requirements or those whose back-end datasource might change at some point, developers needed to be careful not to tie in their front-end presentation code with their back-end database. Otherwise, they’d end up having to rewrite everything if there was a change in the back-end database.

The ADO.NET class architecture is factored somewhat differently from classic ADO. ADO.NET classes are separated into two major categories: datasource- specific and non-datasource-specific.

Understanding .NET Data Providers

Classes specific to a particular datasource are said to work with a specific .NET Data Provider, which is a set of classes that allow managed code to interact with a specific datasource to retrieve, update, and manipulate data. ADO.NET comes with two .NET Data Providers: the SQL Server .NET Data Provider, which provides optimized access to Microsoft SQL Server databases, and the OLE DB .NET Data Provider, which lets you connect to any datasource for which you have an OLE DB provider installed.

The following illustration shows the major classes of the SQL Server .NET Data Provider and how they relate to one another.

The SqlConnection class is used to establish a connection to a SQL Server database. Unlike the ADO Connection object, the SqlConnection class (or its OLE DB equivalent, the OleDbConnection class) cannot be used to execute SQLstatements against a datasource. The SqlConnection class is used solely for opening connections, setting or retrieving properties of a connection, or handling connection-related events. You’ll learn how to use the SqlConnection class to connect to a database later in this chapter.

The SqlCommand class is used to execute SQL statements or stored procedures against a SQL Server database. The SqlCommand class (and its OLE DB equivalent, the OleDbCommand class) can execute statements or stored procedures that do not return values, or that return single values, XML, or datareaders.

The SqlDataReader class provides forward-only, read-only access to a set of rows returned from a SQL Server database. Datareaders (including both the SqlDataReader and OleDbDataReader) provide lightweight, high-performance access to read-only data and are the best choice for accessing data to be displayed in ASP.NET.

The SqlDataAdapter class is used as a bridge between the DataSet class and SQL Server. You can use the SqlDataAdapter class to create a dataset from a given SQL statement or stored procedure represented by a SqlCommand instance, to update the back-end SQL Server database based on the contents of adataset, or to insert rows into or delete rows from a SQL Server database. TheOleDbAdapter class performs the same tasks for OLE DB datasources.

Understanding Datasets

The main class not specific to a datasource is the DataSet class. This is essentially an in-memory representation of one or more tables of data. This data can be read from an XML file or stream, or it can be the result of a query against a datasource.

One important thing about the DataSet class is that it doesn’t know anything about the datasource from which it receives its data, other than what that data is and sometimes the types of the data columns. (See the section on typed datasets later in this chapter.) The following illustration shows the major classes associated with the DataSet class and how they relate to one another. Note that the Rows, Columns, and Constraints objects are properties of the DataTable class.

The fact that a dataset knows nothing about the source of its data means that it’s abstracted from the back-end datasource. This is important because it means that if you pass a dataset from a component used for data retrieval to your WebForms page to be used for data-binding, your Web Forms page neither knows nor cares where the data comes from, as long as the dataset structure remains the same. You can change the back-end database without necessitating any changes in the Web Forms page, making it much easier to maintain a Webapplication.

Datasets contain a collection of tables, each of which contains a collection of rows, a collection of columns, and a collection of constraints. You can use these collections to get information on the objects contained within them, as well as to access and update individual data values. The dataset can also contain a collection of relationships between the tables it contains, allowing hierarchical data to be represented. You’ll learn about the DataSet class and related classes in detail later in this chapter.

Unlike classic ADO, in which XML support was added after the initial object model had been created, XML support has been designed into the ADO.NET model from the ground up. Classes such as SqlCommand, OleDbCommand, and DataSet have built-in support for reading and writing XML data. Datasets in particular can be built from or saved as XML, making them ideal for transporting data between tiers in a multi-tier application or for temporarily storing data to disk for later retrieval.