Connection Pooling and the “Timeout expired” exception FAQ

System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.   This may have occurred because all pooled connections were in use and max pool size was reached.     Well, it has happened again, Yet Another Thread On Leaked Connections (YATOLC). This is one of the most common and painful…

30

Using Sql Server 2005 Snapshot isolation level with ado.net 2.0

Sql Server 2005 now supports non-blocking read operations with the Snapshot isolation level. What does this mean for ADO.NET 2.0?   Not much really, all of the hard work is done in the server and all we have to do is expose it. We already have a System.Data.IsolationLevel Enum and all we have done is…


Programatically accessing ADO.NET 2.0 performance counters

I have found that many times using Perfmon with the new ADO.NET counters is not ideal, a much better approach may be to get the performance counter information directly through code. There is only one minor complication, to make it easy to differentiate between instances with the same name we have formatted instance names as “<instancename>(<pid>)”. here is…

1

ADO.NET 2.0 new SqlClient Provider Statistics

Presenting ODBC-style statistics for SqlClient. SqlClient statistics are strictly per connection, this should be obvious looking at how the API has been designed. Statistics are not kept for operations that are canceled or datareaders closed before their result-set is fully retrieved. These statistics are not going to map one to one to the old ODBC…

3

ADO.NET 2.0 new GetSchema method. Schema is finally a first class citizen of the managed providers.

ADO.NET 2.0 has finally added support for retrieving Schema information. In v1.1 your only option was to go through the OLEDB native provider through the GetOledbSchemaInfo class, now Schema is a first class citizen in all of the Managed Providers. using System.Data;using System;using System.Data.SqlClient; namespace DataViewer.Repro{            public class Repro            {                        public static int Main(string[] args)                        {                                   …

9

ADO.NET 2.0 new Performance Counters

 Today I want to go over one of the least talked about new ADO.NET features, performance counters. This has been a black mark on the ado.net story since RTM. We found out the hard way that performance counters did not work with URT v1.0 and v1.1, it is only with the new reliability enhancements that…

4

Setting the IsolationLevel using the System.Transactions TransactionScope.

Traditionally distributed transactions have always run in Serializable isolation level, the safest but most expensive transaction isolation level where everything that we look at is locked from the moment we modify something and until the distributed transaction completes. This is very expensive and most of the time it is not necessary, fortunately we are no…

2

Format=UserDefined UDTs in Sql Server 2005

Today I am talking about Format=UserDefined User Defined Types (UDTs). Format=Native UDTs are leaner, meaner and faster but only support blittable types as fields (no Strings!). UserDefined UDTs support any field type since they require the user to implement the UDT serialization themselves (in Native UDT the server does all the work). This sounds scary,…

2

Getting started with Sql Server 2005 User Defined Types (UDTs)

I have really tried to start with the simplest possible UDT I could come up with. As you can see, the simplest UDT is still quite a handful, and this is still not a complete example.   using System; using System.Data.Sql; // Required by SqlUserDefinedType . using System.Data.SqlTypes; // Required by INullable.    [SqlUserDefinedType(Format.Native)] public…

7

Whidbey ADO.NET 2.0 User Defined Types (UDTs) and Visual Studio

User Defined Types are a complicated feature, but it is hard to tell when you are using Visual Studio. All you need to do is create a Database project, connect to a Sql Server 2005 server, add a User Defined Type to your project and then click on Deploy. Magic, you are done. As is…

5