DB Access Architecture in Application Code

I have been working on a large project that makes pretty heavy use of a relational DB. The project is in C# and does not use an ORM. I find the application difficult to work with because of the way it accesses the DB in the application code, but I don't have enough experience with large projects to say how it could be better (not that I think it is a good idea to change massive amounts of legacy code, but I want to know how to do it better for next project). I do not care if your answer has anything to do with C# or using an ORM or not, I just want to read various approaches taken to solve this problem.

Here is an outline of the way this project works:

• There are quite a few tables, views, and a small handful of stored procedures.
• There is a data access layer in application code that handles the raw access to the DB (this layer is a bunch of functions like would look like GetUserById(id), GetUserByLastName(lastName), AddUser(firstname, lastName), GetCommentsByDateAndPostId(date, postId), GetCommentsByDateAndPostIdSortedByDate(date, postId), etc...). All of these functions call a hand written SQL query and return basically an in memory representation of a table (i.e. results[0].lastName is column lastName of row 0). In C# this is a DataTable.
• There is a layer above the data access layer for business processing rules. It is a wrapper for each of the data access functions, but may make a couple of business logic checks 开发者_如何学Gobefore calling down to the corresponding (i.e. same-named) data access function. It returns the same thing as the data access layer in all cases. Application code ONLY accesses the DB through this layer, never the data access layer directly.
• There are no one-off queries in the wild. There is a one-to-one correspondence between queries and functions in the data access layer (and thus the business logic layer). Because the database is normalized, there is a view for most queries because joins are necessary.
• There is the very rarely used stored procedure here and there

So If I want to access the DB in a new way today, I have to modify the DB, then create a data access layer function that calls a custom written SQL query to the database, then create a business logic function that calls the data access layer function. And then possibly modify a bunch of existing functions to include this change or not. I don't even know where to begin automated testing in such a volatile environment.

And that is just if I want to modify or add a single column of a DB. If I want to add a new table there are a bunch of new functions to add for all the ways it can be selected (combinations of WHERE clauses), or inserted into, or updated by, or deleted by, or sorted by, etc.

What you describe is not a problem per se. It is in fact a good example of application design and pattern usage. What it lacks that makes it seem problematic, is that it doesn't leverage newer technologies / techniques which aid in maintainability.

For example, from your description, it's apparent the architecture clearly separates functional responsibilities into layers. You have a Presentation (UI) which communicates with a Domain (BLL) which in turn uses a Repository pattern to communicate with its Infrastructure (DAL). Your BLL seems to already implement cross-cutting concerns such as Validation and Security.

What you can do to improve upon this design would be to incorporate a stronger Domain by including a Model. Drop the old ADO.NET DataTable techniques and design a strongly-typed model which reflects your database. Incorporating an ORM can aid in this tremendously as it has the power to generate the model from the database and maintain changes easily.

I won't get into further ORM advantages as you desire. Your DAL should return POCOs and Enumerables. Let your BLL return response objects (I like to call them service response objects or presentation transfer objects) which may contain things like: POCO data, Error Handling Results, Validation Results.

Another possible solution is to change your implementation of Repository pattern to a Generic Repository, though this now bleeds your infrastructure logic into the BLL. For instance, instead of:

public class UserRepository
{
    public User GetUserById(Int32 userId){...}
}

You can create (using generics) a repository which implements IQueryable. Look at nCommon for a nice approach to this. This will allow you to do something like:

var userRepository = new EF4Repository<User>(OrmContextFactory.CreateContext(...));
User u = userRepository.Where(user => user.Id == 1).SingleOrDefault();

The pros to this is that you only need to create domain business logic. If you need to modify your database tables, you only need to change your business logic once. However, that query now exists in the business logic and simply uses a "Repository" as a medium to communicate to your database which some deem improper.

UPDATE

You can use generics for creating a simple response object. Example:

[DataContract(Name = "ServiceResponseOf{0}")]
public class ServiceResponse<TDto> : ResponseTransferObjectBase<TDto> where TDto : IDto
{
    #region Constructors

    /// <summary>
    /// Initializes a new instance of the <see cref="ServiceResponse&lt;TDto&gt;"/> class.
    /// </summary>
    /// <param name="error">The error.</param>
    /// <remarks></remarks>
    public ServiceResponse(ServiceErrorBase error)
        : this(ResponseStatus.Failure, null, new List<ServiceErrorBase> {error}, null)
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="ServiceResponse&lt;TDto&gt;"/> class.
    /// </summary>
    /// <param name="errors">The errors.</param>
    /// <remarks></remarks>
    public ServiceResponse(IEnumerable<ServiceErrorBase> errors)
        : this(ResponseStatus.Failure, null, errors, null)
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="ServiceResponse&lt;TDto&gt;"/> class with a status of <see cref="ResponseStatus.Failure"/>.
    /// </summary>
    /// <param name="validationResults">The validation results.</param>
    public ServiceResponse(MSValidation.ValidationResults validationResults)
        : this(ResponseStatus.Failure, null, null, validationResults)
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="ServiceResponse&lt;TDto&gt;"/> class with a status of <see cref="ResponseStatus.Success"/>.
    /// </summary>
    /// <param name="data">The response data.</param>
    public ServiceResponse(TDto data)
        : this(ResponseStatus.Success, new List<TDto> { data }, null, null)
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="ServiceResponse&lt;TDto&gt;"/> class with a status of <see cref="ResponseStatus.Success"/>.
    /// </summary>
    /// <param name="data">The response data.</param>
    public ServiceResponse(IEnumerable<TDto> data)
        : this(ResponseStatus.Success, data, null, null)
    {
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="ServiceResponse&lt;TDto&gt;"/> class.
    /// </summary>
    /// <param name="responseStatus">The response status.</param>
    /// <param name="data">The data.</param>
    /// <param name="errors">The errors.</param>
    /// <param name="validationResults">The validation results.</param>
    /// <remarks></remarks>
    private ServiceResponse(ResponseStatus responseStatus, IEnumerable<TDto> data, IEnumerable<ServiceErrorBase> errors, MSValidation.ValidationResults validationResults)
    {
        Status = responseStatus;
        Data = (data != null) ? new List<TDto>(data) : new List<TDto>();

        Errors = Mapper.Map<IEnumerable<ServiceErrorBase>, List<ServiceError>>(errors) ??
                 new List<ServiceError>();

        ValidationResults = 
            Mapper.Map<MSValidation.ValidationResults, List<IValidationResult>>(validationResults) ??
            new List<IValidationResult>();
    }

    #endregion

    #region Properties

    /// <summary>
    /// Gets the <see cref="IDto"/> data.
    /// </summary>
    [DataMember(Order = 0)]
    public List<TDto> Data { get; private set; }

    [DataMember(Order = 1)]
    public List<ServiceError> Errors { get; private set; }

    /// <summary>
    /// Gets the <see cref="ValidationResults"/> validation results.
    /// </summary>
    [DataMember(Order = 2)]
    public List<IValidationResult> ValidationResults { get; private set; }

    /// <summary>
    /// Gets the <see cref="ResponseStatus"/> indicating whether the request failed or succeeded.
    /// </summary>
    [DataMember(Order = 3)]
    public ResponseStatus Status { get; private set; }

    #endregion
}

This class is a basic response object which I use to return results from my domain to my service layer or to my presentation. It can be serialized and supports MS Enterprise Library Validation Block. To support validation, it uses AutoMapper to translate Microsoft's validation results to my own ValidationResult object. I don't recommend trying to serialize MS's classes as it proved error prone when using in services.

The overloaded constructors allow you to provide a single poco or enumerable of pocos. POCOs vs DataTables ... anytime you can use strongly typed objects, it's always better. With T4 templating your POCO can automatically be generated from an ORM model. POCOs can also be easily mapped into DTOs for service operations and vice-versa. There's also no real need for DataTables anymore. Instead of List, you can use BindingList for CRUD support with databinding.

Returning a POCO without all its properties filled is perfectly fine. In Entity Framework this is referred to as projection. Usually I'll create custom DTOs for this instead of my domain entities.

UPDATE

Example ValidationResult class:

/// <summary>
/// Represents results returned from Microsoft Enterprise Library Validation. See <see cref="MSValidation.ValidationResult"/>.
/// </summary>
[DataContract]
public sealed class ValidationResult : IValidationResult
{
    [DataMember(Order = 0)]
    public String Key { get; private set; }

    [DataMember(Order = 1)]
    public String Message { get; private set; }

    [DataMember(Order = 3)]
    public List<IValidationResult> NestedValidationResults { get; private set; }

    [DataMember(Order = 2)]
    public Type TargetType { get; private set; }

    public ValidationResult(String key, String message, Type targetType, List<ValidationResult> nestedValidationResults)
    {
        Key = key;
        Message = message;
        NestedValidationResults = new List<IValidationResult>(nestedValidationResults);
        TargetType = targetType;
    }
}

Example AutoMapper code for translating Microsoft Validation Results to a ValidationResult DTO:

Mapper.CreateMap<MSValidation.ValidationResult, IValidationResult>().ConstructUsing(
            dest =>
            new ValidationResult(
                dest.Key,
                dest.Message,
                dest.Target.GetType(),
                dest.NestedValidationResults.Select(mappingManager.Map<MSValidation.ValidationResult, ValidationResult>).ToList()));

I would recommend using a Facade pattern to encapsulate all the data access calls within a single object. Then refactor every existing data access call into a call to the facade object.

I wrote a more in depth explanation of implementing the facade pattern in response to another question at Best approach to Architect the integration of two separate databases? .