I'm going to be taking this next week, so I thought I'd post my study notes as I assemble them.

• May include but is not limited to: using the ConnectionStringBuilder; leveraging the ConfigurationManager; protecting the connection string; using Security Support Provider Interface (SSPI) or SQL Server authentication; correctly addressing the SQL Server instance; managing “User Instance” and AttachDBfilename

The ConnectionStringBuilder objects allow for an abstract way of building a connection string for ADO.NET.  There is a good writeup on the data access blog showing basic usage.  There is a SqlConnectionStringBuilder, OleDbConnectionStringBuilder, OdbcConnectionStringBuilder, and an OracleConnectionStringBuilder.

The ConfigurationManager has a specific section for storing connection strings and the ConnectionStrings property to manipulate them.  See this article on MSDN for more information.  An area that doesn't get much attention is that the Connection string configuration can be stored in a separate config file.  See the section titled "Using external configuration files" to see how to use the configSource property.

MSDN has an article on securing configuration strings.  An important part to pay attention to is encrypting connection string information stored in config files.  Scott Forsyth has a post on using aspnet_regiis to protect web.config files.  Daruish Tasdighi has an article at codeproject about protecting the contents using the ConfigurationSection.ProtectSection / UnprotectSection methods.

• Manage connection objects.
  May include but is not limited to: managing connection state, managing connection pool; implementing persistent data connections; implementing Multiple Active Result Sets (MARS); encrypting and decrypting data

Managing connection state.  I think that is knowing that there are methods on the connection object to open and close the connection.  The connection will be closed when the connection object is disposed.

There is a great post by Sijin Joseph about connection pooling with all the different connection objects.  I wasn't aware that there is no connection pooling with ODBC using the managed providers.  Also interesting was that connection pooling for SQL Server was disabled while debugging in Visual Studio.  Another thing to be aware of is that when using Integrated Security for SQL Server, the domain/user is added to the list of things that need to match for a connection to be reused.

To have a persistent data connection, you disable connection pooling and keep the instance of the connection object around.  The connection will remain open until the connection object is disposed or the close method is used.

Eric Moreau has a post on using MARS.  It is only supported in the SQL Server and Oracle managed providers. 

You can encrypt/decrypt the data passed from the client back and to the database server by adding the Encrypt keyword to the connection string for SQL Server or using the Encrypt property on the SqlConnectionStringBuilder.

• Work with data providers.
  May include but is not limited to: limitations, behaviors, performance, installation issues, deployment issues; ODBC, Microsoft OLE DB, SqlClient, managed providers, third-party providers, native providers

I guess this is a nice way of saying, "Know everything about ADO.NET".  Some of the things I'm aware of are the just because you have a managed provider available, you still need the native dll libraries.  Oracle, for example.  The client needs the oracle client libraries in order to connect to Oracle.  I mentioned some of the shortcomings of ODBC above in regards to pooling.  A great source for third party data providers can be found at the mono project (Firebird, MySql, SQLLite, PostgresSQL, Mimer, Sybase..)

• Connect to a data source by using a generic data access interface.
  May include but is not limited to: System.Data.Common namespace classes

The common namespace is the abstract definition for all data providers.  You would use this when you aren't sure which data provider your application will ultimately use.  Using the DbProviderFactory you can instantiate objects for a specific data provider.   Suman Chakrabarti has a post showing how to use the DbProviderFactory class.

• Handle and diagnose database connection exceptions.
  May include but is not limited to: implementing try/catch handlers

Joydip Kanjilal has a post on handling exceptions in ADO.NET.  If you catch a SqlException, you would examine the number property to determine the root cause of the problem.

Next -> Section II Selecting and Querying Data