IBatis.NET Tips & Tricks Part 4 – Nulls & Lists

In this part of our IBatis.NET series, we’ll go over how to handle null values declaratively. Also, we’ll review how to hydrate list types with your query results instead of having to define new classes. We build on the code from the prior parts of this series, so start there if you want to be fully up to speed. As in prior parts, we use the AdventureWorks database.

Null Values

IBatis.NET gives you the flexibility to replace null values in query results with another pre-defined value. It’s pretty straightforward. Take the SpecialOffers table from the AdventureWorks database.

Let’s say we want to hydrate a list from the SpecialOffer table, and that we want to have all nulls in the MaxQty column be set to a really big number, say 99999. Our select statement and map would look like below.

<resultMap id="GetSpecialOffersRM"
           class="SpecialOffer">
    <result property="Description" column="Description"/>
    <result property="MaxQuantity" column="MaxQty" nullValue="0"/>
</resultMap>

<select id="GetSpecialOffers"
        resultMap="GetSpecialOffersRM">
    select
    Description,
    MaxQty
    from
    Sales.SpecialOffer
</select>

The magic happens by virtue of the nullValue attribute on the MaxQuantity result element. As you can guess, we can tell IBatis.NET to replace null values in the given column with anything we want. The code to take advantage of this looks the same as any other map consumption.

[Test]
public void GetSpecialOffersTest()
{
    IList<SpecialOffer> offers = Mappers.AWMapper.QueryForList<SpecialOffer>
        ("GetSpecialOffers", null);
    Assert.That(offers, Has.None.With.Property("MaxQuantity").Null);
}

More complicated null handling can be effected using custom type handlers, which you can review in Part 2 of this series.

Lists

If all you want is a quick and dirty way to get some data (i.e. without having to write a lot of objects), then you can hydrate a Hashtable (or list of Hashtable objects) with the result of your query. Let’s assume that you want to pull a list of first and last names from the Contact table, along with the corresponding contact id. Our select statement and map would look like below.

<select id="GetContactHashtable"
        resultMap="GetContactHashtableRM">
    select
    *
    from
    Person.Contact
    order by
    Person.Contact.ContactID ASC
</select>

<resultMap id="GetContactHashtableRM"
           class="Hashtable">
    <result property="ContactID" column="ContactID" />
    <result property="FirstName" column="FirstName" />
    <result property="LastName" column="LastName" />
</resultMap>

Note how instead of specifying an object that we’ve created as the target in the class attribute, we’ve specified Hashtable as the target. This allows us to write code like the below.

[Test]
public void GetContactHashtableTest()
{
    IList results = Mappers.AWMapper.QueryForList("GetContactHashtable", null);

    Assert.That(results, Is.Not.Null);
    Assert.That(results.Count, Is.EqualTo(19972));
    Assert.That(((Hashtable)results[0])["ContactID"], Is.EqualTo(1));
    Assert.That(((Hashtable)results[0])["FirstName"], Is.EqualTo("Gustavo"));
}

Definitely a nice little bare bones approach to data access. It gets slightly better though. We can cut the result map entirely out of the equation and rely solely on the select statement.

<select id ="GetContactHashtable2"
        resultClass="Hashtable">
    select
    *
    from
    Person.Contact
    order by
    Person.Contact.ContactID ASC
</select>

And the code that calls this select statement is the same, save for the select statement id of course.

[Test]
public void GetContactHashtable2Test()
{
    IList results = Mappers.AWMapper.QueryForList("GetContactHashtable2", null);

    Assert.That(results, Is.Not.Null);
    Assert.That(results.Count, Is.EqualTo(19972));
    Assert.That(((Hashtable)results[0])["ContactID"], Is.EqualTo(1));
    Assert.That(((Hashtable)results[0])["FirstName"], Is.EqualTo("Gustavo"));
}

Next Steps

In the next part of this series, we’ll go over multiple results in one select statement. We will also go over sub maps and discriminators, and how you can use these to help make hydrating complex objects easier.