How do I update two tables in a single method?

Question

I have a KendoUI Grid in my MVC Application. The grid shows related data via a view in my SQL database. The two tables that it shows are Cars and Bookings

Table: Cars

public int Id
public string Reg
public string Make
public string Model

Table: Bookings

public int Id
public DateTime BookingStart
public DateTime BookingEnd
public int Car_Id

The code for the grid is as follows:

@(Html.Kendo().Grid<MyProject.ViewModels.CarBookings>()
.Name("Bookings")
.Columns(columns => {
    columns.Bound(c => c.Id);
    columns.Bound(c => c.Car_Id);
    columns.Bound(c => c.Reg);
    columns.Bound(c => c.Make);
    columns.Bound(c => c.Model);
    columns.Bound(c => c.BookingStart).Format("{0:dd/MM/yyyy}");
    columns.Bound(c => c.BookingEnd).Format("{0:dd/MM/yyyy}");
    columns.Command(command => { command.Edit(); }).Width(250);
})
.Pageable()
.Sortable()
.Groupable()
.Editable(editable => editable.Mode(GridEditMode.InLine))
.DataSource(dataSource => dataSource
    .Ajax()
    .Model(model => {
        model.Id(p => p.Id);
        model.Field(p => p.Id).Editable(false);
        model.Field(p => p.Car_Id).Editable(false);
    })
    .Read(read => read.Action("GetBookings", "Bookings"))
        .Update(update => update.Action("UpdateBooking", "Booking")))
))

We allow the user to leverage the inline editing features of the grid and update details. The problem I have is that these details are actually from two tables, so when they come to save those changes I need a way to update both the tables with the data the user changes.

Here is the UpdateBooking method with a very crude attempt to update both tables.

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult UpdateBooking([DataSourceRequest] DataSourceRequest request, Car car, Booking booking, CarBookings vm)
{
    unitOfWork.CarRepository.Update(car);
    unitOfWork.BookingRepository.Update(booking);
    unitOfWork.Save();
    return Json(new[] { vm }.ToDataSourceResult(request, ModelState));
}

The problem with this approach is that the Booking Id is being referenced from the Car Id. So it' won't work. Can anyone assist with a better approach to handling this scenario?

Red Wei Red Wei · Accepted Answer · 2018-08-31T09:43:22

You have 2 solutions for this situation.

Solution 1: Write a SQL statment that update your database directly without going through UnitOfWork. I don't recommend this way because this case is not so special to do so. Your UnitOfWork should handle this.

Solution 2:

Step 1: Inside your UnitOfWork try to get the newly added Car Id.
Step 2: Update your Booking object with your Car Id.
Example code:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult UpdateBooking([DataSourceRequest] DataSourceRequest request, Car car, Booking booking, CarBookings vm)
{
    int carId = unitOfWork.CarRepository.Update(car);
    booking.Car_Id = carId;
    unitOfWork.BookingRepository.Update(booking);
    unitOfWork.Save();
    return Json(new[] { vm }.ToDataSourceResult(request, ModelState));
}

Since you ask me how to update only the data that is changed. I will give you steps to do that.

Step 1: Get the object from the UnitOfWork.
Step 2: Update properties that was updated.
Example code: In this example updatingBooking is the object that contains only the fields to update (fields that have new value), some if it's fields can be null, bookInDb is the object from current database (contains old value).

public void UpdateBooking(Booking updatingBooking)
{
    **INIT UNIT OF WORK**
    Booking bookingInDb=unitOfWork.BookingRepository.Find(updatingBooking.Id); 
    //updatingBooking.Id might be null, you need to pass the Id of the row you want to update
    bookingInDb.BookingStart=updatingBooking.BookingStart;
    //We update only BookingStart but not BookingEnd or Car_Id
    unitOfWork.BookingRepository.Update(bookingInDb);
    unitOfWork.Save();
}

How do I update two tables in a single method?

1 Answers