How does connection pooling work under ADO.NET? What are its pros and cons?
Connection pooling works in ADO.NET in a very similar way as it did in OLE DB. Using connection pooling represents cutting dramatically the overhead required to create and destroy connections in SQL Server. Each different connection string, providing the security context doesn’t change, creates a new pool of connections, and a .NET application maintains this pool alive when a connection is disconnected from the client side, maintaining this connection connected to SQL Server for further use.
This technique doesn’t represent many problems, but programmers need to be extra careful with termination code. Any temporary object or outstanding transaction will remain there alive for the new user of this particular connection, potentially producing unexpected results. So make sure you drop any temporary objects you create, and close properly all transactions before disconnecting from ADO.NET, because this doesn’t represent necessarily that SQL Server will close this connection.
Connection pooling provides better scalability to your applications when many different connections to SQL Server must be established, but not many simultaneously. Typical examples are web applications. In this case, trying to use connection pooling represents some extra security challenges. Using SQL Server authentication or Windows authentication will represent using a different pool of connections for each combination of connection string and security context, and this will make using connection pooling almost useless. The whole idea of using connection pooling is to have many chances that when you request a new connection, you could retrieve it directly from the pool, without asking SQL Server to create a new connection for you.
A solution to this problem could be providing some kind of user-defined authentication that will result in access to SQL Server through a few pre-defined roles/logins, trying to limit the number of connection pools, and maximizing the reuse of open connections.
One thing to consider is that using connection pooling is incompatible with using SQL Server application roles.
If your intention is using connection pooling in components that will run in the middle tier, it would be better to leverage the capabilities of COM+ object pooling which will provide a similar effect than connection pooling from the database access point of view.
A final comment about connection pooling, to clarify some common misconception: connection pooling has nothing to do with SQL Server. In other words, SQL Server doesn’t know anything about the existence of any pool of connections. SQL Server sees connections, pulled or not, full stop.
What is the best way to get up to speed on ADO.NET? What resources can I find to help me learn ADO.NET?
The ADO.NET Framework documentation provides a comprehensive starting point for learning about classes, methods and properties available. However, there is not much information on how to do things. I recommend to take a look at the examples installed with the .NET Framework in the samples directory (by default C:Program FilesMicrosoft.NETFrameworkSDKSamplesQuickStarthowtosamples) where you can find excellent examples on ADO.NET.
Another excellent resource is the official ADO.NET newsgroup:
News://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet
GotDotNet (http://www.gotdotnet.com) is an excellent resource on .NET overall.
There are many books available on this subject, and I am sure that they will be far more to come. I wouldn’t want to mention any particular book here, but everybody agrees that Bill Vaughn is always an important reference in this field.
Microsoft Certified Technical Education Centers (CTECs) offer an excellent official course on this topic: MOC 2389: Programming with ADO.NET (http://www.microsoft.com/TRAINCERT/SYLLABI/2389BFINAL.ASP)
Besides everything else we have already discussed, do you have any other comments or suggestions on how to get the most out of ADO.NET and SQL Server?
I’d like to mention again the fact that ADO.NET developers should know as much as possible about SQL Server and SQL Server DBAs should know as much as possible about ADO.NET. Only from this mutual understanding we will start running the efficient database applications that could satisfy our users’ needs. So .NET developers: you should start thinking in the Transact-SQL way, and SQL DBAs: pay a close visit to the .NET world.
Everything I did mention so far applies to the connected layer, because the way the DataSet work has nothing to do with SQL Server. UI developers can focus in this disconnected layer to provide a rich user experience to the application’s users, and they don’t need to know how SQL Server works. However, the way a DataSet is filled with data and the way changes to a DataSet are applied back to SQL Server is the full responsibility of the connected layer, and all my comments here apply to them.
]]>