Welcome to today’s post.
In today’s post I will discuss how to use HTTP PATCH requests to apply SQL updates to resources within a Web API using .NET Core.
In a previous post I showed how to apply HTTP patching to a JSON document and produce a JSON result that reflected the operations within the document.
In this post I will show how to take our patched JSON document and apply the changes to a SQL database.
The use of HTTP patching is useful during the posting of UI data within a grid or form where there are multiple changes by the user and only the deltas are to be posted to the API service without including the remaining fields. This gives significant performance benefits between the front-end and backend API services.
Modifying the HTTP PATCH API Method
In the previous post, we implemented a basic API HTTP method to perform the patching on the submitted JSON document. The revised API method below clones our original resource data, applies patching and then applies changes to the database:
[HttpPatch("api/[controller]/BookPatch")]
public async Task<IActionResult> BookPatch([FromBody]
JsonPatchDocument<PatchBookViewModel> patchDoc)
{
if (patchDoc != null)
{
var originalBooks = await _bookService.GetBooks();
var originalBooksCopy = originalBooks.ConvertAll(x =>
new BookViewModel(x));
var patchOriginalBooksCopy = new PatchBookViewModel()
{
books = originalBooksCopy
};
patchDoc.ApplyTo(patchOriginalBooksCopy, ModelState);
await _bookService.ApplyPatching(patchOriginalBooksCopy);
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
return new ObjectResult(patchOriginalBooksCopy);
}
else
{
return BadRequest(ModelState);
}
}
Note that if we simply had applied a list copy like this:
var originalBooksCopy = new List<BookViewModel>(originalBooks);
then the comparisons we make between changes in the ApplyPatching() method would fail to pick up changes as both original data and patched data would still be referencing the same lists!
By building a new list as a cloned copy of each original item using:
var originalBooksCopy = originalBooks.ConvertAll(x => new BookViewModel(x));
By using the above conversion, we ensure the list to be patched is a genuine copy that can be compared to the original list.
To be able to clone the object we use a constructor overload on the BookViewModel class as follows:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace BookLoan.Models
{
[Serializable]
public class BookViewModel
{
public int ID { get; set; }
..
public BookViewModel() { }
public BookViewModel(BookViewModel obj)
{
this.ID = obj.ID;
this.Title = obj.Title;
this.Author = obj.Author;
this.YearPublished = obj.YearPublished;
this.Genre = obj.Genre;
this.Edition = obj.Edition;
this.ISBN = obj.ISBN;
this.Location = obj.Location;
this.DateCreated = obj.DateCreated;
this.DateUpdated = obj.DateUpdated;
}
}
}
Recall from our previous post, the method ApplyTo() from the JsonPatchDocument class takes two parameters. The first parameter is a typed object instance consisting of the list of objects (Books) that require patching based on the rules (insertions and additions) within the patching document. Once the patching rules have been applied to the object, the model state returns a status of IsValid, that determines if the patching has been successful. The list within object has the patching rules applied to it and is returned as an ObjectResult.
Applying Patching Operations to the SQL Data Store
Below is the ApplyPatching() method in the book service class to apply changes from the patching object (from the result of the ApplyTo() method) into the database table:
public async Task ApplyPatching(PatchBookViewModel vm)
{
BookLoanComparer bookLoanComparer = new BookLoanComparer();
if (vm.books.Count > 0)
{
foreach (BookViewModel book in vm.books)
{
BookLoan.Models.BookViewModel bookoriginal =
await _db.Books.Where(m =>
m.ID == book.ID).SingleOrDefaultAsync();
if (bookoriginal != null)
{
// If different record then update
if (!bookLoanComparer.Equals(bookoriginal, book))
{
await this.UpdateBook(book.ID, book);
}
}
else
{
// If a new record then insert
if (book.ID == 0)
{
await this.SaveBook(book);
}
}
}
}
}
The modified object from the HTTP PATCH BookPatch() method is then passed into the custom ApplyPatching() method, which then loops through all objects within its list, compares each object’s properties to the original (Book) record’s properties in the database, then applies a SQL update to records corresponding to objects that differ. Objects that do not have corresponding record entries in the database table are inserted within the database.
In the above ApplyPatching() method, we completed the following tasks:
Loop through the patched records.
- If the patched record exists in the original SQL book table then we do the following:
- If the original record is identical to the patched record, then update the record with the new patched data changes.
- If the patched record does not exist in the original SQL book table then we do the following:
- Add the patched record to the database as a new record.
Comparing Object Equality between the Modified and Original Records
In the data update section of the code, we use a data comparer class, BookLoanComparer and the following check:
if (!bookLoanComparer.Equals(bookoriginal, book))
This allows us to verify object equality between the original and patched book records. The class to perform equality check between books is below:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using BookLoan.Models;
namespace BookLoan.Catalog.API.Helpers
{
public class BookLoanComparer : IEqualityComparer<BookViewModel>
{
// items are equal if their names and item numbers are equal.
public bool Equals(BookViewModel x, BookViewModel y)
{
//Check whether the compared objects reference the same data.
if (Object.ReferenceEquals(x, y)) return true;
//Check whether any of the compared objects is null.
if (Object.ReferenceEquals(x, null) ||
Object.ReferenceEquals(y, null))
return false;
//Check whether the items' properties are equal.
return (x.ID.Equals(y.ID) &&
x.Author.Equals(y.Author) &&
x.Title.Equals(y.Title) &&
x.Edition.Equals(y.Edition) &&
x.Genre.Equals(y.Genre) &&
x.ISBN.Equals(y.ISBN) &&
x.Location.Equals(y.Location) &&
x.YearPublished.Equals(y.YearPublished) &&
x.DateCreated.Equals(y.DateCreated) &&
x.DateUpdated.Equals(y.DateUpdated));
}
// If Equals() returns true for a pair of objects
// then GetHashCode() must return the same value for these
// objects.
public int GetHashCode(BookViewModel item)
{
//Check whether the object is null
if (Object.ReferenceEquals(item, null)) return 0;
//Get hash code for the Name field if it is not null.
int hashItemName = item.ID.GetHashCode();
//Calculate the hash code for the item.
return hashItemName;
}
}
}
After we run the Web API under IIS Express and post the changes using POSTMAN, we open SSMS and query our book table.
Resulting changes applied to the SQL database using our data patching method are shown:
The blue values are from the PATCH replace operations and the red records are from the add operations.
The above discussion and tasks have shown how to apply patching rules from a JsonPatchDocument to produce a resulting JSON document, then update the changed records within the SQL table that correspond to changes entries within the modified JSON object.
That’s all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
