What is the best approach when creating data access objects?

Question

I have a method in MyWebpage.aspx.cs lik so:

public partial class MyWebpage : PageBase
{
    private readonly DataAccessLayer dataAccessLayer;

    protected string GetMyTitle(string myVar, string myId)
    {
        if (string.IsNullOrEmpty(myVar))
        {
            return string.Empty;
        }

        return dataAccessLayer.GetMyTitle(Convert.ToInt32(myId), myVar);
    }
}

In the DataAccessLayer class, I have a methoud that talks to the DB and does the DAL stuff and returns the title.

What's the best practice on accessing the DAL from MyWebPage.aspx.cs class (as in do I need to create a new DataAccessLayer() object each time? Where should I create it in my PageBase class or everytime I call it in a code behind?

Side

Answer 1

First thing is accessing DAL from your code behind or presentation layer generally is not a good practice. Because in this case you need to put your Business logic code in your code behind(Presentation Layer) which causes conflicting of concerns, high coupling, duplication and many other issues. So, if you're look for the best practices, I suggest to have a look at the following links:

• Data Layer Guidelines
• Layered Application Guidelines
• Object-Relational Metadata Mapping Patterns (especially Repository Pattern) and Domain Logic Patterns

And these are really good books:

• Patterns of enterprise application architecture (By Martin Fowler)
• Microsoft® .NET: Architecting Applications for the Enterprise

Also about having static function for calling the DAL. As you know static functions are vulnerable to the multi-threading, so if you are using anything shared in the DAL functions (which its the case sometimes, like shared connection, command etc) it will break your code, so I think it's better to avoid static functions in this layer.

Mutter

Answer 2

I'm a fan of the repository pattern. Everybody has their own take on it but I like the idea of one sql table => one repository and share the name, just like ORM tools.

Entity Framework might make quick work of your DAL and you can still implement a DAL pattern like repositories.

Here is a code generator that takes a sql connection string and gives a fairly standard implementation of the Enterprise Data Access Application Block. It is not very robust as it was designed against a bland sql schema. If you use the example database have it will give you code samples that you can use to design a data access layer to your liking.

Beanpole

One table per repository - but what happens if you change your database structure? – Wobble

What is your pattern suggestion that doesn't need to be changed when the database changes? – Beanpole

Use something like Entity Framework to do your modeling at the conceptual level, with a mapping layer to map to the physical database structure. That way, the business layer doesn't need to know whether a particular entity is implemented in one table, or three. – Wobble

Valid suggestion. However, if the database changes you still have to change at least the mapping layer so the answer to what to do when the DB changes is still, update C# (orEF model). I'm a fan of db tables being a decent representation of a business obj but there are always exceptions and good reasons to diverge. – Beanpole

Yes it does require code changes. Entities would break if you took a column away without updating the mappings. I consider that a code change even though it happens inside a wizard. :) – Beanpole

It happens in a wizard, or in XML, but the changes in the mapping metadata are all that you would have to change. No need to change C# code. – Wobble