.NET Questions (CLOSED)

Questions and Answers on any aspect of .NET. Now closed.

This discussion group is now closed.

Have a question about .NET development? Try stackoverflow.com, a worldwide community of great developers asking and answering questions 24 hours a day.

The archives of .NET Questions contain years of Q&A. Even older .NET Questions are still online, too.

DAL Architecture Question

i am using a provider based architecture at the moment. So for each object type there are 3 associated classes:

- A user object (username, password, etc. stores the data)
- A provider object. (this is a static class that contains methods such as Save() and GetByPrimaryKey() etc)
- A DAL provider object. this mimicks the provider object methods. but there will be a copy of this class for each database (oracle, firebird, etc)

so if I go user.Save(); then internally it is saying UserProvider.Save(this) which is then calling the appropriate DAL provider e.g. OracleProvider.Save(this.Id, this.Username, etc)

this is my current architecture. i hope you understand. the problem i am having here is that it requires a DAL class to be created each time. moreover i am confined to making provider methods such as GetByPrimaryKey(int id). but this is inherently inflexible. what if i want to get a user with a primary key over 10 with an age of over 30 who has red hair?

i am considering dumping my current DAL architecture for a more flexible sql broker type deal which handles SQL rather than offers rigid methods.

i was wondering if anybody has a clue what im talking about :) and if im not rambling perhaps somebody has tried a solution that i am thinking of and can offer advice? thanks
Martin T Send private email
Monday, January 14, 2008
Just use NHibernate and throw out your DAL. The initial learning curve is steep but after that you'll never have to worry about building a DAL again. And you get all the query goodness you could ever want built right in. For extra-super-simplicity put a framework like Castle ActiveRecord on top and you'll be done in no time.

Of course, if your DB happens to be shared or inherited and you don't control the schema or the updates, or you're limited to SPs only by DBAs, then it's more of a problem (not that those cannot be solved with a decent OR mapper anyway - it's just a little bit more complicated). But if you do it's a no-brainer.

Honestly, I don't see why people continue to write DALs when this stuff is available for free. It's almost as if people *want* to make more work for themselves. Bog-standard CRUD apps take half as long to write with a decent OR mapper in the frame.

Have a look at NHibernate. Even if you don't use it this time around, I'll bet you'll be glad you did.
.NET Guy Send private email
Monday, January 14, 2008
I agree with .NET Guy.  I have been using CodeSmithTools for a while to generate the DAL and it works out great.  And the code generated was over 10x faster than what people had previously made on our product.  Of course the previous code was created by a non-programmer.
SteveM Send private email
Monday, January 14, 2008
Just be careful with NHibernate if you need to send your entities across the wire.  From what I've seen (we're using NHibernate in our current project, instead of LLBLGen Pro, my preferred O/R Mapper), there are 2 modes of data access with NHibernate:

- Lazy loading - fine if you don't need to lock down what the consumer of the object can access.

- Everything...loads all associated entities (within a certain threshold/depth)

While both options are OK for simple apps, they're in no way adequate for large distributed systems.

Using a home-grown O/R mapper (ugh...too much time/work) or something like LLBLGen Pro (which allows prefetch paths) makes way more sense.

I'd be happy to be proven wrong about NHibernate, but I've not found any way to bring back only certain parts back of an object graph.
Giorgio Galante Send private email
Monday, January 14, 2008
Another "Woot" for llblgen.  It simply rocks.
Nathan Edminster Send private email
Monday, January 14, 2008
Hello. Thanks for your replies. I am currently using CodeSmith to autogenerate the DAL and BL for entities. So the hard work aspect really isn't a problem, because I can generate these classes automatically.

What is a problem for me is the inflexibility of the provider model, in that I am restricted to creating separate methods for each of my SELECT statements, etc.

the problem i have got is that if i want to e.g. select a User from the database, then i can not always rely on the different databases having exactly the same structure e.g. table names and column names could be different.

my idea was to create a broker layer where i could make a generic request e.g. 'SELECT User WHERE Id = 1'. this broker layer would then translate the SQL according to the required database.

I wondered if anybody ever tried anything like that?

I know about O/R mappers, and I thank you for your suggestions, I will investigate more. But I also like to think through these architectures myself because it's part of my job. thanks.
Martin T Send private email
Monday, January 14, 2008
Have you tried LINQ and a DataContext in .NET 3.5?  It generates SQL for you, and it's much closer to an O/R mapper than typed DataSets are.
Kyralessa Send private email
Monday, January 14, 2008
hi. unfortunately cannot use .net 3.5 due to required backward compatibilities for client base.

have downloaded llblgen pro trial and am getting stuck into that. impressed so far. will let you know how i get on.

Tuesday, January 15, 2008
Feel free to ask questions about LLBLGen (or NHibernate) there's a bit of a learning curve associated with them (or any O/R mapper for that matter.)  It's much easier when you have help from someone who has experience with the tool...
Giorgio Galante Send private email
Tuesday, January 15, 2008
Ah - I didn't realise that your problem was that the code would have to cope with multiple database schemas. That's rather harder :-)

If you can manage a recompile for each implementation, something like LLBLGen Pro would be good. If you can't, you're going to end up writing some kind of mapping generator. You *can* do this with NHibernate, as an example, in that you can generate mappings at run-time (ActiveRecord does this based on reflection, for example). It would be complex, but you might be able to make it work.

No easy answer if you can't rely on the schema - any way you look at it it's going to be tricky. Can you be a little more specific about *how* the schema varies? Why does one piece of code have to connect to multiple database schemas? More context might help us find a better solution.
.NET Guy Send private email
Tuesday, January 15, 2008
Recompiling isn't that much of an issue though - hopefully nobody is messing around with your database *that much* behind the scenes ;)

Another +1 for ORM tools. I've used LLBLGen, Diamond Binding (awesome!), Subsonic (free!) and vanilla NHibernate and all of them deliver massive performance and agility benefits over hand rolling your own DAL (or using a code-generated one).

As someone mentioned be careful with prefetching. Your ORM isnt broken when it takes ages to get that first record back - just play around with the prefetch paths (llblgen) or tick on lazy-load in a couple of places (DB).
Mike Send private email
Friday, January 25, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz