Future Proof DALs

Question

We are in the beginning of a really long development project with several sub projects. Basically each sub-project will take several months to develop. The code itself will be split up into several C# projects, but the physical database will be shared by all projects.

The problem is maintainability. If we add a column to a table, or split a table into two smaller tables, we'll have to go back and modify our C# DAL to support these changes. This is not acceptable, as we'll constantly be adapting the DB conform to the needs of the company as a whole, and not just the needs of a single program. Constantly changing old code would be a neverending task.

Our DB people suggested a different view. We do all our CRUD through stored procedures, and use Linq across several tables to perform our SELECT statements. Then if we restructure the DB several years from now, we can simply supply the same stored procs and views and not have to modify our older code.

The question we have, is what ORM should be used for something like this? EF seems a bit overkill (maybe it's not). Would something like SubSonic with it's T4 templating allow for a simpiler (and perhaps faster) DAL?

Or perhaps someone has an idea on how to make this entire process less painful? We'd rather not add another layer to our application, but neither do we want to go back and modify code everytime we make a db change.

Edit 1: So when I said "I don't really want to add more layers". This is mostly because we already have several layers. We have Silverlight views, view-models, BLL objects (via CSLA) then we have the DAL, and finally the SQL tables themseleves.

Answer 1

C# DAL... not just the needs of a single program. The whole point of a C# DAL as a separate assembly is that it can be reused across any type of .NET application. The main problem you'll encounter is that if the database changes, then the DAL must change (once), then all applications that depend on the DAL must be re-deployed with the new DAL. You also have the problem that the DAL can't be used by non-.NET applications.

Ok, so how can you centralize the DAL so that you don't have to re-deploy it for every application? Think SOA. You can build a WCF service to contain the DAL (and probably BLL). All your applications (if you use web services, even those that are not .NET) can use this service. When the database changes, you update your WCF service and deploy once. Just make sure you don't make any breaking changes! Create a MyMethod2 if you need to add/change functionality.

Note: When you hear n-tier, it usually refers to three-tier, where each tier is separate software and usually on separate servers: presentation (UI), application (your BLL/DAL), data (your SQL database). There is merit to this architecture.

We'd rather not add another layer to our application. Ok, so three-tier may not be the best approach in your case.

neither do we want to go back and modify code everytime we make a db change Then what your DBA people suggested is the only way.

However, consider this: is changing a stored procedure the same as modifying code? It basically is the same thing. SQL stored procedures are often not version-controlled or tested, but they should be. SQL doesn't have the richness of a language like .NET. WCF can easily be scaled in a web farm. Once you factor these and other reasons, it may be worth going the three-tier/SOA approach.

It really depends on the size of your project, skills of staff, future growth, etc. and that is something only you can determine.

Answer 2

I have started to use BLToolkit based on the performance info from http://ormeter.net/

You can define your model in simple class files, add some methods with attributes applied and presto you have a DAL. Split a table in two and you can maintain the original class file while creating the new ones to support the split tables. Just make sure you create a testing project that hits every method to make sure they all work with each release

Class

    public class DirectoryListing
    {
        [PrimaryKey, Identity]
        public Int64 Id { get; set; }
        public Int64? OldId { get; set; }
        public Int32 CategoryId { get; set; }
        [Nullable]
        public String CompanyName { get; set; }
}

General select or table valued function:

[SqlQuery("SELECT * FROM Ajax_CategorySearch(@SearchString, @ResultCount)")]
[Cache(MaxCacheTime = 10, IsWeak = false)]
public abstract List<String> AjaxCategorySearch(String @SearchString, Int32 @ResultCount = 10);

Or to use a stored proc:

[ActionName("SelectById")]
public abstract Model.DirectoryListing SelectById(Int64 @Id);

This will call the SP DirectoryListing_SelectById

Oh, and doing things in a more classic way is easy too

    using (BIFDbManager db = new BIFDbManager())
    {
        var output = db.SetCommand(
            "SQL GOES HERE",
            db.Parameter("@Id", 1))
            .ExecuteList<DAL.Model.DirectoryListing>();

        totalrecords = output.Count();

        return output;
    }

The last piece of the puzzle is the db manager, which also enables LINQ support.

public class BIFDbManager : DbManager
{
    public BIFDbManager() : base("Connection string name") { }

    public Table<DirectoryListing> DirectoryListings { get { return GetTable<DirectoryListing>(); } }
}