SQL Server 将多个Dapper查询Map到强类型对象？

我有以下存储过程：

CREATE PROCEDURE [dbo].[dapper_test_sproc] 

AS 
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @Status INT = 1
        DECLARE @Msg VARCHAR(256) = 'Hello World'

        DECLARE @StatusLines TABLE
        (
          rule_code VARCHAR(20),
          rule_name VARCHAR(30),
          [status] INT,
          rule_error_msg VARCHAR(256)
        )

        INSERT INTO @StatusLines
        VALUES ('TEST1', 'TEST2', 1, 'TEST3')

        SELECT  @Status,
                @Msg

        SELECT  rule_code,
                rule_name,
                status,
                rule_error_msg
        FROM    @StatusLines
        FOR     XML RAW
    END

在存储过程结束时，您可以看到两个结果集。
我已经在LinqPad中使用了下面的代码成功地查询了数据，但是我很难从数据中获取单个值。我不理解返回类型。

using var connection = this.Connection;

var sql = "dapper_test_sproc";

var p = new DynamicParameters();

var grid = await connection
    .QueryMultipleAsync(
        new CommandDefinition(
            sql,
            p,
            commandType: CommandType.StoredProcedure,
            cancellationToken: this.QueryCancelToken));

LINQPad.Extensions.Dump(grid.Read());
LINQPad.Extensions.Dump(grid.Read());

LinqPad显示以下信息：

例如，调用grid.Read()不会从第一个结果集中返回完整的@Status和@Msg，但是我发现grid.ReadSingle()可以，我不知道为什么。
抛出一个错误，好吧，这是一个对象，但是是什么类型的对象呢？为什么我不能访问任何属性？
我现在有了可以工作的代码，但是代码非常难看。在索引中转换和访问元素似乎很容易出错。有没有更好的方法来做到这一点？

public async Task dapper_test_sproc()
{

    using var connection = this.Connection;

    var sql = "dapper_test_sproc";

    var p = new DynamicParameters();

    var grid = await connection
        .QueryMultipleAsync(
            new CommandDefinition(
                sql,
                p,
                commandType: CommandType.StoredProcedure,
                cancellationToken: this.QueryCancelToken));

    var firstResultSet = await grid.ReadSingleAsync();
    var secondResultSet = await grid.ReadSingleAsync();

    var firstResultSetDict = (IDictionary<String,Object>)firstResultSet;
    var statusBit = (int)firstResultSetDict.ElementAt(0).Value;
    var message = (string)firstResultSetDict.ElementAt(1).Value;
        
    var secondResultSetDict = (IDictionary<String,Object>)secondResultSet;
    var statusLinesXML = (string)secondResultSetDict.Single().Value;
    
    var passwordValidationStatusMessage = new PasswordValidationStatusMessage
    {
        Error = !Convert.ToBoolean(statusBit),
        Message = message
    };

    XmlSerializer serializer = new XmlSerializer(typeof(StatusLinesEnvelope));
    using var reader = new StringReader($"<root>{statusLinesXML}</root>");
    var statusLines = (StatusLinesEnvelope)serializer.Deserialize(reader);

    LINQPad.Extensions.Dump(passwordValidationStatusMessage);
    LINQPad.Extensions.Dump(statusLines);
}

[XmlRoot(ElementName = "row", Namespace = "")]
public class StatusLines
{
    [XmlAttribute(AttributeName = "rule_code")]
    public string RuleCode { get; set; }

    [XmlAttribute(AttributeName = "rule_name")]
    public string RuleName { get; set; }

    [XmlAttribute(AttributeName = "status")]
    public bool Status { get; set; }

    [XmlAttribute(AttributeName = "rule_error_msg")]
    public string RuleErrorMsg { get; set; }
}

[XmlRoot(ElementName = "root")]
public class StatusLinesEnvelope
{
    [XmlElement(ElementName = "row")]
    public List<StatusLines> Row { get; set; }
}

public class PasswordValidationStatusMessage
{
    public bool Error { get; set; }
    public string Message  { get; set; }
}

我对存储和添加的列名做了一点更改。

ALTER PROCEDURE [dbo].[dapper_test_sproc] 

AS 
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @Status INT = 1
        DECLARE @Msg VARCHAR(256) = 'Hello World'

        DECLARE @StatusLines TABLE
        (
          rule_code VARCHAR(20),
          rule_name VARCHAR(30),
          [status] INT,
          rule_error_msg VARCHAR(256)
        )

        INSERT INTO @StatusLines
        VALUES ('TEST1', 'TEST2', 1, 'TEST3')

        -- Add column names
        SELECT  @Status [Status],
                @Msg [Msg]

        -- Add column names
        select (
            SELECT  rule_code,
                    rule_name,
                    status,
                    rule_error_msg
            FROM    @StatusLines
            FOR     XML RAW
        ) as XmlValue
    END

现在代码

using System.Data.SqlClient;
using Dapper;

var c = "connection_string";
var sql = "EXEC [dbo].[dapper_test_sproc]";

using (var connection = new SqlConnection(c))
{
    var res = await connection.QueryMultipleAsync(sql);
    var first = res.Read<First>().ToList();
    var second = res.Read<Second>().First();
    
    first.Dump();
    second.Dump();
}

class First
{
    public int Status {get; set;}
    
    public string Msg {get; set;}
}

class Second 
{
    public string XmlValue {get; set;}
}

不使用列名更新

如果没有列名，您必须使用索引定位（也就是使用存储过程的人）来获取值，命名列总是一个好主意。

using (var connection = new SqlConnection(c))
{
    var res = await connection.QueryMultipleAsync(sql);
    var first = res.Read();
    var second = res.Read();

    var data1 = first.ToList().Select(p => (IDictionary<string, object>)p)
                .Select(o => 
                    new First { 
                        Status = (int)o.Values.ToArray()[0], 
                        Msg = (string)o.Values.ToArray()[1] 
                    }
                );

        
    var data2 = second.ToList().Select(p => (IDictionary<string, object>)p)
                .Select(o => 
                    new Second { 
                        XmlValue = (string)o.Values.ToArray()[0] 
                    }
                );

    data1.Dump();
    data2.Dump();
}

SQL Server 将多个Dapper查询Map到强类型对象？

1条答案

不使用列名更新

相关问题

热门标签

最新问答