Figure 7-4: The list of available schema collections for SQL Server 2000.
The ADO.NET programming model is based on a relatively standard and database-independent sequence of steps. You first create a connection, then prepare and execute a command, and finally process the data retrieved. As far as basic operations and data types are involved, this model works for most providers. Some exceptions are binary large object (BLOB) fields management for Oracle databases and perhaps bulk copy and XML data management for SQL Server databases.
In the rest of the chapter, we'll mostly discuss how ADO.NET data classes work with SQL Server 7.0 and newer versions. However, we'll promptly point out any aspect that is significantly different from other .NET data providers. To start out, let's see how connections take place.
| More Info | For in-depth coverage of ADO.NET 2.0, see Programming Microsoft ADO.NET 2.0 Applications: Advanced Topics by Glenn Johnson (Microsoft Press, 2005) and Programming ADO.NET 2.0 Core Reference by David Sceppa (Microsoft Press, 2005). |
The first step in working with an ADO.NET-based application is setting up the connection with the data source. The class that represents a physical connection to SQL Server is SqlConnection, and it is located in the System.Data.SqlClient namespace. The class is sealed (that is, not inheritable) and cloneable, and it implements the IDbConnection interface. In ADO.NET 2.0, the interface is implemented through the intermediate base class DbConnection, which also provides additional features shared by all providers. (In fact, adding new members to the interface would have broken existing code.)
The SqlConnection class features two constructors, one of which is the default parameterless constructor. The second class constructor, on the other hand, takes a string containing the connection string:
public SqlConnection(); public SqlConnection(string);
The following code snippet shows the typical way to set up and open a SQL Server connection:
string connString = "SERVER= ;DATABASE= ;UID=...;PWD=..."; SqlConnection conn = new SqlConnection(connString); conn.Open(); ... conn.Close();
Table 7-6 details the public properties defined on the SqlConnection class.
| Property | IDbConnection Interface | Description |
|---|---|---|
| ConnectionString | Yes | Gets or sets the string used to open the database. |
| ConnectionTimeout | Yes | Gets the number of seconds to wait while trying to establish a connection. |
| Database | Yes | Gets the name of the database to be used. |
| DataSource | Gets the name of the instance of SQL Server to connect to. It corresponds to the Server connection string attribute. | |
| PacketSize | Gets the size in bytes of network packets used to communicate with SQL Server. Set to 8192, it can be any value in the range from 512 through 32767. | |
| ServerVersion | Gets a string containing the version of the current instance of SQL Server. The version string is in the form of major.minor.release. | |
| State | Yes | Gets the current state of the connection: open or closed. Closed is the default. |
| StatisticsEnabled | Enables the retrieval of statistical information over the current connection. Not available in ADO.NET 1.x. | |
| WorkStationId | Gets the network name of the client, which normally corresponds to the WorkStation ID connection string attribute. |
An important characteristic to note about the properties of the connection classes is that they are all read-only except ConnectionString. In other words, you can configure the connection only through the tokens of the connection string, but you can read attributes back through handy properties. This characteristic of connection class properties in ADO.NET is significantly different than what you find in ADO, where many of the connection properties for example, ConnectionTimeout and Database were read/write.
Table 7-7 shows the methods available in the SqlConnection class.
| Method | IDbConnection Interface | Description |
|---|---|---|
| BeginTransaction | Yes | Begins a database transaction. Allows you to specify a name and an isolation level. |
| ChangeDatabase | Yes | Changes the current database on the connection. Requires a valid database name. |
| Close | Yes | Closes the connection to the database. Use this method to close an open connection. |
| CreateCommand | Yes | Creates and returns a SqlCommand object associated with the connection. |
| Dispose | Calls Close. | |
| EnlistDistributedTransaction | If auto-enlistment is disabled, enlists the connection in the specified distributed Enterprise Services DTC transaction. Not supported in version 1.0 of the .NET Framework. | |
| EnlistTransaction | No, but defined on DbConnection in ADO.NET 2.0 | Enlists the connection on the specified local or distributed transaction. Not available in ADO.NET 1.x. |
| GetSchema | No, but defined on DbConnection in ADO.NET 2.0 | Retrieve schema information for the specified scope (that is, tables, databases). Not available in ADO.NET 1.x. |
| ResetStatistics | Resets the statistics service. Not available in ADO.NET 1.x. | |
| RetrieveStatistics | Gets a hash table filled with the information about the connection, such as data transferred, user details, transactions. Not available in ADO.NET 1.x. | |
| Open | Yes | Opens a database connection. |
Note that if the connection goes out of scope, it is not automatically closed. Later on, but not especially soon, the garbage collector picks up the object instance, but the connection won't be closed because the garbage collector can't recognize the peculiarity of the object and handle it properly. Therefore, you must explicitly close the connection by calling Close or Dispose before the object goes out of scope.
| Note | Like many other disposable objects, connection classes implement the IDisposable interface, thus providing a programming interface for developers to dispose of the object. The dispose pattern entails the sole Dispose method; Close is not officially part of the pattern, but most classes implement it as well. |
In ADO.NET 2.0, the SqlConnection class provides a static method named ChangePassword to let developers change the SQL Server password for the user indicated in the supplied connection string:
public static void ChangePassword( string connectionString, string newPassword)
An exception will be thrown if the connection string requires integrated security (that is, Integrated Security=True or an equivalent setting). The method opens a new connection to the server, requests the password change, and closes the connection once it has completed. The connection used to change the password is not taken out of the connection pool. The new password must comply with any password security policy set on the server, including minimum length and requirements for specific characters.
Note that ChangePassword works only on SQL Server 2005.
In ADO.NET 2.0, all managed providers are expected to implement a GetSchema method for retrieving schema information. The standard providers offer the following overloads of the method:
public override DataTable GetSchema(); public override DataTable GetSchema(string collection); public override DataTable GetSchema(string collection, string[] filterVal)
The schema information you can retrieve is specific to the back-end system. For the full list of valid values, call GetSchema with no parameters. The following code shows how to retrieve all available collections and bind the results to a drop-down list:
// Get schema collections SqlConnection conn = new SqlConnection(connString); conn.Open(); DataTable table = conn.GetSchema(); conn.Close(); // Display their names CollectionNames.DataSource = table; CollectionNames.DataTextField = "collectionname"; CollectionNames.DataBind();
Figure 7-4 shows the available schema collections for a SQL Server 2000 machine. (For SQL Server 2005, it adds only a UserDefinedTypes collection.) Call GetSchema on, say, the Databases collection and you will get the list of all databases for the instance of SQL Server you are connected to. Likewise, if you call it on Tables, you will see the tables in the connected database.
Figure 7-4: The list of available schema collections for SQL Server 2000.
| Note | The preceding code snippet introduces the DataTable class as well as data binding. We will cover the DataTable class one of the most important ADO.NET container classes in the next chapter. Data binding, on the other hand, will be the subject of Chapter 9. |
The list of schema collections is expressed as a DataTable object with three columns CollectionName is the column with names. The following code shows how to retrieve schema information regarding the collection name currently selected in the drop-down list the Views:
string coll = CollectionNames.SelectedValue; string connString = ConnStringBox.Text; SqlConnection conn = new SqlConnection(connString); conn.Open(); DataTable table = conn.GetSchema(coll); conn.Close(); GridView1.DataSource = table; GridView1.DataBind();
As Figure 7-5 demonstrates, the data is then bound to a grid for display.
Figure 7-5: The list of views found in the Northwind database.
In ADO.NET 2.0, all connection objects support GetSchema methods, as they are part of the new intermediate DbConnection class. In ADO.NET 1.x, you have different approaches depending on the target source. If you work with OLE DB, you get schema information through the OLE DB native provider calling the GetOleDbSchemaTable method. The following code shows how to get table information:
OleDbConnection conn = new OleDbConnection(connString); conn.Open(); DataTable schema = cConn.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"}); conn.Close(); GetOleDbSchemaTable takes an OleDbSchemaGuid argument that identifies the schema information to return. In addition, it takes an array of values to restrict the returned columns. GetOleDbSchemaTable returns a DataTable populated with the schema information. Alternately, you can get information on available databases, tables, views, constraints, and so on through any functionality provided by the specific data source, such as stored procedures and views.
SqlConnection conn = new SqlConnection(connString); SqlDataAdapter adapter = new SqlDataAdapter( "SELECT * FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_TYPE = 'BASE TABLE' " + "ORDER BY TABLE_TYPE", conn); DataTable schema = new DataTable(); adapter.Fill(schema);
In ADO.NET 2.0, the GetSchema method unifies the approach for retrieving schema information. The SqlDataAdapter class that appears in the preceding code snippet is a special type of command that we'll explore in depth in the next chapter. One of its key characteristics is that it returns disconnected data packed in a DataTable or DataSet.
To connect to a data source, you need a connection string. Typically made of semicolon-separated pairs of names and values, a connection string specifies settings for the database runtime. Typical information contained in a connection string includes the name of the database, location of the server, and user credentials. Other, more operational information, such as connection timeout and connection pooling settings, can be specified, too.
In many enterprise applications, the usage of connection strings is related to a couple of issues: how to store and protect them, and how to build and manage them. The .NET Framework 2.0 provides excellent solutions to both issues, as we'll see in a moment.
Needless to say, connection strings are database-specific, although huge differences don't exist between, say, a connection string for SQL Server and Oracle databases. In this chapter, we mainly focus on SQL Server but point out significant differences.
The ConnectionString property of the connection class can be set only when the connection is closed. Many connection string values have corresponding read-only properties in the connection class. These properties are updated when the connection string is set. The contents of the connection string are checked and parsed immediately after the ConnectionString property is set. Attribute names in a connection string are not case-sensitive, and if a given name appears multiple times, the value of the last occurrence is used. Table 7-8 lists the keywords that are supported.
| Keyword | Description |
|---|---|
| Application Name | Name of the client application as it appears in the SQL Profiler. Defaults to .Net SqlClient Data Provider. |
| Async | When true, enables asynchronous operation support. Not supported in ADO.NET 1.x. |
| AttachDBFileName or Initial File Name | The full path name of the file (.mdf) to use as an attachable database file. |
| Connection Timeout | The number of seconds to wait for the connection to take place. Default is 15 seconds. |
| Current Language | The SQL Server language name. |
| Database or Initial Catalog | The name of the database to connect to. |
| Encrypt | Indicates whether Secure Sockets Layer (SSL) encryption should be used for all data sent between the client and server. Needs a certificate installed on the server. Default is false. |
| Failover Partner | The name of the partner server to access in case of errors. Connection failover allows an application to connect to an alternate, or backup, database server if the primary database server is unavailable. Not supported in ADO.NET 1.x. |
| Integrated Security or Trusted_Connection | Indicates whether current Windows account credentials are used for authentication. When set to false, explicit user ID and password need to be provided. The special value sspi equals true. Default is false. |
| MultipleActiveResultSets | When true, an application can maintain multiple active result sets. Set to true by default, this feature requires SQL Server 2005. Not supported in ADO.NET 1.x. |
| Network Library or Net | Indicates the network library used to establish a connection to SQL Server. Default is dbmssocn, which is based on TCP/IP. |
| Packet Size | Bytes that indicate the size of the packet being exchanged. Default is 8192. |
| Password or pwd | Password for the account logging on. |
| Persist Security Info | Indicates whether the managed provider should include password information in the string returned as the connection string. Default is false. |
| Server or Data Source | Name or network address of the instance of SQL Server to connect to. |
| User ID or uid | User name for the account logging on. |
| Workstation ID | Name of the machine connecting to SQL Server. |
The network DLL specified by the Network Library keyword must be installed on the system to which you connect. If you use a local server, the default library is dbmslpcn, which uses shared memory. For a list of options, consult the MSDN documentation.
Any attempt to connect to an instance of SQL Server should not exceed a given time. The Connection Timeout keyword controls just this. Note that a connection timeout of 0 causes the connection attempt to wait indefinitely; it does not indicate no wait time.
You normally shouldn't change the default packet size, which has been determined based on average operations and workload. However, if you're going to perform bulk operations in which large objects are involved, increasing the packet size can be of help because it decreases the number of reads and writes.
Some of the attributes you see listed in Table 7-8 are specific to ADO.NET 2.0 and address features that have been introduced lately. They are asynchronous commands and multiple active result sets (MARS). MARS, in particular, removes a long-time constraint of the SQL Server programming model that is, the constraint of having at most one pending request on a given session at a time. Before ADO.NET 2.0 and SQL Server 2005, several approaches have been tried to work around this limitation, the most common of which is using server-side cursors through ADO. We'll return to MARS later, in the section dedicated to SQL Server 2005.
How do you build the connection string to be used in an application? In many cases, you just consider it constant and read it out of a secured source. In other cases, though, you might need to construct it based on user input for example, when retrieving user ID and password information from a dialog box. In ADO.NET 1.x, you can build the string only by blindly concatenating any name/value pairs. There are two major drawbacks with this technique. One is that the use of wrong keywords is caught only when the application undergoes testing. More serious than the lack of compile-time check, though, is that a blind-pair concatenation leaves room for undesired data injections to attach users to a different database or to change in some way the final goal of the connection. Any measures to fend off injections and check the syntax should be manually coded, resulting in a specialized builder class just like the brand new connection string builder classes you find in ADO.NET 2.0.
All default data providers support connection string builders in a guise that perfectly applies to the underlying provider. The following code snippet (and its result, shown in Figure 7-6) builds and displays a connection string for SQL Server:
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = serverName; builder.UserID = userid; builder.Password = pswd; NewConnString.Text = builder.ConnectionString;
Figure 7-6: Building connection strings programmatically and securely.
By using connection string builders, you gain a lot in terms of security because you dramatically reduce injection. Imagine that a malicious user types in the password Foo;Trusted_Connection=true. If you blindly concatenate strings, you might get the following:
Password=Foo;Trusted_Connection=true
Because the last pair wins, the connection will be opened based on the credentials of the logged-on user. If you use the builder class, you get the following appropriately quoted string:
Password="Foo;Trusted_Connection=true"
In addition, the builder class exposes the 20-plus supported keywords through easier-to-remember properties recognized by Microsoft IntelliSense.
Savvy developers avoid hard-coding connection strings in the compiled code. Configuration files (such as the web.config file) purposely support the <appSettings> named section, which is used to store custom data through name/value pairs. All these values populate the AppSettings collection and can be easily retrieved programmatically, as shown here:
string connString = ConfigurationSettings.AppSettings["NorthwindConn"];
This approach is far from perfect for two reasons. First, connection strings are not just data they're a special kind of data not to be mixed up with general-purpose application settings. Second, connection strings are a critical parameter for the application and typically contain sensitive data. Therefore, at a minimum they need transparent encryption. Let's tackle storage first.
In the .NET Framework 2.0, configuration files define a new section specifically designed to contain connection strings. The section is named <connectionStrings> and is laid out as follows:
<connectionStrings> <add name="NWind" connectionString="SERVER= ;DATABASE= ;UID= ;PWD= ;" providerName="System.Data.SqlClient" /> </connectionStrings>
You can manipulate the contents of the section by using <add>, <remove>, and <clear> nodes. You use an <add> node to add a new connection string to the current list, <remove> to remove a previously defined connection, and <clear> to reset all connections and create a new collection. By placing a web.config file in each of the application's directories, you can customize the collection of connection strings that are visible to the pages in the directory. Configuration files located in child directories can remove, clear, and extend the list of connection strings defined at the upper level. Note that each stored connection is identified with a name. This name references the actual connection parameters throughout the application. Connection names are also used within the configuration file to link a connection string to other sections, such as the <providers> section of <membership> and <profile> nodes.
All the connection strings defined in the web.config file are loaded into the new ConfigurationManager.ConnectionStrings collection. To physically open a connection based on a string stored in the web.config file, use following code:
string connStr; connStr = ConfigurationManager.ConnectionStrings["NWind"].ConnectionString; SqlConnection conn = new SqlConnection(connStr);
The full support from the configuration API opens up an interesting possibility for consuming connection strings declarative binding. As we'll see in Chapter 9, ASP.NET 2.0 supports quite a few data source objects. A data source object is a server control that manages all aspects of data source interaction, including connection setup and command execution. You bind data source objects to data-bound controls and instruct the data source to retrieve data from a specific source. The great news is that you can now indicate the connection string declaratively, as follows:
<asp:SqlDataSource runat="server" ProviderName="System.Data.SqlClient" ConnectionString='<%# ConfigurationSettings.ConnectionStrings["NWind"].ConnectionString %>' SelectCommand="SELECT * FROM employees">
There's a lot more to be known about this feature, though, and we'll delve deeply into that later in the book. For now, it suffices to say that connection strings are much more than strings in the .NET Framework 2.0.
ASP.NET 2.0 introduces a system for protecting sensitive data stored in the configuration system. It uses industry-standard XML encryption to encrypt specific sections of configuration files that might contain sensitive data. XML encryption (which you can learn more about at http://www.w3.org/TR/xmlenc-core) is a way to encrypt data and represent the result in XML. Prior to version 2.0, only a few specific ASP.NET sections that contain sensitive data support protection of this data using a machine-specific encryption in a registry key. This approach requires developers to come up with a utility to protect their own secrets typically connection strings, credentials, and encryption keys.
In the .NET Framework 2.0, encryption of configuration sections is optional, and you can enable it for any configuration sections you want by referencing the name of the section in the <protectedData> section of the web.config file, as shown here:
<protectedData> <protectedDataSections> <add name="connectionStrings" provider="RSAProtectedConfigurationProvider" /> </protectedDataSections> </protectedData>
You can specify the type of encryption you want by selecting the appropriate provider from the list of available encryption providers. The .NET Framework 2.0 comes with two predefined providers:
DPAPIProtectedConfigurationProvider. Uses the Windows Data Protection API (DPAPI) to encrypt and decrypt data
RSAProtectedConfigurationProvider. Default provider; uses the RSA encryption algorithm to encrypt and decrypt data
Being able to protect data stored in the web.config file is not a feature specific to connection strings. It applies, instead, to all sections, with very few exceptions. This said, let's see how to encrypt connection strings stored in the web.config file.
You can use the newest version of a popular system tool aspnet_regiis.exe or write your own tool by using the ASP.NET 2.0 configuration API. If you use aspnet_regiis, examine the following code, which is a sample used to encrypt connection strings for the ProAspNet20 application:
aspnet_regiis.exe -pe connectionStrings -app /ProAspNet20
Note that the section names are case-sensitive. Note also that connection strings are stored in a protected area that is completely transparent to applications, which continue working as before. If you open the web.config file after encryption, you see something like the following:
<configuration> <protectedData> <protectedDataSections> <add name="connectionStrings" provider="RSAProtectedConfigurationProvider" /> </protectedDataSections> </protectedData> <connectionStrings> <EncryptedData > ... <CipherData> <CipherValue>cQyofWFQ =</CipherValue> </CipherData> </EncryptedData> </connectionStrings> </configuration>
To restore the web.config file to its original clear state, you use the -pd switch in lieu of the -pe in the aforementioned command line.
| Caution | Any page that uses protected sections works like a champ as long as you run it inside the local Web server embedded in Visual Studio .NET 2005. You might get an RSA provider configuration error if you access the same page from within a canonical (and much more realistic) IIS virtual folder. What's up with that? The RSA-based provider the default protection provider needs a key container to work. A default key container is created upon installation and is named NetFrameWorkConfigurationKey. The aspnet_regiis.exe utility provides a lot of command-line switches for you to add, remove, and edit key containers. The essential point is that you have a key container created before you dump the RSA-protected configuration provider. The container must not only exist, it also needs to be associated with the user account attempting to call it. The system account (running the local Web server) is listed with the container; the ASP.NET account on your Web server might not be. Assuming you run ASP.NET under the NETWORK SERVICE account (the default on Windows Server 2003 machines), you need the following code to add access to the container for the user: aspnet_regiis.exe -pa "NetFrameworkConfigurationKey" "NT AUTHORITY\NETWORK SERVICE" It is important that you specify a complete account name, as in the preceding code. Note that granting access to the key container is necessary only if you use the RSA provider. |
Both the RSA and DPAPI providers are great options for encrypting sensitive data. The DPAPI provider dramatically simplifies the process of key management keys are generated based on machine credentials and can be accessed by all processes running on the machine. For the same reason, the DPAPI provider is not ideal to protect sections in a Web farm scenario where the same encrypted web.config file will be deployed to several servers. In this case, either you manually encrypt all web.config files on each machine or you copy the same container key to all servers. To accomplish this, you create a key container for the application, export it to an XML file, and import it on each server that will need to decrypt the encrypted web.config file. To create a key container, you do as follows:
aspnet_regiis.exe -pc YourContainerName -exp
Next, you export the key container to an XML file:
aspnet_regiis.exe -px YourContainerName YourXmlFile.xml
Next, you move the XML file to each server and import it as follows:
aspnet_regiis.exe -pi YourContainerName YourXmlFile.xml
As a final step, grant the ASP.NET account permission to access the container.
| Note | We won't cover the .NET Framework configuration API in this book. You can find deep coverage of the structure of configuration files and related APIs in my other recent book, Programming Microsoft ASP.NET 2.0 Applications: Advanced Topics (Microsoft Press, 2005). |
Connection pooling is a fundamental aspect of high-performance, scalable applications. For local or intranet desktop applications that are not multithreaded, connection pooling is no big deal you'll get nearly the same performance with and without pooling. Furthermore, using a nonpooled connection gives you more control over the lifetime. For multithreaded applications, the use of connection pooling is a necessity for performance reasons and to avoid nasty, hardware-dependent bugs. Finally, if ASP.NET applications are involved, every millisecond that the connection is idle steals valuable resources from other requests. Not only should you rely on connection pooling, but you should also open the connection as late as possible and close it as soon as you can.
Using connection pooling makes it far less expensive for the application to open and close the connection to the database, even if that is done frequently. (We'll cover this topic in more detail later.) All standard .NET data providers have pooling support turned on by default. The .NET data providers for SQL Server and Oracle manage connection pooling internally using ad hoc classes. For the OLE DB data provider, connection pooling is implemented through the OLE DB service infrastructure for session pooling. Connection-string arguments (for example, OLE DB Service) can be used to enable or disable various OLE DB services, including pooling. A similar situation occurs with ODBC, in which pooling is controlled by the ODBC driver manager.
Some settings in the connection string directly affect the pooling mechanism. The parameters you can control to configure the SQL Server environment are listed in Table 7-9.
| Keyword | Description |
|---|---|
| Connection Lifetime | Sets the maximum duration in seconds of the connection object in the pool. This keyword is checked only when the connection is returned to the pool. If the time the connection has been open is greater than the specified lifetime, the connection object is destroyed. (We'll cover this topic in more detail later.) |
| Connection Reset | Determines whether the database connection is reset when being drawn from the pool. Default is true. |
| Enlist | Indicates that the pooler automatically enlists the connection in the creation thread's current transaction context. Default is true. |
| Max Pool Size | Maximum number of connections allowed in the pool. Default is 100. |
| Min Pool Size | Minimum number of connections allowed in the pool. Default is 0. |
| Pooling | Indicates that the connection object is drawn from the appropriate pool or, if necessary, is created and added to the appropriate pool. Default is true. |
With the exception of Connection Reset, all the keywords listed in Table 7-9 are acceptable to the Oracle managed provider, too.
As far as SQL Server and Oracle providers are concerned, connection pooling is automatically enabled; to disable it, you need to set Pooling to false in the connection string. To control pooling for an ODBC data source, you use the ODBC Data Source Administrator in the Control Panel. The Connection Pooling tab allows you to specify connection pooling parameters for each ODBC driver installed. Note that any changes to a specific driver affect all applications that make use of it. The .NET data provider for OLE DB automatically pools connections using OLE DB session pooling. You can disable pooling by setting the OLE DB Services keyword to -4.
In ADO.NET 2.0, auto enlistment (the Enlist keyword) works in the connection strings of all standard data providers, including providers for OLE DB and ODBC. In ADO.NET 1.x, only managed providers for SQL Server and Oracle support auto-enlistment because they are made of native managed code instead of being wrappers around existing code. The new EnlistTransaction method on connection classes allows you to enlist a connection object programmatically, be it pooled or not.
Each connection pool is associated with a distinct connection string and the transaction context. When a new connection is opened, if the connection string does not exactly match an existing pool, a new pool is created. Once created, connection pools are not destroyed until the process ends. This behavior does not affect the system performance because maintenance of inactive or empty pools requires only minimal overhead.
When a pool is created, multiple connection objects are created and added so that the minimum size is reached. Next, connections are added to the pool on demand, up to the maximum pool size. Adding a brand-new connection object to the pool is the really expensive operation here, as it requires a roundtrip to the database. Next, when a connection object is requested, it is drawn from the pool as long as a usable connection is available. A usable connection must currently be unused, have a matching or null transaction context, and have a valid link to the server. If no usable connection is available, the pooler attempts to create a new connection object. When the maximum pool size is reached, the request is queued and served as soon as an existing connection object is released to the pool.
Connections are released when you call methods such as Close or Dispose. Connections that are not explicitly closed might not be returned to the pool unless the maximum pool size has been reached and the connection is still valid.
A connection object is removed from the pool if the lifetime has expired (which will be explained further in a moment) or if a severe error has occurred. In these cases, the connection is marked as invalid. The pooler periodically scavenges the various pools and permanently removes invalid connection objects.
| Important | Unlike in ADO, connection pools in ADO.NET are created based on the connection string applying an exact match algorithm. In other words, to avoid the creation of an additional connection pool you must ensure that two connection strings carrying the same set of parameters are expressed by two byte-per-byte identical strings. A different order of keywords, or blanks interspersed in the text, is not ignored and ends up creating additional pools and therefore additional overhead. |
To make connection pooling work effectively, it is extremely important that connection objects are returned to the pool as soon as possible. It is even more important, though, that connections are returned. Note that a connection object that goes out of scope is not closed and, therefore, not immediately returned. For this reason, it is highly recommended that you work with connection objects according to the following pattern:
SqlConnection conn = new SqlConnection(connString); try { conn.Open(); // Do something here } catch { // Trap errors here } finally { conn.Close(); } Alternately, you can resort to the C# using statement, as follows:
using (SqlConnection conn = new SqlConnection(connString)) { // Do something here // Trap errors here } The using statement is equivalent to the preceding try/catch/finally block in which Close or Dispose is invoked in the finally block. You can call either Close or Dispose or even both they do the same thing. Dispose cleans the connection string information and then calls Close. In addition, note that calling each multiple times doesn't result in run-time troubles, as closing or disposing an already closed or disposed connection is actually a no-operation.
| Note | Before the .NET Framework 2.0, there was no sort of using statement in Visual Basic .NET. Starting with Visual Studio .NET 2005, you can rely on a shortcut keyword for try/catch/ finally blocks also in Visual Basic .NET. The keyword is Using End Using: Using conn As New SqlConnection() ... End Using |
In ADO.NET 2.0, you can more easily figure out whether you're leaking connections, thanks to some new performance counters. In particular, you can monitor the NumberOfReclaimedConnections counter; if you see it going up, you have the evidence that your application is making poor use of connection objects. A good symptom of connection leaking is when you get an invalid operation exception that claims the timeout period elapsed prior to obtaining a connection from the pool. You can make this exception disappear or, more exactly, become less frequent by tweaking some parameters in the connection string. Needless to say, this solution doesn't remove the leak; it simply changes run-time conditions to make it happen less frequently. Here's a quick list of things you should not do that relate to connection management:
Do not turn connection pooling off. With pooling disabled, a new connection object is created every time. No timeout can ever occur, but you lose a lot in performance and, more important, you are still leaking connections.
Do not shrink the connection lifetime. Reducing the lifetime of the connection will force the pooler to renew connection objects more frequently. A short lifetime (a few seconds) will make the timeout exception extremely unlikely, but it adds significant overhead and doesn't solve the real problem. Let's say that it is only a little better than turning pooling off.
Do not increase the connection timeout. You tell the pooler to wait a longer time before throwing the timeout exception. Whatever value you set here, ASP.NET aborts the thread after three minutes. In general, this option worsens performance without alleviating the problem.
Do not increase the pool size. If you set the maximum pool size high enough (how high depends on the context), you stop getting timeout exceptions while keeping pooling enabled. The drawback is that you greatly reduce your application's scalability because you force your application to use a much larger number of connections than is actually needed.
To avoid leaking connections, you need to guarantee only that the connection is closed or disposed of when you're done, and preferably soon after.
In the previous section, I emphasized the importance of writing code that guarantees the connection is always closed. However, there might be nasty cases in which your code places a call to Close, but it doesn't get called. Let's see why. Consider the following code:
SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.ExecuteNonQuery(); conn.Close();
What if the command throws an exception? The Close method is not called, and the connection is not returned to the pool. Wrapping the code in a using statement would do the trick because it ensures that Dispose is always invoked on the object being used. Here's the correct version of the code:
using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.ExecuteNonQuery(); conn.Close(); // Not called in case of exception } // Dispose always called That's the only way to avoid connection leaking.
The Connection Lifetime keyword indicates in seconds the time a connection object is considered valid. When the time has elapsed, the connection object should be disposed of. But why on earth should you get rid of a perfectly good connection object? This keyword is useful only in a well-known situation, and it should never be used otherwise. Imagine that you have a cluster of servers sharing the workload. At some point, you realize the load is too high and you turn on an additional server. With good reason, you expect the workload to be distributed among all servers. However, this might not happen the newly added server is idle.
A plausible and common reason for this is that middle-tier components cache the connections and never open new ones. By disposing of working connections, you force the middle-tier applications to create new connections. Needless to say, new connections will be assigned to the least loaded server. In the end, you should set Connection Lifetime only if you're in a cluster scenario. Finally, note that in ADO.NET 2.0 the connection builder classes use a different (and more intuitive) name to address the keyword LoadBalanceTimeout.
| Note | The LoadBalanceTimeout is not a newly supported attribute for a connection string. If you use the SqlConnectionStringBuilder class to programmatically build the connection string, you'll find a LoadBalanceTimeout property to set the Connection Lifetime attribute. |
Until ADO.NET 2.0, there was no way to programmatically clear the pool of open connections. Admittedly, this is not an operation you need to perform often, but it becomes essential in case the database server goes down for whatever reason. Consider the following scenario: your ASP.NET pages open and then successfully close some connections out of the same pool. Next, the server suddenly goes down and is restarted. As a result, all connection objects in the pool are now invalid because each of them holds a reference to a server connection that no longer exists. What happens when a new page request is issued?
The answer is that the pooler returns an apparently valid connection object to the page, and the page runs the command. Unfortunately, the connection object is not recognized by the database server, resulting in an exception. The connection object is removed from the pool and replaced. The exception will be raised for each command as long as there are connection objects in the pool. In summary, shutting down the server without shutting down the application brings the connection pool into an inconsistent, corrupted state.
This situation is common for applications that deal with server reboots, like a failover cluster. Only one solution is possible flushing the connection pool. It is not as easy to implement as it might seem at first, though. An easier workaround is catching the exception and changing the connection string slightly to force the use of a new connection pool.
In ADO.NET 2.0, the solution to this issue comes with the framework. ADO.NET 2.0 is smart enough to recognize when an exception means that the pool is corrupted. When an exception is thrown during the execution of a command, ADO.NET 2.0 determines if the exception means that the pool is corrupted. In this case, it walks down the pool and marks each connection as obsolete. When does an exception indicate pool corruption? It has to be a fatal exception raised from the network layer on a previously opened connection. All other exceptions are ignored and bubble up as usual.
Two new static methods ClearPool and ClearAllPools, defined for both SqlConnection and OracleConnection can be used to programmatically clear the pool, if you know that the server has been stopped and restarted. These methods are used internally by ADO.NET 2.0 to clear the pool as described earlier.
