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

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.

Related

Категории