CODEWITHSUNDEEP
c#asp.netasp.net-mvc
Steven
Steven

Reputation: 18869

Best practice for inserting/updating data in multiple tables at once

So I have two tables in my database, Contacts and Addresses:

Contacts : ContactID | AddressID | FirstName | LastName

Addresses: AddressID | Address1 | Address2 | City | State | Zipcode

I have a page where you can add a contact. It contains all the information for a contact and an address for a contact. Here's my code for when you submit the form:

[HttpPost]
public ActionResult Edit(ContactsViewModel viewModel)
{
    //if editing an contact, fetch it; otherwise, create a new one
    Contact contact = viewModel.ContactId == 0
        ? new Contact()
        : _adminRepository.GetContact(viewModel.ContactId);
    TryUpdateModel(contact);

    if (ModelState.IsValid)
    {
        _adminRepository.SaveAddress(contact.Address);
        _adminRepository.SaveContact(contact);

        return RedirectToAction("Index");
    }
    return View(viewModel);     // validation error, so redisplay same view
}

Now my main concern is that the address is added successfully, but then an error occurs when I try to save the contact, leaving the address in the database with no contact.

What's the best practice here for rolling back any changes if an error occurs?

Upvotes: 1

Views: 5783

Answers (4)

John Hoge
John Hoge

Reputation: 2391

My preference would be to make a single stored procedure to insert both records. You can use TSQL transactions. Using a TransactionScope() would also work, so the decision should probably be based on your data access layer. If you're already using a lot of SPROCs, make make a complex one with a transaction.

If you're using Entity Framework (... the pain!!!!) then a .net TransactionScope() would be better.

Upvotes: 0

Nuri YILMAZ
Nuri YILMAZ

Reputation: 4331

"Unit Of Work" patterns nice and accepted solution for multiple operations on db -or anothers operations also- please read sample from Faisal Mohamood, Program Manager, Entity Framework blogs.

Upvotes: 1

RPM1984
RPM1984

Reputation: 73113

Well that should be one method, not two.

If an Address is always related to a Contact (it seems that way), then you should only have one repository - e.g a ContactRepository.

You should have one repository per aggregate root, or in simple terms - a repository for each entity that needs to be retrieved on it's own.

I don't know what _adminRepository is, but i think you just just have a SaveContact(contact) method.

Are you using an ORM like Entity Framework? If so, both inserts will be done in one transaction (handled by EF), so if one fails, both will fail.

If you start making changes against multiple repositories/aggregate roots, then you will need something like a Unit of Work, or a TransactionScope.

So it sounds like you need to rethink your repository design a little bit. There should be a generic Save method for a single aggregate entity (Contact), not seperate ones for each relation.

Upvotes: 4

Matt Phillips
Matt Phillips

Reputation: 11519

You need to use TransactionScope() in your repository. Check out MSDN forum entry. First reply shows a simple example for integrating it into a method. The key is that without it there isn't a way to specifically rollback changes.

Upvotes: 0

Related Questions