How can I execute this sql statement with .NET and Dapper.NET?

Question

I have the following sql query:

BEGIN TRAN;

UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = xxx;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', xxx);

ROLLBACK TRAN;

and this is for a list of id's. eg.

var fooIds = new [] { 1, 2, 3, 4, 5, 6 };

so then I expect this..

BEGIN TRAN;

UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = 1;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', 1);

UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = 2;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', 2);

UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = 3;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', 3);

ROLLBACK TRAN;

Can this be done with Dapper?

NOTE: If the TRAN makes this hard, I can drop that.

Marc Gravell · Accepted Answer · 2015-03-19 09:10:20Z

3

Dapper has only minimal support for altering queries internally (it supports list expansion for IN, literal injection, and some OPTION/UNKNOWN tweaks. You have two options here:

use StringBuilder to create a single large operation that you can execute (this can be parameterized via a dictionary)
move the transaction to ADO.NET rather than TSQL

For the latter, perhaps something like:

using(var tran = conn.BeginTransaction())
{
    try
    {
        conn.Execute(@"
UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = @id;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', @id);",
            fooIds.Select(id => new { id }), transaction: tran);
    }
    finally // in this example, we always want to rollback
    {
        tran.Rollback();
    }
}

answered Mar 19, 2015 at 9:10

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pure.Krome Over a year ago

Ah ok - so I'm trying to grok this .... so this is using Dapper still, but the tran is created manually (instead of in the query) which dapper 'uses' because dapper's just :sparkles: on the existing connection?

Marc Gravell Over a year ago

@Pure.Krome No sparkles required - we're explicitly passing the transaction in as a parameter

Pure.Krome Over a year ago

Ah yeah, I missed the last param when I was reviewing the code snippet, above. Simple - makes perfect sense. cheers! :heart: your work Marc - keep fighting the good fight :) :) :cheers:

Pure.Krome Over a year ago

Oh - and I have to manually conn.Open() before I create the transaction scope (based on your comment in the other answer in this thread)?

Marc Gravell Over a year ago

@Pure that isn't a "transaction scope" - it is an ADO.NET transaction. "Transaction scope" is a separate but related concept. But yes, the connection would need to be open to begin a transaction.

Alex Erygin · Accepted Answer · 2015-03-20 08:04:35Z

1

you can do something like this:

using (var connection = new SqlConnection(yourConnectionString))
{
    connection.Open();
    using (var tx = connection.BeginTransaction())
    {
        foreach (var fooId in fooIds)
        {
            connection.Execute("UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = @id; INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', @id);", new {id = fooId}, tx);
        }

        tx.Rollback();
    }
}

edited Mar 20, 2015 at 8:04

answered Mar 19, 2015 at 9:06

Alex Erygin

3,2401 gold badge25 silver badges23 bronze badges

2 Comments

Marc Gravell Over a year ago

Minor ADO.NET nuisance: you can't connection.BeginTransaction() until you connection.Open()

Pure.Krome Over a year ago

this would do 2 x fooIds.Count round trips to the DB, instead of one trip to the db?

Collectives™ on Stack Overflow

How can I execute this sql statement with .NET and Dapper.NET?

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related