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.

Overblouse

So, by trying to have one Schema to serve them all, you'll basically arrive to the model that sucks the least. I always wonder how people can expect to change the DB without touching Apps that access it. This seems surreal. – Alexisaley

Integration through the database went out of fashion in the late 70s. Unless you're using an OODB like Gemstone. – Cinderella

Am I the only one who thinks this question will get lots of views and lots of answers but no really useful ones? – Afterclap

@Alexisaley - Imagine a payroll program. Let's say that the payroll system really only cares about the salary of a individual, and the number of hours they worked. Later on, we add a billing system that bills out to clients based on the bill rate of the employee. That bill rate has nothing to do with payroll, so why should I have to go back and edit code that may be years old, simply to add a field required by the reporting program. – Overblouse

@Timothy: "so why should I have to go back and edit code that may be years old, simply to add a field required by the reporting program" Let me strongly suggest you stay far far far far away from any "ORM" that is constrained to generating one class per table. This includes Linq2Sql and SubSonic. – Pigling

why don't you create a service (e.g WCF/OData, etc) that also acts as a DAL and lives on it's own server. This service talks to the database using <insert technology here>. then all projects talk to the WCF service. if the db changes, only the WCF service needs to - the service contract stays the same. – Ddene

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.

Chirr

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>(); } }
}

Branham

So you're suggesting I create my DAL objects by hand? – Overblouse

Well i do just so i can keep control, but you don't have to. bltoolkit.net/Doc.T4Templates.ashx?HL=t4 – Branham

Answer 3

How long can you work without the database? If it is a problem, introduce it late. And yes, that probably means you add a layer.

Cinderella