In my last post, I mentioned that I think that hand rolled data access layers using stored procedures are a bad thing, and one of my co-workers asked me to elaborate.

The main reason that I don’t like hand rolled data access layers is that by writing your own DAL, you are basically reimplementing a solved problem. Think about all of the things you have worry about if you write your own DAL:

• Creating the CRUD stored procs
• Writing ADO.NET code to call the stored procs
• Figuring out which entities are dirty and need to be saved, and which properties have changed
• What relationships do you load when you load an entity from the database? How do you do lazy loading of relationships?
• Writing tests to test all of this custom code that you have to write

There are numerous object-relational mapping (ORM) tools out there which take care of all of this. NHibernate is my favorite (more specifically, Fluent NHibernate). NHibernate has been around for several years and is very mature. There are ORMs from Microsoft (LINQ to SQL, Entity Framework), and numerous other ORMs out there.

These ORMs have thousands of people using them, so they have been well tested and have proven over time to be very effective at what they do. Countless hours have gone into the development, design, and testing of these ORMs.

Think about what you are trying to do by writing your own hand rolled data access layer. Who are you to think that you can do a better job in a short amount of time than the people who developed these mature solutions over several years? Data access is a very complicated thing to try and implement, and some of the most painful code that I’ve ever had to deal with was found in someone’s hand rolled data access layer.

Here’s the thing — a large percentage of your data access is vanilla loading and saving of objects from the database. In these cases, performance is not a concern and you do not need any special queries in order to optimize the loading and saving of these objects.

For a very small percentage of your data access, performance may be a concern and you may need to use custom stored procedures. So in these cases, you can bypass the ORM and write your stored procedures and custom code in order to optimize the loading and saving of these specific objects.

If you use stored procedures and custom code, you have more control over things. This also comes with a cost (longer time to develop). If you are accepting this cost for cases where you don’t need to optimize the data access, I would say that you’ve wasted time on premature optimization, not to mention that you’ve probably had to spend time implementing all of that data access code.

I would rather use an ORM that has implemented everything that is difficult about data access. Now all I have to tell it how to map to the database (which isn’t that hard) and tell it to do things for me. With lazy loading, it won’t load entity relationships (e.g. child collections on an entity object) unless it needs to. It knows when objects are dirty and if they need to be saved or not. I have an easy hook so that I can validate entities when they are saved.

The other day I configured a cache in NHibernate in 15 minutes. With very little code I was able to set NHibernate up so that it will cache entities that don’t ever change in memory so that it doesn’t have to get to the database to get them every time. There were numerous examples on the internet telling me how to do this, and I’m extremely confident that it’s going to work (I didn’t have to write any of the difficult code for caching because NHibernate has solved that problem).

I want to write code that solves business problems. Thankfully other people have written libraries that will help me do that. So I’m going to use them.

Software development is hard. You have to know how to use so many different tools and you have to try and keep up with it all.

Lately I’ve been hearing some griping about certain tools that people feel are trading good software design principles for ease of use. Many of these tools usually have some kind of drag-and-drop designer functionality that is designed to help you get things done faster. The griping comes from the fact that these tools can cause developers to make poor design decisions because the drag-and-drop tool led them in the wrong direction. I can’t say that I disagree with these complaints.

There are two sides of every coin, so let’s swing the pendulum the other way. Many open source libraries that I’ve seen take a different approach to design and the people that write them say that they’re not writing them for the novice user and that you will have to be familiar with advanced design patterns in order to use these tools. I think this is a good thing because software development is complicated and it’s OK to have make a library more complicated so that it fits in with good design patterns (like the need for an IoC container in FubuMVC).

Sometimes I wonder if we could do more to make these complicated tools easier to use. Take ORMs for example. Most people out there would agree that NHibernate is the most fully-functional ORM that we have. Yet I was on a project a couple years ago where developers floundered around trying to get their NHibernate mapping files to work (usually because of stupid stuff like typos, not making the .hbm.xml files Embedded Resources, not fully qualifying an assembly name, not making something virtual, etc.). Eventually we got better at it and we learned the tricks, but we wasted a ton of time trying to fix stupid mapping file issues.

