使用Dapper读取Oracle多个结果集-无忧网

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

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

                 1
                public
                class
                 OracleDynamicParameters : SqlMapper.IDynamicParameters

                 2
                    {

                 3
                private
                readonly DynamicParameters dynamicParameters = new DynamicParameters();
 4 5privatereadonly List<OracleParameter> oracleParameters = new List<OracleParameter>();
 6 7publicvoid Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)
 8        {
 9            OracleParameter oracleParameter;
10if (size.HasValue)
11            {
12                 oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);
13            }
14else15            {
16                 oracleParameter = new OracleParameter(name, oracleDbType, value, direction);
17            }
1819            oracleParameters.Add(oracleParameter);
20        }
2122publicvoid Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
23        {
24var oracleParameter = new OracleParameter(name, oracleDbType, direction);
25            oracleParameters.Add(oracleParameter);
26        }
2728publicvoid AddParameters(IDbCommand command, SqlMapper.Identity identity)
29        {
30            ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);
3132var oracleCommand = command as OracleCommand;
3334if (oracleCommand != null)
35            {
36                oracleCommand.Parameters.AddRange(oracleParameters.ToArray());
37            }
38        }
39     }

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, 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;
        }
        publicstring C_Title
        {
            get;
            set;
        }
    }

    class RecordCount
    {
        publicint 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;
/createorreplace package body pkg_test_dapper isprocedure p_get_list(cur_out1 out t_cursor,
                       p_fid    int,
                       cur_out2 out t_cursor) asbeginopen cur_out1 forselect*from t_um_event
       where rownum <10and c_fid > p_fid;
    open cur_out2 forselectcount(*) ascountfrom t_um_event;
  end;
  procedure p_get_count(cur_out out t_cursor) asbeginopen cur_out forselectcount(*) ascountfrom 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/

原文：http://www.cnblogs.com/weiweictgu/p/5908446.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！

使用Dapper读取Oracle多个结果集

相关教程推荐

其他课程推荐