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>查询返回DatatTable
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 try
18 {
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 catch
29 {
30 throw;
31 }
32 finally
33 {
34 if (isSessionLocal)
35 {
36 session.CloseConnection();
37 }
38 }
39 return dataTable;
40 }
1 /// <summary>iBatisNet 1.6版本 返回DataSet
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)利用反射返回DataSet
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 try
12 {
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 catch
24 {
25 throw;
26 }
27 finally
28 {
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)获取DbCommand
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 else
14 {
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>返回DataTable
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 try
22 {
23 cmd.Connection = session.Connection;
24 IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
25 adapter.Fill(ds);
26 }
27 finally
28 {
29 if (isSessionLocal)
30 {
31 session.CloseConnection();
32 }
33 }
34
35 return ds.Tables[0];
36
37 }
如果参数中，包含有output参数，则使用下面的方法
1 /// <summary>返回DataTable，包含output参数
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 try
23 {
24 cmd.Connection = session.Connection;
25 IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
26 adapter.Fill(ds);
27 }
28 finally
29 {
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