如題。要返回一個ADO.NET對象好像沒有使用ORM的必要，而且從編程的角度看這樣的實現一點也不OO，但是實際的開發場景中還是會碰到這種需求的。下面我就借鑒前人的經驗，結合實際的示例，再總結一下。如果您認真看完，應該可以體會得到我的一些嘗試，而不是人云亦云的照搬代碼。

1、獲得DbCommand對象

對于SQL語句，方法如下：

/// <summary>/// SQL語?句?，?獲?取?DbCommand/// </summary>/// <param name="sqlMapper"></param>/// <param name="statementName"></param>/// <param name="paramObject"></param>/// <returns></returns>protected virtual IDbCommand GetDbCommand(ISqlMapper sqlMapper, string statementName, object paramObject){IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement;IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName);ISqlMapSession session = new SqlMapSession(sqlMapper);if (sqlMapper.LocalSession != null){session = sqlMapper.LocalSession;}else{session = sqlMapper.OpenConnection();}RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);IDbCommand cmd = session.CreateCommand(CommandType.Text);cmd.CommandText = request.IDbCommand.CommandText;//return request.IDbCommand;return cmd;}

對于存儲過程，因為對于參數類型的不同，需要多幾步處理(因為需要多維護一個參數字典和其對應的ParameterDirection字典)：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

/// <summary> /// 獲取DbCommand,主要是針對存儲過程 /// </summary> /// <param name="sqlMapper"></param> /// <param name="statementName"></param> /// <param name="paramObject">參數</param> /// <param name="dictParam">參數字段</param> /// <param name="dictParmDirection">ParameterDirection字典</param> /// <param name="cmdType"></param> /// <returns></returns> protected?virtual?IDbCommand GetDbCommand(ISqlMapper sqlMapper, string?statementName, object?paramObject, IDictionary dictParam, IDictionary<string, ParameterDirection> dictParmDirection, CommandType cmdType) { ????if?(cmdType == CommandType.Text) ????{ ????????return?GetDbCommand(sqlMapper, statementName, paramObject); ????} ????IStatement statement = sqlMapper.GetMappedStatement(statementName).Statement; ????IMappedStatement mapStatement = sqlMapper.GetMappedStatement(statementName); ????ISqlMapSession session = new?SqlMapSession(sqlMapper); ????if?(sqlMapper.LocalSession != null) ????{ ????????session = sqlMapper.LocalSession; ????} ????else ????{ ????????session = sqlMapper.OpenConnection(); ????} ????RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session); ????mapStatement.PreparedCommand.Create(request, session as?ISqlMapSession, statement, paramObject); ????IDbCommand cmd = session.CreateCommand(cmdType); ????cmd.CommandText = request.IDbCommand.CommandText; ????if?(cmdType != CommandType.StoredProcedure || dictParam == null) ????{ ????????return?cmd; ????} ????foreach?(DictionaryEntry de in?dictParam) //存儲過程 ????{ ????????string?key = de.Key.ToString(); ????????IDbDataParameter dbParam = cmd.CreateParameter(); ????????dbParam.ParameterName = key; ????????dbParam.Value = de.Value; ????????if?(dictParmDirection != null?&& dictParmDirection.ContainsKey(key)) ????????{ ????????????dbParam.Direction = dictParmDirection[key]; //ParameterDirection ????????} ????????cmd.Parameters.Add(dbParam); ????} ????return?cmd; }

代碼寫得可能還有改進的必要，有需要從事這方面開發的童鞋，如果您看著有更好的辦法請不吝賜教。

備注：

a、對于1.6.1之前的版本，獲得命令的方式可以通過RequestScope的IDbCommand屬性，但是1.6.1版本的IDbCommand屬性返回的是IBatisNet.DataMapper.Commands.DbCommandDecorator對象，您可以注釋代碼驗證一下。

b、網上有些文章貼的方法返回的DbCommand對象都是對于拼接SQL語句而言，沒有實現獲取存儲過程的DbCommand(有參數無參數的都要考慮)。本文在原有資料的基礎上，嘗試著做出改進，目前支持SQL語句和存儲過程。

2、返回DataSet對象

通過SQL語句，獲取DataSet：

1 2 3 4 5

public?DataSet GetDSPerson(int?id) ???{ ???????string?sql = this.GetRuntimeSql(this.SqlMapper, this.GetStatementName("GetDSPerson"), id); ???????return?this.QueryForDataSet(this.SqlMapper, this.GetStatementName("GetDSPerson"), id); ???}

XML配置：

1 2 3 4

<select?id="GetDSPerson" parameterClass="int" resultClass="System.Data.DataSet"> ??<include?refid="CommonPersonColumns4Select"></include> ??WHERE 1=1 AND Id=$id$ </select>

客戶端的調用：

1 2 3

