Using the Connection Methods

There are a host of methods on the SqlConnection class and several new ones implemented in the 2.0 Framework. I've already talked about some of these, including the Open and Close methods, and how you don't necessarily have to use these in your applications (if you use the SqlDataAdapter Fill or Update methods). Table 9.4 lists the key SqlConnection methods. I'll leave out those that you won't be likely to need and focus on those you'll use or abuse.

Table 9.4. Key SqlConnection Methods

Method	DataType (Default)	Description
ChangeDatabase	String	Requests initial catalog/default database to be changed (see discussion).
ChangePassword	String, String	New for 2.0 Framework. Permits ADO.NET to change a Login account's password (see discussion).
ClearAllPools	(none)	New for 2.0 Framework. Clears all connection pools of idle connections owned by this process (see discussion).
ClearPool	SqlConnection	New for 2.0 Framework. Clears specific pool of idle connections (see discussion).
Close	(none)	Set a pooled connection as idle or closes an unpooled physical connection (see discussion).
Dispose	(none)	Closes the connection and releases its resources (see discussion).
New	(none)	Creates a new (empty) instance of the SqlConnection class. You'll have to fill in the properties by hand before the Connection can be used.
New (constructor)	String	Creates a new instance of the SqlConnection class using a ConnectionString to initialize the instance (see discussion).
Open	(none)	Attempts to open a physical connection to the Data Source specified in the SqlConnection instance properties (see discussion).
ResetStatistics	(none)	If StatisticsEnabled is true, this method flushes the current set of operational statistics. (See the StatisticsEnabled property.)
BeginTransaction	(various)	Starts and returns an ADO.NET-managed Transaction-based on the arguments passed in the constructors (see discussion).
CreateCommand	(none)	Creates an empty SqlCommand instance associated with the connection (rarely used).
GetSchema	(various)	New for 2.0 Framework. Returns schema information from the Data Source as a DataTable (see discussion).
RetrieveStatistics	(none)	Returns an IDictionary set of name/value pairs containing the Connection operational metrics (see the StatisticsEnabled property).

Let's take a closer look at some of the more important methods.

• ChangeDatabase: This method requests that ADO.NET change the initial catalog (database) or default database setting. Of course, this assumes that your credentials have rights on the new default database. If you don't or the string supplied is not a valid database name, ADO.NET throws an exception. This change (if it succeeds) is temporary. That is, when you close the connection and it's returned to the pool, the original connection string setting is appliedeven if there is no value set for the Initial Catalog/Database keyword. In this case, the initial catalog is set based on the default database assigned to the Login account. This means if your application reopens the connection, the initial catalog is the same as when the Connection is first opened.

• ChangePassword: When the SQL Server Login account password expires, your application will trip a SqlException with the Number property set to 18487 (password expired) or 18488 (password must be reset). The new 2.0 ChangePassword method permits you to change the password. The first parameter must be a valid ConnectionString using the Login name that failed and the existing (expired) password, along with enough information to address the Data Source. The second parameter must be a valid password that conforms to whatever password restrictions are set by the SQL Server configuration.

• ClearAllPools: This new 2.0 method (of the SqlConnection class) closes the physical connection of all idle connections in all pools created by the current process. Those connections in the pool that are still open are marked for permanent closure, so once you execute Close (or Dispose), these connections are closed.

  The pool flusher methods are useful in situations where you need to ensure that no connections are being held in the pool while you perform administrative work on the serverfor example, when detaching databases or attempting to switch SQL Server into "single-user" mode as required by the recovery routines. No, don't expect to hear any noise while these methods executethey don't make a flushing sound except in parts of Australia where the water spins in the opposite direction.

  
  

• ClearPool: This new 2.0 method (of the SqlConnection class) closes the physical connection of all idle connections in a specific pool, as referenced by the SqlConnection object. Those connections in the pool that are still open are marked for permanent closure, so once you execute Close (or Dispose), these connections are closed. The following example (see Figure 9.30)^[19] shows the code to clear the connection pool for a specific SqlConnection object (cn) and all pools for the current process.

  ^[19] See the "Connection Methods" example on the DVD.

  Figure 9.30. Flushing the connection pools.

  

• Close: This method sets a pooled connection to "idle" and changes the State to Closed. If applied to unpooled SqlConnection objects, the physical connection is closed. Closing a connection rolls back any pending transactions. Never depend on an object's Finalizer to close connections for you.

