使用Dapper讀取Oracle多個結果集

標籤：

Dapper對SQL Server支援很好，但對於Oracle有些用法不一樣，需要自己進行特殊處理。

1、首先要自訂一個Oracle參數類

 1  public class OracleDynamicParameters : SqlMapper.IDynamicParameters 2     { 3         private readonly DynamicParameters dynamicParameters = new DynamicParameters(); 4  5         private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>(); 6  7         public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null) 8         { 9             OracleParameter oracleParameter;10             if (size.HasValue)11             {12                 oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);13             }14             else15             {16                 oracleParameter = new OracleParameter(name, oracleDbType, value, direction);17             }18 19             oracleParameters.Add(oracleParameter);20         }21 22         public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)23         {24             var oracleParameter = new OracleParameter(name, oracleDbType, direction);25             oracleParameters.Add(oracleParameter);26         }27 28         public void AddParameters(IDbCommand command, SqlMapper.Identity identity)29         {30             ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);31 32             var oracleCommand = command as OracleCommand;33 34             if (oracleCommand != null)35             {36                 oracleCommand.Parameters.AddRange(oracleParameters.ToArray());37             }38         }39     }

OracleDynamicParameters

 

2、對於一個sql語句中的多個結果集處理

 string sql = @"beginopen :rslt1 for select * from t_um_event where rownum<10and c_fid>:fid;open :rslt2 for select count(*) as count from t_um_event;end;                    ";                OracleDynamicParameters dynParams = new OracleDynamicParameters();                dynParams.Add(":rslt1", OracleDbType.RefCursor, ParameterDirection.Output);                dynParams.Add(":rslt2", OracleDbType.RefCursor, ParameterDirection.Output);                dynParams.Add(":fid", OracleDbType.Int64, ParameterDirection.Input, 1000);                using (var muti = cn.QueryMultiple(sql, param: dynParams))                {                    List<TestClass> firstRes = muti.Read<TestClass>().ToList();                    RecordCount secondRes = muti.ReadFirstOrDefault<RecordCount>();                }

    class TestClass    {        public string C_ID        {            get;            set;        }        public DateTime? C_REG_TIME        {            get;            set;        }        public string C_Title        {            get;            set;        }    }    class RecordCount    {        public int Count { get; set; }    }

2、對於oracle預存程序的多個結果集處理，預存程序定義

create or replace package pkg_test_dapper is  TYPE t_cursor IS REF CURSOR;  procedure p_get_list(cur_out1 out t_cursor,                       p_fid    int,                       cur_out2 out t_cursor);  procedure p_get_count(cur_out out t_cursor);end pkg_test_dapper;/create or replace package body pkg_test_dapper is  procedure p_get_list(cur_out1 out t_cursor,                       p_fid    int,                       cur_out2 out t_cursor) as  begin    open cur_out1 for      select *        from t_um_event       where rownum < 10         and c_fid > p_fid;    open cur_out2 for      select count(*) as count from t_um_event;  end;  procedure p_get_count(cur_out out t_cursor) as  begin    open cur_out for      select count(*) as count from t_um_event;  end;end pkg_test_dapper;/

c#中用dapper調用預存程序返回oracle多個結果集

   using (IDbConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings["ZWGCDB"].ConnectionString))            {                TestClass Evt = cn.QueryFirstOrDefault<TestClass>("select * from t_um_event where c_id=:Id", new { id = "1BA2BF30-658A-4A79-A179-05A77C527150" });                //int cnt = cn.Execute("update t_um_event set c_reg_time=:reg_time where c_id=:id", new { id = "CEA00DA2-79D2-48CC-A9E1-D3CBB3842E54", reg_time = DateTime.Now });                              OracleDynamicParameters dynParams = new OracleDynamicParameters();                dynParams.Add("cur_out1", OracleDbType.RefCursor, ParameterDirection.Output);                dynParams.Add("cur_out2", OracleDbType.RefCursor, ParameterDirection.Output);                dynParams.Add("p_fid", OracleDbType.Int64, ParameterDirection.Input, 1000);                using (var muti = cn.QueryMultiple("pkg_test_dapper.p_get_list", dynParams, commandType: CommandType.StoredProcedure))                {                    List<TestClass> firstRes = muti.Read<TestClass>().ToList();                    RecordCount secondRes = muti.ReadFirstOrDefault<RecordCount>();                }            }

參考：http://stackoverflow.com/questions/18772781/using-dapper-querymultiple-in-oracle

使用到的包檔案如下：

<?xml version="1.0" encoding="utf-8"?><packages>  <package id="Dapper" version="1.50.2" targetFramework="net40" />  <package id="Oracle.ManagedDataAccess" version="12.1.24160419" targetFramework="net40" /></packages>

 

自：http://weiweictgu.cnblogs.com/

使用Dapper讀取Oracle多個結果集