ADO.NET Cookbook

Chapter 1. Connecting to Data

Introduction

This chapter describes how to connect to a variety of data sources from ADO.NET; how to handle security-related issues including storing connection strings and using different authentication methods; and how to set up, monitor, and optimize connection pooling.

ADO.NET Overview

ADO.NET is designed to support data access requirements of loosely coupled n-tier application architectures including web services. ADO.NET can access a variety of different data sources, including relational databases such as Microsoft SQL Server, Oracle, and Microsoft Access, as well as other data sources such as Microsoft Excel, Outlook, and text files.

A .NET data provider is used to connect to a data source, execute commands, and retrieve results. The .NET Framework ships with four .NET-managed data providers: Microsoft SQL Server, Oracle, OLE DB, and ODBC.

Other providers are also available—for example, Oracle has developed its own .NET data provider for Oracle. Data providers also exist for databases such as Sybase and MySQL. Database-specific providers usually access the underlying data store directly and offer the best performance, broadest functionality, and support for database-specific features. Since a data provider needs only to implement a set of standard interfaces, the capabilities and performance of data providers for the same data source can differ significantly.

In addition to database-specific providers, the OLE DB .NET data provider allows access to most OLE DB data sources through OLE DB providers. Similarly, the ODBC .NET data provider uses the ODBC drivers to access most ODBC data sources. You can also develop your own data provider to access proprietary data sources or to meet special requirements.

ADO.NET is fundamentally different from ADO despite sharing a similar name. ADO.NET is based on a disconnected architecture and has both connected and disconnected classes. Each data provider is responsible for providing the connected classes:

Connection

A unique session with the data source.

Command

Executes SQL statements and stored procedures against the data source.

DataReader

Forward-only, read-only access to a query result set stream.

DataAdapter

Bridges the connected classes with the disconnected classes; also used to fill a disconnected DataSet and update the data source with changes made to a disconnected DataSet.

The disconnected classes are part of the ADO.NET classes in the .NET Framework. They provide a consistent programming model regardless of the data source or data provider. The disconnected classes include:

DataSet

An in-memory relational database.

DataTable

A single table of memory-resident data.

DataColumn

The schema of a column in a DataTable.

DataRow

A row of data in the DataTable.

DataView

A data-bindable view of a DataTable used for custom sorting, filtering, searching, editing, and navigation.

DataRelation

A parent/child relationship between two DataTable objects in a DataSet.

Constraint

A constraint on one or more columns in a DataTable used to maintain data integrity.

The DataSet retains no information about the source of the data used to fill it with data. It maintains both current and original versions of data allowing the data source to be updated with changes at some future time. Disconnected data classes facilitate transport-independent marshaling vertically between application tiers and horizontally across a distributed application, and persisting data.

ADO.NET and XML converge in .NET. You can save the DataSet as an XML document, or fill it from an XML document. You can access and modify data simultaneously using both the DataSet classes and XML classes.

Connections, Connection Strings, and Connection Pooling

Database connections are a critical and limited resource. Connections must be managed to ensure that an application performs well and is scalable. SQL Server and Oracle data providers provide connection pooling while the OLE DB and ODBC providers use the pooling provided by OLE DB or ODBC respectively.

Connections should be opened as late as possible and closed as soon as possible using either the Close( ) or Dispose( ) method. The connection should be used as briefly as possible, meaning that connections should not last longer than a method call. Connections should not be passed between methods—in addition to creating performance problems and limiting scalability, this can lead to security vulnerabilities.

Data providers use a connection string containing a collection of attribute/value pairs to establish the connection with the database. Recipe 1.1 through Recipe 1.11 show how to connect to different data sources.

Connection strings need to be stored securely while still being configurable. Recipe 1.12 shows how to use different techniques to store connection strings and compare them. Recipe 1.13 shows how to use the Data Links Properties dialog box to allow the user to build connection strings at runtime.

SQL Server supports both SQL Server authentication and integrated authentication. SQL Server authentication is easier to program, but not as secure or manageable as integrated security, which uses Windows authentication to authenticate and authorize data access. Integrated security can be used from ASP.NET applications, but because the ASPNET account that is used by most ASP.NET applications has limited permissions, it needs to be configured to allow integrated security to be used from an ASP.NET application. Recipe 1.8 demonstrates this.

Connection pooling allows an application to reuse connections from a pool instead of repeatedly creating and destroying them. This can significantly improve the performance and scalability of applications by reusing connections and eliminating the overhead of establishing new connections. Recipe 1.15, Recipe 1.16, and Recipe 1.17 show how to take advantage of connection pooling, how to configure connection pooling with different .NET data providers, and how to use connection pooling together with transactions.

You can use the SQL Server Profiler to monitor SQL Server connections. You can also use the Windows Performance Monitor. .NET also adds Common Language Runtime (CLR) Data counters to the Window Performance Monitor that you can use to monitor, optimize, and troubleshoot connections in SQL Server. Recipe 1.14 shows how to monitor connections in ADO.NET applications.

.NET data providers implement common interfaces in their classes. You write database-independent code by operating on these interfaces rather than on the actual classes. Code becomes more portable but the database-specific functionality available in the provider is usually lost. Recipe 1.11 shows how to develop database-independent code by using common interfaces.

See About the Code in the Preface for information about the code shown in the solutions.