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.

Important

The OLE DB .NET Data Provider does not support the OLE DB 2.5 interfaces. This means that you cannot use the Microsoft OLE DB Provider for Exchange and the Microsoft OLE DB Provider for Internet Publishing with the OLE DB .NET Data Provider. Additionally, the OLE DB .NET Data Provider is incompatible with the OLE DB Provider for ODBC. For accessing data via ODBC, use the.NET Framework Data Provider for OCBD, which is integrated into the Microsoft Windows .NET Framework version 1.1. Also new in version 1.1 of the Framework is the .NET Framework Data Provider for Oracle.

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 SQL statements 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 a dataset, or to insert rows into or delete rows from a SQL Server database. The OleDbAdapter class performs the same tasks for OLE DB datasources.

Important

You may have noticed that in the discussion of the classes that make up the SQL Server .NET Data Provider, the names of the classes start with Sql rather than SQL. This is because the names of the classes in the .NET Framework use Pascal casing (after the style of the Pascal language), in which the first character of each distinct word in a given class is capitalized. This naming convention is especially important because you must declare class and namespace names with the proper case when using a case-sensitive language such as C#. Using the incorrect case name (for example, SQLConnection instead of SqlConnection) will result in a compiler error. So if you get an error complaining that the type isn’t defined or the namespace doesn’t exist, you’re probably dealing with a capitalization problem. But even in a language that isn’t case sensitive, such as Visual Basic .NET, using the proper case for namespaces and classes will result in code that’s easier to read and maintain.

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 Web Forms 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 Web application.

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.