四． IBatisNet組件使用
DomSqlMapBuilder，其作用是根據(jù)配置文件創(chuàng)建SqlMap實(shí)例?？梢酝ㄟ^(guò)這個(gè)組件從Stream, Uri, FileInfo, or XmlDocument instance 來(lái)讀取sqlMap.config文件。
2. SqlMap
SqlMapper是IBatisnet的核心組件，提供數(shù)據(jù)庫(kù)操作的基礎(chǔ)平臺(tái)。所有的操作均通過(guò)SqlMapper實(shí)例完成。SqlMapper可通過(guò)DomSqlMapBuilder創(chuàng)建。
這個(gè)例子中我們可以將所有的配置文件按照嵌入資源文件方式存放，從程序集去加載sqlmap.config文件。也可以直接以文件方式加載sqlmap.config。
/// <summary>
/// IsqlMapper實(shí)例
/// </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);
//從文件加載創(chuàng)建實(shí)例
DomSqlMapBuilder builder = new DomSqlMapBuilder();
sqlMap = builder.Configure(fileName);
}
SqlMap是線程安全的，也就是說(shuō)，在一個(gè)應(yīng)用中，可以共享一個(gè)SqlMap實(shí)例。
SqlMap提供了眾多數(shù)據(jù)操作方法，下面是一些常用方法的示例，具體說(shuō)明文檔參見(jiàn) ibatis net doc,或者ibatisnet的官方開(kāi)發(fā)手冊(cè)。
基本操作示例
例1：數(shù)據(jù)寫(xiě)入操作（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：數(shù)據(jù)查詢：
Int Id = 1;
Person person = SqlMap.QueryForObject<Person>("", Id);
return person;
例3：執(zhí)行批量查詢（Select）
IList<Person> list = null;
list = SqlMap.QueryForList<Person>("SelectAllPerson", null);
return list;
例4：查詢指定范圍內(nèi)的數(shù)據(jù)（Select）
IList<Person> list = null;
list = SqlMap.QueryForList<Person>("SelectAllPerson", null, 0, 40);
return list;
例5：結(jié)合RowDelegate進(jìn)行查詢：
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. 存儲(chǔ)過(guò)程操作
下面特別說(shuō)明一下ibatisnet對(duì)Stored Procedures的處理，iBatis數(shù)據(jù)映射把存儲(chǔ)過(guò)程當(dāng)成另外一種聲明元素。示例演示了一個(gè)基于存儲(chǔ)過(guò)程的簡(jiǎn)單數(shù)據(jù)映射。
<!-- 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>
示例是調(diào)用存儲(chǔ)過(guò)程swapEmailAddress的時(shí)候?qū)?huì)在數(shù)據(jù)庫(kù)表的列和兩個(gè)email地址之間交換數(shù)據(jù)，參數(shù)對(duì)象亦同。參數(shù)對(duì)象僅在屬性被設(shè)置成INOUT或者OUT的時(shí)候才會(huì)被修改。否則，他們將不會(huì)被修改。當(dāng)然，不可變得參數(shù)對(duì)象是不會(huì)被修改的，比如string.
.Net中，parameterMap屬性是必須的。DBType，參數(shù)方向，大小由框架自動(dòng)發(fā)現(xiàn)的。（使用CommandBuilder實(shí)現(xiàn)的）。
五． IBatisNet封裝類:BaseSqlMapDao
為了日后的重復(fù)使用和代碼簡(jiǎn)潔，我們可以像DbHelperSQL一樣，對(duì)SqlMap的各種操作進(jìn)行封裝。
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的數(shù)據(jù)訪問(wèn)基類
/// </summary>
public class BaseSqlMapDao
{
/// <summary>
/// IsqlMapper實(shí)例
/// </summary>
/// <returns></returns>
public static ISqlMapper sqlMap;
#region 構(gòu)造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);
//從文件加載創(chuàng)建實(shí)例
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>
/// 執(zhí)行添加
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">參數(shù)</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>
/// 執(zhí)行添加，返回自動(dòng)增長(zhǎng)列
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">參數(shù)</param>
/// <returns>返回自動(dòng)增長(zhǎng)列</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>
/// 執(zhí)行修改
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">參數(shù)</param>
/// <returns>返回影響行數(shù)</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>
/// 執(zhí)行刪除
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">參數(shù)</param>
/// <returns>返回影響行數(shù)</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">實(shí)體類型</typeparam>
/// <param name="statementName">操作名稱，對(duì)應(yīng)xml中的Statement的id</param>
/// <param name="parameterObject">參數(shù)</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>
/// 得到指定數(shù)量的記錄數(shù)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="statementName"></param>
/// <param name="parameterObject">參數(shù)</param>
/// <param name="skipResults">跳過(guò)的記錄數(shù)</param>
/// <param name="maxResults">最大返回的記錄數(shù)</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>
/// 得到分頁(yè)的列表
/// </summary>
/// <param name="statementName">操作名稱</param>
/// <param name="parameterObject">參數(shù)</param>
/// <param name="pageSize">每頁(yè)記錄數(shù)</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>
/// 查詢得到對(duì)象的一個(gè)實(shí)例
/// </summary>
/// <typeparam name="T">對(duì)象type</typeparam>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">參數(shù)</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);
}
}
}
}
調(diào)用該基類實(shí)現(xiàn)映射文件的數(shù)據(jù)訪問(wèn)代碼：
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;
}
}
}
