UPGRADE: MCAD Skills to MCPD Web Developer by Using the Microsoft .NET Framework

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.

Again, I still haven't found a "Connection Wizard".  Referring to my earlier post, There is a Data Source Configuration Wizard. 

To configure a connection in the server explorer, right click on Data Connections node, and select Add New Connection.

Also, there is no Connection class.  There are a few objects that have a Connection property.  There are classes that implement IDbConnection, but strictly speaking, (unless we talking about sharepoint) there is no Connection class.  However, if you have a class that implements IDbConnection (SqlConnection, OleDbConnection...), you configure it using the ConnectionString property.

To connect to a SQL Server database, use the SqlConnection object.  Call the Open method after setting the connection string.

Alex Homer has an example of how to use DbProviderFactories.GetFactoryClasses at Devx.com.  It returns a datatable of all the classes that registered as DbProviderFactories in the machine.config.

To close the connection, invoke the close method on the connection object.  Obviously, an exception will be thrown if the connection is no longer open.  You can inspect the state property to see if the connection is still active.

To protect Datasource information, you're supposed to put the ConnectionStrings in the ConnectionStrings section of the web.config.  Then you encrypt the contents using the method outlined by David Hayden's site.

To create a connection that will stay in a pool, make sure your connections are connecting to the database with the same login.  Reportedly having minor differences in the connection string will cause it not to pool. 

There is information about controlling connection pooling at MSDN via ConnectionString values.

There are two events worth listening to on a SqlConnection.  InfoMessage (fired when warnings or informational messages are returned by SQL Server), and StateChanged (fired when the state of the connection has changed).

To handle exceptions when connecting to a database, catch SqlException (if SQL rejected the connection), or InvalidOperationException (The connection was already opened, or you haven't given the connection object enough info.)

To Begin a transaction on an open connection, call the BeginTransaction method on the SqlConnection object.  That returns a transaction object that you control the Commit or Rollback with.  There is a new locking level, snapshot isolation which bears understanding.

There is also a new Systems.Transaction namespace.  This is used for distributed transactions.  John Papa has a writeup on MSDN covering the feature.

Next up-> Data objects