Based on what I've been reading, it is my understanding that TBH uses the "traditional" CRUD approach to the DAL, using stored procedures. I've just started to look at Commerce Starter Kit (CSK) and following this, the SubSonic O/R mapper which the CSK uses. This got me contemplating on the never-ending debate on whats better: stored procs or O/R mapping. I'd love to hear what other people here think about this!

__________________
Alex

- TheBeerHouse Mods Repository
http://www.sashka.com/TheBeerHouse/thebeerhouse.html

They're quite different, but one isn't always better than the other. The key is to know how to correctly apply each of these.

nTier is the traditional model that ideally doesn't allow any business knowledge in stored procs, and it doesn't even allow joins. The DAL has to be a light wrapper around each table, with strict CRUD-only features. However, the "modified pragmatic" version of nTier does allow joins and some strong SPs that have business knowledge. This is used by TBH.

ORM starts out with the same restriction: absolutely only CRUD-oriented SQL is allowed within the DB, but the ORM tool is allowed to generate SQL containing joins. ORM tools may generate code at designtime, or runtime, depending on the tool in question. Some of the high-end ORM tools can support more functional SPs, even though some people claim that shouldn't be allowed. I like strong SPs in some cases, and I don't care if it pisses off the college professors.

I like them both: nTier and ORM, but ORM has a certain allure. MS is also moving towards ORM in their LINQ technology, but the ink isn't dry on that yet. LLblGenPro is the best high-end commercial ORM tool, Paul Wilson's ORMapper is a good low-end tool. nHibernate is the best open source option.

The single biggest weakness of ORM isn't really a weakness of ORM itself. Companies like to select ORM because of the promise of simplifying the development effort, and speeding the timeline. This is fine. But they select one strong senior developer to set up the tool and all the maps, and that becomes their single point-of-failure. The few senior developers who can do a good job setting this up are the same people who could change jobs instantly, and likely get a higher paycheck. Or, they'll pick a high paid consultant to come in and set it up, and they plan on retaining him for 3 months. In the end, the senior guy who made it all work like clockwork may leave the company. And nobody else will understand what he did. Their only options will be to hire a super-high-end guy, like a Wintellect person, to come in and quickly fix the issues or fully document what they have. This is going to cost dearly, and it will more than wipe out any savings they enjoyed.

At this early stage in the ORM world, the only right way to do it is to have a tag team of 2 senior people to set it up and document it along the way. Don't count on saving money at first because those 2 guys will cost you a fair bit. Your savings come later in the GUI development when your junior-to-mid programmers become far more productive. But you should always have 2 senior people who fully understand the ORM tool and the maps, unless you have a farily small system (I would say TBH is small in this context).

Because of the interest in this subject, I will be writing more about it over time. For now I have a simple paper:
http://www.EricEngler.com/downloads/ORM.doc

And I'll be speaking about these issues, and how it relates to TBH at the next AtlantaDotNet User Group meeting:

http://www.AtlantaDotNet.org

I'll post the slides here after the meeting:

http://www.EricEngler.com/Presentations.aspx

Eric,

Good article. I dug a little and found this page which lists 31 different ORM tools specifically for .NET. The discussion turns up a couple more.

http://www.theserverside.net/news/th...d=29914#145704

What to do?

My instinct here is to go with the most popular cheap or free tool. Given that you warn about the difficulties in changing tools, the stakes are raised even higher.

BTW impedence mismatchs are crucial to opera.

Eric,

Thank you for your in-depth reply. I have heard good things about CodeSmith, that it allows you to generate stored procs. I've also seen a discussion by Wilson on the merits of his mapper, here:

http://weblogs.asp.net/pwilson/archi...18/231188.aspx

Microsoft is working on an ORM solution for LINQ. If you can wait a while longer that would be a good direction. Even Paul Wilson said he liked Microsoft's approach, and he's an expert on ORM.

If I had to do it now, I'd want something that can scale well and handle anything I might need it to do. LLBLGen Pro fits the bill, or else the free nHibernate, but both are hard to learn. Or, for a small system I'd use Paul's ORMapper just to get my feet wet and learn the basics.