• Dispose: The Dispose method calls the Close method, releases all resources held by the Connection, and resets its propertiesit's not necessary to call both Close and Dispose. Don't use Dispose if you plan to reuse the SqlConnection object.

• New (Constructor): The New method creates a new instance of the SqlConnection class. If you pass a string argument, it's considered to be a valid ConnectionString. The constructor code parses the ConnectionString and populates the Connection properties. If an invalid argument is supplied, a System.ArgumentException is thrown.

• Open: This method takes the properties set in the ConnectionString property and attempts to open a connection to the specified data source using the credentials and option settings provided. I've discussed this at length throughout this chapter. Once you open the connection, the ConnectionString property is frozen. If you set "Persist Security Information" to true in the ConnectionStrin any access to the ConnectionString property is returned stripped of its security credentials. Most of the other properties are also frozen once the connection has been opened at least once.

• BeginTransaction: This method initiates a SQL Server Transaction and marks the Connection as participating in a transaction context. Once a SqlConnection is enlisted in a Transaction, it cannot be reused, so an independent pool is created to manage it. Closing a SqlConnection automatically rolls back any pending transaction.

• Caution: If you initiate a Transaction and a Severity 16 (or higher) error occurs, the transaction is not automatically rolled backthat's up to you.

• GetSchema: This new method for the 2.0 Framework permits tool developers to retrieve data structures (schema) information about the data source, database, table, columns, or any specific object in the database. I'll be focusing on SQL Server here, but this technology is included in all of the providers, so OLE DB developers can use it to mine the schema of their JET databases. While coding the GetSchema method is very simple (as shown in Figure 9.31), the voluminous data it returns seems somewhat daunting at first.

  Figure 9.31. Using GetSchema to fetch a list of databases visible on the Connection.

  

I've created a simple example application^[20] to view and filter the metadata returned by the GetSchema method. I expect that simply running this application will lead to a much better understanding of how GetSchema works.

^[20] See "GetSchemaExample" on the DVD.

GetSchema works against a specific SQL Server connection and a default database. You can point to another database (as I do in the example application) by using the Connection.ChangeDatabase method. GetSchema returns a DataTable containing a set of rows that contains metadata from the selected MetaDataCollection elements. These entries are shown in Table 9.5.

Table 9.5. GetSchema Collections and What They Contain

Collection Name	Description
DataSourceInformation	Returns metrics regarding SQL Server and how to interface with it.
DataTypes	Lists datatypes and their metrics, including column size, parameters need to create the type, and more.
Restrictions	Lists the server-dictated restrictions on access to the listed elements.
ReservedWords	Lists the strings used in T-SQL and elsewhere that may not be used as identifiers, like database, table, view, stored procedure, or column names.
Users	Lists all user names.
Databases	Lists all databases
Tables	Lists all tables by database. Note only the default database is listed.
Columns	Lists all data table columns, including all column metrics.
Views	Lists all views and its metrics.
ViewColumns	Lists all columns of all views.
ProcedureParameters	Lists all parameters for all data source stored procedures.
ForeignKeys	Lists all foreign keys and their metrics.
IndexColumns	Lists all Columns from all Indexes and their metrics.
Indexes	Lists all Indexes and their metrics.
UserDefinedTypes	Lists all CLR User-defined datatypes.

Since GetSchema returns a DataTable that includes all columns or parameters from the entire database, you'll want to set limits to focus on just those objects you need to examine. To this end, ADO.NET exposes "Restrictions" that are used to limit the amount of metadata returned from GetSchema. These Restrictions are implemented by passing an array of strings to the GetSchema method (as shown in Figure 9.32). The array must map to the Restrictions metadata returned for each metadata object. Yes, this is returned by the same methodGetSchema ("Restrictions") returns a DataTable with all of the Restrictions for all of the objects (that have restrictions). Figure 9.34 shows a DataGridView populated from a bound DataTable containing the valid Restrictions for a selected metadata element (in this case, ProcedureParameters).

Figure 9.32. A DataGridView control bound to a DataTable containing valid Restrictions.

Figure 9.33 shows how I take the contents of a DataTable containing all of the Restrictions (that's been bound to the DataGridView) for the specific object collection and populate the string array of Restrictions. This array is passed to GetSchema along with the current metadata element to return a filtered list of elements. Yes, it's possible to set any number of the Restriction "values". Note that the Value must be set to Nothing (or null in C#) if you do not want that value to be tested.

Figure 9.33. Populating the Restrictions string array from a DataGridView.