Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:Connections – Part 1

Posted by Ravi Varma Thumati on November 13, 2009

Connections

Before you can perform any task with an ADO.NET data source, you need to open a connection. In ADO.NET, this means creating and using a Connection object. Connection objects are one of the simplest components in ADO.NET, but they encapsulate a fair bit of lower-level functionality, including user authentication information, a connection pooling mechanism, and a network connection (assuming the data source is located on a separate computer).

We’ll examine the basics of the ADO.NET Connection object and the connection string settings you can configure. We’ll also consider some finer points, including connection pooling—a key to highly scalable database applications—and connection events.

All Connection objects implement the IDbConnection interface from the System.Data namespace and are thus quite similar. Provider-specific connection objects sometimes differ by adding new informational properties and slightly different methods for supporting transactions and connection pooling.

Some Connection classes for ADO.NET providers are shown in Table.

Class Data Source
System.Data.SqlClient.SqlConnection SQL Server
System.Data.OleDb.OleDbConnection An OLE DB provider
System.Data.Odbc.OdbcConnection An ODBC driver
System.Data.OleDb.OracleConnection Oracle

Connection Object Overview

The Connection object, like all provider-specific ADO.NET objects, comes in more than one version. You use the version that’s tailored for your specific data source. Here are two examples:

  • System.Data.SqlClient.SqlConnection allows you to connect to a SQL Server database (Version 7.0 or later).
  • System.Data.OleDb.OleDbConnection allows you to connect to almost any data source with an associated OLE DB provider.

Every Connection object that accesses relational databases implements the common System.Data.IDbConnection interface. By looking at the IDbConnection interface, you’ll quickly see the small set of properties and methods that every Connection object is guaranteed to support (see Tables 3-1 and 3-2). The most important of these are the Close ( ) and Open ( ) methods, and the ConnectionString property, which specifies a variety of options about the data source and how to connect to it. All IDbConnection properties are read-only, except ConnectionString.

Table 3-1: IDbConnection properties
Member Description
ConnectionString A string with name-value pairs of connection settings. These settings often include information such as the user to log in and the location of the database server. This is the only writeable property.
ConnectionTimeout The time to wait for a connection to open before failing with a provider-specific exception (such as SqlException or OleDbException). The default is 15 seconds; 0 waits indefinitely. This value must be set through the connection string; the property is read-only.
Database The name of the database to use once the connection is open. This can be set in the connection string and changed with the ChangeDatabase( ) method. Oracle databases don’t support this property.
State A bitwise connection of values from the ConnectionStateOpen and Closed are supported, and information isn’t provided about whether the connection is currently retrieving data or executing a query.enumeration. Currently, only

Table 3-2. IDbConnection methods
Member Description
BeginTransaction(  ) Programmatically starts a database transaction.
ChangeDatabase(  ) Sets a new database to be used for subsequent operations. Alternatively, you can execute the SQL USE command with SQL Server. Oracle databases don’t support this method.
CreateCommand(  ) Returns a provider-specific IDbCommand object that is set to use this connection. This method is primarily useful when writing provider-agnostic code.
Open() and Close(  ) Attempts to connect to or disconnect from the data source.

Other providers add additional members. For example, most Connection objects add the ServerVersion property (which contains a string with version information for the database product) and two events: StateChange (which fires when the connection is opened or closed) and InfoMessage (which fires when warning or error messages are received). SqlConnection also adds a WorkstationId and PacketSize property with additional information.

The Connection String

When creating a connection, you must specify several pieces of required information. Typically, this includes the type of authentication or user to authenticate, the location of the database server, and the name of the database. In addition, OLE DB connection strings specify an OLE DB provider, and ODBC connection strings specify an ODBC driver. To specify this information, use the ConnectionString property.

The ConnectionString contains a series of name/value settings delimited by semicolons (;). The order of these settings is unimportant, as is the capitalization. Taken together, they specify the information needed to create a connection. Table 3-3 describes some settings you can use. Parameters that are used for connection pooling are omitted.

