I answered a fun question on StackOverflow today. The programmer was getting an error about an “=” sign that didn’t belong there. I looked at the code and it was flawless. The SQL didn’t contain any = sign. So what was the error? It turned out that he mixed up his syntax and sent the connection string in place of the SQL command. It is a simple mistake, but extremely easy to overlook. You usually require “a second set of eyes” (eg. a colleague) to see it.
I can’t count the number of times that I have made simple mistakes like that in my career. Because of stuff like that, I like to have a DB interface library that does most of the work for me. I only pass a command and parameters and it wires up the connection, etc. It ends up being less code for me to write (and potentially make mistakes).
Even better than that is when the code is auto-generated. ORMs can be pretty nice for that, except there are some quirks to each ORM. Also, maybe I am a little superstitious, but I like to be able to step debug through some of that code. ORMs remove some of the work and complexity, but they remove some of the flexibility at the same time.
The middle ground between these two, is to use a code-generator. I’ve seen some good demos with T4 toolkit. However, I still like My Generation. I use it for generating SPs and POCOs with some built-in validation. I also made some templates for generating “audit triggers” and associated tables for a database. That template has been pretty handy on several occasions. Once, I even considered making a template for generating some RPG code. I quickly came to my senses and avoided the RPG instead.
The thing that I like best about code generators is that I can tailor my templates to generate whatever code I want. If I am handed a project with a strange DB design or some strange requirement, I just tweak one of my templates and generate all of my DB layer code to handle the oddity. Especially since .NET 2.0 introduced partial classes, all (non-generated) additional DB code can be manually made in separate partial classes (files) without fear of conflicting or being overwritten with the generated code.
If you are not familiar with code generators, take the time to familiarize yourself with one or two and add it to your bag-of-tricks. Someday, it will save you a lot of time and headaches (and evasive errors).