Understanding the Connection Properties

The SqlConnection class is derived from the System.Data.Common.DbConnection class. No, I have not talked about the Data.Common namespace, as I plan to touch on this class in an upcoming EBook. Suffice it to say that, like most .NET Framework classes, SqlConnection inherits a number of properties common to all .NET Data Provider "Connection" classes.

Table 9.3 shows the key SqlClient properties that you're likely to useI'll leave the details of the .NET object-management properties to the Microsoft reference topics. Many of these properties are set through the ConnectionString or the SqlConnectionStringBuilder, so I've already discussed them quite a bit.

Table 9.3. Key SqlConnection Properties

Property	Data Type (Default)	Description
ConnectionString	String	Addresses server, specifies credentials, and configures Connection object.
ConnectionTimeout	Integer (15)	Number of seconds to wait before abandoning connection attempt.
Database	String	Initial catalog or default database.
DataSource	String	Addresses SQL Server instance.
FireInfoMessageEvent OnUserErrors	Boolean (False)	When set to true, errors previously treated as exceptions are handled as InfoMessage events (see discussion).
PacketSize	Integer (8192)	Determines size in bytes of network packets used to communicate with SQL Server. Larger packets mean fewer packets but (potentially) more wasted space.
ServerVersion	String	Returns SQL Server version hosting connection.
State	Data. ConnectionState	Returns state of connection. Open, Closed, Connecting, Executing, Fetching, Broken (not used).
StatisticsEnabled	Boolean (false)	Enables gathering statistics for connection (see discussion). Use the RetrieveStatistics method to retrieve the performance statistics.
WorkstationID	String (name of client computer)	Names the database client.

The FireInfoMessageEventOnUserErrors property is new for the 2.0 Framework. It addresses a problem voiced by stored procedure developers. They found it easier to handle RAISERROR messages in the InfoMessage event handler instead of having lower-severity errors appear in InfoMessage and higher-severity errors returned by (expensive) Exception Catch routines. When set to true, all events fire immediately and are handled by the event handler. When set to false, InfoMessage events are handled at the end of the procedure.

The StatisticsEnabled property is also new for the 2.0 Framework. Apparently, enabling statistics gathering has a minor but measurable (negative) effect on performance and, therefore, should be enabled only when it is required. This property exposes another way to provide real-time monitoring of the SQL Server operations used to execute your query. These connection statistics are reset when you close the connection (as it's returned to the connection pool). Use the RetrieveStatistics method to retrieve the performance statistics and ResetStatistics to reset the current set of statistics, as shown in Figure 9.29. Note that individual metrics can be addressed by name from the IDictionary object created by the RetrieveStatistics method.

Figure 9.29. Capturing performance statistics from a SQL Server query.