Connection Pooling in .NET/SQL Server?

Question

Is it necessary or advantageous to write custom connection pooling code when developing applications in .NET with an SQL Server database? I know that ADO.NET gives you the option to enable/disable connection pooling -- does that mean that it's built into the framework and I don't need to worry about it? Why do people talk about writing their own connection pooling software and how is this different than what's built into ADO.NET?

Answer 1

My understanding is that the connection pooling is automatically handled for you when using the SqlConnection object. This is purposefully designed to work with MSSQL and will ensure connections are pooled efficiently. You just need to be sure you close them when you are finished with them (and ensure they are disposed of).

I have never heard of people needing to roll their own myself. But I admit my experience is kind of limited there.

Answer 2

Well, it is going to go away as the answer to all these questions will be LINQ. Incidentally, we have never needed custom connection pooling for any of our applications, so I am not sure what all the noise is about.

Answer 3

The connection pooling built-in to ADO.Net is robust and mature. I would recommend against attempting to write your own version.

Answer 4

I'm no real expert on this matter, but I know ADO.NET has its own connection pooling system, and as long as I've been using it it's been faultless.

My reaction would be that there's no point in reinventing the wheel... Just make sure you close your connections when you're finished with them and everything will be fine!

I hope someone else can give you some more firm anwers!

Answer 5

With the advent of ADO.Net and the newer version of SQL connection pooling is handled on two layers, first through ADO.Net itself and secondly by SQL Server 2005/2008 directly, eliminating the need for custom connection pooling.

I have been informed that similar support are being planned or have been implemented in Oracle and MySQL out of interest.