Использование Dapper QueryMultiple в Oracle

Ответ 1

ОП, возможно, уже давно решил эту проблему, но на момент написания этого вопроса на этот вопрос был только один ответ, и он не решал проблему использования метода Dapper QueryMultiple() с Oracle. Как правильно утверждает @Kamolas81, используя синтаксис из официальных примеров, вы действительно получите сообщение об ошибке " ORA-00933: SQL command not properly ended. Я потратил некоторое время на поиск какой-то документации о том, как сделать QueryMultiple() с Oracle, но был удивлен, что на самом деле не было ни одного места, где был бы ответ. Я бы подумал, что это довольно распространенная задача. Я думал, что я опубликую ответ здесь, чтобы спасти меня :) кто-то когда-нибудь в будущем на случай, если у кого-то случится такая же проблема.

Кажется, что Даппер просто передает команду SQL прямо в ADO.NET и любой провайдер базы данных, выполняющий эту команду. В синтаксисе из примеров, где каждая команда отделяется разрывом строки, сервер SQL будет интерпретировать это как несколько запросов к базе данных, и он будет выполнять каждый из запросов и возвращать результаты в отдельные выходные данные. Я не специалист по ADO.NET, поэтому, возможно, я испортил терминологию, но в конечном итоге Dapper получает несколько выходных запросов, а затем выполняет свою магию.

Oracle, однако, не распознает несколько запросов; он считает, что команда SQL искажена, и возвращает сообщение ORA-00933. Решение состоит в том, чтобы использовать курсоры и возвращать выходные данные в коллекции DynamicParameters. Например, тогда как версия SQL Server будет выглядеть так:

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

версия запроса Oracle должна выглядеть следующим образом:

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;";

Для запросов, запускаемых к SQL Server, Dapper может обработать его оттуда. Однако, поскольку мы возвращаем результирующие наборы в параметры курсора, нам необходимо использовать коллекцию IDynamicParameters чтобы указать параметры для команды. Чтобы добавить дополнительную складку, обычный метод DynamicParameters.Add() в Dapper использует System.Data.DbType для необязательного параметра dbType, но параметры курсора для запроса должны иметь тип Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor, Чтобы решить эту проблему, я использовал решение, которое @Daniel Smith предложил в этом ответе, и создал пользовательскую реализацию интерфейса 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());
        }
    }
}

Таким образом, весь код вместе выглядит примерно так:

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

Ответ 2

Основываясь на полезном ответе greyseal96, я создал эту реализацию IDynamicParameters:

public class OracleDynamicParameters : SqlMapper.IDynamicParameters
{
    private readonly DynamicParameters dynamicParameters;

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

    public OracleDynamicParameters(params string[] refCursorNames) {
        dynamicParameters = new DynamicParameters();
        AddRefCursorParameters(refCursorNames);
    }

    public OracleDynamicParameters(object template, params string[] refCursorNames) {
        dynamicParameters = new DynamicParameters(template);
        AddRefCursorParameters(refCursorNames);
    }

    private void AddRefCursorParameters(params string[] refCursorNames)
    {
        foreach (string refCursorName in refCursorNames)
        {
            var oracleParameter = new OracleParameter(refCursorName, OracleDbType.RefCursor, ParameterDirection.Output);
            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());
        }
    }
}

Предположив тот же запрос, его можно использовать так:

var queryParams = new { id };
string[] refCursorNames = { "rslt1", "rslt2", "rslt3" };
var dynParams = new OracleDynamicParameters(queryParams, refCursorNames);
...
var multi = dbConn.QueryMultiple(sql, param: dynParams);

Ответ 3

Я подозреваю, что это две или три отдельные вещи:

• В вашем первом запросе не должно быть полуколонии
• Между запросами не существует символа новой строки
• В примечаниях к использованию подразумевается, что символ привязки @ not : (не знаю, зависит ли это от используемой СУБД).

Если вы посмотрите страницу Dapper Google Code, приведенный ниже для QueryMultiple():

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

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
} 

Удалите половину двоеточия; добавьте новую строку, и если у вас все еще есть проблемы, измените символ привязки.