About a week ago I posted the source code for my DataStore API. Among other cool features, it uses custom iterators when returning data from the database - that is, the Select<T> methods return IEnumerable<T> (the code below has been stripped to show only its basic structure):

public IEnumerable<T> Select<T>(Action<IDbCommand> initializeCommand)
{
using (IDbConnection connection = this.GetConnection())
{
using (IDbCommand command = connection.CreateCommand())
{
initializeCommand.Invoke(command);
connection.Open();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{

                    // ..populate item
yield return item;
}
}
}
}
} 

When I did a presentation at NNUG a few weeks back discussing the implementation of this, someone raised the question of what would happen to the database connection in such a scenario - would it not be left open? Or more generally - what happens with disposable resources within a method that returns a custom iterator?

Well, the answer can be deduced from the fact that IEnumerator inherits IDisposable. Since a custom iterator is basically syntaxical sugar, we can look at the code the compiler emits to find out exactly what is going on beneath the covers. If we look at the compiled assembly (using Reflector), we can see that .NET has generated the iterator class for us:

Notice that it has indeed implemented the Dispose method. If you open it up and inspect it, you'll see that it takes care of disposing the connection, command and reader objects.

So then, we've discovered that using disposable resources within a method that returns a custom iterator is safe. That is, as long as we remember to call the Dispose method on the iterator when we're through with it... So what then, if we were to use the foreach statement? For example:

foreach (Post post in this.DataStore.Select<Post>())
{
// do something
}           

When using a foreach statement, we're basically letting the compiler deal with creating the enumerator and etc. As such, we've not got a reference to it that we can use to call the Dispose method. Not to worry however - if we look at the IL code a foreach statement turns into, we can se that the compiler takes care of that for us:

As a final note, there are a few things to keep in mind when leaving a database connection open within a method that functions as a custom iterator. For example, if we do a foreach and perform heavy work on each item returned, then we'll be leaving the connection open for a long time - which is seldom a good thing. In such a scenario, loading the data from the iterator into a collection of some sort and then performing the heavy operations 'offline' will be a better choice.