SqlKata Transactions

Question

I am looking to use SqlKata for a project. However, part of the project criteria is that queries should be able to be carried out as transactions. Is there a way I can perform a query or multiple queries with MSSQL transactions?

Many thanks.

score 5 · Answer 1 · answered Jul 30 '19 at 14:12

SQLKata uses Dapper as part of the execution of queries. Since Dapper supports transactionScopes, you can wrap your SQL Kata queries in transactions as well

 using System.Transactions;
....
       using (var scope = _db.Connection.BeginTransaction())
       {
            _db.Query("Posts").WhereNull("AuthorId").AsUpdate(new {
               AuthorId = 10
           });
           ...
            scope.Commit();

        }

score 2 · Answer 2 · answered Mar 12 '19 at 20:44

2

currently, there is no direct support for transactions in Sqlkata, it's planned in the near future, for now, you can invoke transactions directly using the db.Statement() method.

db.Statement("BEGIN TRANSACTION");

db.Query("Transactions").Where(...).Update(new {Amount = 100});
db.Query("...").Delete();

if(err) {

    db.Statement("ROLLBACK TRANSACTION");

}

db.Statement("COMMIT TRANSACTION");

answered Mar 12 '19 at 20:44

amd

20,637
6
49
67

Thank you for you response. However, when I try invoke transactions directly using the db.Statement() method, I get the following error: 'The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.' – Ben Mar 19 '19 at 11:29
check this https://stackoverflow.com/questions/10153648/correct-use-of-transactions-in-sql-server-2008 – amd Mar 19 '19 at 11:33
I have this, however I get the following error: System.Data.SqlClient.SqlException: 'Incorrect syntax near 'BEGIN'.' on the BEGIN TRY ` db.Statement("BEGIN TRANSACTION"); db.Statement("BEGIN TRY"); db.Query("Customers").Insert(...); db.Statement("END TRY"); db.Statement("BEGIN CATCH"); db.Statement("ROLLBACK TRANSACTION"); db.Statement("END CATCH"); db.Statement("COMMIT TRANSACTION");` – Ben Mar 19 '19 at 12:06
2

Does SqlKata use a new connection foreach query? – Ben Mar 20 '19 at 12:36

SqlKata Transactions

2 Answers2