The framework database programming classes are referred to as ADO.NET. ADO.NET introduces the DataSet class that works with relational data in a relational manner while you are disconnected from any data source. You need not connect and update or query the database unless you have a specific reason for doing so. You can, of course, work in the traditional connected manner if you choose.
ADO.NET data providers  allow you to execute commands directly against the data source. Functionality is exposed directly without intermediary objects such as OLEDB, which stands between ADO and the data source. The .NET DataAdapter class models a data source as a set of database commands and a connection to that data source. Differences between data sources are not hidden by generic interfaces. The OLEDB data provider allows for nested transactions with data sources that support that functionality; the SqlServer data provider does not. 
 .NET Data Providers are what used to be called in the beta literature managed providers. You may still see them referred to by that term .
 There is a Begin method on the OleDbTransaction class; the SqlTransaction class does not have such a method.
.NET Data Providers supply data to a dataset or a data reader. A dataset is a memory-resident, lightweight relational database that is not connected to any database. You can also obtain a dataset from an XML document or create an XML document from a dataset. This allows you to work, if it makes sense, with your data as relational data, or as hierarchical XML data.  Data readers model the traditional method of working with a database.
 The many-to-many relations that you can have in a relational database do not automatically map to XML hierarchies. But this is no different from working with the classic object-relational model clash .
The data access classes that currently ship with the framework are found in the namespaces: System.Data , System.Data.SqlClient, System.Data.OleDb , System.Data.Common , and System.Data.SqlTypes . The Sql and OleDb namespaces reflect the SqlServer and OleDb .NET Data Providers. An ODBC .NET Data Provider has been written, and additional ones will be written in the future.
This chapter changes the implementation of the Customer and Hotel assemblies of the Case Study to use SQL Server. An air travel service that the Acme Travel Agency can use to make air travel reservations is added to illustrate the use of XML.
To make our examples concrete we use SQL Server 2000 and the SQL Server data provider.  Nonetheless, much of the basic functionality discussed in this chapter applies to the OleDb data provider as well.
 If you do not have an SQL Server available you can go to the Microsoft site and download the MSDE, which is a scaled down version of SQL Server. As of this writing MSDE is available for free. Microsoft suggests using MSDE in the future instead of Access. Since we use vanilla functionality you should be able to use the OleDb data provider against the Access version of the Northwind traders by changing the Sql classes to the corresponding OleDb classes. We have not yet tested this scenario, however.
This chapter assumes you have some understanding of database concepts.
Setting Up the Example Databases
This chapter assumes that SQL Server 2000 has been installed using the Local System account, with authentication mode set to Mixed Mode. The user is assumed to be sa, with a blank password.
Several examples in this chapter make use of the Northwind Traders sample database, which is installed along with SQL Server. In addition, there are other example programs that use the HotelBroker and AirlineBroker databases, which are supplied specifically for use with this book.
Some of the example programs make changes to these databases, and other examples assume a freshly installed database. This means that some of the examples will not always work as expected unless you reinstall them again. You can reinstall each of these databases by running the SQL scripts that are provided.
Please refer to the readme.txt file in the sample code directory for this chapter for more information about database setup.