4

Our organisation uses inline sql. We have been tasked with providing a suitable data access layer and are weighing up the pro's and cons of which way to go...

  • Datasets
  • ADO.net
  • Linq
  • Entity framework
  • Subsonic
  • Other?

Some tutorials and articles I have been using for reference:

I'm extremely torn, and finding it very difficult to make a decision on which way to go. Our site is a series of 2 internal portals and a public web site. We are using vs2008 sp1 and framework version 3.5.

Please can you give me advise on what factors to consider and any pro's and cons you have faced with your data access layer.

PS. I found the subsonic web site's information a bit shallow, and a lot of emphasis seems to be placed on how 'cool' it and it's developers are. I'd much prefer a spoken explanation to their demo video than the music? Does anyone else agree?

Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
Phil
  • 1,811
  • 9
  • 38
  • 60
  • It really doesn't look like you are "building" rather you are looking for one to use - or are you really planning to build one from the ground up? (Nothing wrong with that, I've done that myself.) – Otávio Décio May 03 '10 at 13:01
  • We are very green on the subject, but do have development resources and budget and are open to coding / using. The main thing we want to avoid is jumping in feet first so we get it right! (I have edited the question to provide more clarity) – Phil May 03 '10 at 13:05
  • 1
    See: http://stackoverflow.com/questions/200279 http://stackoverflow.com/questions/66156 http://stackoverflow.com/questions/1055710 http://stackoverflow.com/questions/2138814 http://stackoverflow.com/questions/168287 http://stackoverflow.com/questions/563775 http://stackoverflow.com/questions/67831 http://stackoverflow.com/questions/82393 http://stackoverflow.com/questions/1691575 http://stackoverflow.com/questions/380620 – George Stocker May 03 '10 at 13:08
  • @Phil: Stack Overflow isn't meant to cover duplicate ground in many different questions. It's designed to have one authoritative source. Yours is a duplicate of the two listed as 'Exact Duplicates' under the close reasons, and there are a multitude that answer your question without being 'exact duplicates'. – George Stocker May 03 '10 at 16:56
  • 1
    I don't agree that "Stack Overflow isn't meant to cover duplicate ground in many different questions. It's designed to have one authoritative source". In reality it's a bit of a blend. If the question is well asked and the OP has researched other posts (looks like you have) then it's OK to have *good* related questions that are similar but not exactly identical. **Bottom line: we're not wikipedia, there isn't one canonical, perfect article on "asphalt" -- there are a myriad of questions about the principles, properties, and usage of "asphalt".** And this is OK. – Jeff Atwood May 04 '10 at 06:59
  • Also, George putting in the links does help, and is not noise, as it shows others related questions they might want to explore. – Jeff Atwood May 04 '10 at 07:00
  • @Phil For what it's worth, you emailed me via my blog twice, but didn't leave an email address for me to talk to you. This is the only avenue I have open to me. 2) Stack Overflow provides for 'linking' the questions if they show up in the comments, so that's why I've linked to them: They all cover the same ground yours does, and yours *exactly duplicates* a few as well; 3) Any *other* comment I've left has been a response to a comment you've since deleted. 4) If you believe I've done something wrong, email the SO team or take your grievance to meta.stackoverflow.com and ask if I'm wrong there. – George Stocker May 04 '10 at 07:00
  • 1
    @george the community is way more strict about duplicates than they should be. Duplicates are bad yes, but good related questions are a net positive even if there is a bit of overlap. A lot of the dupes you list are pretty bad. I need to blog about this. – Jeff Atwood May 04 '10 at 07:07
  • @Jeff Atwood I don't want to get too meta in the course of this question; but don't you see that it's possibly the result of not solidifying answers *in one place*? – George Stocker May 04 '10 at 07:15

4 Answers4

3

I would suggest Entity Framework 4 if you can go with .NET 4.0. It's a good ORM that will only get better as time goes on. You can write your queries, inserts etc. using Linq, which gives you a syntax that is reasonably close to the inline SQL you are using today. Obviously, it has MS behind it so you can expect lots of information and skilled developers to be available now and into the future.

If you have to stay with .NET 3.5, I would look at NHibernate. It is more mature than EF4 overall but lacks full support for Linq right now. Although you will be able to use Linq for relatively simple queries, you will have to go to the somewhat less SQL-like criteria API for more complex queries. I would also use Fluent NHibernate to allow in-code configuration of the mapping. It has strong community support and should have much better Linq support in the very near future. It also has the advantage of being open source.

