Dec 072010
 

In a previous post about my extensions for Enterprise Library pre-version 5, There was quite a bit of customized logic to create custom entities from a result set. Enterprise Library 5 now takes care of almost all of my customizations with the advent of accessors, row mappers, result set mappers, and parameter mappers. In this post I’ll show a few different ways to use out-of-the-box Enterprise Library 5 features to access data. In Part 2, I’ll also show a few of my own extensions that simply extend Enterprise Library and reduce repetitive code in my data access layer code.

Out-of-the-box Features

The most simplistic scenario exists when your database queries bring back results with column names exactly matching the property names. This is by far the easiest code to write with Enterprise Library, and requires far less code than with all previous versions. Here is a sample showing the default mapping of input parameters and result set columns/values using the new database extension method ExecuteSprocAccessor. You simply pass in the stored procedure name and the params, returning an IEnumerable of your custom entity (in this case, a Jeep object).

public Jeep GetJeepByID(int id)
{
    Database db = DatabaseFactory.CreateDatabase();
    IEnumerable<Jeep> jeeps = db.ExecuteSprocAccessor<Jeep>("GetJeepByID", id);
    return jeeps.First();
}

You can only use this method if all public properties of the custom entity can be mapped to a result set column/value. If any public property values cannot be mapped, you will receive a System.InvalidOperationException stating that the column was not found on the IDataRecord being evaluated. If your parameter or result set mapping becomes more complicated, you can specify a parameter mapper, row mapper, result set mapper, or a combination thereof to customize how your procedure is called, and how the results are interpreted. Here is an example of a custom parameter mapper and row mapper used to replicate the default mapping performed in the first example:

internal class JeepParameterMapper : IParameterMapper
{
    public void AssignParameters(DbCommand command, object[] parameterValues)
    {
        DbParameter parameter = command.CreateParameter();
        parameter.ParameterName = "@JeepID";
        parameter.Value = parameterValues[0];
        command.Parameters.Add(parameter);
    }
}

internal class JeepRowMapper : IRowMapper<Jeep>
{
    public Jeep MapRow(System.Data.IDataRecord row)
    {
        return new Jeep()
        {
            ID = row.GetInt32(0),
            Name = row.GetString(1),
            Description = row.GetString(2),
            Status = row.GetBoolean(3)
        };
    }
}

Below you will see the same task being performed in the first example, but this time with our custom mappers.

public Jeep GetJeepByIDWithMappers(int id)
{
    IParameterMapper jeepParameterMapper = new JeepParameterMapper();
    IRowMapper<Jeep> jeepRowMapper = new JeepRowMapper();

    Database db = DatabaseFactory.CreateDatabase();
    IEnumerable<Jeep> jeeps = db.ExecuteSprocAccessor<Jeep>("GetJeepByID", jeepParameterMapper, jeepRowMapper, id);
    return jeeps.First();
}

ResultSetMappers can be used to map more complex result sets to custom entities with deeper object graphs. Consider a stored procedure that returns multiple result sets similar to that seen in the following image. The first result set contains the custom entity details, and the second result set is some collection of child objects. In this case, we see an article with a child collection of article images.

article_resultset

You would have a hard time building up your custom entity without using an IDataReader and iterating through the result sets with .NextResult. ResultSetMappers allow you to code for this scenario. Below we’ll create a custom result set mapper for articles that will map all of the relevant result sets to the Article object.

internal class ArticleResultSetMapper : IResultSetMapper<Article>
{
    public IEnumerable<Article> MapSet(System.Data.IDataReader reader)
    {
        Dictionary<int, Article> articles = new Dictionary<int, Article>();

        Article article;
        ArticleImage articleImage;
        while (reader.Read())
        {
            article = new Article
            {
                ID = reader.GetInt32(0),
                Title = reader.GetString(1),
                Description = reader.GetString(2),
                Images = new Collection<ArticleImage>()
            };
            articles.Add(article.ID, article);
        }
        if (reader.NextResult())
        {
            while (reader.Read())
            {
                int articleID = reader.GetInt32(0);
                if (articles.ContainsKey(articleID))
                {
                    articleImage = new ArticleImage
                    {
                        DisplayOrder = reader.GetInt32(1),
                        Url = reader.GetString(2),
                        Caption = reader.GetString(3)
                    };
                    articles[articleID].Images.Add(articleImage);
                }
            }
        }

        return articles.Select(a => a.Value);
    }
}

Below you will see the code used to create a new IEnumerable<Article> using our ArticleResultSetMapper:

public Article GetArticleByID(int id)
{
    ArticleResultSetMapper articleResultSetMapper = new ArticleResultSetMapper();

    Database db = DatabaseFactory.CreateDatabase();
    IEnumerable<Article> articles = db.ExecuteSprocAccessor<Article>("GetArticleByID", articleResultSetMapper, id);
    return articles.First();
}

As you can probably tell, Enterprise Library 5 gives you more power and control over the mapping and generation of your custom entities. The previous version of my Enterprise Library extensions focused primarily on performing just the types of mappings that are now built into the product. After seeing just a few examples, you should be ready to jump into Enterprise Library 5 Data Access head first. In the next post, we’ll walk through usage scenarios for a few of my Enterprise Library extensions that makes these routine tasks easier to read, maintain, and train.

 Posted by at 4:42 am

  5 Responses to “Extending Enterprise Library 5 Data Access Part 1: Out-of-the-box Features”

  1. Thank you very much.
    I was exactly looking for this Article/ArticleImage mapping with Enterprise Library! 🙂

    Carlo.

  2. Hi, it’s great, any full source code for sample your extensions ?

    And question about Ent Lib 5.0 + Oracle ODP.NET + XmlType oracle, any sample about it ?

    thx

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)