Table 3-3. Basic connection string parameters
Parameter Description
AttachDBFilename / Initial File Name Used only if you want to connect to an attachable database file (for example, an .mdf file that isn’t registered with the database system). Normally, you use the Initial Catalogparameter instead.
Connect Timeout / Connection Timeout The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Defaults to 15 seconds, and 0 seconds represents an infinite wait.
Data Source / Server / Address / Addr / Network Address The server name or network address of the database product to connect to. Use localhostfor the current computer.
Initial Catalog / Database The name of the database to use for all subsequent operations (insertions, deletions, queries, and so on).
Integrated Security / Trusted_Connection Defaults to false. When set to trueSSPI, the .NET provider attempts to connect to the data source using Windows integrated security.or
Persist Security Info When set to falseConnectionString(the default), security-sensitive information such as the password is removed from theproperty as soon as the connection is opened. Thus, you can’t retrieve this information in your code.
User ID The database account user ID.
Password/Pwd The password corresponding to the User ID.

Setting Connection String Parameters

The following code snippet shows how you might set the ConnectionString property on a SqlConnection object. The actual connection string details are omitted.

SqlConnection con = new SqlConnection ();

con.ConnectionString = “…”;

All standard ADO.NET Connection objects also provide a constructor that accepts a value for the ConnectionString property. For example, the following code statement creates a SqlConnection object and sets the ConnectionString property in one statement. It’s equivalent to the previous example.

SqlConnection con = new SqlConnection (“…”);

The next few sections present some sample connection strings with commonly used settings. Because the connection string varies depending on the provider, these examples are separated into provider-specific sections.

The SQL Server connection string

When using a SQL Server database, you need to specify the server name using the Data Source parameter (use localhost for the current computer), the Initial Catalog parameter (the database name), and the authentication information.

You have two options for supplying the authentication information. If your database uses SQL Server authentication, you can pass a user ID and password defined in SQL Server. This account should have permissions for the tables you want to access:

SqlConnection con = new SqlConnection(“Data Source=localhost;” +

“Initial Catalog=Northwind;user id=userid;password=password“);

If your database allows integrated Windows authentication, you can signal this fact with the Integrated Security=SSPI connection string parameter. The Windows operating system then supplies the user account token for the currently logged-in user. This is more secure because the login information doesn’t need to be visible in the code (or transmitted over the network):

SqlConnection con = new SqlConnection(“Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”);

Keep in mind that integrated security won’t always execute in the security context of the application user. For example, consider a distributed application that performs a database query through a web service. If the web service connects using integrated authentication, it uses the login account of the ASP.NET worker process, not the account of the client making the request. The story is similar with a component exposed through .NET remoting, which uses the account that loaded the remote component host.

The OLE DB connection string

The OLE DB connection string resembles the SQL Server connection string. However, the support for some parameters depends on the OLE DB provider you use. Typically, an OLE DB connection string requires a Data Source parameter (use localhost for the current computer), the Initial Catalog parameter (the database name), and the user id and password parameters. It also requires a Provider setting that indicates which OLE DB provider to use.

The following code snippet shows a sample connection string that connects to a SQL Server database through the OLE DB provider. This is the only way to connect to a version of SQL Server earlier than 7.0:

OleDbConnection con = new OleDbConnection(“Data Source=localhost;” +

“Initial Catalog=Northwind;user;password=secret;” +

“Provider=SQLOLEDB”);

Here’s an example that connects to an Access database file through the Jet provider:

OleDbConnection con = new OleDbConnection(“Data Source=localhost;” +

“Initial Catalog=c:\Nortwdind.mdb;” +

“Provider=Microsoft.Jet.OLEDB.4.0”);

The ODBC .NET connection string

The ODBC connection string resembles the SQL Server and OLE DB connection strings. However, the support for some parameters depends on the ODBC driver used. Typically, an ODBC connection string requires a Data Source parameter (use localhost for the current computer), the Initial Catalog parameter (the database name), and the user id and password parameters. It also requires a Driver setting that indicates the ODBC driver to use, or its data source name (DSN), which associates a symbolic name with a group of database settings that otherwise goes into the connection string. The DSN must be enclosed in curly braces and match exactly.