Either of these ORMs will take care of mapping the objects in your system to the database. This will allow you to focus more of your effort on building the application logic. For that reason, I would not suggest using ADO.Net/Datasets. Linq2Sql is pretty good for what it does but is never going to be as strong as Nhibernate or EF4. Linq2Sql always looked like a first effort from MS and seems to be relegated to playing second fiddle to Entity Framework.

Tom Cabanski
  • 7,828
  • 2
  • 22
  • 25
2

Not quite an easy decision. I look for simplicity and flexibility. For example I don't quite like configuring NHibernate's xml files; I don't like the ton of code Entity Framework spews out; I am concerned about linq-to-sql end of life. I don't mind deriving from a base class (some people want the pure POCO objects) to support the mappings. I also want to be able to switch databases without changing my code, just changing my configuration. Other small things are good, such as: being able to map stored procedure results to objects; selecting subset of columns for performance reasons; being able to send a pure sql statement if necessary.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
  • 1
    Not necessary to write xml mappings on nhibernate :-) http://fluentnhibernate.org/ – Claudio Redi May 03 '10 at 13:20
  • Fluent NHibernate lowers the barrier to entry w/ NHibernate significantly. It however isn't a complete set. You can do most of your simple mappings w/ FNH but some items aren't implemented--mapping stored procedures for example is a huge one that jumps to mind. – bakasan May 04 '10 at 06:12
2

  1. Datasets > too much overhead for what you get. This is generally considered an idea that was past it's prime the day it was released.

  2. ADO.Net (or Enterprise Library) > my personal preference for a host of reasons. However, we never use inline sql for a host of other reasons (mainly security) as well, so your mileage may vary. One reason we do go this route is that our team is very knowledgable about SQL and every one of the generators either falls short on performance or you have to know a fair amount of sql to tweak them. With that choice we opt to just do it ourselves.

  3. Linq > Skip this. New development on LINQ has basically stopped and the resources at MS were moved to the Entity Framework project. I've also found threading and other issues within the framework that haven't been resolved.

  4. Entity Framework > Some people like this. You will still need a good amount of SQL knowledge to get the best out of it.

  5. Subsonic, etc > Don't have enough experience with 3rd party ones to really tell you the pros/cons.

To sum up, we prefer control and have the knowledge to get the most out of it. We've tried the linq / EF route and weren't exactly impressed. I've seen others, and briefly worked with them, but they failed to be performant on anything but the most basic CRUD type statements.

If most of what you are doing is CRUD related, then EF, Subsonic, or similiar will work.

Some things to consider when evaluating:

  • How does it handle ad-hoc queries?
  • How does it handle data paging / sorting (SQL side or does it pull everything into the web server)?
  • What are the memory leaks?
  • Is threading a requirement? Write a simple app to test that in each of the ones you are evaluating. Run it a LOT under different memory / cpu conditions.

Good luck.

NotMe
  • 87,343
  • 27
  • 171
  • 245
1

The question as stated right now is a little ambiguous. A "data layer" can be comprised of many, many, many different needs and desires.

I'll presume that SQL Server is the backing DB. But is it the only one? How homogenous is the data tier?

Are you just looking for a simple way to query and get data out? Or do you have the need for a full bore object relational mapping solution? Do you have needs beyond just querying? Migrations and scaffolding for example?

Are you willing or able to sacrifice time up front to ramp up to learn the more complex (but flexible) toolsets like NHibernate or Subsonic?

Having worked w/ varying degrees of complexity with EF, Linq to SQL, Subsonic, and NHibernate, none are "hard" to get going at first, but finding solutions to more complex problems or edge cases will vary between each. One can argue that EF and NH have the largest body of users and thus a little bit easy to find blogs/Stackoverflow posts to answer your questions. I had better luck with EF/NH in that regard. That said, Subsonic is a more approachable code base to roll up your sleeves and fix your own problems (IMO).

Personally, after having done the run around a few times, my preference now is to always start w/ what I view to be the simplest solution--Linq to SQL. I find its barrier to entry to be the lowest and it's the least "invasive". It however comes w/ notable restrictions (such as a dependence on SQL server). But for quick n' dirty, just get the job done, it's my favorite as I'd rather not spend inordinate amounts of time worrying about my data tier. Only once requirements come in that dictate a shift would I consider moving away--a recent proj for example needed to read/write to SQLite and SQL Server, and thus we went w/ NH.

Understanding what you actually need might help shape answers away from generic commentary.

bakasan
  • 2,262
  • 2
  • 26
  • 33