IBatis.Net使用总结（二）-- IBatis返回DataTable/DataSet（网上例子的集合）

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             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         }

查询返回DatatTable

 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         }

iBatisNet 1.6版本返回DataSet

 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             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         }

利用反射返回DataSet

以下是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             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         }

获取DbCommand

这种返回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             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         }

返回DataTable

如果参数中，包含有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             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         }

返回DataTable，包含output参数

参考链接：

原文地址：ibatis 返回DataTable和DataSet作者：happytor

原文地址：[IBatisNet]关于返回DataTable的一点问题作者：Daniel Pang

posted @ 2015-11-05 10:01 weiqinl 阅读(522) 评论(0) 编辑收藏举报

刷新页面返回顶部

web知识-平时工作生活中的总结

有志于web前端开发 https://github.com/weiqinl https://weiqinl.com

IBatis.Net使用总结（二）-- IBatis返回DataTable/DataSet（网上例子的集合）

公告