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 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:
A unique session with the data source.
Executes SQL statements and stored procedures against the data source.
Forward-only, read-only access to a query result set stream.
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
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:
An in-memory relational database.
A single table of memory-resident data.
The schema of a column in a
A row of data in the
A data-bindable view of a
used for custom sorting, filtering, searching, editing, and
A parent/child relationship between two
objects in a
A constraint on one or more columns in a
used to maintain data integrity.
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
DataSet classes and XML classes.
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.