We help IT Professionals succeed at work.

Best way to build a DAL?

wooderz
wooderz asked
on
2,948 Views
Last Modified: 2013-12-17
Whats the best way to build a DAL? In my latest projects I used the Strongly Typed Dataset, using the designer in Visual Studio, but that's difficult to get transactions on, without using TransactionScope which is resource intensive? Is it best to build your own objects and simulate the functionality?

Secondly, can one transaction be attached to two db connections? I have a transaction that spans databases.
Comment
Watch Question

Top Expert 2009

Commented:
If you have this kind of enterprise level requirements for a DAL then I recommend using NHibernate, which is build (and has been used) for the most demanding of db challenges.

It has a steep learning curve, but to get you quickly up and running I have found it easiest to teach people by first automating the process (several tools exist for this purpose) and only later dive into the details of NHibernate.

But since your requirements seem so high, the enterprise example (which can seamlessly be used with multiple databases) and the best practices explained in this celebrated article are a good start (assuming you know about DAL and DAO terminology it isn't even such a terse read): http://www.codeproject.com/KB/architecture/NHibernateBestPractices.aspx

In conjunction with generics (a MUST when you do DAL), you can achieve a very transparent yet very customizable and programmable layer.

I have researched other DAL tools but I always end up working with NHibernate. It has its shortcomings, but it's use of reflection and code extension make that you can use your entities and dao's right away without the need for building and linking: it is all done in runtime based on XML files containing the definitions.
Top Expert 2009

Commented:
Oh, and I forgot: there's excellent documentation available. More on the Java part then the .NET part, but the APIs are the same anyway and the differences are trivial. If you want to go that path I can give you some pointers on where to start and what to put on your bookshelf...

Commented:
I wrote a code generator to build not just the DAL but also the BLL. If you would like a copy to play with email me nick@patchwork.co.uk - i'm not looking for anything for it, just be nice to know it's being used.

I've now used it on loads of projects including a commercial web site within our organisation.

As far as transactions are concerned, i've not had the need for these yet, but have looked into it, I might need to do some work on that area.

The tool is very simplistic, it has a design tool for desiging each table. You can then design SQL statement for qurying the data. That's about it really, the program then spits out a .VB file with the DAL and BLL code in it including a CreateTable function which is brilliant for deploying to a new remote web server. Note this only works with Microsoft SQL 2000 and 2005 databases at the moment.
You can use MyGeneration code generator to do that. It automatically creates DAL and BLL for you based on your database schema: http://www.mygenerationsoftware.com/portal/default.aspx

An alternative way is to create your own DAL and BLL with Strong Typed Dataset. Scott Mitchell wrote some excellent tutorials for this: http://www.asp.net/learn/data-access/

Commented:
Good advice on the tutorials. My code gereator was writen with the help of the code examples from that site.
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2009

Commented:
That's the way it will end up looking like in the code. The generators will make sure the right mapping files are created (i.e., where it says that "BirthDate" is of type DateTime, and NHibernate will automatically do the necessary conversion to and from the DB Server datatypes). NHibernate will notice when an object is new and when it is updated and will act accordingly. It will work with server-generated Ids seamlessly.

And, most importantly, it will map relationships onto IEnumerable and ICollection and IList generic types (one to one, one to many, many to many). These relationships will make it even easier to work with the objects, as you can loop, select, add, query etc these lists.

Ok, I admit, I am a bit of an NHibernate addict by now... but that's because -- apart from some annoying newbie misunderstandings of vague errors -- it just never failed me.
There are many O/R mapping tools out there, some are free and some are not, some are good and some are not so good. The reason I suggest MyGeneration is our developers are using it and have no problem with it, and it surely is the same reason you suggest NHibernate. I personally prefer to create my own DAL and BLL with Strong Typed Dataset, the reasons are simple: 1. It is part of Visual Studio 2005, so I don't have to rely on any thrid-party tool; 2. Maintanence. if a developer who used a code generator and left, whoever takes over the developer's project(s) will have to first learn how to use the code generator in order to maintain the project(s). 3. Support. What if there is a bug in the code generator tool? Where can I get the support and the bug fix?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.