This post originated from an RSS feed registered with .NET Buzz by Peter van Ooijen. Original Post: Minimizing the number of open SQLconnections when using components Feed Title: Peter's Gekko Feed URL: /error.htm?aspxerrorpath=/blogs/peter.van.ooijen/rss.aspx Feed Description: My weblog cotains tips tricks and opinions on ASP.NET, tablet PC's and tech in general. Latest .NET Buzz Posts Latest .NET Buzz Posts by Peter van Ooijen Latest Posts From Peter's Gekko

Yesterday David had an interesting post on opening and closing sql connections to the database where he discussed how a sql-adapter automatically opens (and closes) the connection. As I'm a big consumer of data-adapters myself and would like to share some of my experiences.

The database I'm working with is quite complex, it takes Red-Gate sqlcompare to stay in control of the great number of tables and sprocs. The (asp.net) application communicates with the database in components.

A component bundles a bunch of related data-adapters with a connection and exposes its functionality through public methods and properties. The component is used on the web pages. In this post you can find a more detailed description. The essential point for now is that the component itself manages the connection. The good thing is that the connection is opened once on component creation, kept open to be used by the data-adapters and sqlCommands. The sqlCommand class does not automatically open the connection. The connection is closed when the component is disposed.

To monitor how well this code behaves perfmon is a nice tool. Add a performance counter to the number on the number of user connections

This component approach works very well in my asp.net application. The number of open connections stays at a stable level. Even when a page completely crashes, the dispose pattern in asp.net does a good cleanup and closes the connection.

The database is too complex to house all interaction in one component so I built a whole list of them. As described the components are lazy properties and only created on demand. When a page does a lot of different things with the database it will instantiate several components. The downside is that every component opens its own connection to the database. Which is a waste of resources. An asp.net page executes sequentially and there is no way it would utilize the fact it has several connections to the db. Perfmon shows exactly when demanding pages are being served.

Now there is a dilemma between the good design time experience of a component and a good runtime behavior of the application. To edit the sql in the components adapters you do need the sqlConnection component. At run time you want one connection to be shared by all components used. A first approach might be to build a new connection component which inherits from sqlConnection. Alas, that is a sealed class. Instead I created a baseclass for my components which has a shared sqlconnection member.

The actual connection is a shared private member. This connection is shared by all objects of classes which inherit from this base class. The connection is published in a public instance property. The property getter instantiates the connection object when needed and ensures the connection is open. The dispose method closes the connection when the component is disposed.

Now I have my shared connection but still have to hook my code and components into it. As I don't want to ruin my design time experience  the original connection component has to stay. The component class has two constructors. The one with the container parameter is used when designing and the default parameterless constructor is used run-time. Both constructors make a call to InitializeComponent which will set up all adapters. Instead of opening the connection in the default (run-time) constructor I now set the sqlconnection to the shared connection in the base class.

 And now I have the best of both worlds. A good design time experience (you make puke if you want to :)) and a good run-time behavior.

These are my bits, now I wander what Sahil is going to say.