int?id = 1; DataSet ds = ServiceFactory.CreatePersonService().GetDSPerson(id); Console.WriteLine(ds.GetXml());

執行結果返回如下：

3、返回DataTable對象

a、通過SQL語句

/// <summary>/// 通?用?的?執′行DSQL語?句?以?DataTable的?方?式?得?到?返う?回?的?結á果?(xml文?件t中D參?數簓要癮使?用?$標括?記?的?占?位?參?數簓)/// </summary>/// <param name="sqlMapper"></param>/// <param name="statementName"></param>/// <param name="paramObject"></param>/// <returns></returns>protected virtual DataTable QueryForDataTable(ISqlMapper sqlMapper, string statementName, object paramObject){DataSet ds = new DataSet();bool isSessionLocal = false;IDalSession session = sqlMapper.LocalSession;if (session == null){session = new SqlMapSession(sqlMapper);session.OpenConnection();isSessionLocal = true;}IDbCommand cmd = GetDbCommand(sqlMapper, statementName, paramObject);//SQL text commandtry{cmd.Connection = session.Connection;IDbDataAdapter adapter = session.CreateDataAdapter(cmd);adapter.Fill(ds);}finally{if (isSessionLocal){session.CloseConnection();}}return ds.Tables[0];}

這個相對簡單，因為前面2中已經得到了DataSet，DataTable的提取就輕而易舉了。

b、通過含OUTPUT參數的存儲過程

這個地方主要就是改進后的GetDbCommand重載方法的使用，

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

/// <summary> ??/// 查詢返回DataTable，對于包括OUTPUT參數的存儲過程同樣適用 ??/// </summary> ??/// <param name="sqlMapper"></param> ??/// <param name="statementName"></param> ??/// <param name="paramObject">參數</param> ??/// <param name="dictParam">參數字典</param> ??/// <param name="dictParamDirection">ParameterDirection字典</param> ??/// <param name="htOutPutParameter">返回的Output參數值哈希表</param> ??/// <returns></returns> ??protected?virtual?DataTable QueryForDataTable(ISqlMapper sqlMapper, string?statementName, object?paramObject, IDictionary dictParam, IDictionary<string, ParameterDirection> dictParamDirection, out?Hashtable htOutPutParameter) ??{ ??????DataSet ds = new?DataSet(); ??????bool?isSessionLocal = false; ??????ISqlMapSession session = sqlMapper.LocalSession; ??????if?(session == null) ??????{ ??????????session = new?SqlMapSession(sqlMapper); ??????????session.OpenConnection(); ??????????isSessionLocal = true; ??????} ??????IDbCommand cmd = GetDbCommand(sqlMapper, statementName, paramObject, dictParam, dictParamDirection, CommandType.StoredProcedure); //存儲過程 ??????try ??????{ ??????????cmd.Connection = session.Connection; ??????????IDbDataAdapter adapter = session.CreateDataAdapter(cmd); ??????????adapter.Fill(ds); ??????} ??????finally ??????{ ??????????if?(isSessionLocal) ??????????{ ??????????????session.CloseConnection(); ??????????} ??????} ??????htOutPutParameter = new?Hashtable(); ??????foreach?(IDataParameter parameter in?cmd.Parameters) ??????{ ??????????if?(parameter.Direction == ParameterDirection.Output) ??????????{ ??????????????htOutPutParameter[parameter.ParameterName] = parameter.Value; ??????????} ??????} ??????return?ds.Tables[0]; ??}

測試的存儲過程如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

USE [TestDb] GO --根據id查詢某人 并返回所有人中,最大體重,最小身高 CREATE????PROCEDURE?[dbo].[usp_GetPersonById] ????@MaxWeight float?output, ????@MinHeight float?output, ????@Id int AS BEGIN SELECT ????Id, ????FirstName, ????LastName, ????Weight, ????Height FROM ????Person ????WHERE?Id=@Id SET?@MaxWeight= (SELECT?MAX(Weight) FROM?Person) SET?@MinHeight= (SELECT?MIN(Height) FROM?Person) END

本文的示例測試通過，返回的結果如下：

從上圖中，我們可以看到最大體重是200，最矮身高是177。

4、小結和注意點

a、返回ADO.NET對象的方法，iBatis拼接的SQL語句必須通過而不是熟悉的#符號連接，這個會有眾所周知的SQL注入的風險。

b、我還沒有實際嘗試過最新版本的iBatis，對于較新的版本，不知道本文的方法還適不適用。

c、我參考這篇文章的時候，發現說有一個無法返回output參數的問題。我嘗試著重現這個問題。功夫不負有心人，本文示例中我已經實現好了，給自己鼓勵一下。

總結

以上是生活随笔為你收集整理的iBatis.Net实现返回DataTable和DataSet对象的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。