The task was simple and sounded like a decent technical challenge and an interesting opportunity.  Build an ASP.NET 2.0 with SQL Server 2005 transactional website that the financial industry can use to list special events and webinars so traders and investors can find it all in one location.  I worked with the business owner and came up with the high level requirements.  They are listed next and will look familiar to most of you.

1.    Provide ability for users to register.

2.    Allow registered users to list their events and webinars.

3.    Allow registered users to perform CRUD actions to entered data (create, read, update, delete).

4.    Provide administration interface for CRUD to all application data.

5.    Display all events on the website in a format that allows sorting.

Of course, there were more requirements than what is listed above, but this is the core that we focus on moving forward in this article.

I have my own technical requirements that I bring to projects I lead, namely, that it should be as object oriented as possible and utilize the tools and technology available.  ASP.NET is object oriented itself, so extending this concept into the other layers of the application is logical and advantageous.  The second requirement is that the design should not only promote code reuse, it should provide for it.  Repetitive code and functionality is a waste and a design “smell” that I try to avoid. 

This project is the type that could easily end up with repetitive and hard to maintain code, especially in the data layer.  Let us take a look at the options for data access.  First, we could write inline SQL for all data access.  We can rule that one out easily, as it ends up being difficult to maintain and makes OOP concepts difficult to apply.  Let us not jump in the tank with that octopus.

The second option is to use stored procedures and wrap them with their own methods.  This is without a doubt the most popular way to build a data driven solution with any of the .NET languages and technologies.  If you ask any group of developers if they use stored procedures for their data access at any point in time you will see that four out of five reply with an affirmative.  Take that to the next logical step and ask why they use stored procedures; the answer is almost consistently – (a) it is faster because it is compiled and (b) that it is more secure.  I will accept (b) as a technically sound and savvy answer, but will question (a) all night long.  Without rehashing this impassioned argument, I will simple say you can get the speed of stored procedures within .NET without writing a single stored procedure.  Plus, you can get that with a better, cleaner and more object oriented manner.