Chapter 1. Connecting to Data
Recipe 1.1. Connecting to an ODBC Data Source
Recipe 1.2. Connecting to a Microsoft Excel Workbook
Recipe 1.3. Connecting to a Password-Protected Access Database
Recipe 1.4. Connecting to a Secured Access Database
Recipe 1.5. Connecting to an Access Databasefrom ASP.NET
Recipe 1.6. Using an IP Address to Connect to SQL Server
Recipe 1.7. Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)
Recipe 1.8. Connecting to SQL Server Using Integrated Security from ASP.NET
Recipe 1.9. Connecting to an Oracle Database
Recipe 1.10. Connecting to Exchange or Outlook
Recipe 1.11. Writing Database-Independent Code
Recipe 1.12. Storing Connection Strings
Recipe 1.13. Using the Data Link Properties Dialog Box
Recipe 1.14. Monitoring Connections
Recipe 1.15. Taking Advantage of Connection Pooling
Recipe 1.16. Setting Connection Pooling Options
Recipe 1.17. Using Transactions with Pooled Connections
Recipe 1.18. Changing the Database for an Open Connection
Recipe 1.19. Connecting to a Text File
This chapter describes how to connect to a variety of data
sources from ADO.NET; how to handle security-
ADO.NET is designed to support data access requirements of
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 availablefor 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
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
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:
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
ADO.NET and XML converge in .NET. You can save the
as an XML document, or fill it from an XML
document. You can access and modify data
Connections, Connection Strings, and Connection Pooling
Database connections are a critical and limited resource.
Connections must be managed to ensure that an application
Connections should be opened as late as possible and closed as
soon as possible using either the
method. The connection should be used as
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
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
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.