Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

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.

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.

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.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.