Usando Dapper QueryMultiple en Oracle

dapper oracle

Pregunta

Estoy tratando de usar dapper con Oracle (ODP.NET) y me gustaría usar la funcionalidad "QueryMultiple".

Pasando esta cadena al método QueryMultiple:

 var query = "Select CUST_ID CustId from Customer_info WHERE CUST_ID=:custId;" +
                   "Select CUST_ID CustId from BCR WHERE CUST_ID=:custId";

Obtengo un ORA-00911: error de carácter no válido

¿Hay alguna manera de hacer esto o no es posible?

Tks

Respuesta popular

Es probable que OP haya resuelto el problema desde hace mucho tiempo, pero en el momento de la redacción, esta pregunta solo tiene una respuesta y realmente no resuelve el problema de usar el método QueryMultiple() Dapper con Oracle. Como @ Kamolas81 declara correctamente, al utilizar la sintaxis de los ejemplos oficiales, uno de hecho obtendrá el mensaje de error ORA-00933: SQL command not properly ended . Pasé un tiempo buscando algún tipo de documentación sobre cómo hacer QueryMultiple() con Oracle, pero me sorprendió que en realidad no hubiera un solo lugar que tuviera una respuesta. Habría pensado que esto es una tarea bastante común. Pensé que había puesto una respuesta aquí para salvarme :) alguien algún momento en el futuro por si alguien pasa a tener este mismo problema.

Parece que Dapper simplemente pasa el comando SQL directamente a ADO.NET y cualquier proveedor de db está ejecutando el comando. En la sintaxis de los ejemplos, donde cada comando está separado por un salto de línea, el servidor SQL interpretará eso como múltiples consultas para ejecutar en la base de datos y ejecutará cada una de las consultas y devolverá los resultados a resultados separados. No soy un experto en ADO.NET, así que podría estar arruinando la terminología, pero el efecto final es que Dapper obtiene los múltiples resultados de la consulta y luego hace su magia.

Oracle, sin embargo, no reconoce las múltiples consultas; piensa que el comando SQL tiene un formato incorrecto y devuelve el mensaje ORA-00933 . La solución es usar cursores y devolver el resultado en una colección DynamicParameters. Por ejemplo, mientras que la versión de SQL Server se vería así:

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

La versión de Oracle de la consulta debería verse así:

var sql = "BEGIN OPEN :rslt1 FOR SELECT * FROM customers WHERE customerid = :id; " +
                "OPEN :rslt2 FOR SELECT * FROM orders WHERE customerid = :id; " +
                "OPEN :rslt3 FOR SELECT * FROM returns Where customerid = :id; " +
          "END;";

Para consultas ejecutadas contra SQL Server, Dapper puede manejarlo desde allí. Sin embargo, debido a que estamos devolviendo los conjuntos de resultados a los parámetros del cursor, necesitaremos usar una colección IDynamicParameters para especificar los parámetros para el comando. Para agregar una arruga adicional, el método DynamicParameters.Add() normal en Dapper usa un System.Data.DbType para el parámetro opcional dbType, pero los parámetros del cursor para la consulta deben ser del tipo Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor . Para resolver esto, utilicé la solución que @Daniel Smith propuso en esta respuesta y creé una implementación personalizada de la interfaz de IDynamicParameters :

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

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());
        }
    }
}

Así que todo el código juntos es algo como esto:

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

int selectedId = 1;
var sql = "BEGIN OPEN :rslt1 FOR SELECT * FROM customers WHERE customerid = :id; " +
                "OPEN :rslt2 FOR SELECT * FROM orders WHERE customerid = :id; " +
                "OPEN :rslt3 FOR SELECT * FROM returns Where customerid = :id; " +
          "END;";

OracleDynamicParameters 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(":id", OracleDbType.Int32, ParameterDirection.Input, selectedId);

using (IDbConnection dbConn = new OracleConnection("<conn string here>"))
{
    dbConn.Open();
    var multi = dbConn.QueryMultiple(sql, param: dynParams);

    var customer = multi.Read<Customer>().Single();
    var orders = multi.Read<Order>().ToList();
    var returns = multi.Read<Return>().ToList();
    ...
    dbConn.Close();
}


Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow