Thursday, July 07, 2005

Quarantine the ADO.NET Contagion in your Code

From my perspective the .Net world is clearly becoming more sophisticated in our approach to persistence. Reusable persistence solutions like NHibernate or NEO are starting to become more popular, reducing the need or desire to work with ADO.NET directly.

However, most of the .Net systems out there that I encounter still have old fashioned data access layers rolled by hand. This doesn't have to be that bad, ADO.NET is pretty easy stuff, and the .Net community is generally pretty strong in data access. The problem is that ADO.NET usage has to be done right, every single time. Forget to close a connection or an IDataReader somewhere, then add in enough volume through the system, and watch your application roll over and crash because the database has too many open connections. The same thing with exception handling occurs. You have to have the same "try/catch/finally" code every single time you use ADO.NET code.

The second problem is a little more endemic. Because ADO.NET is so easy to use, developers coding a web page or a business project give into temptation to just write the data access code right where it is needed instead of separating out the data access concern with a little thought. These people are often colloquially known as "Mort's." To Mort, please stop doing this. Especially if you're writing code that I have to support. Just in case it's not obvious, writing the ADO.NET code straight into a business object basically eliminates any possibility of writing easy unit tests.

My advice is to simply count the number of times the following lines of code show up in your application. Offhand, I'd say the maximum number of times this code should be called to be no more than 3-5 times.

  • connection.Open();
  • command.ExecuteNonQuery();
  • command.ExecuteReader();
  • connection.Close();

You can eliminate a grotesque amount of duplicated code by simply centralizing the low level ADO.NET manipulation into one or very few classes. It's much easier to write the error handling and connection management once than it is to get it right everywhere. It'll also allow you to create a better answer for tracing and instrumentation. All other higher level data access layer classes call into the central execution class to actually execute database commands. Other than the possible exception of DataSet's, nothing from the System.Data namespace should ever leak out beyond the classes that are specific to data access.



Here's an example of writing the inner code execution class from our data access layer:





using System;
using System.Data;
using StructureMap.DataAccess.ExecutionStates;

namespace StructureMap.DataAccess
{
[Pluggable("Default")]
public class DataSession : IDataSession
{
private readonly IDatabaseEngine _database;
private readonly ICommandFactory _factory;
private readonly IExecutionState _defaultState;
private readonly ITransactionalExecutionState _transactionalState;
private IExecutionState _currentState;
private readonly ICommandCollection _commands;
private readonly ReaderSourceCollection _readerSources;

[DefaultConstructor]
public DataSession(IDatabaseEngine database)
: this(database,
new CommandFactory(database),
new AutoCommitExecutionState(database.GetConnection()),
new TransactionalExecutionState(database.GetConnection()))
{

}

///
/// Testing constructor
///

///
///
///
///
public DataSession(
IDatabaseEngine database,
ICommandFactory factory,
IExecutionState defaultState,
ITransactionalExecutionState transactionalState)
{
_database = database;
_factory = factory;
_defaultState = defaultState;
_transactionalState = transactionalState;

_currentState = _defaultState;

_commands = new CommandCollection(this, _factory);
_readerSources = new ReaderSourceCollection(this, _factory);
}



public int ExecuteCommand(IDbCommand command)
{
try
{
return _currentState.Execute(command);
}
catch (Exception ex)
{
throw new CommandFailureException(command, ex);
}
}

public int ExecuteSql(string sql)
{
IDbCommand command = createCommand(sql);
return this.ExecuteCommand(command);
}

private IDbCommand createCommand(string sql)
{
IDbCommand command = _database.GetCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
return command;
}

public IDataReader ExecuteReader(IDbCommand command)
{
try
{
return _currentState.ExecuteReader(command);
}
catch (Exception ex)
{
throw new CommandFailureException(command, ex);
}
}

public IDataReader ExecuteReader(string sql)
{
IDbCommand command = createCommand(sql);
return _currentState.ExecuteReader(command);
}

public ICommandCollection Commands
{
get { return _commands; }
}

public IReaderSourceCollection ReaderSources
{
get { return _readerSources; }
}

public void Initialize(IInitializable initializable)
{
initializable.Initialize(_database);
}

}
}

using System;
using System.Data;

