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.
I've never been one to make use of a lot of wizards. I was not able to find a "Connection Wizard". I was able to find the "Data Source Configuration Wizard". Not sure if that's what they mean. In the Data Source connection wizard, if you specify that you want a Database data source, you are then prompted to select a connection. On that screen you can push the New Connection button, which brings up a Choose Data Source dialog. I'm not sure I'd call it a wizard. Again, if anyone finds the "Connection Wizard", let me know where it is.
Configuring a connection in the Server Explorer is as easy as right clicking on the Data Connections node, and selecting add new connection.
There are a few objects that have a Connection property, but there is no Connection class. 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 database, you instatiate a SqlConnection instance, set the Connectionstring, and call the Open method. There is now a SqlConnectionStringBuilder class to help you build a connection string.
To enumerate through SQL Instances, use the SqlDataSourceEnumerator as explained by Sushil on his blog.
To Close an open connection to a database, use the Close method on the Connection object. You can also call Dispose. This isn't on topic, but while I was looking around, I noticed they added a ClearPool method to the SqlConnection object. Nice!
To protect Datasource information, you're supposed to put the ConnectionStrings in the ConnectionStrings section of the app.config. Then you're supposed to encrypt it. What's fustrating is that there are dozens of examples of how to do this for ASP.NET. Near as I can figure, it looks like you would, during installation with an installer class, get a reference to the SectionInformation and call the ProtectSection method.
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 on MSDN.
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-> XML Reader/Writer