使用Dapper读取Oracle多个结果集

Dapper对SQL Server支持很好，但对于Oracle有些用法不一样，需要自己进行特殊处理。

1、首先要自定义一个Oracle参数类

  public class OracleDynamicParameters : SqlMapper.IDynamicParameters

     {

         private readonly DynamicParameters dynamicParameters = new DynamicParameters();

         private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();

         public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)

         {

             OracleParameter oracleParameter;

             if (size.HasValue)

             {

                 oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);

             }

             else

             {

                 oracleParameter = new OracleParameter(name, oracleDbType, value, direction);

             }

             oracleParameters.Add(oracleParameter);

         }

         public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)

         {

             var oracleParameter = new OracleParameter(name, oracleDbType, direction);

             oracleParameters.Add(oracleParameter);

         }

         public void AddParameters(IDbCommand command, SqlMapper.Identity identity)

         {

             ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);

             var oracleCommand = command as OracleCommand;

             if (oracleCommand != null)

             {

                 oracleCommand.Parameters.AddRange(oracleParameters.ToArray());

             }

         }

     }

OracleDynamicParameters

2、对于一个sql语句中的多个结果集处理

 string sql = @"

begin

open :rslt1 for select * from t_um_event where rownum<10

and 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, );

                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, );

                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/