Cómo mezclar sentencias SQL ejecutables y queriesables en Dapper

dapper

Pregunta

I Dapper Puedo ejecutar algunos SQL que no dan una respuesta usando Execute :

var sql = @"INSERT INTO Items (id, userId, name, description, isPublic) VALUES (@id, @userId, @name, @description, @isPublic)";
using (var connection = new SqlConnection(ConnectionString))
{
    connection.Execute(sql, new
    {
        id = value.Id,
        userId = value.UserId,
        name = value.Name,
        description = value.Description,
        isPublic = value.IsPublic
    });
}

Puedo consultar usando Query :

var sql = @"SELECT * FROM Items WHERE id = @id";
using (var connection = new SqlConnection(ConnectionString))
{
    var item = connection.Query<Item>(sql, new { id = id }).Single();
    return item;
}

Usando [QueryMultiple]() puedo enviar varias consultas simultáneamente:

var sql = @"SELECT * FROM Collections WHERE id = @collectionId
            SELECT * FROM Items WHERE id = @itemId";
using (var connection = new SqlConnection(ConnectionString))
{
    var multi = connection.QueryMultiple(sql, new { collectionId = collectionId, itemId = itemId });
    //...

¿Cómo podría ejecutar y consultar de una vez? Es decir, cómo llamaría a esta combinación:

var sql = @"INSERT INTO Items (id, userId, name, description, isPublic) VALUES (@id, @userId, @name, @description, @isPublic)
            SELECT * FROM Items WHERE id = @itemId";"

Respuesta popular

Es bastante estrecho hacia adelante:

[TestFixture]
public class DapperTests
{
    private SqlConnection _sqlConnection;

    [SetUp]
    public void Setup()
    {
        _sqlConnection = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=MyDb");
        _sqlConnection.Open();

        _sqlConnection.Execute(@"IF (NOT EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND  TABLE_NAME = 'temp'))
            BEGIN
                create table temp
                (
                    Id int IDENTITY(1,1),
                    Value varchar(10)
                )
            END");
    }

    [TearDown]
    public void Teardown()
    {
        _sqlConnection.Close();
    }

    [Test]
    public void Test_Basic_Insert_Select()
    {
        var result = _sqlConnection.Query<int>("SELECT @value as Id into #temp; select Id from #temp where Id = @value", new { value = 1 }).Single();

        Assert.That(result, Is.EqualTo(1));
    }

    [Test]
    public void Test_Basic_Insert_Select_QueryMultiple()
    {
        var result = _sqlConnection.QueryMultiple("insert into temp(Value) select @value; select 1 as foo; select 'bar' as bar", new { value = 1 });

        var id = result.Read<int>().Single();
        var bar = result.Read<string>().Single();

        Assert.That(id, Is.EqualTo(1));
        Assert.That(bar, Is.EqualTo("bar"));
    }

    [Test]
    public void Test_Identity()
    {
        var result = _sqlConnection.Query<int>("insert into temp(Value) select @value ;select Id from temp where Id = (SELECT SCOPE_IDENTITY())", new { value = 1 });

        Assert.That(result.Count(), Is.GreaterThan(0));
    }
}


Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué