Professional C# 2nd Edition : Data Access with .NET

Overview
In this chapter, we'll be discussing how to get at data from your C# programs using ADO.NET. Over the course of this chapter, we'll be covering the following areas:

• Connecting to the database - how to utilize the new SqlConnection and OleDbConnection classes to connect to and disconnect from the database. Connections utilize the same form of connection strings as did OLEDB providers (and therefore ADO), and these are briefly discussed. We then go through a set of best practices for utilizing database connections, and show how to ensure that a connection is closed after use, which is one of the sources of poor application performance.
• Executing Commands - ADO.NET has the concept of a command object, which may execute SQL directly, or may issue a stored procedure with return values. The various options on command objects are discussed in depth, with examples to show how commands can be used for each of the options presented by the Sql and OleDB classes.
• Stored Procedures - How to call stored procedures using command objects, and how the results of those stored procedures may be integrated back into the data cached on the client.
• The ADO.NET object model - this is significantly different from the objects available with ADO, and the DataSet, DataTable, DataRow, and DataColumn classes are all discussed. A DataSet can also include relationships between tables, and also constraints. These issues are also discussed.
• Using XML and XML Schemas - ADO.NET is built upon an XML framework, so we'll examine how some of the support for XML has been added to the data classes.

We'll also present a guide to the naming conventions that preside in the world of ADO.NET and explain some of the reasoning behind them. First, though, let's take a brief tour of ADO.NET and see what's on offer.

ADO.NET Overview
Like most of the .NET Framework, ADO.NET is more than just a thin veneer over some existing API. The similarity to ADO is in name only - the classes and method of accessing data are completely different.

ADO (Microsoft's ActiveX Data Objects) was a library of COM components that has had many incarnations over the last few years. Currently at version 2.7, ADO consists primarily of the Connection, Command, Recordset, and Field objects. A connection would be opened to the database, some data selected into a recordset, consisting of fields, that data would then be manipulated, updated on the server, and the connection would be closed. ADO also introduced the concept of a disconnected recordset, which was used where keeping the connection open for long periods of time was not desirable.

There were several problems that ADO did not address satisfactorily, most notably the unwieldiness (in physical size) of a disconnected recordset. This support was more necessary than ever with the evolution of "web-centric" computing, so a fresh approach was taken. There are a number of similarities between ADO.NET programming and ADO (not only the name), so upgrading from ADO shouldn't be too difficult. What's more, if you're using SQL Server, there's a fantastic new set of managed classes that are very highly tuned to squeeze maximum performance out of the database. This alone should be reason enough to move.

ADO.NET ships with two database client namespaces - one for SQL Server, the other for databases exposed through an OLE DB interface. If your database of choice has an OLE DB driver, you will be able to easily connect to it from .NET - just use the OLE DB classes and connect through your current database driver.

Namespaces
All of the examples in this chapter access data in one way or another. The following namespaces expose the classes and interfaces used in .NET data access:

• System.Data - All generic data access classes
• System.Data.Common - Classes shared (or overridden) by individual data providers
• System.Data.OleDb - OLE DB provider classes
• System.Data.SqlClient - SQL Server provider classes
• System.Data.SqlTypes - SQL Server data types

The main classes in ADO.NET are listed below:

Shared Classes
ADO.NET contains a number of classes that are used regardless of whether you are using the SQL Server classes or the OLE DB classes. The following are contained in the System.Data namespace:

• DataSet - This object may contain a set of DataTables, can include relationships between these tables, and is designed for disconnected use.
• DataTable - A container of data. A DataTable consists of one or more DataColumns, and when populated will have one or more DataRows containing data.
• DataRow - A number of values, akin to a row from a database table, or a row from a spreadsheet.
• DataColumn - Contains the definition of a column, such as the name and data type.
• DataRelation - A link between two DataTables within a DataSet. Used for foreign key and master/detail relationships.
• Constraint - Defines a rule for a DataColumn (or set of data columns), such as unique values.

These next two classes are to be found in the System.Data.Common namespace:

• DataColumnMapping - Maps the name of a column from the database with the name of a column within a DataTable.
• DataTableMapping - Maps a table name from the database to a DataTable within a DataSet.

Database Specific Classes
In addition to the shared classes above, ADO.NET contains a number of database-specific classes shown below. These classes implement a set of standard interfaces defined within the System.Data namespace, allowing the classes to be used if required in a generic manner. For example, both the SqlConnection and OleDbConnection classes implement the IDbConnection interface.

• SqlCommand, OleDbCommand - A wrapper for SQL statements or stored procedure calls.
• SqlCommandBuilder, OleDbCommandBuilder - A class used to generate SQL commands (such as INSERT, UPDATE, and DELETE statements) from a SELECT statement.
• SqlConnection, OleDbConnection - The connection to the database. Similar to an ADO Connection.
• SqlDataAdapter, OleDbDataAdapter - A class used to hold select, insert, update, and delete commands, which are then used to populate a DataSet and update the Database.
• SqlDataReader, OleDbDataReader - A forward only, connected data reader.
• SqlParameter, OleDbParameter - Defines a parameter to a stored procedure.
• SqlTransaction, OleDbTransaction - A database transaction, wrapped in an object.

The most important new feature of the ADO.NET classes is that they are designed to work in a disconnected manner, which is important in today's highly web-centric world. It is now common practice to architect a service (such as an online bookshop) to connect to a server, retrieve some data, and then work on that data on the client PC before reconnecting and passing the data back for processing. The disconnected nature of ADO.NET enables this type of behavior.

ADO 2.1 introduced the disconnected recordset, which would permit data to be retrieved from a database, passed to the client for processing, and then reattached to the server. This was often cumbersome to use, as disconnected behavior hadn't been designed in from the start. The ADO.NET classes are different - in all but one case (the Sql/OleDb DataReader) they are designed for use offline from the database.