How to create a proper database layer?

Currently I use a lot of the classes (SqlConnection, SqlCommand, SqlDataAdapter etc..) to make calls to our sql server. Is that a bad idea? I mean it works. However I see many articles that use an ORM, nHibernate, subsonic etc.. to connect to SQL. Why are those better? I am just trying to understand why I would need to change this at all?


I did check the following tutorial on using nHibernate with stored-procedures.

However it looks to me that this is way to overkill. Why would I have to create a mapping file? Even if I create a mapping file and lets say my table changes, then my code wont work anymore. However if I use to return a simple datatable then my code will still work. I am missing something here?

13.10.2009 15:38:01

There's nothing wrong with using the basic ADO.NET classes.

You might just have to do a lot more manual work than necessary. If you e.g. select your top 10 customers from a table with SqlCommand and SqlDataReader, it's up to you go iterate over the results, pull out each and every single item of data (like customer number, customer name, and so forth), and you're dealing very closely with the database structures, e.g. rows and columns. That's fine for some scenarios, but too much work in others.

What an ORM gives you is a lot of this "grunt work" being handled for you. You just tell it to get a list of your top 10 customers - as "Customer" objects. The ORM will go off and grab the data (most likely using SqlCommand, SqlDataReader) and then pulling out the bits and pieces, and assemble nice, easy to use "Customer" objects for you, that are a lot easier to use, since they are what your code is dealing with - Customer objects.

So there's definitely nothing wrong with using ADO.NET and it's a good thing if you know how it works - but an ORM can save you a lot of tedious, repetitive and boring grunt work and let you focus on your real business problems on the object level.


13.10.2009 15:44:20
But isnt it good to have control over the sql that is being used? I use sprocs (don't kill me for that), however they work for us. Why do I fix something that isn't broken?
vikasde 13.10.2009 15:53:09
Yes and no- yes, if you're really really good at writing SQL. But in at least 90% of the cases, the SQL generated by the ORM's is at least as good as the hand-crafted SQL your average developer can produce. There's really no big loss of performance here. And most ORM will happily support stored procedures for SELECT, UPDATE, INSERT, and DELETE operations - so that way, in the end, you have total control over your SQL again, if you feel more comfortable that way.
marc_s 13.10.2009 15:55:46

First of all, the ORMs are likely to do a much better job at producing the SQL queries than your normal non-SQL specialized Joe :)

Secondly, ORMs are a great way to somewhat "standardize" your DALs, increasing flexibility over different projects.

And lastly, with a good ORM, you're likely to have an easier time substituting your underlaying data-source, as a good ORM will have many different dialects. Of course, this is just a side-bonus :)

13.10.2009 15:41:41

You don't need to change. If SqlConnection, SqlCommand, etc. work for you then that's great.

They work just peachy fine for the DB app I'm developing, and I have dozens of concurrent users with no problems.

13.10.2009 15:43:02
Not sure your point Meeh. I've come to the conclusion that I can be made to feel guilty about pretty much any new framework or technology that I'm not using yet. So I'm just saying it's all right to use a slightly older framework.
John 13.10.2009 15:53:41
Uh, Joh W, I think Meeh is trying to tell you, that he would love to upvote you, but that would spoil the special number 1337.
Daren Thomas 13.10.2009 15:58:33
John, it's hacker sp33k. I'd love to be stuck on that number. LEET hooah!
Travis Heseman 13.10.2009 16:06:13

One thing to consider: will a future "new developer" be more inclined to know or learn a well documented and widely adopted OR/M or your custom data access layer?

The number one thing for me though is the time. Minutes with my favorite OR/M, nHibernate vs. hours/days writing a custom data access layer using ADO.NET.

I also favor OR/Ms because maintaining declarative XML mappings is way easier than maintaining potentially thousands of lines of imperative code... or worse thousands of lines of C# data access code on top of thousands of lines of stored procedure code. In my current project I have 58 objects mapped in 58 XML mapping files, each with less than 50 lines. I cringe when I think about writing/maintaining CRUD code for 58 entities in ADO.NET.

I must warn you to read the documentation. Many, dare I say most, folks with whom I've worked will jump on a tool like mice on cheese, but they'll never read the documentation and learn the technology. I recommend reading the docs BEFORE moving to a new technology like nHibernate. A good cup o' jo and an hour or two of hard reading before-hand will pay dividends.

13.10.2009 16:02:21
I did take a look at nHibernate and have to say though that to me it looks like that I would spending more time with nHibernate then using directly
vikasde 13.10.2009 15:54:35
I would argue that once you've learned to use nHibernate or iBatis, it'll always be faster than writing ADO.NET. I've written on both sides and even with writing CRUD for single entity, it took me minutes with nHibernate (when I knew exactly what to do) vs a standard hour or longer to write ADO.NET code (when I knew exactly what to do) -- of course, the imperative could be generated with a tool like CodeSmith.
Travis Heseman 13.10.2009 16:00:52

ORM's are great to avoid code repetition. You can often find that your object model and database model are extremely close to each other and whenever you add a field you'll be adding it to the database, your objects, your sql statements as well as everywhere else. If you use an ORM then you change your code in one place and it builds the rest of it for you.

As for performance, this can go either way. You will probably find that a lot of the simple sql that is written for you is often extremely tailored with various shortcuts that you would have been too lazy to write, such as only returning the absolutely required data. On the other hand, if you have some extremely complex queries and joins that an automated system could not possibly build then you're better of keeping these written yourself.

In summary though, they're fantastic for fast builds!

13.10.2009 15:50:28

I haven't find pointing to an general idea of ORM in any answer. The general idea of ORM is to perform an Object-Relational mapping and provide your business classes with persistence. It means that you will think only about you business logic and will let ORM tool to save its state for you. Sure there are a lot of different scenarios. As was already said, it is nothing bad in using pure ADO.NET and may be your application (that is already written in this stile won't get any benefit), but using ORM tool in new projects is a very good idea. As for other - I totally agree with other answers.

29.10.2013 02:45:40

There's nothing wrong with using straight up ADO.Net, but using an ORM will save you time, both in development and maintenance. Thats the biggest benefit.

13.10.2009 15:54:21