There's still no update in SqlKata.Execution, i want to use Transaction

Any update on this? Really need to use transactions with the queries, other than that, this package is perfect for my project.

@RichardAnderson for now I got transaction by using TransactionScope. it's work with MySQL.

@nauhalf Any codesnippits on how you managed to do that, I'm working with MSSQL, but I might be able to adjust my code to accommodate it.

@RichardAnderson

using(var scope = new TransactionScope()){ ... //all off insert query or update or delete ... scope.Complete(); }

like this

Thanks @nauhalf

This works perfectly for insert / update operations. However it unfortunately does not reverse the .Statement() commands I run through QueryFactory.StatementAsync()

+1 Need this for my application.

@ceastwood any news?

@ahmadmuzavi Any updates? 😢

@angelul I haven't been committing to the SqlKata.Execution project but will take a deeper look this PR afternoon. At first glance I don't think the PR as-is is complete for merging, but hold my reservation.

As an update, I started to take a look and I think we might want to add the support to just a few more methods. I will circle back after work and try to update.

@angelul actually I am looking for a better way to handle transactions, I don't find that passing the transaction in all methods is the right way.

Hi - any update on this at all? This is the ONLY blocker for many of my projects at present, where it's not possible to reverse all queries and statements.

@RichardAnderson why you can't use the solution suggested by @nauhalf ?
I've used this approach and it seems that it works as expected.

I tried using @nauhalf's solution to get the id of the inserted (so that @@IDENTITY is executed in the same scope), but it does not work either.

@myargeau do you mind sharing code that reproduce the issue?, since I am using it with no problem with SqlServer,
just for info, I am working on a release that tackle the transactions issue but it may need sometime to get released.

This gives an error saying 'The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.', Like each statement is in its own context.

db.Statement("BEGIN TRANSACTION");

db.Query("DispatchHistory").Insert(...);
var insertedId = db.Select("SELECT @@IDENTITY");

db.Statement("COMMIT TRANSACTION");

This executes, but returns null (maybe because of our Trigger 'INSTEAD OF INSERT')

using (var scope = new TransactionScope())
{
	db.Query("DispatchHistory").Insert(...);
	var insertedIdString = db.Select("SELECT @@IDENTITY");
	scope.Complete();
}

This throws an exception at the first Query "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized." The Connection.Open() was added because the BeginTransaction() was throwing an exception saying that the connection was closed.

Db.Connection.Open();
using (var scope = Db.Connection.BeginTransaction())
{
    db.Query("DispatchHistory").Insert(...);
    var insertedIdString = db.Select("SELECT @@IDENTITY");
    scope.Commit();
}
Db.Connection.Close();

This works, but since I have 10 fields to insert, I lose the advantage to use SqlKata because I have to write my whole query manually.

var result = Db.Select<string>($@"INSERT INTO DispatchHistory (
                    Action,
                    VALUES('{actionValue}');
                    SELECT @@IDENTITY AS ID;");

The ability to pass in an existing transaction is vital to me as I already have a large codebase that makes heavy use of transactions. There is currently no way for me integrate Kata without using Mitch528's changes.

This commit would get the work done and requires the caller to only pass the transaction in the constructor of the QueryFactory.
Also this is the way that Dapper uses for Transactions.
Why is it not getting merged?
There could be a better way to do it, but this looks like a good way and is ready...
Other proposed solutions do not work with every database