Wrappers For Performing SQL Transactions

Question

I am trying to make my ASP.NET MVC application use transactions for all database operations, to ensure safety when there are multiple users. To do this, I have created static methods in my DbContext class that take Actions or Funcs as parameters, wrap them in using blocks for a new instance of DbContext and then a DbContextTransaction, and then execute the Action or Func.

Here are the transaction methods:

public static void PerformTransaction(
    Action<SlideDbContext, IList<Slide>> action,
    IList<Slide> slides)
{
    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                action(slideDbContext, slides);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }
}

public static void PerformTransaction(
    Action<SlideDbContext, Slide> action,
    Slide slide)
{
    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                action(slideDbContext, slide);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }
}

public static void PerformTransaction(
    Action<SlideDbContext, int> action,
    int id)
{
    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                action(slideDbContext, id);
                slideDbContext.SaveChanges();
                dbContextTransaction.Rollback();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }
}

public static void PerformTransaction(
    Action<SlideDbContext, Section> action,
    Section section)
{
    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                action(slideDbContext, section);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }
}

public static Answer PerformTransactionForAnswer(
    Func<SlideDbContext, int, Answer> func,
    int slideId)
{
    Answer answer = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                answer = func(slideDbContext, slideId);
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return answer;
}

public static IList<Answer> PerformTransactionForAnswers(
    Func<SlideDbContext, int, IList<Answer>> func,
    int slideId)
{
    IList<Answer> answers = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                answers = func(slideDbContext, slideId);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return answers;
}

public static bool PerformTransactionForBool(
    Func<SlideDbContext, int, bool> func,
    int slideId)
{
    var didSucceed = false;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                didSucceed = func(slideDbContext, slideId);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return didSucceed;
}

public static Role PerformTransactionForRole(
    Func<SlideDbContext, int, Role> func,
    int roleId)
{
    Role role = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                role = func(slideDbContext, roleId);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return role;
}

public static IList<Role> PerformTransactionForRoles(
    Func<SlideDbContext, IList<Role>> func)
{
    IList<Role> roles = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                roles = func(slideDbContext);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return roles;
}

public static Section PerformTransactionForSection(
    Func<SlideDbContext, int, Section> func,
    int sectionId)
{
    Section section = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                section = func(slideDbContext, sectionId);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return section;
}

public static IList<Section> PerformTransactionForSections(
    Func<SlideDbContext, IList<Section>> func)
{
    IList<Section> sections = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                sections = func(slideDbContext);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return sections;
}

public static Slide PerformTransactionForSlide(
    Func<SlideDbContext, Slide> func)
{
    Slide slide = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                slide = func(slideDbContext);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return slide;
}

public static Slide PerformTransactionForSlide(
    Func<SlideDbContext, int, Slide> func,
    int slideId)
{
    Slide slide = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                slide = func(slideDbContext, slideId);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }
    return slide;
}

public static async Task<Slide> PerformTransactionForSlideAsync(
    Func<SlideDbContext, int, Task<Slide>> func,
    int slideId)
{
    Slide slide = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                slide = await func(slideDbContext, slideId);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return slide;
}

public static Slide PerformTransactionForSlideConditional(
    Func<SlideDbContext, Func<Slide, bool>, Slide> func,
    Func<Slide, bool> conditional)
{
    Slide slide = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                slide = func(slideDbContext, conditional);
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return slide;
}

public static IList<Slide> PerformTransactionForSlides(
    Func<SlideDbContext, IList<Slide>> func)
{
    IList<Slide> slides = null;

    using (var slideDbContext = new SlideDbContext())
    {
        using (var dbContextTransaction =
            slideDbContext.Database.BeginTransaction())
        {
            try
            {
                slides = func(slideDbContext);
                slideDbContext.SaveChanges();
                dbContextTransaction.Commit();
            }
            catch (Exception)
            {
                dbContextTransaction.Rollback();
            }
        }
    }

    return slides;
}

Here are some examples of how these are used:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "Id,Title")] Role role)
{
    if (!ModelState.IsValid)
    {
        return View(role);
    }

    SlideDbContext.PerformTransaction(Create, role);
    return RedirectToAction("Index");
}

public ActionResult Delete(int? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }

    var role =
        SlideDbContext.PerformTransactionForRole(Get, id.Value);

    if (role == null)
    {
        return HttpNotFound();
    }

    return View(role);
}

private static void Create(SlideDbContext slideDbContext, Role role)
{
    slideDbContext.Roles.Add(role);
}

private static Role Get(SlideDbContext slideDbContext, int id)
{
    return slideDbContext.Roles.Find(id);
}

Is there something I should be doing different?

Answer 1

You have multiple overloads for different type of parameters but those parameters come from outside and you just pass-through. I see two different scenarios:

• Use generics to have only a couple of methods.
• Do not use parameters at all (for when you want to write body directly inside the lambda).

Another thing: you're swallowing the exception and it will make debugging a nightmare. I'm usually against catching Exception but in this case I think it's right (and you may simplify code little bit):

I simplified names little bit, for example I do not think that - in this context - dbContextTransaction is more readable than simply transaction.

public static void PerformTransaction<T>(Action<SlideDbContext, T> action, T items)
{
    using (var dbContext = new SlideDbContext())
    using (var transaction = dbContext.Database.BeginTransaction())
    {
        try
        {
            action(dbContext, items);

            dbContext.SaveChanges();
            transaction.Commit();
        }
        catch
        {
            dbContext.Rollback();
            throw;
        }
    }
}

That's all, just one method. You might also want to add an overload without any parameter (when business logic is simple and in the right place the Controller logic is pretty straightforward).

When you need a return value the prototype may be something like this:

TResult PerformTransaction<TParameter, TResult>(
    Func<SlideDbContext, TParameter, TResult> action, TParameter parameter)

Note: not every error is unrecoverable, sometimes waiting or trying again will solve the problem. Especially if you're using SQL Server take a look to Know when to retry or fail when calling SQL Server from C#?