Data Providers

• These are the implemented providers in .NET: SQL Server, OLE DB, ODBC, Oracle, EntityClient Provider, SQL Server Compact 4.0.

Connection String Builders

• Each of the .NET Framework data providers supported different syntax for connection string keywords, which made constructing valid connection strings difficult if done manually.
• To address this problem, ADO.NET 2.0 introduced new connection string builders for each .NET Framework data provider.
• Each data provider includes a strongly typed connection string builder class that inherits from DbConnectionStringBuilder.

var connectionStringBuilder = new SqlConnectionStringBuilder
{
	DataSource = @"(localdb)\\v11.0",
	InitialCatalog = "ProgrammingInCSharp"
};

var entityConnectionStringBuilder = new EntityConnectionStringBuilder
{
	Provider = "System.Data.SqlClient",
	ProviderConnectionString = connectionStringBuilder.ToString(),
	Metadata = @"res://NinjaDomain.OldStyleContext/NinjaObjectContext.csdl|" +
					 @"res://NinjaDomain.OldStyleContext/NinjaObjectContext.ssdl|" +
					 @"res://NinjaDomain.OldStyleContext/NinjaObjectContext.msl"
};

Connection Pooling

• In practice, most applications use only one or a few different configurations for connections.
  • This means that during application execution, many identical connections will be repeatedly opened and closed.
• To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.
• Connection pooling reduces the number of times that new connections must be opened.
• The pooler maintains ownership of the physical connection.
  • It manages connections by keeping alive a set of active connections for each given connection configuration.
  • Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool.
  • If a pooled connection is available, it returns it to the caller instead of opening a new connection.
  • When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it.
  • Once the connection is returned to the pool, it is ready to be reused on the next Open call.
• Only connections with the same configuration can be pooled.
• ADO.NET keeps several pools at the same time, one for each configuration.
• Connections are separated into pools by connection string, and by Windows identity when integrated security is used.
• Connections are also pooled based on whether they are enlisted in a transaction.
• Unless you explicitly disable it, the pooler optimizes the connections as they are opened and closed in your application.
• When connection pooling is enabled, and if a timeout error or other login error occurs, an exception will be thrown and subsequent connection attempts will fail for the next five seconds, the "blocking period".
  • If the application attempts to connect within the blocking period, the first exception will be thrown again.
  • Subsequent failures after a blocking period ends will result in a new blocking periods that is twice as long as the previous blocking period, up to a maximum of one minute.
• When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.
  • Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity.
  • Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.
• If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity.
  • However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends.