IBatis.Net使用總結（二）--IBatis返回DataTable/DataSet（網上例子的集合）

2019-11-14 13:49:04

字體：大中小

來源：轉載

供稿：網友

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注入，因為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         }

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