Here is an example that accesses an Excel file:

OdbcConnection con = new OdbcConnection(

“Driver={Microsoft Excel Driver (*.xls)};” +

“DBQ=c:\book1.xls”);

Here’s an example that uses the ODBC driver for MySQL (available from www.mysql.com). It adds a new connection string setting, Option, which configures certain low-level behaviors to support specific clients. For more information, refer to the MySQL documentation.

OdbcConnection con = new OdbcConnection(

“Driver={MySQL ODBC 3.51 Driver};” +

“Database=test;UID=root;PWD=secret;Option=3”);

Use the Data Sources icon (in the Administrative Tools portion of the Control Panel) to configure ODBC DSN settings or add new drivers.

The Oracle .NET connection string

The Microsoft Oracle provider supports a smaller subset of connection-string options, as shown in Table 3-3. The Oracle provider also includes connection string settings that allow you to configure connection pooling.

Here’s how you can create an OracleConnection with a connection string:

OracleConnection con = new OracleConnection(

“Data Source=Oracle8i;Integrated Security=true”);

Security Risks of the Connection String

Be careful if you are constructing a connection string dynamically based on user input. For example, make sure you check that the user has not inserted any extra semicolons (or that all semicolons are contained inside apostrophes). Otherwise, the user can add additional connection string parameters, possibly tricking your code into connecting to the wrong database.

For example, you might request a password and place it in a connection string as follows:

connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;usertext-align: justify;”>  “;password=” + txtPassword.Text;

In this case, a problem occurs if the user submits a password in the form ValidPassword;Initial Catalog=ValidDatabase. The connection string will now have two Initial Catalog parameters, and it will use the second one, which the user appended to the end of the password!

To overcome this sort of problem, you should never allow a user to specify connection string parameters directly. Consider storing this information in a configuration file.

Opening and Closing Connections

You’ve now seen all the ingredients you need to create and use a connection. You simply create the Connection object required for your data source, apply the appropriate connection string settings, and open the connection. In Example 3-1, a connection is created to a SQL Server database on the local computer using integrated authentication. The code opens the connection, tests its state, and closes it.

Example 3-1. Opening and testing a connection

// ConnectionTest.cs – Opens and verifies a connection

using System;

using System.Data.SqlClient;

public class ConnectionTest

{

public static void Main()

{

SqlConnection con = new SqlConnection(“Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”);

con.Open();

Console.WriteLine(“Connection is ” + con.State.ToString());

con.Close();

Console.WriteLine(“Connection is ” + con.State.ToString());

}

}

The output clearly indicates whether the connection test is successful:

Connection is Open
Connection is Closed

Connection Events

Connection objects expose only two events. The InfoMessage event can retrieve warnings and other messages from a data source. Generally, you use this event if you wish to receive specific information messages that don’t correspond to errors (in SQL Server, these are messages with a severity of 10 or less). You can’t use this event to be informed about errors, which simply causes the Connection object to throw an exception.

The message information is wrapped into a provider-specific EventArgs object, such as OleDbInfoMessageEventArgs or SqlInfoMessageEventArgs.

You can examine this object for the error number and message text, as well as the additional provider-specific information. For example, SQL Server provides information about the database, stored procedure, and line number where the message originated.

Here’s an example event handler for the InfoMessage event:

private void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)

{

foreach (SqlError err in args.Errors)

{

Console.WriteLine(“The {0} has received a severity {1}, ” +

“state {2} error number {3} on line {4} of procedure {5} ” +

“on server {6}”, err.Source, err.Class, err.State, err.Number,

err.LineNumber, err.Procedure, err.Server);

}

}

You can connect the event handler any time after creating the Connection object:

con.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

Connection objects also provide a StateChange event that fires when the connection is opened or closed. This event provides a StateChangeEventArgs object with information about the current and previous state as a value from the System.Data.ConnectionState enumeration. Currently, the StateChange event fires only when the connection is opened or closed (not to inform you about other ConnectionState values), which reduces its usefulness dramatically.

Connections and Exception Handling

Exception handling is critical when accessing an external resource such as a database, as you can’t guarantee the success of your operations. Problems such as a heavy user load, a misbehaving network connection, or invalid connection string parameters can derail your attempts to use or open a connection. However, because database connections are a finite resource, you also need to make sure you close a connection after a problem occurs. For example, consider this poorly written ADO.NET code:

try

{

con.Open();

// (Execute an ADO.NET command here.)

con.Close();

}

catch (Exception err)

{

Console.WriteLine(err.ToString());

}

If an error occurs after opening the connection but before closing it, the connection is left open. This potentially locks out other users until the garbage collector finds the Connection object and disposes it (at which point any outstanding transactions are rolled back, and the Dispose( ) method is invoked automatically). In a large, heavily used distributed application, this mistake can have a serious detrimental effect on user concurrency.

The correct approach is to create your ADO.NET objects outside the exception handler, open the connection inside the exception handler, and use the finally block to close the connection. This ensures that the Connection will always be closed, even if an error occurs.

try

{

con.Open();

// (Execute an ADO.NET command here.)

}

catch (err)

{

Console.WriteLine(err.ToString());

}

finally

{

con.Close();

}

If an exception such as SqlException, OleDbException, or OdbcException is thrown by a Connection or Command object, it’s provider-specific. This exception can represent invalid query syntax, invalid user credentials, the inability to find the specified data source, or any one of a host of other problems. Provider-specific exceptions don’t derive from a common base class that identifies them as ADO.NET exceptions, which can make it difficult to write generic code that works with different providers.

Disposing Connections

Instead of using an exception handling block, you can use the C# using statement with the Connection object. This ensures that Dispose( ) will be called on the Connection when code inside the using block ends. The Dispose( ) method is always called, regardless of whether the statements conclude successfully or an unhandled exception is thrown.

using (con)

{

con.Open();

// (Execute an ADO.NET command here.)

}

// (con.Dispose() is called automatically.)

This approach is similar to the previous example because the Dispose( ) method also closes the connection. However, it’s not equivalent because the Dispose( ) method also releases all the unmanaged resources associated with the Connection object and removes it from the connection pool. This distinction means that this approach isn’t suitable for most large-scale applications because it removes the key benefits of connection pooling.

Connection Pooling

Connection pooling recycles a set of open connections to save time—a key requirement in enterprise-level database applications. Without connection pooling, your application might be able to support a large throughput but will provide poor scalability. For example, you might find that your system can easily handle 10 simultaneous clients performing 1,000 transactions/minute but falters with 1,000 clients performing 10 transactions/minute, even though the overall transaction throughput is the same.

The problem is that acquiring a database connection automatically imposes some overhead. This is because the process of establishing a connection requires several lower-level operations, including a verification of security credentials. If your system is characterized by a large number of clients that frequently connect, perform a single operation, and then disconnect, the overhead required to create connections can become a crippling bottleneck. This is typical in a large stateless distributed application, such as an ASP.NET web site or web service.

To counteract this effect, most database access frameworks support connection pooling. Connection pooling works by retaining a pool of available connections. When a client requests a connection, it’s served directly from the available pool, rather than recreated. In a large system with a quick turnover of database connections, this automatic reuse can save CPU cycles and network bandwidth.

ADO.NET doesn’t include a connection-pooling mechanism. However, most ADO.NET providers do implement some form of connection pooling. In the case of the SQL Server and Oracle providers, this connection pooling mechanism is written entirely in managed code. In the case of the OLE DB and ODBC providers, this connection pooling depends on a lower level and has a few limitations. Most providers enable connection pooling automatically. However, you may be able to use connection string parameters to configure pool size settings.

All forms of connection pooling work by examining the connection string. A connection is reused only if the connection string matches exactly. Most ADO.NET providers use a case-sensitive full-text matching algorithm. This means that even if you have the same connection string parameters, but they are in a different order, the connection isn’t reused. (The ODP .NET provider from Oracle is one exception.) To ensure that your connections can be reused, store the connection string in a single location (such as a configuration file) and don’t enter it directly in your code.

