四． IBatisNet组件使用
DomSqlMapBuilder，其作用是根据配置文件创建SqlMap实例。可以通过这个组件从Stream, Uri, FileInfo, or XmlDocument instance 来读取sqlMap.config文件。
2. SqlMap
SqlMapper是IBatisnet的核心组件，提供数据库操作的基础平台。所有的操作均通过SqlMapper实例完成。SqlMapper可通过DomSqlMapBuilder创建。
这个例子中我们可以将所有的配置文件按照嵌入资源文件方式存放，从程序集去加载sqlmap.config文件。也可以直接以文件方式加载sqlmap.config。
/// <summary>
/// IsqlMapper实例
/// </summary>
/// <returns></returns>
public static ISqlMapper sqlMap ;
private string fileName = "sqlMap.Config";
public BaseSqlMapDao()
{
//从程序集资源中加载
//Assembly assembly = Assembly.Load("IBatisNetDemo");
//Stream stream = assembly.GetManifestResourceStream("IBatisNetDemo.sqlmap.config");
//DomSqlMapBuilder builder = new DomSqlMapBuilder();
//sqlMap = builder.Configure(stream);
//从文件加载创建实例
DomSqlMapBuilder builder = new DomSqlMapBuilder();
sqlMap = builder.Configure(fileName);
}
SqlMap是线程安全的，也就是说，在一个应用中，可以共享一个SqlMap实例。
SqlMap提供了众多数据操作方法，下面是一些常用方法的示例，具体说明文档参见 ibatis net doc,或者ibatisnet的官方开发手册。
基本操作示例
例1：数据写入操作（insert、update、delete）
SqlMap.BeginTransaction();
Person person = new Person();
Person.FirstName = “li”;
Person.LastName = “tianping”;
int Id = (int) SqlMap.Insert("InsertPerson", person);
SqlMap.CommitTransaction();
例2：数据查询：
Int Id = 1;
Person person = SqlMap.QueryForObject<Person>("", Id);
return person;
例3：执行批量查询（Select）
IList<Person> list = null;
list = SqlMap.QueryForList<Person>("SelectAllPerson", null);
return list;
例4：查询指定范围内的数据（Select）
IList<Person> list = null;
list = SqlMap.QueryForList<Person>("SelectAllPerson", null, 0, 40);
return list;
例5：结合RowDelegate进行查询：
public void RowHandler(object obj, IList list)
{
Product product = (Product) object;
product.Quantity = 10000;
}
SqlMapper.RowDelegate handler = new SqlMapper.RowDelegate(this.RowHandler);
IList list = sqlMap.QueryWithRowDelegate("getProductList", null, handler);
4. 存储过程操作
下面特别说明一下ibatisnet对Stored Procedures的处理，iBatis数据映射把存储过程当成另外一种声明元素。示例演示了一个基于存储过程的简单数据映射。
<!-- Microsot SQL Server -->
<procedure id="SwapEmailAddresses" parameterMap="swap-params">
ps_swap_email_address
</procedure>
...
<parameterMap id="swap-params">
<parameter property="email1" column="First_Email" />
<parameter property="email2" column="Second_Email" />
</parameterMap>
<!-- Oracle with MS OracleClient provider -->
<procedure id="InsertCategory" parameterMap="insert-params">
prc_InsertCategory
</procedure>
...
<parameterMap id="insert-params">
<parameter property="Name" column="p_Category_Name"/>
<parameter property="GuidString" column="p_Category_Guid" dbType="VarChar"/>
<parameter property="Id" column="p_Category_Id" dbType="Int32" type="Int"/>
</parameterMap>
<!-- Oracle with ODP.NET 10g provider -->
<statement id="InsertAccount" parameterMap="insert-params">
prc_InsertAccount
</statement>
...
<parameterMap id="insert-params">
<parameter property="Id" dbType="Int32"/>
<parameter property="FirstName" dbType="VarChar2" size="32"/>
<parameter property="LastName" dbType="VarChar2" size="32"/>
<parameter property="EmailAddress" dbType="VarChar2" size="128"/>
</parameterMap>
示例是调用存储过程swapEmailAddress的时候将会在数据库表的列和两个email地址之间交换数据，参数对象亦同。参数对象仅在属性被设置成INOUT或者OUT的时候才会被修改。否则，他们将不会被修改。当然，不可变得参数对象是不会被修改的，比如string.
.Net中，parameterMap属性是必须的。DBType，参数方向，大小由框架自动发现的。（使用CommandBuilder实现的）。
五． IBatisNet封装类:BaseSqlMapDao
为了日后的重复使用和代码简洁，我们可以像DbHelperSQL一样，对SqlMap的各种操作进行封装。
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Web;
using System.Reflection;
using IBatisNet.Common;
using IBatisNet.Common.Pagination;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Exceptions;
using IBatisNet.DataMapper.Configuration;
namespace IBatisNetLib
{
/// <summary>
/// 基于IBatisNet的数据访问基类
/// </summary>
public class BaseSqlMapDao
{
/// <summary>
/// IsqlMapper实例
/// </summary>
/// <returns></returns>
public static ISqlMapper sqlMap;
#region 构造ISqlMapper
private string fileName = "sqlMap.Config";
public BaseSqlMapDao()
{
//从程序集中加载
//Assembly assembly = Assembly.Load("IBatisNetDemo");
//Stream stream = assembly.GetManifestResourceStream("IBatisNetDemo.sqlmap.config");
//DomSqlMapBuilder builder = new DomSqlMapBuilder();
//sqlMap = builder.Configure(stream);
//从文件加载创建实例
DomSqlMapBuilder builder = new DomSqlMapBuilder();
sqlMap = builder.Configure(fileName);
}
#endregion
/// <summary>
/// 是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
protected bool ExecuteExists(string statementName, object parameterObject)
{
try
{
object obj = sqlMap.QueryForObject(statementName, parameterObject);
int cmdresult;
if ((Object.Equals(obj, null)) || (obj == null))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
catch (Exception e)
{
throw (e);
}
}
/// <summary>
/// 执行添加
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数</param>
protected object ExecuteInsert(string statementName, object parameterObject)
{
try
{
return sqlMap.Insert(statementName, parameterObject);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for insert. Cause: " + e.Message, e);
}
}
/// <summary>
/// 执行添加，返回自动增长列
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数</param>
/// <returns>返回自动增长列</returns>
protected int ExecuteInsertForInt(string statementName, object parameterObject)
{
try
{
object obj=sqlMap.Insert(statementName, parameterObject);
if (obj != null)
{
return Convert.ToInt32(obj);
}
else
{
return 0;
}
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for insert. Cause: " + e.Message, e);
}
}
/// <summary>
/// 执行修改
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数</param>
/// <returns>返回影响行数</returns>
protected int ExecuteUpdate(string statementName, object parameterObject)
{
try
{
return sqlMap.Update(statementName, parameterObject);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for update. Cause: " + e.Message, e);
}
}
/// <summary>
/// 执行删除
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数</param>
/// <returns>返回影响行数</returns>
protected int ExecuteDelete(string statementName, object parameterObject)
{
try
{
return sqlMap.Delete(statementName, parameterObject);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for delete. Cause: " + e.Message, e);
}
}
/// <summary>
/// 得到列表
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="statementName">操作名称，对应xml中的Statement的id</param>
/// <param name="parameterObject">参数</param>
/// <returns></returns>
protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject)
{
try
{
return sqlMap.QueryForList<T>(statementName, parameterObject);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
}
/// <summary>
/// 得到指定数量的记录数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="statementName"></param>
/// <param name="parameterObject">参数</param>
/// <param name="skipResults">跳过的记录数</param>
/// <param name="maxResults">最大返回的记录数</param>
/// <returns></returns>
protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject, int skipResults, int maxResults)
{
try
{
return sqlMap.QueryForList<T>(statementName, parameterObject, skipResults, maxResults);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
}
/// <summary>
/// 得到分页的列表
/// </summary>
/// <param name="statementName">操作名称</param>
/// <param name="parameterObject">参数</param>
/// <param name="pageSize">每页记录数</param>
/// <returns></returns>
protected IPaginatedList ExecuteQueryForPaginatedList(string statementName, object parameterObject, int pageSize)
{
try
{
return sqlMap.QueryForPaginatedList(statementName, parameterObject, pageSize);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for paginated list. Cause: " + e.Message, e);
}
}
/// <summary>
/// 查询得到对象的一个实例
/// </summary>
/// <typeparam name="T">对象type</typeparam>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数</param>
/// <returns></returns>
protected T ExecuteQueryForObject<T>(string statementName, object parameterObject)
{
try
{
return sqlMap.QueryForObject<T>(statementName, parameterObject);
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for object. Cause: " + e.Message, e);
}
}
}
}
调用该基类实现映射文件的数据访问代码：
using System;
using System.Collections.Generic;
using System.Text;
namespace IBatisNetLib
{
public class PersonService : BaseSqlMapDao
{
public PersonService()
{
}
/// <summary>
/// 是否存在该记录
/// </summary>
public bool Exists(object Id)
{
return ExecuteExists("Exists", Id);
}
public void Insert(Person person)
{
ExecuteInsert("InsertPerson", person);
}
public void Update(Person person)
{
ExecuteUpdate("UpdatePerson", person);
}
public void Delete(Person person)
{
ExecuteDelete("DeletePerson", person);
}
public IList<Person> GetAllPerson()
{
IList<Person> list = null;
list = ExecuteQueryForList<Person>("SelectAllPerson", null);
return list;
}
public Person GetPerson(object Id)
{
Person person = ExecuteQueryForObject<Person>("SelectByPersonId", Id);
return person;
}
}
}
