在Oracle中使用Dapper QueryMultiple [英] Using Dapper QueryMultiple in Oracle

问题描述

我正在尝试将dapper与Oracle(ODP.NET)结合使用，我想使用"QueryMultiple"功能.

I´m trying to use dapper with Oracle (ODP.NET) and I would like to use the "QueryMultiple" functionality.

将此字符串传递给QueryMultiple方法:

Passing this string to the QueryMultiple method:

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

我收到ORA-00911:无效的字符错误

I´m getting a ORA-00911: invalid character error

有什么方法可以做到吗?

Is there any way to do this or it´s not possible?

Tks

推荐答案

OP可能已经解决了很久了，但是截至撰写本文时，这个问题只有一个答案，并不能真正解决.在Oracle中使用Dapper的QueryMultiple()方法的问题.正如@ Kamolas81正确指出的那样，通过使用官方示例中的语法，确实可以得到ORA-00933: SQL command not properly ended错误消息.我花了一段时间寻找关于如何使用Oracle进行QueryMultiple()的某种文档，但令我惊讶的是，实际上并没有一个地方能找到答案.我会认为这是一个相当常见的任务.我以为我会在这里发布答案以保存 me :)以便将来某人保存，以防万一有人碰巧遇到同样的问题.

The OP has probably long since solved the issue by now, but as of the time of writing, this question has only one answer and it doesn't really solve the problem of using Dapper's QueryMultiple() method with Oracle. As @Kamolas81 correctly states, by using the syntax from the official examples, one will indeed get the ORA-00933: SQL command not properly ended error message. I spent a while searching for some sort of documentation about how to do QueryMultiple() with Oracle, but I was surprised that there wasn't really one place that had an answer. I would have thought this to be a fairly common task. I thought that I'd post an answer here to save me :) someone some time in the future just in case anybody happens to have this same problem.

Dapper似乎只是将SQL命令直接传递给ADO.NET，并且任何执行该命令的数据库提供程序都可以.在示例的语法中，每个命令由换行符分隔，SQL Server会将其解释为针对数据库运行的多个查询，它将运行每个查询并将结果返回到单独的输出中.我不是ADO.NET专家，所以我可能会弄混术语，但是最终结果是Dapper获得了多个查询输出，然后发挥了魔力.

Dapper seems to just pass the SQL command straight along to ADO.NET and whatever db provider is executing the command. In the syntax from the examples, where each command is separated by a line break, SQL server will interpret that as multiple queries to run against the database and it will run each of the queries and return the results into separate outputs. I'm not an ADO.NET expert, so I might be messing up the terminology, but the end effect is that Dapper gets the multiple query outputs and then works its magic.

Oracle无法识别多个查询.它认为SQL命令格式错误，并返回ORA-00933消息.解决方案是使用游标并在DynamicParameters集合中返回输出.例如，SQL Server版本如下所示:

Oracle, though, doesn't recognize the multiple queries; it thinks that the SQL command is malformed and returns the ORA-00933 message. The solution is to use cursors and return the output in a DynamicParameters collection. For example, whereas the SQL Server version would look like this:

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

查询的Oracle版本应如下所示:

the Oracle version of the query would need to look like this:

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集合为命令指定参数.为了增加皱纹，Dapper中的常规DynamicParameters.Add()方法使用System.Data.DbType作为可选的dbType参数，但是查询的游标参数必须为Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor类型.为了解决这个问题，我使用了@Daniel Smith在此答案中提出的解决方案，并创建了IDynamicParameters的自定义实现界面:

For queries run against SQL Server, Dapper can handle it from there. However, because we're returning the result sets into cursor parameters, we'll need to use an IDynamicParameters collection to specify parameters for the command. To add an extra wrinkle, the normal DynamicParameters.Add() method in Dapper uses a System.Data.DbType for the optional dbType parameter, but the cursor parameters for the query need to be of type Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor. To solve this, I used the solution which @Daniel Smith proposed in this answer and created a custom implementation of the IDynamicParameters interface:

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

所以所有代码在一起都是这样的:

So all of the code together goes something like this:

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

这篇关于在Oracle中使用Dapper QueryMultiple的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！