.NET(C#) Dapper Oracle(ODP.NET)或SQL Server 执行多条查询(select)语句的方法代码

本文主要介绍.NET(C#) Dapper Oracle或SQL server中，在一次数据库连接中，执行多条select查询语句，并且获取到相对应的查询结果。

 

1、查询SQL语名

Dapper似乎只是直接将SQL命令传递给ADO.NET以及正在执行该命令的任何db提供程序。在示例的语法中，每个命令由一个换行符分隔，SQL Server将其解释为针对数据库运行的多个查询，并将运行每个查询并将结果返回到单独的输出中。

1）SQL Server查询句

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

2）Oracle查询句

Oracle不能识别多个查询;它认为SQL命令格式不正确，并返回ORA-00933消息。解决方案是使用游标并在DynamicParameters集合中返回输出。

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

2、执行多条查询(select)语句实现代码

将结果集返回到游标参数，所以需要使用IDynamicParameters集合来指定命令的参数。 要添加额外的皱纹，Dapper中的常规DynamicParameters.Add()方法使用System.Data.DbType作为可选的dbType参数，但查询的游标参数必须是Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor类型，为了解决这个问题，创建了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();
}

参考文档：https://stackoverflow.com/questions/18772781/using-dapper-querymultiple-in-oracle