Seeing Ibatis action in the JPetstore make me wonder if there is similar framework is available in .NET platform. A fellow in JUG Indonesia also claimed that Ibatis increase his productivity in project's development. Now there is a good news, Ibatis is available in .NET as Ibatis.NET. Below are my POC of IBatis.NET
The interface:
IReferenceCodeDao.cs
using System;
using System.Collections;
using System.Text;
using Dallanube.VIP.Cafestol.Model.Setup;
namespace Dallanube.VIP.Cafestol.Dao.Factory.Setup
{
public interface IReferenceCodeDao
{
IList GetReferenceCodesByCriteria(ReferenceCode code);
ReferenceCode GetReferenceCode(ReferenceCode code);
String GetReferenceCodeDescription(ReferenceCode code);
Object AddReferenceCode(ReferenceCode code);
int UpdateReferenceCode(ReferenceCode code);
int DeleteReferenceCode(ReferenceCode code);
IList GetReferenceCategoryByCriteria(ReferenceCode code);
ReferenceCode GetReferenceCategory(ReferenceCode code);
}
}
DAO implementation:
ReferenceCodeDao.cs
using System;
using System.Collections;
using System.Text;
using Dallanube.Caffeine.Dao.Persistence;
using Dallanube.VIP.Cafestol.Model.Setup;
using Dallanube.VIP.Cafestol.Dao.Factory.Setup;
namespace Dallanube.VIP.Cafestol.Dao.Persistence.Setup
{
public class ReferenceCodeDao : BasePersistenceDao, IReferenceCodeDao
{
public IList GetReferenceCodesByCriteria(ReferenceCode code)
{
return ExecuteQueryForList("GetReferenceCodesByCriteria", code);
}
public ReferenceCode GetReferenceCode(ReferenceCode code)
{
return (ReferenceCode)ExecuteQueryForObject("GetReferenceCode", code);
}
public String GetReferenceCodeDescription(ReferenceCode code)
{
return Convert.ToString(ExecuteQueryForObject("GetReferenceCodeDescription", code));
}
public Object AddReferenceCode(ReferenceCode code)
{
return ExecuteInsert("AddReferenceCode", code);
}
public int UpdateReferenceCode(ReferenceCode code)
{
return ExecuteUpdate("UpdateReferenceCode", code);
}
public int DeleteReferenceCode(ReferenceCode code)
{
return ExecuteUpdate("DeleteReferenceCode", code);
}
public IList GetReferenceCategoryByCriteria(ReferenceCode code)
{
return ExecuteQueryForList("GetReferenceCategory", code);
}
public ReferenceCode GetReferenceCategory(ReferenceCode code)
{
return (ReferenceCode)ExecuteQueryForObject("GetReferenceCategory", code);
}
}
}
XML Mapping:
ReferenceCode.xml
<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="ReferenceCode"
xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<alias>
<typeAlias alias="ReferenceCode" type="Dallanube.VIP.Cafestol.Model.Setup.ReferenceCode, Dallanube.VIP.Cafestol.Core" />
<typeAlias alias="ReferenceCategory" type="Dallanube.VIP.Cafestol.Model.Setup.ReferenceCategory, Dallanube.VIP.Cafestol.Core" />
</alias>
<resultMaps>
<resultMap id="ReferenceCodeResult" class="ReferenceCode">
<result property="Category" column="CATEGORY"/>
<result property="Code" column="CODE"/>
<result property="Description" column="DESCRIPTION"/>
<result property="MapCode" column="MAP_CODE"/>
</resultMap>
<resultMap id="ReferenceCategoryResult" class="ReferenceCategory">
<result property="Category" column="CATEGORY"/>
<result property="Description" column="DESCRIPTION"/>
<result property="Int01" column="INT01"/>
</resultMap>
</resultMaps>
<!-- =============================================
MAPPED STATEMENTS
=============================================
-->
<statements>
<select id="GetReferenceCodesByCriteria" resultMap="ReferenceCodeResult">
select
CATEGORY,
CODE,
DESCRIPTION,
MAP_CODE
from REF_CODE
<dynamic prepend="WHERE">
<isNotEmpty prepend="AND" property="Category">
CATEGORY = #Category#
</isNotEmpty>
<isNotEmpty prepend="AND" property="Code">
CODE like '$Code$%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="Description">
DESCRIPTION like '%$Description$%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="IsTitle">
IS_TITLE = #IsTitle#
</isNotEmpty>
</dynamic>
</select>
<select id="GetReferenceCode" resultMap="ReferenceCodeResult">
select
CATEGORY,
CODE,
DESCRIPTION,
MAP_CODE
from REF_CODE
WHERE CATEGORY = #Category#
and CODE = #Code#
</select>
<select id="GetReferenceCodeDescription" resultClass="String">
select DESCRIPTION from REF_CODE
where CATEGORY = #Category#
and CODE = #Code#
and IS_TITLE = #IsTitle#
</select>
<select id="GetReferenceCategory" resultMap="ReferenceCategoryResult">
select code1.CATEGORY, code2.CODE, code1.INT01, code2.DESCRIPTION
from (
select category, count(code) int01 from REF_CODE
group by category
) as code1
left join REF_CODE code2
on code1.CATEGORY = code2.CATEGORY
and code2.IS_TITLE = 1
<dynamic prepend="WHERE">
<isNotEmpty prepend="AND" property="Category">
code1.CATEGORY = #Category#
</isNotEmpty>
<isNotEmpty prepend="AND" property="Description">
code2.DESCRIPTION like '%$Description$%'
</isNotEmpty>
</dynamic>
</select>
<insert id="AddReferenceCode" parameterClass="ReferenceCode">
insert into REF_CODE
(CATEGORY, CODE, DESCRIPTION, IS_TITLE, MAP_CODE)
values
(#Category#, #Code#, #Description#, #IsTitle#, #MapCode#)
</insert>
<update id="UpdateReferenceCode" parameterClass="ReferenceCode">
update REF_CODE set
DESCRIPTION = #Description#,
MAP_CODE = #MapCode#
where CATEGORY = #Category#
and CODE = #Code#
and IS_TITLE = #IsTitle#
</update>
<update id="DeleteReferenceCode" parameterClass="ReferenceCode">
delete from REF_CODE
<dynamic prepend="WHERE">
<isNotEmpty prepend="AND" property="Category">
CATEGORY = #Category#
</isNotEmpty>
<isNotEmpty prepend="AND" property="Code">
CODE = #Code#
</isNotEmpty>
<isNotEmpty prepend="AND" property="IsTitle">
IS_TITLE = #IsTitle#
</isNotEmpty>
</dynamic>
</update>
</statements>
</sqlMap>
Base class for Dao, all DAO class inherit from this:
BasePersistenceDao.cs
using System;
using System.Collections;
using System.Data;
using System.Runtime.Serialization;
using System.Text;
using IBatisNet.Common.Exceptions;
using IBatisNet.Common.Pagination;
using IBatisNet.DataAccess;
using IBatisNet.DataAccess.Interfaces;
using IBatisNet.DataAccess.DaoSessionHandlers;
using IBatisNet.DataMapper;
namespace Dallanube.Caffeine.Dao.Persistence
{
///
/// Desson Ariawan
///
public class BasePersistenceDao: IDao
{
protected const int PAGE_SIZE = 4;
private ISqlMapper localSqlMap;
protected String tableName;
public String TableName
{
get
{
return tableName;
}
set
{
tableName = value;
}
}
/// /// Looks up the parent DaoManager, gets the local transaction
/// (which should be a SqlMapDaoTransaction) and returns the
/// SqlMap associated with this DAO.
/// /// The SqlMap instance for this DAO.
protected ISqlMapper GetLocalSqlMap()
{
if (localSqlMap == null)
{
IDaoManager daoManager = DaoManager.GetInstance(this);
SqlMapDaoSession sqlMapDaoSession = (SqlMapDaoSession)daoManager.LocalDaoSession;
localSqlMap = sqlMapDaoSession.SqlMap;
}
return localSqlMap;
}
/// /// Simple convenience method to wrap the SqlMap method of the same name.
/// Wraps the exception with a IBatisNetException to isolate the SqlMap framework.
///
protected IList ExecuteQueryForList(string statementName, object parameterObject)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
return sqlMap.QueryForList(statementName, parameterObject);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
}
/// /// Simple convenience method to wrap the SqlMap method of the same name.
/// Wraps the exception with a IBatisNetException to isolate the SqlMap framework.
///
protected IList ExecuteQueryForList(string statementName, object parameterObject, int skipResults, int maxResults)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
return sqlMap.QueryForList(statementName, parameterObject, skipResults, maxResults);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
}
///// ///// Simple convenience method to wrap the SqlMap method of the same name.
///// Wraps the exception with a IBatisNetException to isolate the SqlMap framework.
/////
//protected IPaginatedList ExecuteQueryForPaginatedList(string statementName, object parameterObject, int pageSize)
//{
// ISqlMapper sqlMap = GetLocalSqlMap();
// try
// {
// return sqlMap.QueryForPaginatedList(statementName, parameterObject, pageSize);
// }
// catch (Exception e)
// {
// throw new IBatisNetException("Error executing query '" + statementName + "' for paginated list. Cause: " + e.Message, e);
// }
//}
/// /// Simple convenience method to wrap the SqlMap method of the same name.
/// Wraps the exception with a IBatisNetException to isolate the SqlMap framework.
///
protected object ExecuteQueryForObject(string statementName, object parameterObject)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
return sqlMap.QueryForObject(statementName, parameterObject);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for object. Cause: " + e.Message, e);
}
}
/// /// Simple convenience method to wrap the SqlMap method of the same name.
/// Wraps the exception with a IBatisNetException to isolate the SqlMap framework.
///
protected int ExecuteUpdate(string statementName, object parameterObject)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
return sqlMap.Update(statementName, parameterObject);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for update. Cause: " + e.Message, e);
}
}
/// /// Simple convenience method to wrap the SqlMap method of the same name.
/// Wraps the exception with a IBatisNetException to isolate the SqlMap framework.
///
protected object ExecuteInsert(string statementName, object parameterObject)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
return sqlMap.Insert(statementName, parameterObject);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for insert. Cause: " + e.Message, e);
}
}
protected DataSet ExecuteQueryForDataSet(String statement)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
IDbCommand objCmd = sqlMap.LocalSession.CreateCommand(CommandType.Text);
objCmd.CommandText = statement;
DataSet ds = new DataSet();
IDbDataAdapter objDA = sqlMap.LocalSession.CreateDataAdapter();
objDA.SelectCommand = objCmd;
objDA.Fill(ds);
return ds;
}
catch (Exception e)
{
throw new IBatisNetException("Error executing statement for DataSet. Cause: " + e.Message, e);
}
}
protected IDataReader ExecuteQueryForDataReader(String statement)
{
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
IDbCommand objCmd = sqlMap.LocalSession.CreateCommand(CommandType.Text);
objCmd.CommandText = statement;
if (objCmd.Connection.State == ConnectionState.Closed)
objCmd.Connection.Open();
IDataReader dr = objCmd.ExecuteReader();
return dr;
}
catch (Exception e)
{
throw new IBatisNetException("Error executing statement for DataSet. Cause: " + e.Message, e);
}
}
}
}
Pretty cool, heh? I'm using it in my Windows application with Spring.NET
Reference:
https://ibatisnet.sourceforge.net/Tutorial.html