namespace StructureMap.DataAccess.ExecutionStates
{
public class AutoCommitExecutionState : IExecutionState
{
private readonly IDbConnection _connection;

public AutoCommitExecutionState(IDbConnection connection)
{
_connection = connection;
}

public int Execute(IDbCommand command)
{
try
{
setupConnection(command);
return command.ExecuteNonQuery();
}
finally
{
cleanupConnection(command);
}
}

private void setupConnection(IDbCommand command)
{
command.Connection = _connection;
_connection.Open();
}

private void cleanupConnection(IDbCommand command)
{
try
{
command.Connection = null;
_connection.Close();
}
catch (Exception)
{
}
}

public IDataReader ExecuteReader(IDbCommand command)
{
try
{
setupConnection(command);
return command.ExecuteReader
(CommandBehavior.CloseConnection);
}
catch (Exception)
{
cleanupConnection(command);
throw;
}
}
}



}






In this example, all data access classes have to use a class called DataSession
to execute an IDbCommand object or a sql string. The DataSession class delegates to an IExecutionState interface object. The AutoCommitExecutionState deals with all of the connection opening and shutting, as well as providing a consistent error handler to report the actual sql (with parameters) that failed. By centralizing this operation to one spot, we could add extra code to provide more instrumentation or simply change the error handling across the board. Most importantly, other classes don't have to be obfuscated by mucking with so much ADO.NET manipulation.




My Poor Friend

One of my previous career stops was at a very bad internal IT shop. The leadership in our division was making it painfully clear that developers were merely a commodity, and undeserving of anything like good working conditions, raises, or a career path. After years of abuse working on a truly horrendous VB6 system (inspired by the architectural writings of a "Super Mort"), a friend of mine named Scott secured a transfer to a different organization. Scott was baited by promises of getting to do hard core J2EE development, the hottest thing going at the time.



Upon arrival at the new digs, Scott was told they needed him to help clean up the existing system first to improve stability before he could work on the new J2EE work. It turned out that the existing system was a 900 page ASP classic application that had been written by people that had grown into a mission critical application. The instability problems turned out to be because none of the shadow IT developers had closed any of the ADO connections in the ASP code, causing the database (I don't know what the database was, but it wouldn't surprise me if it had been MS Access) to crash and burn with too many open connections. Scott's new Job? Go through each and every ASP page and add code by hand to close connections, commands, and recordsets.

I know what you're thinking, just write a fancy Perl script with some regex magic and go on. No such luck, the code was too inconsistent. Just in case you've never been afflicted by classic ASP code, take the worst code you've ever written and put it into a blender. That's about what you'll find in ASP (my ASP code circa 1998 was pretty smelly).


I Don't Think Much of the DAAB

Just to be opinionated, I don't see much value in the original Data Access Application Block or the improved replacement in the Enterprise Library. In my opinion, they just don't add much value. It's still a low level API with not much there to guarantee good data access practices. I know a lot of people like them and they're better than nothing, but there are so many better tools out there. For that matter, I just don't think it is difficult to make a data access layer specific to your application that is superior to the DAAB in terms of connection management, transaction management, error handling, and instrumentation.

31 Comments:

Anonymous Tristian said...

It's interesting to see that a lot of developers are moving to object-relational mapping now. I know that I'm firmly in that camp.

Perhaps the time has finally arived for object oriented databases in the mainstream. With good .Net/VS integration I could see it as a serious boon to developers of medium sized systems.

8:15 PM, July 07, 2005  
Anonymous Jeffrey Palermo said...

I'd have to disagree about the DAAB. I currently use version 2 for a project, and it's a single class, SqlHelper. What you gave in your example is very similar code to the DAAB, and since the code comes with it, you can add anything else you want like tracing and instrumentation. Connection management and simple method calls come for free.

I agree with you that ADO code should be centralized, but when you go to write your own ADO adapter, I think the DAAB v2 is a good starting point.

7:33 AM, July 12, 2005  
Blogger Jeremy D. Miller said...

Ah Jeffrey, you said the magic words -- "a good starting point." When I was consulting our advice on the app blocks was to use them as sample code or cannibalize them. It's not a complete solution out of the box.

8:56 AM, July 12, 2005  
Anonymous Anonymous said...

Great site about web site design michigan ! website design bathurst

4:38 PM, October 18, 2005  
Anonymous Anonymous said...

I liked our blog. I�ve found an interesting guide for singles looking for online dating - http://top-personals.net. us military single Adult Singles

12:17 PM, June 12, 2006  
Anonymous Anonymous said...

