IBatis.NET – Stored Procedures & Return Values

By | April 3, 2010

We’ve started to use IBatis.NET as a data mapping solution for one of our larger legacy databases. As is usually the case, we have large amounts of legacy SQL that our applications depend on that we cannot refactor wholesale. As our projects allow, we extract the tons of business logic buried in these stored procedures into our new domain layer.

Recently we were having to interface with a particularly large stored procedure chock full of joyous business logic. This stored procedure needed to return a primary key that it generated near the top of its multi-hundred lines or so of code. The IBatis.NET documentation gives good guidance on how to return an auto-generated key from a single insert statement, but wasn’t so clear how to cleanly capture a return value. Below is how we ended up doing this.

Note that the below assumes that you have already wired IBatis.NET both to your solution and to your database. If you need help there, see my earlier post regarding getting IBatis.NET set up properly.

Step 1: Identify the Nasty Procedure

In our case, the procedure looked like the one below.

A really nasty stored procedure

A really nasty stored procedure

A bit oversimplified, but I believe you get the idea.

Step 2: Write the Map

Below is the statement portion of the map.

<procedure id="ReallyNastyLegacyStoredProcedure"
           parameterMap="ReallyNastyLegacyStoredProcedurePM"
           resultClass="int">
   uspReallyNastyLegacyStoredProcedure
</procedure>

And below is the corresponding parameter map.


<parameterMap id="ReallyNastyLegacyStoredProcedurePM"
              class="int">
   <parameter property="IdToStartWith" column="theIdOfTheRecordToStartWith"/>
</parameterMap>

Step 3: Write the Code

A pretty straightforward one-liner, shown below.

public static void CallTheNastyStoredProcedure()
{
   int theKeyWeNeed = (int)Mapper.Instance()
            .Insert("ReallyNastyLegacyStoredProcedure", 10);
}

The only real mojo here is the use of the Insert method on the mapper instance. If you dive into the IBatis.NET code, you’ll see that the Insert code line does the work to attempt to retrieve a return avlue if the resultClass property is set on the procedure tag; the Update code line does nothing of the sort.

And that’s it. Party on with your legacy code and the best free data mapping tool out there.

Make sure to check out my other IBatis.NET posts: