NET Dapper 基于 Oracle 参数化查询，返回多个结果集

1.添加Nuget包：

Dapper
Oracle.ManagedDataAccess

2.添加帮助类：OracleDynamicParameters

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Collections.Generic;

namespace xOracleDal.CustomEntitys
{
    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());
            }
        }
    }
}

3.后端Sql

BEGIN
OPEN :RSLT1 FOR SELECT COUNT(1) FROM Tabl1 WHERE Pk = :Pk;
OPEN :RSLT2 FOR SELECT COUNT(1) FROM Tabl2 WHERE AddUser = :AddUser;
OPEN :RSLT3 FOR SELECT COUNT(1) FROM Tabl3 WHERE Uk = :Uk;
END;

4.构建：Mssql关键字是[@]，Oracle关键字是[:]

            var dynParams = new OracleDynamicParameters();

            // 返回值：添加的顺序决定取值的顺序
            dynParams.Add(":rslt1", OracleDbType.RefCursor, ParameterDirection.Output);
            dynParams.Add(":rslt2", OracleDbType.RefCursor, ParameterDirection.Output);
            dynParams.Add(":rslt3", OracleDbType.RefCursor, ParameterDirection.Output);
            // 参数
            dynParams.Add(":Pk", OracleDbType.Varchar2, ParameterDirection.Input, do.Pk);
            dynParams.Add(":AddUser", OracleDbType.Varchar2, ParameterDirection.Input, do.AddUser);
            dynParams.Add(":Uk", OracleDbType.Varchar2, ParameterDirection.Input, do.Uk);

5.调用

        public void QueryMultiple(string sql, OracleDynamicParameters param)
        {
　　　　　　  using (IDbConnection connection = new OracleConnection("User Id=AAA;Password=BBB;Connect Timeout=120; Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=CCC)(PORT=DDD)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=EEE)))"))
            {
                SqlMapper.GridReader reader = connection.QueryMultiple(sql, param);
                var num1 = reader.Read<int>().FirstOrDefault();
                var num2 = reader.Read<int>().FirstOrDefault();
                var lnum3 = reader.Read<int>().FirstOrDefault();
            }
        }