你的位置:首页 > ASP.net教程

[ASP.net教程]IBatis.Net使用总结(二)


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     }

查询返回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       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     }

利用反射返回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       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     }

获取DbCommand

这种返回DataTable的方式,容易引起Sql注入,因为

 1     /// <summary> 2     /// 通用的以DataTable的方式得到Select的结果( 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     }

返回DataTable

如果参数中,包含有output参数,则使用下面的方法

 1 /// <summary> 2     /// 通用的以DataTable的方式得到Select的结果( 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     }

返回DataTable,包含output参数

 

参考链接:

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

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