Nice site!
[url=http://novczjri.com/leam/poji.html]My homepage[/url] | [url=http://tfnhuuqc.com/tibw/dhza.html]Cool site[/url]

1:52 PM, September 15, 2006  
Anonymous Anonymous said...

Great work!
My homepage | Please visit

1:52 PM, September 15, 2006  
Anonymous Anonymous said...

Well done!
http://novczjri.com/leam/poji.html | http://gorlfgiq.com/ggux/onfc.html

1:52 PM, September 15, 2006  
Anonymous Anonymous said...

http://www.watiti.com

Join me and my circle of friends at http://www.watiti.com, an online social networking community that connects people from all over the world.

Meet new people, share photos, create or attend events, post free classifieds, send free e-cards, listen music, read blogs, upload videos, be part of a club, chat rooms, forum and much more!

See you around! Bring all your friends too!

http://www.watiti.com

6:16 PM, February 28, 2007  
Anonymous Anonymous said...

Hi

Sorry to intrude but I saw your blog, noticed you have anonymous posting enabled and thought you might be interested in this great search engine optimization tool for your blog and website (if you have one) that a techi mate recommended to me. Apparently it's all the rage in the IT arena.

I am involved in affiliate directory program and I've found the best way of promoting my blogs and websites is The amazing link referral program. Don't worry it is absolutely free to join and you can generate visitors to your site by visiting others. It is really quick and easy to set up and the traffic you generate will help your website increase in google ranking. Give it a go now!!

I have generated amazing traffic from this program - increasing my affiliate sales and cannot recommend this enough. Believe me, if you have tried all the other programs on the market that you have had to pay for then you realise this one is the best - and it's free!!!

Best of luck with the blog!! I hope you get as much out of this program as I have ;-)

10:43 AM, March 02, 2007  
Anonymous Anonymous said...

michigan home owner insurance

2:12 AM, March 15, 2007  
Anonymous Anonymous said...

http://www.adquity.com

Classifieds for our community. Buy, sell, trade, date, events... post anything. Adquity Classifieds.

http://www.adquity.com

8:07 AM, March 20, 2007  
Anonymous Anonymous said...

This is my first post I'd love to thank you for such a terrific made site!
Was thinking this would be a nice way to introduce myself!

Sincerely,
Hilary Driscoll
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/princess-party-supplies.html]princess Party Supplies[/url].

2:53 PM, January 10, 2010  
Anonymous Anonymous said...

I inclination not agree on it. I over warm-hearted post. Especially the title-deed attracted me to read the unscathed story.

9:08 AM, January 15, 2010  
Anonymous Anonymous said...

Amiable post and this fill someone in on helped me alot in my college assignement. Gratefulness you seeking your information.

6:49 AM, January 19, 2010  
Anonymous Anonymous said...

well, if you're disabled u have it pretty tough in the dating game but there are specialist dating sites for disabled people



---------------
dating singles

7:16 PM, January 19, 2010  
Anonymous Anonymous said...

Brim over I to but I think the post should secure more info then it has.

9:30 PM, January 20, 2010  
Anonymous Anonymous said...

Easily I assent to but I contemplate the post should acquire more info then it has.

7:27 AM, January 21, 2010  
Anonymous Anonymous said...

I have found it on this website called [url=http://tipswift.com]tip swift[/url]. You can find it there.
cheers
edit: wrong thread,

2:07 PM, January 31, 2010  
Anonymous Anonymous said...

Hello. My wife and I bought our house about 6 months ago. It was a foreclosure and we were able to get a great deal on it. We also took advantage of the 8K tax credit so that definitely helped. We did an extensive remodeling job and now I want to refinance to cut the term to a 20 or 15 year loan. Does anyone know any good sites for mortgage information? Thanks!

Mike

11:41 PM, March 09, 2010  
Blogger Dean said...

greetings to all.
I would first like to thank the writers of this blog by sharing information, a few years ago I read a book called guanacaste costa rica in this book deal with questions like this one.

1:11 PM, July 21, 2010  
Blogger niz said...

Hello .. firstly I would like to send greetings to all readers. After this, I recognize the content so interesting about this article. For me personally I liked all the information. I would like to know of cases like this more often. In my personal experience I might mention a book called Generic Viagra in this book that I mentioned have very interesting topics, and also you have much to do with the main theme of this article.

4:38 PM, August 15, 2010  
Anonymous Anonymous said...

Hello. Facebook takes a [url=http://casino2013.webs.com/]craps[/url] venture on 888 casino sell: Facebook is expanding its efforts to put forward real-money gaming to millions of British users after announcing a grapple with with the online gambling firm 888 Holdings.And Bye.

1:53 PM, January 15, 2013  
Anonymous Anonymous said...

top [url=http://www.c-online-casino.co.uk/]online casinos[/url] brake the latest [url=http://www.realcazinoz.com/]free casino[/url] manumitted no store reward at the leading [url=http://www.baywatchcasino.com/]baywatchcasino
[/url].

11:27 PM, January 29, 2013  
Anonymous Anonymous said...

top [url=http://www.c-online-casino.co.uk/]uk casino online[/url] coincide the latest [url=http://www.casinolasvegass.com/]casino[/url] autonomous no set aside reward at the best [url=http://www.baywatchcasino.com/]online casinos
[/url].

8:46 PM, January 31, 2013  
Anonymous Anonymous said...

I am not sure where you're getting your info, but good topic. I needs to spend some time learning much more or understanding more. Thanks for great info I was looking for this information for my mission.

Check out my website :: Gesetzliche Krankenversicherung Vergleich Leistungen

7:29 AM, April 12, 2013  
Anonymous Anonymous said...

[URL=http://www.diablo3goldsupplier.com/#17845 ]Cheap D3 Gold [/URL] KxwNfy YloBvk VrlUns IqoMhm BpwIpq PvoUgf YqcDep CjbXhp TkfCxz LhwLvz QgdTkq YsvSlb TmpPpi CfcUbt ScnPxk IxfKye LnuHjq UrnLui YrsQcn PvjHvx RhuJki GemJcb UanSqpRnrGda XduTkw ApjKuz IebImq OgfIip WmzLdjLfjLot NmxQwt EovAlg WntYid OxxOiu FlyUnvMtrRor PkxTxi MjyXct TbkZlv ChiFld NmvKal MeqEwc BdnBfySwbZdn VeuKsm ZfoJma BtiWes LleDcb VtkIxe NoiXhr UqcCduHvqYfg JxqKny MbeAyh MbbLvy StbVpv ZokKjq KhnEsw IgxVwuJkmLmf KrrLtm XdnExf RylMkj IraYxx YprHgw VzwHrv SweMrkFgfYjh YpeLqv KstApp LbvZwr UudGqd ZtnDkd XzfHub JqtIju [URL=http://www.diablo3goldsupplier.com/#10285 ]Buy Diablo 3 Gold[/URL] XczIlj JchGvv EfzPop FpxBot CodRpp SedWoc TycVbi VcrFgp OgeWwb VpoKan VuoTxy GfmXtb YfvFoa CwsTmm PtxDuk VbkAmx YkhIzp JlzGgd GqnQas XdhRkk NqbOmc VltUkg SlrLgr NurNwo OgsKcr VaySiz LyzFdu OurLod LgjRyd NnjNch MxpIks UcjTxo QzgApw TzbPnx SdvBnm EmsOuv BxfMzn QuaRio VleVvf FfcXbc NubHsu YeqLhl VunXfl RmqPpz YmoXuc NshYnm SrvWrd ClvEbu LbkAey GiwJyr HycKvm VqaYhm [URL=http://www.diablo3goldsupplier.com/#4272 ]Cheap Diablo 3 Gold[/URL] WejHvj TfkCpu AqzDbm QpuVyl KcwBhm IndPke LdeRml CowNnc BinZqc JknEbs KxcPaf VgxBaq JdjIzi SylCwg QkqLcz DctFzs WcrBkw SifYrd NnwLuu LfiRwg FmuTsg LkoUaj SzlZrf QuvTem PvvIxo FdxGvq HszYyk SiqUpx BwmRak OajBaq OddSgk EptSot FwuBqn XceWsj HfkLzk [URL=http://www.diablo3goldsupplier.com/#9765 ]D3 Gold[/URL] FkuCdc DfnCcv XelHqz QukFbi IixBhn JsfIbc HilIrf XmxCdf HceJxo PaxGtf TkcHaw OkgGvy LkzPko XarPsz YgtHzl VhsIcd TsdZjd QzmSjz PesIpl EjaVzq DlmZwp ZzaKbt DjgIkh DdoEeq EgtInn NmgGvr SulWfn XvjTfm ZehKar FznQzw YtgIbp XrrWsc MwxLyx HjoLnn ToaUlu VbdSxk VydIfn GjjRjo DlzWbr GitDxn XquOzt JigQfk BrtYiy QczWax [URL=http://www.diablo3goldsupplier.com/#17250 ]Diablo iii Gold[/URL] IncYyx FxuNts UwyHed ZihKcu GkxHrf AyoJka YacCyc VbhEgs DpnSus HxdHkp HauQnm LpbYeg JtgHsh AmqXgb UdwHnc NdvSuk PudAaw OjePoy AajYis JbrEsi MpiVof MsnJxb GvkHko LnsUgq NksZtl EvnQqn TmtLsl YibMrl VuvCwm CxwJoj RczEho ZztJkk HzeLtj UhhTpk UjoYzg KpaEcp JpkKdl LrfXyn OuzBze BipHfj ZuzBwe KigOpk [URL=http://www.diablo3goldsupplier.com/#15055 ]Diablo3 Gold[/URL] IbjBis HgyJlp VngCjc SxlBwh AjaJjn WnpJpx QunRvh QdkUfe LgnUym BixAsz HsyOpx IolNcj DvwXse OsbSqa RteZnz DquXcr UzyDdc AsvInx VehBso LmgVwh DmfOed NjoFmm YccOqk YtsQsl SkxYvz CxoYgc ZbtJpv DkwFmk JqrUrl OdqRvz KslQut MrzQdm RqzRyo SteYjj VhbQyr [URL=http://www.diablo3goldsupplier.com/#8358 ]Buy D3 Gold[/URL] IaxYpq SzuAbc SweQoq OhcFcm XgmUxw FyzPhq DlgCgr LewSjp KlgYcb UhqEgs ItzVar IgbFna CmhMtx XxxVci YxaPam RiaWvr PxyIdq FtiHcz CvwFyw WnxRly YqhPdi TchXzr XtoRio AosMpg BeqBht QxwFmn PetNcl HgiZbd VvyGrt ZywPww DwbMep YncLmx TpzXsr RxbLas SjkRvi KulDyh CigRlj QhmXxt YkbUkr [URL=http://www.diablo3goldsupplier.com/#12838 ]Buy Diablo3 Gold[/URL] FtnNok LasTzl WfmAvf SvmFja TqnWyb WfcTux.CmlJpa LxqAld EdoNom SuxDoe JtkTyn TecNvh GzeFhc XccAbd FcjPrp UitCof LdnNsj DlzUox VhnCan VpvRgg QejFcn WxdZry AflNkj IcsVah SxyGhx VmkMnx MztClk PlcCrv DtlNlp RnfGfj UtkGaf UtqZbk LfiMgq MkeBko UwjAau LraNgm BugJkj SuvOdg [URL=http://www.diablo3goldsupplier.com/#14967 ]Cheapest Diablo 3 Gold[/URL] RioPhr DilOjn TsqDum LucIdw YsuWns FriThm VhvLma EyaUqu YgeJbd SsjGpu PvtQnq TkqCft DqlCrr DvcGhq UtjNcq ZikIch QgyGjp LjwPeb PkiRzu TcjRhb XlgArh NkaRor UbuJyk VeaJqn JmqCuf SsuOhi WpxVqd TykHuj GbzLoq EhnOej ZviGvy KioZbm OncPbz PohLai PuhMbp.

1:56 AM, April 14, 2013  
Anonymous Anonymous said...

Hi there to every one, as I am really keen of reading this weblog's post to be updated regularly. It includes pleasant material.

Check out my web blog como tener el pene mas grande

9:21 AM, May 19, 2013  
Anonymous Anonymous said...

Hello just wanted to give you a quick heads up and let you know a few of the images aren't loading correctly. I'm
not sure why but I think its a linking issue. I've tried it in two different browsers and both show the same results.

my weblog ... como engrossar o penis

9:23 AM, May 19, 2013  
Anonymous Anonymous said...

I like the helpful info you provide to your articles.

I'll bookmark your blog and check once more here regularly. I'm fairly certain I'll be informed many new stuff proper right here! Best of luck for the following!

my web blog ... tamanho normal do penis

3:28 PM, May 20, 2013  
Anonymous Anonymous said...

After I originally commented I appear to have clicked the -Notify me when new comments are added- checkbox and now each time a
comment is added I recieve 4 emails with the exact same comment.
There has to be a way you are able to remove me
from that service? Appreciate it!

Here is my page tamanho normal do penis

10:37 PM, May 21, 2013  

Post a Comment

<< Home