Way 2 Web

Web development tips

ORM & iBatis

Laziness - the mother of all invention

If there's one thing programmers can't stand, it's work. We avoid it wherever possible. We find shortcuts. We outsource. So when programmers are faced with frequent, repetitive tasks, their first impulse is: "How do we get out of it?"

This is how ORM was born.


ORM, or Object - Relational Mapping, automates a widespread programming task - moving data between objects and relational database tables.

To be fair, laziness is not the only motivation. Copying from data collections using string field names is an error prone process that spawns run-time errors. <shudder> Using hard-typed classes instead, the compiler catches these mistakes for us.

More than that, ORM makes code more modular. Database access is neatly confined to the ORM tool - our convenient "data layer," or our M in MVC (Model-Control-View) architecture - achieving that elusive development grail known as "separation of concerns." Sloppy coders are saved from themselves: no mixing of database connectivity with business or presentation code, no duplicate database calls throughout the code. Code is clean, tight and sexy. I mean, elegant.

For the rest of article, we'll look at a popular ORM implementation for .Net - iBatis.

iBatis for .Net

iBatis is the .NET version of the popular Java tool of the same name. Both are open source, courtesy of those nice folks at Apache. (See references below.)

From version, iBatis utilizes features for .Net Framework 2.0, such as Generics.

The principle works as follows:

  • Write a simple class to mirror your database table

  • Create an SqlMap.xml file to contain all of your Sql statements, as well as the object property / table field mappings

  • In your business layer code, call the iBatis DataMapper, specifying the query ID in the Xml file, to run database queries.

  • Add an SqlMap.config and providers.config files to your project root and... voila!

Let's provide code samples for each step.

Simple class

using System;
using System.Text;

namespace Tests.Model {
    public class MyClass {
        private long accountID;
        private string name;
        private DateTime startDate;

        public long AccountID { 
            get { return accountID; } 
            set { accountID = value; } 
        public string Name { 
            get { return name; } 
            set { name = value; } 
        public DateTime StartDate { 
            get { return startDate; } 
            set { startDate = value; } 


<?xml version="1.0" encoding="utf-8" ?>

  <!-- XML "behind" document for the Account service class. -->

    <typeAccount account="MyClass" type="Tests.Model.MyClass" />

    <resultMap id="MyClassMap" class="MyClass">
      <result property="AccountID" column="account_id" />
      <result property="Name" column="account_name" />
      <result property="StartDate" column="start_date" />

    <select id="SelectAccount" parameterClass="long" resultMap="MyClassMap">
      FROM TEST.account
      <dynamic prepend="WHERE">
          account_id = #value#

    <insert id="InsertAccount" parameterClass="MyClassMap">
      INSERT INTO TEST.account
      (account_id, account_name, start_date)
      VALUES (
      #AccountID#, #Name#, #StartDate#)

    <update id="UpdateAccount" parameterClass="MyClassMap">
      UPDATE TEST.account SET
      account_name = #Name#,
      start_date = #StartDate#
      WHERE account_id = #AccountID#

    <delete id="DeleteAccount" parameterClass="long">
      DELETE FROM TEST.account
      WHERE account = #AccountID#



iBatis allows you to refer to the properties of the parameter class.

Business layer class

using System;
using System.Collections.Generic;
using IBatisNet.DataMapper;
using Tests.Model;

namespace Tests.Logic {
    public class AccountHelper {

        private static volatile ISqlMapper Mapper = 
        public AccountHelper(){
        public IList<MyClass> SelectAllAccounts() {
            return Mapper.QueryForList<MyClass>("Account.SelectAccount", null);
        public MyClass SelectAccount(long accountID) {
            return (Account)Mapper.QueryForObject("Account.SelectAccount", 

        public string InsertAccount(MyClass account) {
            return (string)Mapper.Insert("Account.InsertAccount", account);

        public int UpdateAccount(MyClass account) {
            return Mapper.Update("Account.UpdateAccount", account);
        public int DeleteAccount(long accountID) {
            return Mapper.Update("Account.DeleteAccount", account);


Note the following:

  • The query identifier is are prefixed with "Account" because this is the namespace of the SqlMap.xml file.

  • We use query "SelectAccount" twice. When it has a parameter it returns a single Account. Without the parameter, it returns all the Accounts, in the form of a .Net 2.0 generic list, of type MyClass.

  • The code is very short for the purposes of the example. It would make sense for perform validation of input parameters and other business logic in real-world business-layer classes.


<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig xmlns="http://ibatis.apache.org/dataMapper" 

  <properties resource="properties.config"/>
		<setting useStatementNamespaces="true"/>
		<setting cacheModelsEnabled="true"/>
		<provider name="${provider}"/>
		<dataSource name="AccountsDataSource" 
                        connectionString="Data Source=${datasource};
                            User Id=${userid};

        <sqlMap resource="${root}Sql/SqlMap.xml"/>

This configuration file specifies the properties file to be used (<properties>), the database provider and connection string (<database>), as well as the SqlMap.xml files to be used (<sqlMaps>).

Where is the actual data? you may ask. In the properties.config file:

<?xml version="1.0" encoding="utf-8" ?> 
	<!--   User application and configured property settings go here.-->
	<!--   Example: <add key="settingName" value="settingValue"/> -->
	<add key="provider" value="oracle9.2" />
  <add key="datasource" value="test-db"/>
  <add key="userid" value="test-user"/>
  <add key="password" value="test-user-pwd"/>
	<add key="root" value="../../" />

See the providers.config file provided with iBatis for the (many) supported database types.

Logging and iBatis

How do I tell what Sql queries have actually been run? iBatis logs all queries. To view them, you'll need to add configuration settings to Web.config:

    <sectionGroup name="iBATIS">
      <section name="logging" 
        IBatisNet.Common" />
        <arg key="showLogName" value="true" />
        <arg key="showDataTime" value="true" />
        <arg key="level" value="ALL" />
        <arg key="dateTimeFormat" value="yyyy/MM/dd HH:mm:ss:SSS" />

iBatis can also log using Log4Net, when you change Web.config to read:

 <sectionGroup name="iBATIS">
  <section name="logging" 
    IBatisNet.Common" />
  <logFactoryAdapter type="IBatisNet.Common.Logging.Impl.Log4NetLoggerFA, 
   <arg key="configType" value="file" />
   <arg key="configFile" value="log4Net.config" />

and add the following to your Log4Net configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<log4net debug="true">
  <!-- iBatis internal logging -->
  <logger name="IBatisNet.DataMapper">
    <level value="DEBUG" />
  <logger name="IBatisNet.DataMapper.Commands.DefaultPreparedCommand">
    <level value="DEBUG" />
  <logger name="IBatisNet.DataMapper.Configuration.Cache.CacheModel">
    <level value="DEBUG" />
  <logger name="IBatisNet.DataMapper.LazyLoadList">
    <level value="DEBUG" />

The configuration is slightly different when your log4net configuration is in Web.config. See the iBatis user manual for details. Oh, and don't forget to add the IBatisNet.Common.Logging.Log4Net.dll to your class path.


  • The iBatis Log4Net logging for my test ASP .Net application did not work, despite assurances from the official email listing that it should work. I suspect this may result from version conflicts for Log4Net - iBatis uses 1.2.9 (for Framework 1.1), whereas my code uses 1.2.10 (for Framework 2.0).

    Please prove me wrong.

  • As with Log4Net, it seems that iBatis will fail when ASP 2.0's default "Medium" trust level applies, such as in hosted environments. The issue, once again, is the permissions required to read in configuration data.

    Again, feel free to prove me wrong.