The following example demonstrates how connection pooling works with a single Connection object:

string conString1 = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”);

string conString2 = “Data Source=localhost;” +

“Initial Catalog=pubs;Integrated Security=SSPI”);

SqlConnection con = new SqlConnection();

con.ConnectionString = conString1;

con.Open();

// The initial pool is created (we’ll call it pool A).

con.Close()

// The connection is returned to pool A.

con.ConnectionString = conString2;

con.Open();

// A new pool is created (pool B), because the connection strings differ.

con.Close()

// This connection is returned to pool B.

con.ConnectionString = conString1;

con.Open();

// The open connection from pool A is reused. This saves time.

con.Close()

// The connection is returned to pool A.

You’ll notice several important factors in this example:

  • It doesn’t matter whether you are using one Connection object or several Connection objects. When you call Close( ), the underlying connection is placed in the pool. When you call Open( ), the provider searches the pool for available connections.
  • Connections are reused only if the parameters match. This makes sense—in the previous example, you wouldn’t reuse a connection to the Northwind database if the client thinks it is opening a connection to the pubs database. Similarly, a change in security information (for example, the user account and password) or the location of the database server would cause problems if connections were reused indiscriminately.
  • If no pool exists, it is created the first time you call Open( ). Depending on connection string settings, the pool may be initially populated with a set number of connections, or it may be limited to a certain maximum number.

SQL Server and Oracle Connection Pooling

With SQL Server, Oracle, and MSDE, connection pooling is implemented by the managed provider, unless you have specifically disabled it with a connection string parameter.

Both the SQL Server and Oracle .NET providers give you some control over connection pooling with the connection string parameters in Table 3-5. Note that if you require a minimum pool size, the application incurs a small performance overhead when the first client connects, and the initial pool of connections is created. Note also that the connections are created one after the other, ensuring that the database server isn’t flooded with simultaneous requests when the pool is first created.

Table 3-5. Connection string parameters for connection pooling
Parameter Description
Connection Lifetime Specifies a time interval in seconds. If a connection is returned to the pool, and it is older than the specified Connection Lifetime, it is destroyed. The default is 0, which disables this behavior. This feature is useful primarily when you need to recycle a large number of connections at once, such as when you want to balance the load with a new server that has just been brought online.
Connection Reset[1] If true (the default), the connection state is reset when a pooled connection is reused. This setting requires an extra round trip but makes for easier programming (and is recommended). State includes session-level SET statements and the currently selected database.
Enlist When true (the default), the connection is enlisted in the current transaction context of the creation thread.
Max Pool Size The maximum number of connections allowed in the pool (defaults to 100).
Min Pool Size The minimum number of connections always retained in the pool (defaults to 0).
Pooling When true (the default), the connection object is drawn from the appropriate pool or, if necessary, is created and added to the appropriate pool.

As with all forms of connection pooling, every pool is divided into multiple transaction-specific pools (and one pool for connections that aren’t currently enlisted in a transaction). Fortunately, this process is managed transparently, and threads associated with a particular transaction context automatically receive a connection from the appropriate pool. However, it does mean that using client-initiated transactions can reduce the efficacy of connection pooling.

OLE DB and ODBC Connection Pooling

OLE DB connection pooling doesn’t need to be explicitly enabled because it uses the built-in OLE DB session pooling features. However, OLE DB connection pooling doesn’t support explicit configuration of thresholds and pool sizes. Thus, it is technically possible to actually achieve better performance by disabling connection pooling and implementing COM+ object pooling in its place. However, this is a complex task, and isn’t recommended unless you are comfortable with terms such as manual transaction enlistment.[2] Microsoft provides OLE DB developer documentation that includes more information about this low-level process. The first step is to disable OLE DB resource pooling and automatic transaction enlistment by including OLE DB Services=-4 in the connection string.

[2] If you’d still like to dive right into to this topic, try the excellent OLE DB developer documentation on MSDN at http://msdn.microsoft.com/library/en-us/oledb/htm/oledb_providers_overview.asp.

