What is the best approach to make DAL?

Question

I want to make a perfect custom DAL (data abstraction layer) class to use with all my projects.

I've searched the internet and found some samples for this but I never know which is the best approach.

Is it to make [Attributes]? Or use <Generics> or something else?

So please just give me a head line and I'll go on from there.

Thanks again and forgive my language.

Answer 1

I can recommend you to read this article first. And take a look at EnterPrise Library's Data Access Application Block.

Answer 2

Best approach is:

Don't do it yourself unless its for an academic research project or you intend to build a business shipping ORMs.

Try out the dozens of existing ORM solutions first. (Entity framework, subsonic, nhibernate etc etc...). They all have their quirks and limitations mixed in with tons of awesomeness.

ORMs are incredibly hard to get right and a huge undertaking.

Slightly related and on the money: http://blog.wekeroad.com/blog/youre-not-your-data-access/

Answer 3

If you are a starter I would recommend use of SubSonic (more so if you are on web development).

Answer 4

as also one mentioned, don't try to implement a ORM tool yourself, there are a lot of them freely available. But a DAL isn't a ORM tool, the ORM tool will be used within your DAL. The DAL is just for hiding the data access logic from the rest of your app in order to have a more maintainable solution. In the end you could also have normal SQL statements i. your DAO class. What you should pay attention at when creating your DAL is to decouple it as much as possible from the rest of the app/other layers. This can be achieved by coding against interfaces and by using dependency injection. Spring is a great help here (given you program in Java). Beside that, there is no big magic on building such a layer.

Answer 5

Definitely don't write your own persistence manager. You should use an Object-Relational Mapper (ORM) if you want to start from a class structure and have the ORM generate the SQL table structures for you, or use an SQL Mapper if you want to start from SQL tables and want to have your classes represent table rows.

I've had great experience using the iBatis SQL Mapper, and a lot of people like Hibernate for an ORM (though there's a learning curve).

Martin Fowler describes several good approaches for writing data access layers in Patterns of Enterprise Application Architecture (here's a catalog).

For instance, iBatis for .NET uses Fowler's Table Data Gateway pattern. In iBatis you specify Table Data Gateway objects in XML. Each Gateway typically governs access to one SQL table, although you can do multi-table operations too. A Gateway is composed of SQL statements, each wrapped in a bit of XML. Each SELECT returns one or more row objects, which are just sets of attributes plus getter and setter methods (in .NET these are called POCOs or PONOs, Plain Old C# Objects or Plain Old .NET Objects.). Each INSERT or UPDATE takes a POCO as its input. This seemed pretty intuitive, and not too hard to learn.

Answer 6

Trying to create the ulimate, best, perfect DAL seems a bit crazy - there are so many different application scenarios with different and competing requirements and needs that I don't believe anyone can come up with THE ONE ultimate DAL.

You need to check out some of the existing ORM tools, get to know one or two of them, know their strengths and possibly drawbacks, and then be able to pick the best one for every given situation. I doubt it'll always be the same.....

SubSonic is great for smaller, nimbler projects - as is Linq-to-SQL, as long as you use SQL Server as your backend. If you need more enterprise power, you should look at NHibernate, ADO.NET Entity Framework, or other bigger, more capable players (which are just too complex and ill suited for a small, simple scenario).

I don't think there's THE perfect way to create a DAL - learn what's available, learn how to choose the one best suited to your current need, and don't reinvent yourself - use what's available out there!

Marc

Answer 7

Please read Data Access Layer Design Considerations

Answer 8

Just make sure you:

• Always use stored procedures
• Never use stored procedures
• Sometimes use stored procedures
• Use nHibernate
• Use SubSonic
• Use Entity Framework
• Write your own
• Never write you own
• Use POCO
• Use ActiveRecord
• Use IRepository
• Always do what Fowler says
• Never do what Fowler says
• Don't use Linq to SQL, it's dead
• Use Linq to SQL, it's no longer dead

Do all that and you will be fine.

Answer 9

Linq to SQL is the best solution or you can try da easiest solution http://fluentado.codeplex.com/