I’ve been thinking about some basic ideas recently related to the design of a Object Layer for the C#/.Net project that I’m working on. Actually, it’s not a proper OL really, more of a set of enhancements to the Data Access Layer generated with MyGeneration for the Gentle framework. Of course, this is all rather old-hat for anyone who’s utilised Datasets in .Net 2 or later, but there you go.

The pattern the system is modelled on really seems to be based entirely on primary key auto-incrementing identifier columns. There are few multi-part keys, and little use of objects as parameters, and as a result, there is an awful lot of code that in an ‘average’ stack of method calls may retrieve the same database record several times! When tracing a path through code and you see the same xxxxx.Retrieve(id) however many times, it certainly feels inefficient. It can also be dangerous; if any one of those routines updates the object / db record, than caller functions will have an outdated in-memory copy and either need to refresh from the datebase or risk writing old data back to the db if they are persisted. Doubtless it also wastes memory and time and increases work for the garbage collector… though performance is not a major issue for us right now.

In comparison, passing an object as a function parameter is cheap (it’s a class, therefore a reference type, and also therefore a copy of a single reference / pointer, that points to the same in-memory instance), and does not expose you to the same risks as multiple-copies. If a function I call with my ‘loan’ instance changes the content, I will see that by default. Super.

So, now, how to go about designing new functions and redesigning old ones that fit a sensible pattern? Here are the simple ideas I’ve come up with:

Think about the Parameter Types

If the function you need to write operates on an object, then put the code in with that class as an instance method, and don’t use a parameter at all (unless you need additional data, of course). If you find that several callers do not already have the object, then wrap it in the same class with a static function that fetches the object for the particular id (that presumably will be accessible to the caller).

What a Method Returns is Where it Should Go

If a function returns a collection of loans, a count of loans, or any other information about a Loan object, place the method in the Loan class (probably) declared as static, and with appropriate parameters.

Say we have a person table and a loan table. A person may have many loans. We want a function that will return the latest loan that a customer / person had. As the function returns a loan, we’ll put it in the Loan code; it’s going to need a personId parameter, so we’ll end up declaring something like:

public partial class Loan
{
    ...
    public static Loan GetLatestLoan(long personId)
    {
        ...
    }
    ...
}

The Parameters a Function take give Clues as to where you Might Wrap It

The parameters you require may give a hint as to other classes that it would be useful to have this method accessible from. e.g. if you pass a personId, it is likely that access from an instance of person would be useful. Wrap the static loan function as an instance function on person. e.g.

public partial class Person
{
    ...
    public Loan GetLatestLoan()
    {
        return Loan.GetLatestLoan(this.PersonId);
    }
    ...
}

Wrap DB-Specific Types with Enumerations

Our database has several look-up values that on the DB map to a table but in reality we would rarely wish to refer to in queries. With the Gentle framework it is far easier to retrieve ‘all loans with a loan type of 1’ than it is to join SQL to join to another table. As the database design uses standard types for these lookups, integers etc. the historic code in the system tends not to refer to the enumerations that exist on the system because of the casts that are required.

I therefore decided to add properties to the key classes that are essentially enumerated types publicly, but simply map to the database type internally with casts. As the name I’d like to use was already taken (bear in mind I’m extending a pre-existing MyGeneration / Gentle class rather than writing a separate Business Object layer) I decided on postfixing these properties with an ‘E’ for Enumeration.

As a result of this:

// Code that used to look like this:
    l.LoanType == 3;

// ... becomes ...  (LoanType in this case is the enumeration)
    l.LoanTypeE == LoanType.Mortgage;

This does make the code massively more readable and understandable for general changes, with one exception. When I am investigating a bug I often find myself looking at code and database records; at those times, I really wish that Visual Studio showed the actual value of enumeration members and constants in a tooltip, rather than forcing me to ‘Go To Definition’ to find the value. Maybe VS 2008 already does this?

Overall I’m really pleased with the progress I and others are making with the code – but the list above is far from comprehensive. Does anyone have any suggestions for resources that suggest ‘what code should be put where’ in a multi-tier data access model?