CodeSmith is a code generator that can use custom templates. You can define the templates, and they look a little like an .aspx page - very cool stuff. But I've written my own code generators for the most part.

You want to see something cool? Download the free Olymars tool and run it against an existing DB - you get an instant DAL and stored procedures for CRUD operations. This is extremely easy to use and it's worth a couple hours of your time to investigate. This is the overall fastest way to get a new project started: just design the tables and run this on it. It can save days of work and it lets you focus on the more advanced stuff beyond simple CRUD. This was designed by a Microsoft employee in France. This only makes SPs and a DAL, so it's not an ORM tool.

You can see my SQL editor addin (which auto-generates C# command and parameter objects) and presentation here:
http://www.EricEngler.com/Presentations.aspx
Wrox will post the video of this presentation soon. This is simpler than a DAL, but a very easy way to generate wrapper code for SQL and stored procs. Whenever you need to execute a SQL statement in your code, you just right-click in the VS code editor and select the SQL Editor addin. Then create a named query, and enter your SQL with parameters, like:

  Select Name, HireDate, Salary
  from Employee
  where EmpID = @EmpID

Then tell it to scan your SQL and it finds the parameter(s). Then assign a type to the parameter(s) and press Save. It creates a C# command object, and all the parameter objects for you. And it's a 2-way tool, so you can edit what you entered.

Eric

Thanks Eric, I will check out Olymars.

I've been looking at the code for Commerce Starter Kit (CSK). It uses SubSonic for o/r mapping, which is based on Ruby on Rails. Now trying to decide whether to continue to extend CSK or TBH ? I think I like the way TBH is organized better than CSK, to me it seems the BLL and the DAL are not as clean. I see IDataReaders all over the place in the BLL, and I see some business logic in the DAL. Has anyone else looked at both the CSK and TBH ?

Eric, which tools do you think would fit best to continue building upon the existing code of TBH to keep the architecture, yet making things a little quicker to write the code?

Be careful about complaining about impure separation of tiers. If you want a college professor level of pure separation it'll perform like a dog. You wouldn't even be allowed to have a single join in any SQL statement because that implies business awareness, and that's only legal in the BLL, and definitely not in SQL. SQL is only allowed simple CRUD, and only on one table at a time. I like to hammer on the purists because they don't understand the real world.

A pragmatic nTier design does break the rules, but it leverages the DB for a lot more than mere persistance. I used to hate passing around DataReaders until I saw the performance metrics of doing that vs custom collections or DataSets. The DataReaders beat all the other possibilities. Custom collections are elegant, and almost as performant as DataReaders. We don't even want to discuss DataSets - yuck (although you can use 2.0 DataTables as a lighter alternative). Just make sure the readers get closed even if exceptions occur, and have a mechanism for multiple connections to handle updates and other operations while the DataReader is open.

ORM is a way to cheat without the appearance of cheating. There is absolutely no separation within the ORM cloud, so the DAL and BLL are effectively one piece. That's not a bad idea because you want performance to be high. And the SQL generated by a good ORM tool can be quite advanced, and would seriously piss off the nTier purists.

But I'm not ready to marry a specific ORM tool yet. I'm still dating and fooling around :-)

I spend most of my time learning AJAX now. After using it, you simply can't go back! The constant screen-clearing required after postbacks in TBH are quite annoying. AJAX is our salvation. But it's got a learning curve. The difficulty for me is that I have to buckle down and learn JavaScript and DHTML. I personally hate this style of coding but the end result is sweet.

Eric

jgd12345 on the asp.net forums made a code generator for TBH-style nTier, unfortunately seems like no one has gotten it to work yet :)

keep watch on this thread:
http://forums.asp.net/thread/1468610.aspx

I am hoping someone with CodeSmith experience will create templates for TBH.

If your code generator takes more time and effort than would be required by hand-writing the code, you may want to think about this approach. It's pretty easy to write your own code generator.

Eric

I agree, I am thinking this will be the best approach, or to use CodeSmith to create my own templates, it looks easy to use. I've looked at the code generated by nettiers, it looks too cumbersome, although it claims to use the best practices of the Enterprise Library.

How would you compare the code in TBH, do you think it is at a level where it can be run live, or does it need a lot more additions to be production-grade?