In my apps I organize all interaction with my databases in components. On my component is a connection to the database and a couple of data-adapters to read and write data.
These components are housed in a separate assembly. From there I can use them straight to fill the datasets on my web– or win-forms, or write a webservice (or other) remoting layer around them (see this 2005 example). How many adapters a component should have is a point of discussion. In my first 2003 applications they were gigantic. Nowadays I make them a lot smaller. Based on experimenting with datasets 2005–style (and all the SOA stories) I beleive a component should work with just a couple of related tables, like invoices and invoicelines. In this article on 2005 datasets that idea is explored a little further.
Inside the component the constructor opens the connection to the database.
public OnderwijsComponent()
{
InitializeComponent();
sqlConnection1.Open();
}
The component publishes a couple of public methods which expose the data as XML datasets.
public StudieOnderdeel StudieOnderdeel(int id)
{
StudieOnderdeel ds = new StudieOnderdeel();
StudieOnderdeelCRUD.SelectCommand.Parameters["@id"].Value = id;
StudieOnderdeelCRUD.Fill(ds.STUDOND);
return ds;
}
The connection to the database is closed in the componenet’s dispose method.
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
sqlConnection1.Close();
components.Dispose();
}
}
base.Dispose( disposing );
}
Using the using statement my webpages can interact with the database and will be sure that the connection to the database is allways closed.
private void Page_Load(object sender, System.EventArgs e)
{
using (StudieOnderdeel onderwijsData = new StudieOnderdeel())
{
studieOnderdeel1.Merge(onderWijsData.StudieOnderdeel(id));
}
}
Using will always fire the dispose method of the (Idisposable) object it is using, also when an exception is hit.
Working this way does have one drawback. Every time I need a componenet it is created, the db-connection is opened, the db is fiddled with and the component is disposed. When I need to do something with the data on several points in the page lifecycle, say in the pagerender (not the pageload) and in a button click, the component is created (and the db-connection is opened) twice on one roundtrip. Ado.net connection pooling is great but there is a more efficient way. To create my components only once on a roundtrip and only when really needed I add the componenets as “lazy” properties of the webform.
private OnderwijsComponent _onderWijsData;
private OnderwijsComponent onderWijsData
{
get
{
if (_onderWijsData == null)
_onderWijsData = new OnderwijsComponent();
return _onderWijsData;
}
}
The componenets are now created on demand. But I’ve lost the invocation of their Dispose method. Where to do that ? On a web form the unload event is always fired, also when the page throws an exception. The handler of the unload events checks if the component was created. If so it will invoke its dispose method.
private void StudieOnderdeel_Unload(object sender, System.EventArgs e)
{
if (_onderWijsData != null)
_onderWijsData.Dispose();
}
Following this pattern has the following advnatages
- A component (and a connection to the database) is only created when really needed
- A component is never created more than once on a roundtrip
- The component is always disposed. (And the connection to the database is always closed)
Works like a charm.