For the next project I was on, we chose LINQ to SQL over NHibernate (this was in early 2008 when LINQ to SQL had just come out). I had an existing database with my tables in it, so I dragged them all into the LINQ to SQL designer and started writing business logic. It was amazing! It all just worked!

Now that led me into other pitfalls because now I had to map my objects 1:1 with my database tables, and crap from the database concerns cluttered up my model and caused a lot of grief. But even with that, we got work done much faster with LINQ to SQL than with NHibernate. Does this mean that LINQ to SQL is “better” than NHibernate? I wouldn’t say that, yet I was able to be more successful with LINQ to SQL. This is an important thing to note.

Open source tools like NHibernate are usually written by really smart people. Really smart people have no trouble figuring out complicated stuff. Sure, they may experience the same pain at first, but they pick it up quickly. All of the other really smart people that they talk to understand it too.

Not everyone is really really smart, unfortunately. I hear a lot of people harp on NHibernate and say it’s way too hard to use, that it’s not worth it, that there has to be a better way, etc. Most of these opinions come from a bad experience that they had with NHibernate.

I can’t discount these opinions that people may have. While these same people may agree that NHibernate is the most fully-featured ORM out there, for them it is not the ORM that will make them the most successful. I know a lot of people in this camp and they are not stupid people and they aren’t too lazy to learn NHibernate. It’s just that NHibernate did not lead them into the pit of success.

The fact is that most developers that are going to be using tools like NHibernate are your common, average, everyday developer — not bad developers by any stretch of the imagination, but they’re not as smart as the really smart people. I’m certainly not saying that we should sacrifice functionality and good design patterns to make things easier to use. I just think that more effort should be spent on making sure that average developers can figure out how to use something correctly, whether through documentation, good examples, or coming up with easier ways to do things. Really smart people don’t need these things to be successful with these tools, but the average developer will benefit immensely.

For me, this is what Fluent NHibernate did for NHibernate – it abstracted away a lot of what was difficult about NHibernate. Recently I’ve been able to use it on a project and it totally turned the tables. Now I get the power of NHibernate but I can use conventions to auto-wire my mappings, and the mappings are all in code instead of in XML. For me this is a huge deal. Now I get the power of NHibernate and I’m not stuck doing all of the crap that slowed me down before.

Another example of this is the reams and reams of documentation that the Microsoft patterns & practices team has written up for writing secure applications with WCF. WCF is very complex and powerful, but Microsoft did not leave us hanging and wrote up what to do in almost every scenario imaginable. They didn’t just give us a tool, they gave us what we needed to be successful with it.

We need more stuff like this.

In 10 minutes you can be stepping into NHibernate code to see exactly what’s it’s doing with your project. Here’s how to do it:

1) Go download the NHibernate source code from here. As I’m writing this, the latest release version is 1.2.0.GA. (I’m assuming here that you already have NHibernate set up to work with your application.)
2) Unzip the source to a location on your machine.
3) Open the NHibernate.sln file in Visual Studio that corresponds to the version of the .NET framework that you are using (1.1 or 2.0). If you really want to build everything, including the Iesi.Collections assemblies and all the unit tests, then open the NHibernate-Everything.sln file, but for me this is overkill because I usually only want to step into the NHibernate code. If you build NHibernate-Everything.sln, you’ll have to have NUnit 2.2.8 installed to build the unit test projects.
4) Make sure the Debug configuration is selected and build the entire solution.
5) If you open the \src\NHibernate\bin\Debug-2.0 (or Debug-1.1) folder, you will see all of the assemblies along with the .pdb files (if you just build the NHibernate-1.1/-2.0.sln file, you will only see one .pdb — NHibernate.pdb). Copy all of the .dll and .pdb files to the location where you currently have your NHibernate .dll files in your project.

Now when you debug your project, you should be able to step into NHibernate code!

Obvious caveat: we’re assuming that the source code that you downloaded is the exact same code that they used to build the release assemblies. I don’t see any reason why this wouldn’t be the case, but I’ve run into this problem before with other third party packages (like Infragistics NetAdvantage controls). So it might not be a bad idea to remove the debug NHibernate dlls when you’re done stepping into them and put the release dlls back in so that you don’t get burned.

Hope this helps! If you encounter any problems with this process, please let me know.