Managing Data Streams With C# Iterators

Architecturally, I lean pretty heavily towards 2 philosophies - Domain Driven Design and the Rich Domain Model pattern.  And while neither requires the other for success (contrary to much of what you read out there), they seem to work well together for me, and hence, I have internalized them into a single approach for building solutions.

As a result of my approach, I typically start with (and end up with) an assembly for my domain model (another assembly for my domain model's automated unit tests), an assembly for my presentation logic, and an assembly for my data access layer - or where to put it into DDD terms, the layer where my repository objects live.  There is an ongoing debate about how the assembly references should look - I'm not going to get into the nuances of the arguments, but these seem to be the 2 basic philosophies:

1. Have the data access assembly reference the domain model assembly with the UI layer referencing both (and calling the repository objects directly to get domain instances)
2. Have the UI layer reference only the domain model assembly reference and have the domain model objects call their respective repository objects in the data access layer

There are a couple other more specific approaches, but I think that those give you an idea of the underlying philosophies.  I tend to gravitate towards philosophy #1, though I'm much more comfortable with #2 when done in conjunction with a DI container like Windsor.

So there you have my general layering philosophy - UI talks to repository to get and work on domain objects.  Why is this important?  Because one of the cases that has always made me a little uncomfortable is the classic case where I need to generate a UI element like a grid and I don't need to bring back the entire domain object (or in DDD terms, the entire aggregate).  The 3 major approaches I have seen and used are:

1. Return a IDbDataReader instance from the appropriate repository object in the data access layer
2. Fetch all of the relevant results into an in-memory data structure in the data access layer and hand that back to the caller
3. Use a SqlDataSource object at the UI layer and configure it with all of the proper query information

I don't really like any of these solutions.  The problem with solution #1 is that once you pass the stream reader out of the scope where it was initiated, you are relying on the consumer code to close the reader.  The problem with solution #2 is that you're potentially buffering a lot of data that is going to be almost immediately discarded.  The problem with solution #3 (which is the one I have ended up using most frequently) is that it puts you in a situation where you are managing data access in multiple places.

So I was thinking the other night that the ideal solution would be one where I could, in the same scope, manage the lifetime of my stream reader object, but still hand back some kind of safe reference to that stream to calling code - and then I remembered that really cool C# iterators feature that I had thought was nifty but very rarely used.  I added the following method to my repository class (DB is standard AdventureWorks - ignore the horrible practice of not parameterizing my query).

public class ProductsRepository
{
    private const string COMMAND_TEXT =
        "select top {0} ProductID, [Name] from Production.vProductAndDescription where CultureID = 'en'";

    public static IEnumerable<object> GetProducts(int numberRecords) {
        /*
         * in this example, numberRecords is a very simple illustration 
         * of how you could expose more complex criteria parameters based
         * on the specific domain
         */
        using (var conn = new SqlConnection(Settings.Default.AdWksCN))
        using (var cmd = conn.CreateCommand()) {
            cmd.CommandText = string.Format(COMMAND_TEXT, numberRecords);
            conn.Open();
            using (var rdr = cmd.ExecuteReader()) {
                if (rdr != null) {
                    while (rdr.Read()) {
                        var productID = rdr.GetInt32(0);
                        var name = rdr.GetString(1);
                        yield return new {productID, name};
                    }
                }
            }
        }
    }
}

Note the yield return statement - when the compiler sees this, it generates a whole other class that implements IEnumerable<object> and contains the code in your method along with a whole bunch of other code to manage the enumeration activity.  For example, here's the code for the MoveNext method of my generated enumerable class (ala Reflector).

private bool MoveNext()
{
    bool CS$1$0000;
    try
    {
        int CS$4$0001 = this.<>1__state;
        if (CS$4$0001 != 0)
        {
            if (CS$4$0001 != 4)
            {
                goto Label_0127;
            }
            goto Label_00F7;
        }
        this.<>1__state = -1;
        this.<conn>5__1 = new SqlConnection(Settings.Default.AdWksCN);
        this.<>1__state = 1;
        this.<cmd>5__2 = this.<conn>5__1.CreateCommand();
        this.<>1__state = 2;
        this.<cmd>5__2.CommandText = string.Format(
            "select top {0} ProductID, [Name] from Production.vProductAndDescription " + 
            "where CultureID = 'en'", this.numberRecords);
        this.<conn>5__1.Open();
        this.<rdr>5__3 = this.<cmd>5__2.ExecuteReader();
        this.<>1__state = 3;
        if (this.<rdr>5__3 != null)
        {
            while (this.<rdr>5__3.Read())
            {
                this.<productID>5__4 = this.<rdr>5__3.GetInt32(0);
                this.<name>5__5 = this.<rdr>5__3.GetString(1);
                this.<>2__current = new { productID = this.<productID>5__4, name = this.<name>5__5 };
                this.<>1__state = 4;
                return true;
            Label_00F7:
                this.<>1__state = 3;
            }
        }
        this.<>m__Finally8();
        this.<>m__Finally7();
        this.<>m__Finally6();
    Label_0127:
        CS$1$0000 = false;
    }
    fault
    {
        this.System.IDisposable.Dispose();
    }
    return CS$1$0000;
}

If you don't go cross-eyed from reading the compiler's choice of variable names, what you can see here is that the generated enumerator runs the query the first time MoveNext is called, then for subsequent calls, it simply calls the Read method on the data reader and returns the data elements extracted from the row.  When it is done enumerating, it cleans itself up, which includes calling the rest of the cleanup code you defined in the body of your method.

So what's nice about this approach?  Well, first and foremost, I can keep all of my data access code, including projection style queries with potentially complex criteria, in the same place as my object persistence data access code.  At the same time, since I'm returning IEnumerable, I still retain all the benefits of a simple usage pattern (including data binding).  For me, this is a nice hybrid solution for many of those cases where you just need to get a little off the rails!