- Part 2
- Topic 2
Manage connections and transactions.
- Configure a connection to a database by using the Connection Wizard.
- Configure a connection to a database by using Server Explorer.
- Configure a connection to a database by using the Connection class.
- Connect to a database by using specific database Connection objects.
- Enumerate through instances of Microsoft SQL Server.
- Open an ADO.NET connection to a database.
- Close an ADO.NET connection to a database by using the Close method of the Connection object.
- Protect access to data source connection details.
- Create a connection designed for reuse in a connection pool.
- Control a connection pool by configuring ConnectionString values based on database type.
- Use the Connection events to detect database information.
- Handle exceptions when connecting to a database.
- Perform transactions by using the Transaction object.
The Connection wizard and Server Explorer both allow you to easily configure Database connections for use in your applications. See the resources section if you are unfamiliar with creating Database connections.
With ADO.Net 2.0, there are new classes that are more generic classes for Connections and a variety of tools have been provided to make it easier to configure and use them in provider independent ways. The DbConnection Class is the base class for the SqlClient Class and can be used to make your code provider independent.What’s more is that connection objects can be created through using Factories, similar to the Enterprise Library Data Access Application block. The best way to understand the concepts is to walk through a code snippet found in the “Generic Coding with the ADO.NET 2.0 Base Classes and Factories” article mentioned in the resources section.
public DbConnection GetInitializedConnectionBaseClass()
DbConnection conn = null;
ConnectionStringSettings s =
DbProviderFactory f = DbProviderFactories.GetFactory(s.ProviderName);
if ((f.SupportedClasses & DbProviderSupportedClasses.DbConnection) > 0)
conn = f.CreateConnection();
conn.ConnectionString = s.ConnectionString;
In the sample above, the conn variable is of type DbConnection so that it can hold a reference to any type of Connection that the factory will create. The Connection string is loaded from the web config. The Connection string references a provider which is also defined in the machine or web config. This provider is used to create a provider specific instance of the factory. This instance can then be used to create the connection object itself.
ADO.Net also introduces a data source enumerator class that can be initialized from any factory. To enumerate through SQLServer, create a factory pointing to the SQL Server Provider then use code like the following:
DbDataSourceEnumerator e = f.CreateDataSourceEnumerator();
DataTable t = e.GetDataSources();
You can then cycle through the data table to get at each data source. A specific example is provided in the resources article: Coding with the ADO.NET 2.0 Base Classes and Factories.
To secure the connection string in your app config file, you can use the aspnet_regiis tool, but this is really only appropriate for web applications as it uses keys defined at the machine level to encrypt the data.
Connection Pooling is still managed in the ConnectionString itself with keywords like Max Pool Size and Min Pool Size, but ADO.Net has one additional feature when it comes to pooling. The Connection object now has a static static method to Clear the bool for a specific instance of a Connection. See the second page of the What .NET 2.0 Has in Store for ADO.NET article listed in resources for details.
The connection object also exposes two events, the infomessage which is fired when informational messages are returned from the data source and the more useful StateChange event which is fired when the state of the connection changes.
As far as exception handling goes, there is now a base exception type called DbException which is the base type for SqlException and other provider specific exceptions.
The Connection Object also exposes a BeginTransaction method which can be assigned to a generic DBTransaction type. Each provider implements their own specific type (SqlTransaction). The Transaction can then be assigned to the DBCommand object or specific provider Command (SqlCommand).
Other Resources & Links:
Connecting to Data in Visual Studio Overview
Data Source Configuration Wizard
How to: Add New Data Connections in Server Explorer/Database Explorer
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
What .NET 2.0 Has in Store for ADO.NET
How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA
Working with Connection Events