如何在Dapper中混合可执行和可查询的SQL语句

dapper

我Dapper我可以使用Execute执行一些不提供响应的SQL:

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

我可以使用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;
}

使用[QueryMultiple]()我可以同时发送几个查询:

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 });
    //...

我将如何一次执行和查询?即我怎么称呼这个组合:

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

热门答案

这是相当紧张的前进:

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


许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因