Welcome to 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 JSON 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.
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));
we guarantee 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 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;
}
}
}
Below is a method in the book service class to apply changes from the patching object:
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);
}
}
}
}
}
In the above source we complete 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.
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.
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.
