How to mix executable and querieable SQL statements in Dapper

Question

How to mix executable and querieable SQL statements in Dapper

dapper

Question

I Dapper I can execute some SQL that does not give a response using 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
    });
}

I can query using 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;
}

Using [QueryMultiple]() I can simultaneously send several queries:

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

How would I execute and query in one go? I.e. how would I call this combination:

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

dumbledad

View more on Stack Overflow

Void Ray · Accepted Answer · 3/4/2016 6:34:58 PM

It's pretty strait forward:

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

How to mix executable and querieable SQL statements in Dapper

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator