IBatis返回DataTable,DataSet
ibatis.net QueryForDataTable
完整的为ibatis.net 引入datatable支持要改动很多地方,所以描述的是最小化的改动.不过我们可以大概了解一下比较完整的集成要做那些事情.
ibatis.net 的基本运行原理就是获得一个reader后,然后进行循环,对每条记录使用ResultStrategy中的对应实现进行处理,然后返回到结果集.因此,首先,需要实现一个DataTableStrategy 用来为每条记录产生一个新DataRow. 大家可以看到,下面的实现已经绕开了ibatis.net的处理逻辑.
你可以在网上google到一些ibatis返回dataset的代码,可在最新的版本1.6 ibatis.net 这些代码都无法工作,这是因为RequestScope.IDbCommand现在返回的是一个DbCommandDecorator对象实例(一个实现IDbCommand接口并代理一个具体的IDbCommand实现的对象),而DataAdapter的实现,需要对应的idbcommand实现,如 SqlDataAdapter需要SqlCommand.因此,如下代码会导致cast错误
Mapper.LocalSession.CreateDataAdapter(scope.IDbCommand).Fill(dataTable);
这里有两种解法,一是使用datatable.Load方法来装载IDbCommand.ExecuteReader的返回结果,这是可行的
其次是利用反射,实际的idbcommand在DbCommandDecorator中被保存为_innerDbCommand field ,下面是两种实现. 大约的感觉,如果你在意性能的话,第一种会快些
/// <summary> /// 查询返回DatatTable /// </summary> /// <param name="statementName"></param> /// <param name="parameterObject"></param> /// <returns></returns> public DataTable QueryForDataTable(string statementName, object parameterObject) { bool isSessionLocal = false; ISqlMapSession session = SqlMap.LocalSession; DataTable dataTable = null; if (session == null) { session = SqlMap.CreateSqlMapSession(); isSessionLocal = true; } try { IMappedStatement statement = SqlMap.GetMappedStatement(statementName); dataTable = new DataTable(statementName); RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameterObject, session); statement.PreparedCommand.Create(request, session, statement.Statement, parameterObject); using (request.IDbCommand) { dataTable.Load(request.IDbCommand.ExecuteReader()); } } catch { throw; } finally { if (isSessionLocal) { session.CloseConnection(); } } return dataTable; }
查询返回DatatTable
/// <summary> /// iBatisNet 1.6版本 返回DataSet /// </summary> /// <param name="statementName"></param> /// <param name="paramObject"></param> /// <returns></returns> public DataSet QueryForDataSet(string statementName, object paramObject) { DataSet ds = new DataSet(); ISqlMapper mapper = Mapper.Instance(); IMappedStatement statement = mapper.GetMappedStatement(statementName); if (!mapper.IsSessionStarted) { mapper.OpenConnection(); } RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession); statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject); IDbCommand command = mapper.LocalSession.CreateCommand(CommandType.Text); command.CommandText = scope.IDbCommand.CommandText; foreach (IDataParameter pa in scope.IDbCommand.Parameters) { command.Parameters.Add(new SqlParameter(pa.ParameterName, pa.Value)); } mapper.LocalSession.CreateDataAdapter(command).Fill(ds); return ds; }
iBatisNet 1.6版本 返回DataSet
public DataSet QueryForDataSet2(string statementName, object parameterObject) { bool isSessionLocal = false; ISqlMapSession session = _sessionStore.LocalSession; DataSet ds = new DataSet(statementName); if (session == null) { session = CreateSqlMapSession(); isSessionLocal = true; } try { IMappedStatement statement = GetMappedStatement(statementName); RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameterObject, session); statement.PreparedCommand.Create(request, session, statement.Statement, parameterObject); FieldInfo info = request.IDbCommand.GetType().GetField("_innerDbCommand", BindingFlags.NonPublic | BindingFlags.Instance); using (IDbCommand cmd = (IDbCommand)info.GetValue(request.IDbCommand)) { session.CreateDataAdapter(cmd).Fill(ds); } } catch { throw; } finally { if (isSessionLocal) { session.CloseConnection(); } } return ds; }
利用反射返回DataSet
以下是1.6.1版本之前返回DataTable的方法。
private IDbCommand GetDbCommand(string statementName, object paramObject) { IStatement statement = sqlMap.GetMappedStatement(statementName).Statement; IMappedStatement mapStatement = sqlMap.GetMappedStatement(statementName); IDalSession session = new SqlMapSession(sqlMap); if (sqlMap.LocalSession != null) { session = sqlMap.LocalSession; } else { session = sqlMap.OpenConnection(); } RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session); mapStatement.PreparedCommand.Create(request, session, statement, paramObject); return request.IDbCommand; }
获取DbCommand
这种返回DataTable的方式,容易引起Sql注入,因为xml文件中,sql语句需要使用$作为占位符。
/// <summary> /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数) /// </summary> /// <param name="statementName">语句ID</param> /// <param name="paramObject">语句所需要的参数</param> /// <returns>得到的DataTable</returns> protected DataTable ExecuteQueryForDataTable(string statementName, object paramObject) { DataSet ds = new DataSet(); bool isSessionLocal = false; IDalSession session = sqlMap.LocalSession; if (session == null) { session = new SqlMapSession(sqlMap); session.OpenConnection(); isSessionLocal = true; } IDbCommand cmd = GetDbCommand(statementName, paramObject); try { cmd.Connection = session.Connection; IDbDataAdapter adapter = session.CreateDataAdapter(cmd); adapter.Fill(ds); } finally { if (isSessionLocal) { session.CloseConnection(); } } ]; }
返回DataTable
如果参数中,包含有output参数,则使用下面的方法
/// <summary> /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数) /// </summary> /// <param name="statementName">语句ID</param> /// <param name="paramObject">语句所需要的参数</param> /// <param name="htOutPutParameter)">Output参数值哈希表</param> /// <returns>得到的DataTable</returns> protected DataTable ExecuteQueryForDataTable(string statementName, object paramObject, out Hashtable htOutPutParameter) { DataSet ds = new DataSet(); bool isSessionLocal = false; IDalSession session = sqlMap.LocalSession; if (session == null) { session = new SqlMapSession(sqlMap); session.OpenConnection(); isSessionLocal = true; } IDbCommand cmd = GetDbCommand(statementName, paramObject); try { cmd.Connection = session.Connection; IDbDataAdapter adapter = session.CreateDataAdapter(cmd); adapter.Fill(ds); } finally { if (isSessionLocal) { session.CloseConnection(); } } foreach (IDataParameter parameter in cmd.Parameters) { if (parameter.Direction == ParameterDirection.Output) { htOutPutParameter[parameter.ParameterName] = parameter.Value; } } ]; }
返回DataTable,包含output参数
参考链接: