1.配置Ibaties首先在DLL引用中添加Ibaties相关引用：IBatisNet.Common.dll;IBatisNet.Common.Logging.Log4Net.dll;IBatisNet.DataMapper.dll
2.添加providers.config、sqlmap.config配置文件
providers.config主要为数据库驱动
sqlmap.config主要为数据库配置和路由映射，配置信息如下
<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig
xmlns="http://ibatis.apache.org/dataMapper"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <settings>
<!--Mapping是否启用namespace-->
<setting useStatementNamespaces="true"/>
<!--Mapping是否缓存-->
<setting cacheModelsEnabled="false"/>
</settings> <providers resource="providers.config"/> <!-- Database connection information -->
<database>
<provider name="sqlServer2005"/>
<dataSource name="iBatisNet" connectionString="Max Pool Size = 512;Data Source=.;
Initial Catalog=test;
User ID=sa;password=!test;Max Pool Size = 512;connect timeout = 20; "/>
</database> <sqlMaps>
<sqlMap resource="Mappings/LocalDB/TestMapping.xml"></sqlMap>
</sqlMaps> </sqlMapConfig>
3.在Mappings/LocalDB文件夹下建立映射XML文件TestMapping.xml，相关信息如下：
<sqlMap namespace="TestMapping" xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <statements>
<select id="GetByid" resultClass="System.Data.DataSet">
select * from test where id=#value#
</select> <update id="UpdateNum" parameterClass="Hashtable">
update test
set num = #num#,
where id = #id#
</update> <insert id="CreatePurchaseOrder" parameterClass="Hashtable" >
INSERT INTO test
(
Num
)
VALUES
(
#Num#
)
<selectKey resultClass="int" type="post" property="id" >
select @@IDENTITY as value
</selectKey>
</insert> </statements> </sqlMap>
4.实例化SqlMap，对数据库进行操作
IBaseDAL：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections; public interface IBaseDAL
{
/// <summary>
/// 插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="statementName"></param>
/// <param name="t"></param>
/// <returns></returns>
int Insert<T>(string statementName, T t); int Update<T>(string statementName, T t); int Delete(string statementName, int primaryKeyId);
int Delete(string statementName, string primaryKeyId);
int Delete(string statementName, object parameterObject); T Get<T>(string statementName, int primaryKeyId) where T : class; T Get<T>(string statementName, string primaryKeyId) where T : class; T Get<T>(string statementName, object parameterObject) where T : class; object Get(string statementName, object parameterObject); IList<T> QueryForList<T>(string statementName, object parameterObject = null); int Add<T>(string statementName, T t); /// <summary>
/// 获取DataTable主要针对存储过程
/// </summary>
/// <param name="statementName"></param>
/// <param name="paramObject"></param>
/// <param name="dictParam"></param>
/// <param name="dictParamDirection"></param>
/// <param name="htOutPutParameter"></param>
/// <returns></returns>
DataTable QueryForDataTable(string statementName, object paramObject, IDictionary dictParam, IDictionary<string, ParameterDirection> dictParamDirection, out Hashtable htOutPutParameter); /// <summary>
/// 获取DataTable
/// </summary>
/// <param name="statementName"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
DataTable QueryForDataTable(string statementName, object paramObject); }
BaseDAL：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IBatisNet.DataMapper;
using System.Data;
using IBatisNet.Common;
using IBatisNet.DataMapper.Configuration.Statements;
using IBatisNet.DataMapper.MappedStatements;
using IBatisNet.DataMapper.Scope;
using System.Collections; public class BaseDAL : IBaseDAL
{
ISqlMapper sqlMapper; public BaseDAL()
{ sqlMapper = Mapper.Instance();
} public BaseDAL(ISqlMapper map)
{
sqlMapper = map;
} public int Insert<T>(string statementName, T t)
{
if (sqlMapper != null)
{
IDbCommand cmd = GetDbCommand(statementName, t);//SQL text command
object obj = sqlMapper.Insert(statementName, t);
if (obj != null)
{
return (int)obj;
}
else
return ;
}
return ;
} public int Add<T>(string statementName, T t)
{
if (sqlMapper != null)
{
return sqlMapper.Update(statementName, t);
}
return ;
} public int Update<T>(string statementName, T t)
{
if (sqlMapper != null)
{
IDbCommand cmd = GetDbCommand(statementName, t);//SQL text command
return sqlMapper.Update(statementName, t);
}
return ;
} public int Delete(string statementName, int primaryKeyId)
{
if (sqlMapper != null)
{
return sqlMapper.Delete(statementName, primaryKeyId);
}
return ;
} public int Delete(string statementName, string primaryKeyId)
{
if (sqlMapper != null)
{
return sqlMapper.Delete(statementName, primaryKeyId);
}
return ;
} public int Delete(string statementName, object parameterObject)
{
if (sqlMapper != null)
{ return sqlMapper.Delete(statementName, parameterObject);
}
return ;
} public T Get<T>(string statementName, int primaryKeyId) where T : class
{
if (sqlMapper != null)
{
return sqlMapper.QueryForObject<T>(statementName, primaryKeyId);
}
return null;
} public T Get<T>(string statementName, string primaryKeyId) where T : class
{
if (sqlMapper != null)
{
return sqlMapper.QueryForObject<T>(statementName, primaryKeyId);
}
return null;
} public T Get<T>(string statementName, object parameterObject) where T : class
{
if (sqlMapper != null)
{
IDbCommand cmd = GetDbCommand(statementName, parameterObject);//SQL text command
return sqlMapper.QueryForObject<T>(statementName, parameterObject);
}
return null;
} public object Get(string statementName, object parameterObject)
{
if (sqlMapper != null)
{
IDbCommand cmd = GetDbCommand(statementName, parameterObject);//SQL text command
return sqlMapper.QueryForObject(statementName, parameterObject);
}
return null;
} public IList<T> QueryForList<T>(string statementName, object parameterObject = null)
{
if (sqlMapper != null)
{
IDbCommand cmd = GetDbCommand(statementName, parameterObject);//SQL text command
return sqlMapper.QueryForList<T>(statementName, parameterObject);
}
return null;
} /// <summary>
/// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
/// </summary>
/// <param name="statementName">语句ID</param>
/// <param name="paramObject">语句所需要的参数</param>
/// <returns>得到的DataTable</returns>
public DataTable QueryForDataTable(string statementName, object paramObject)
{
DataSet ds = new DataSet();
bool isSessionLocal = false; IDalSession session = sqlMapper.LocalSession;
if (session == null)
{
session = new IBatisNet.DataMapper.SqlMapSession(sqlMapper);
session.OpenConnection();
isSessionLocal = true;
} IDbCommand cmd = GetDbCommand(statementName, paramObject);//SQL text command try
{
cmd.Connection = session.Connection;
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
adapter.Fill(ds);
}
finally
{
if (isSessionLocal)
{
session.CloseConnection();
}
} return ds.Tables[]; } private IDbCommand GetDbCommand(string statementName, object paramObject)
{
IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement; IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName); ISqlMapSession session = new SqlMapSession(sqlMapper);
if (sqlMapper.LocalSession != null)
{
session = sqlMapper.LocalSession;
}
else
{
session = sqlMapper.OpenConnection();
} RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session); mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);
IDbCommand cmd = session.CreateCommand(CommandType.Text);
cmd.CommandText = request.IDbCommand.CommandText;
return cmd; } /// <summary>
/// 获取DbCommand,主要是针对存储过程
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject">参数</param>
/// <param name="dictParam">参数字段</param>
/// <param name="dictParmDirection">ParameterDirection字典</param>
/// <param name="cmdType"></param>
/// <returns></returns>
protected virtual IDbCommand GetDbCommand(string statementName, object paramObject, IDictionary dictParam, IDictionary<string, ParameterDirection> dictParmDirection, CommandType cmdType)
{
if (cmdType == CommandType.Text)
{
return GetDbCommand(statementName, paramObject);
} IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement;
IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName);
ISqlMapSession session = new SqlMapSession(sqlMapper); if (sqlMapper.LocalSession != null)
{
session = sqlMapper.LocalSession;
}
else
{
session = sqlMapper.OpenConnection();
} RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);
IDbCommand cmd = session.CreateCommand(cmdType);
cmd.CommandText = request.IDbCommand.CommandText;
if (cmdType != CommandType.StoredProcedure || dictParam == null)
{
return cmd;
}
foreach (DictionaryEntry de in dictParam) //存储过程
{
string key = de.Key.ToString();
IDbDataParameter dbParam = cmd.CreateParameter();
dbParam.ParameterName = key;
dbParam.Value = de.Value; if (dictParmDirection != null && dictParmDirection.ContainsKey(key))
{
dbParam.Direction = dictParmDirection[key]; //ParameterDirection
}
cmd.Parameters.Add(dbParam);
}
return cmd;
} /// <summary>
/// 查询返回DataTable，对于包括OUTPUT参数的存储过程同样适用
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject">参数</param>
/// <param name="dictParam">参数字典</param>
/// <param name="dictParamDirection">ParameterDirection字典</param>
/// <param name="htOutPutParameter">返回的Output参数值哈希表</param>
/// <returns></returns>
public DataTable QueryForDataTable(string statementName, object paramObject, IDictionary dictParam, IDictionary<string, ParameterDirection> dictParamDirection, out Hashtable htOutPutParameter)
{ DataSet ds = new DataSet();
bool isSessionLocal = false;
ISqlMapSession session = sqlMapper.LocalSession;
if (session == null)
{
session = new SqlMapSession(sqlMapper);
session.OpenConnection();
isSessionLocal = true;
} IDbCommand cmd = GetDbCommand(statementName, paramObject, dictParam, dictParamDirection, CommandType.StoredProcedure); //存储过程 try
{
cmd.Connection = session.Connection;
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
adapter.Fill(ds);
}
finally
{
if (isSessionLocal)
{
session.CloseConnection();
}
}
htOutPutParameter = new Hashtable();
foreach (IDataParameter parameter in cmd.Parameters)
{
if (parameter.Direction == ParameterDirection.Output)
{
htOutPutParameter[parameter.ParameterName] = parameter.Value;
}
}
return ds.Tables[];
} }
BLL调用：
public DataTable GetTest(Hashtable hs)
{
DataTable dt = new DataTable();
IBaseDAL dal = new BaseDAL();
dt = dal.QueryForDataTable("TestMapping.GetByid", hs); return dt;
}
至此，整个流程结束。