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 ,下面是两种实现. 大约的感觉,如果你在意性能的话,第一种会快些
1 /// <summary> 2 /// 查询返回DatatTable 3 /// </summary> 4 /// <param name="statementName"></param> 5 /// <param name="parameterObject"></param> 6 /// <returns></returns> 7 public DataTable QueryForDataTable(string statementName, object parameterObject) 8 { 9 bool isSessionLocal = false;10 ISqlMapSession session = SqlMap.LocalSession;11 DataTable dataTable = null;12 if (session == null)13 {14 session = SqlMap.CreateSqlMapSession();15 isSessionLocal = true;16 }17 try18 {19 IMappedStatement statement = SqlMap.GetMappedStatement(statementName);20 dataTable = new DataTable(statementName);21 RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameterObject, session);22 statement.PreparedCommand.Create(request, session, statement.Statement, parameterObject);23 using (request.IDbCommand)24 {25 dataTable.Load(request.IDbCommand.ExecuteReader());26 }27 }28 catch29 {30 throw;31 }32 finally33 {34 if (isSessionLocal)35 {36 session.CloseConnection();37 }38 }39 return dataTable;40 }
1 /// <summary> 2 /// iBatisNet 1.6版本 返回DataSet 3 /// </summary> 4 /// <param name="statementName"></param> 5 /// <param name="paramObject"></param> 6 /// <returns></returns> 7 public DataSet QueryForDataSet(string statementName, object paramObject) 8 { 9 DataSet ds = new DataSet();10 ISqlMapper mapper = Mapper.Instance();11 IMappedStatement statement = mapper.GetMappedStatement(statementName);12 if (!mapper.IsSessionStarted)13 {14 mapper.OpenConnection();15 }16 RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);17 statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject);18 IDbCommand command = mapper.LocalSession.CreateCommand(CommandType.Text);19 command.CommandText = scope.IDbCommand.CommandText;20 foreach (IDataParameter pa in scope.IDbCommand.Parameters)21 {22 command.Parameters.Add(new SqlParameter(pa.ParameterName, pa.Value));23 }24 mapper.LocalSession.CreateDataAdapter(command).Fill(ds);25 return ds;26 }
1 public DataSet QueryForDataSet2(string statementName, object parameterObject) 2 { 3 bool isSessionLocal = false; 4 ISqlMapSession session = _sessionStore.LocalSession; 5 DataSet ds = new DataSet(statementName); 6 if (session == null) 7 { 8 session = CreateSqlMapSession(); 9 isSessionLocal = true;10 }11 try12 {13 IMappedStatement statement = GetMappedStatement(statementName);14 RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameterObject, session);15 statement.PreparedCommand.Create(request, session, statement.Statement, parameterObject);16 FieldInfo info = request.IDbCommand.GetType().GetField("_innerDbCommand", BindingFlags.NonPublic | BindingFlags.Instance);17 using (IDbCommand cmd = (IDbCommand)info.GetValue(request.IDbCommand))18 {19 session.CreateDataAdapter(cmd).Fill(ds);20 }21 22 }23 catch24 {25 throw;26 }27 finally28 {29 if (isSessionLocal)30 {31 session.CloseConnection();32 }33 }34 return ds;35 }
以下是1.6.1版本之前返回DataTable的方法。
1 private IDbCommand GetDbCommand(string statementName, object paramObject) 2 { 3 IStatement statement = sqlMap.GetMappedStatement(statementName).Statement; 4 5 IMappedStatement mapStatement = sqlMap.GetMappedStatement(statementName); 6 7 IDalSession session = new SqlMapSession(sqlMap); 8 9 if (sqlMap.LocalSession != null)10 {11 session = sqlMap.LocalSession;12 }13 else14 {15 session = sqlMap.OpenConnection();16 }17 18 RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);19 20 mapStatement.PreparedCommand.Create(request, session, statement, paramObject);21 22 return request.IDbCommand;23 24 }
这种返回DataTable的方式，容易引起Sql注入，因为xml文件中，sql语句需要使用$作为占位符。
1 /// <summary> 2 /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数) 3 /// </summary> 4 /// <param name="statementName">语句ID</param> 5 /// <param name="paramObject">语句所需要的参数</param> 6 /// <returns>得到的DataTable</returns> 7 protected DataTable ExecuteQueryForDataTable(string statementName, object paramObject) 8 { 9 DataSet ds = new DataSet();10 bool isSessionLocal = false;11 IDalSession session = sqlMap.LocalSession;12 if (session == null)13 {14 session = new SqlMapSession(sqlMap);15 session.OpenConnection();16 isSessionLocal = true;17 }18 19 IDbCommand cmd = GetDbCommand(statementName, paramObject);20 21 try22 {23 cmd.Connection = session.Connection;24 IDbDataAdapter adapter = session.CreateDataAdapter(cmd);25 adapter.Fill(ds);26 }27 finally28 {29 if (isSessionLocal)30 {31 session.CloseConnection();32 }33 }34 35 return ds.Tables[0];36 37 }
如果参数中，包含有output参数，则使用下面的方法
1 /// <summary> 2 /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数) 3 /// </summary> 4 /// <param name="statementName">语句ID</param> 5 /// <param name="paramObject">语句所需要的参数</param> 6 /// <param name="htOutPutParameter)">Output参数值哈希表</param> 7 /// <returns>得到的DataTable</returns> 8 protected DataTable ExecuteQueryForDataTable(string statementName, object paramObject, out Hashtable htOutPutParameter) 9 {10 DataSet ds = new DataSet();11 bool isSessionLocal = false;12 IDalSession session = sqlMap.LocalSession;13 if (session == null)14 {15 session = new SqlMapSession(sqlMap);16 session.OpenConnection();17 isSessionLocal = true;18 }19 20 IDbCommand cmd = GetDbCommand(statementName, paramObject);21 22 try23 {24 cmd.Connection = session.Connection;25 IDbDataAdapter adapter = session.CreateDataAdapter(cmd);26 adapter.Fill(ds);27 }28 finally29 {30 if (isSessionLocal)31 {32 session.CloseConnection();33 }34 }35 36 foreach (IDataParameter parameter in cmd.Parameters)37 {38 if (parameter.Direction == ParameterDirection.Output)39 {40 htOutPutParameter[parameter.ParameterName] = parameter.Value;41 }42 }43 44 return ds.Tables[0];45 46 }
参考链接：
原文地址：ibatis 返回DataTable和DataSet作者：happytor
原文地址：[IBatisNet]关于返回DataTable的一点问题 作者：Daniel Pang