The ODBC .NET provider uses the connection pooling that’s implemented by the ODBC driver. You can configure pooling settings for ODBC drivers using the Connection Pooling tab in the Data Sources window.

Connection Pooling and Application Domains

One caveat applies with the managed connection pooling mechanisms in the SQL Server and Oracle providers: connections can be pooled only in a single application domain. In other words, if your system is made up of Windows clients that use your custom data access class locally, they can’t share a pool of connections. Instead, they each have their own local pool of connections, which is far less useful. Figure 3-1 shows this situation.

Connection pooling1

Figure 3-1: Direct database access: no connection pooling

To allow connection pooling, you need to host the custom data class out-of-process on a separate server, using ASP.NET, web services, or .NET remoting. In the case of ASP.NET and web services, the ASP.NET worker threads share a common pool of connections. With remoting, every component host shares a connection pool. Figure 3-2 diagrams this approach.

Connection pooling2

Figure 3-2: Using a remote data class: connection pooling is possible

Of course, that doesn’t mean you should replace local data classes with a more complex architecture based on web services or remoting. In fact, if you do, you could actually harm performance in a small-scale system. Communicating with an out-of-process component is an order of magnitude slower than communicating with a local in-memory object. When you also factor in the time taken to send a call over a network or serialize a message to XML, the latency becomes a noticeable drag on performance.

What you are faced with is a tradeoff between sheer performance for small numbers and scalability. Using clients with local data classes ensures the best performance for small numbers of clients, but the system will reach a bottleneck as the number of users increases and, under a large enough load, start to perform horribly. Using connection pooling with remoting or web services gives you the chance to ensure optimal scalability for large numbers of clients. But performance as measured by application speed isn’t the only issue. The maintainability, extensibility, reusability, consistency, and security of the system all need to be explored. Good architecture and design is about balance of these factors, and for many developers architecting mid-size systems, performance isn’t a key driving factor.

Connection Pooling and Performance Counters

With large-scale distributed systems that handle thousands of clients, it’s important to carefully choose the maximum connection pool size. Determining the correct size requires a little insight, some real-world experience, and a healthy dose of trial-and-error profiling. It depends on the number of clients, the pattern of usage, and the server hardware. This section will help with the insight.

To gauge the best pool size, you can use performance counters. By default, .NET includes a set of performance counters that work with the managed SQL provider. To work with them, choose Programs Administrative Tools Performance from the Start menu. Right-click on the graph, and choose Add Counter. Under the .NET CLR Data group is a series of useful counters for monitoring connection pool usage. The counters are described in Table 3-6. All these counters provide two options: a global total or a process-specific total (you simply choose the corresponding application name).

Table 3-6. Connection pooling performance categories
Counter Description
Current # connection pools Current number of pools. Each pool contains a group of connections that can be reused for different clients. Separate pools will be created for requests with different connection strings or for connections in different transaction contexts.
Current # pooled and nonpooled connections The total number of connections, including those in the pool.
Current # pooled connections The total number of connections currently in the pool.
Peak # pooled connections The highest number of pooled connections that was reached since the .NET process was started.
Total # failed commands The total number of SQL commands that have failed for any reason. Not directly related to connection pooling, but can suggest other problems.
Total # failed connects The total number of connection attempts that have failed for any reason. Not directly related to connection pooling, but can suggest other problems.

The .NET data counters can be added to the graph and logged through the MMC Performance snap-in, or you can use a dedicated testing tool such as Microsoft Application Center Test. In a typical test, you might measure the pool-usage statistics and record the performance of your application using a machine-specific counter simultaneously. This test allows you to see how the performance is affected by different numbers of users and usage patterns.

Advertisements

One Response to “ADO.NET Core Classes:Connections – Part 1”

  1. Extremely rated post. I study something totally new on completely different blogs everyday. Deciding on one . stimulating to read the paper content from other writers and learn slightly one thing from their website. I’d like to use certain of this content material on my blog you’re mind. Natually I’ll give a link here we’re at your internet-site. Admire your sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: