After the thousandth line of boilerplate database code, I decided I had had enough and it was time too look into something new.
Most multi-tier db apps these days consist of a database layer, a business object layer, and a presentation layer. The GUI and database update the business objects, so you should always have an accurate reflection of the state being modeled.
The major problem for a dev is that you have to map the DB to the business objects. Typically this involves a whole lot of diddling with type conversions as you get your programming language’s types into DB types and vice versa. The end result is nice though – you have a UI with no connection to your functionality – so if you need to turn your webapp into a full-blown OS-native application – it’s just a matter of writing a new presentation layer.
I have started to gravitate towards ORM (Object-Relational Mapping) and code generators to get through the mind-numbing CRUD and get to the fun parts.
The gold standard is Hibernate, but it’s Java and the .NET version is still alpha software.
Olero Software’s ORM.NET is fairly popular, but it appears to be abandonware which has now found a home on SourceForge.
I found a tool from France called LLBLGen which pretty much handles all the crufty filth associated with database apps. Point it at a table, click a button, and get sprocs and an object which models the table.
No more conversion of native string and integer types to DB-native types. No more dealing with NULL. No more creating and assigning parameters. You get an object, set the fields, call insert(), update(), or delete. Cake.
For simple tasks, you can use their free, one-way data tier generator. It’s a nice medium between the ORM world and the “SQL in the code” world.
Another task that I can’t stand is writing out the getters / setters (accessors / mutators) for an object, especially big ASP.NET User Control classes with 1000 form elements. Eclipse has a nice feature to let you automatically generate getters & setters from your private class vars, but VS.NET lacks the feature. Not anymore. It’s not perfect, but it works.
All you devs out there writing SQL in your loosely-typed scripting language probably feel that I’m just making things too hard. All I can say is this: your apps suck.