c# - QueryMultiple 结果集顺序已更改

Question

我正在使用 QueryMultiple 执行存储过程以返回多组数据。

var gridReader = db.QueryMultiple("sp", 
                                  parameters, 
                                  commandType: CommandType.StoredProcedure);

如果我知道它们返回的顺序，我可以很容易地得到每套。

SELECT * FROM dbo.Set1;
SELECT * FROM dbo.Set2;
SELECT * FROM dbo.Set3;

var set1 = gridReader.Read<Set1>();
var set2 = gridReader.Read<Set2>();
var set3 = gridReader.Read<Set3>();

但是，我的情况是他们回来的顺序可能会改变。另一个开发人员可以出于任何原因决定更改顺序。存储过程现在变成这样:

SELECT * FROM dbo.Set1;
SELECT * FROM dbo.Set3;
SELECT * FROM dbo.Set2;

我该如何处理？

我最初的尝试是遍历每个网格，检查列名。起初这似乎工作得很好，但除了手动设置每个字段之外，我无法弄清楚如何将网格投影到一个类中。我使用 Dapper 的主要原因是它可以为我做这件事。

while (true)
{
    var grid = gridReader.Read();
    IDictionary<string, object> row = grid.FirstOrDefault();

    if (row == null)
        break;

    if (row.Keys.Contains("Set1_UniqueColumnName"))
    {
        // Need something like grid.Read<Set1>();
    }
    else if (row.Keys.Contains("Set2_UniqueColumnName")) { }
    else if (row.Keys.Contains("Set3_UniqueColumnName")) { }
}

我的第二个想法是将每个网格读入一个类，检查该类的唯一字段是否有空值/默认值，如果测试失败则尝试下一个类。但这显然行不通。 .Read() 将返回下一个 结果网格。此解决方案要求我能够一遍又一遍地读取相同的网格。

Answer 1

Dapper 提供了一个IDataReader.GetRowParser 扩展方法，可以实现每行的类型切换。来自 Dapper 文档 here ...

Usually you'll want to treat all rows from a given table as the same data type. However, there are some circumstances where it's useful to be able to parse different rows as different data types. This is where IDataReader.GetRowParser comes in handy.

Imagine you have a database table named "Shapes" with the columns: Id, Type, and Data, and you want to parse its rows into Circle, Square, or Triangle objects based on the value of the Type column.

var shapes = new List<IShape>();
using (var reader = connection.ExecuteReader("select * from Shapes"))
{
    // Generate a row parser for each type you expect.
    // The generic type <IShape> is what the parser will return.
    // The argument (typeof(*)) is the concrete type to parse.
    var circleParser = reader.GetRowParser<IShape>(typeof(Circle));
    var squareParser = reader.GetRowParser<IShape>(typeof(Square));
    var triangleParser = reader.GetRowParser<IShape>(typeof(Triangle));

    var typeColumnIndex = reader.GetOrdinal("Type");

    while (reader.Read())
    {
        IShape shape;
        var type = (ShapeType)reader.GetInt32(typeColumnIndex);
        switch (type)
        {
            case ShapeType.Circle:
                shape = circleParser(reader);
                break;
            case ShapeType.Square:
                shape = squareParser(reader);
                break;
            case ShapeType.Triangle:
                shape = triangleParser(reader);
                break;
            default:
                throw new NotImplementedException();
        }

        shapes.Add(shape);
    }
}

您需要访问 GridReader 包装的 IDataReader 或更改您的代码以使用良好的老式 ADO.NET SqlConnection & SqlCommand 这样的对象...

using (command = new SqlCommand("sp", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddRange(parameters);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // read row columns
        }
    }
}