Ormlite adding the SavePoint API.

[mylvghb]

A transaction is a group of database operations that are executed as a single unit of work, which means that either all of them are completed successfully or none of them are. This helps to ensure data consistency and integrity in the database.

A savepoint is a point within a transaction where you can rollback to in case an error occurs, without rolling back the entire transaction. In other words, it provides a way to undo part of the transaction without undoing the entire transaction.

I can't find an API in Ormlite that can set and roll back to the specified savepoint, so I suggest adding these API，e.g:

using (var db = OpenDbConnection())
{
    using (var transaction = db.BeginTransaction())
    {
        try
        {
            // Execute some database operations here
            db.Insert(new Customer { Name = "John Doe" });
            db.Insert(new Order { CustomerId = 1, OrderDate = DateTime.Now });

            // Create a savepoint
            transaction.Save("MySavePoint");

            // Execute some more database operations here
            db.Update<Order>(new { OrderDate = DateTime.Now.AddDays(1) }, o => o.CustomerId == 1);

            // Rollback to the savepoint
            transaction.RollbackTo("MySavePoint");

            // Execute some more database operations here
            db.Update<Order>(new { OrderDate = DateTime.Now.AddDays(2) }, o => o.CustomerId == 1);

            // Commit the transaction
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // Rollback the transaction
            transaction.Rollback();
            throw ex;
        }
    }
}

See the Sqlserver SavePoint and Postgresql SavePoint page for more info on working with SavePoint.

[mythz]

Thanks for the code example, FYI transactions in OrmLite should be created with OpenTransaction, e.g:

using var db = OpenDbConnection();
using (var transaction = db.OpenTransaction())
{
  //...
}

[mylvghb]

I found relevant information, Mysql and Sqlite also support this feature.

[mythz]

Support for Transaction SavePoints have been added for all supported Databases in the latest ServiceStack v6.10 Release, which look like:

// Sync
using (var trans = db.OpenTransaction())
{
    try
    {
        db.Insert(new Person { Id = 2, Name = "John" });

        var firstSavePoint = trans.SavePoint("FirstSavePoint");

        db.UpdateOnly(() => new Person { Name = "Jane" }, where: x => x.Id == 1);

        firstSavePoint.Rollback();

        var secondSavePoint = trans.SavePoint("SecondSavePoint");

        db.UpdateOnly(() => new Person { Name = "Jack" }, where: x => x.Id == 1);

        secondSavePoint.Release();

        db.Insert(new Person { Id = 3, Name = "Diane" });

        trans.Commit();
    }
    catch (Exception e)
    {
        trans.Rollback();
    }
}

Async Version

using (var trans = db.OpenTransaction())
{
    try
    {
        await db.InsertAsync(new Person { Id = 2, Name = "John" });

        var firstSavePoint = await trans.SavePointAsync("FirstSavePoint");

        await db.UpdateOnlyAsync(() => new Person { Name = "Jane" }, where: x => x.Id == 1);

        await firstSavePoint.RollbackAsync();

        var secondSavePoint = await trans.SavePointAsync("SecondSavePoint");

        await db.UpdateOnlyAsync(() => new Person { Name = "Jack" }, where: x => x.Id == 1);

        await secondSavePoint.ReleaseAsync();

        await db.InsertAsync(new Person { Id = 3, Name = "Diane" });

        trans.Commit();
    }
    catch (Exception e)
    {
        trans.Rollback();
    }
}

[mylvghb]

Thank you for providing this feature.