Just a note. My basketball finally won. On the last game before the playoffs (every team gets to go to the playoffs) we won 69-68 in overtime. Anyway here is tonights topic.

Section 2

• Part 2
  
  • Topic 2

 

Manage connections and transactions of databases.

• Configure a connection to a database graphically by using the Connection Wizard.
• Configure a connection 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 by using the DbProviderFactories.GetFactoryClasses method.
• Open a connection by using the Open method of a connection object.
• Close a connection by using the connection object.
• Secure a connection to protect access to your data source.
• Create a connection designed for reuse in a connection pool.
• Control connection pooling by configuring ConnectionString values based on database type.
• Use connection events to detect database information.
• Handle connection exceptions when connecting to a database.
• Perform transactions by using the ADO.NET Transaction object.

Summary

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 =
ConfigurationSettings.ConnectionStrings["Publications"];
DbProviderFactory f = DbProviderFactories.GetFactory(s.ProviderName);
if ((f.SupportedClasses & DbProviderSupportedClasses.DbConnection) > 0)
{
conn = f.CreateConnection();
conn.ConnectionString = s.ConnectionString;
}
return conn;
}

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 web 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
http://msdn2.microsoft.com/wxt2cwcc(en-US,VS.80).aspx

Data Source Configuration Wizard
http://msdn2.microsoft.com/w4dd7z6t.aspx

How to: Add New Data Connections in Server Explorer/Database Explorer
http://msdn2.microsoft.com/3d1wkhas.aspx

Generic Coding with the ADO.NET 2.0 Base Classes and Factories
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp

What .NET 2.0 Has in Store for ADO.NET
http://www.developer.com/net/net/article.php/3504146