ASP.NET CRUD apps include interaction with data that consists of create, read, update, and delete. In part 1, we setup a basic ASP.NET Core project. In part 2, we added the first read endpoint, using a SQL++ query against wishlist data. In part 3, we added another read endpoint, this time using the key-value API.

Until this point, the only data modifications we’ve made have been directly in the Couchbase Capella UI. In this post, let’s add an endpoint to handle creating and updating wishlist data.

SQL++ vs Key-Value: Revisited

SQL++ includes INSERT, UPDATE, DELETE syntax. So, we could use SQL++ to build an endpoint for mutating data. It would be very similar to the code written in part 2.

One keyword in SQL++ that you might not have seen before is UPSERT. As you might guess, it’s a combination of UPDATE and INSERT. An update will happen if the data already exists; an insert will happen if it doesn’t. Here’s how an upsert endpoint using SQL++ might look:

Two important things to point out about this code:

    • It handles both create and update. If item has a null ID, this code assumes that a new wishlist item is being created.
    • Parameterization: just as in relational databases, SQL++ can be vulnerable to SQL injection, so parameterization is highly recommended. Notice that SQL++ syntax uses $ to indicate named paramters (e.g. $id and $name).

This endpoint will work. But as was the case in part 3, we do not have to use SQL++ to interact with Couchbase. In fact, very similar criteria apply to making the decision when to use key-value and when to use SQL++:

Use case Key-value? Why or why not?
Create a new user with key “73892” Yes Direct access
Modify a user with key “73892” Yes Direct access
Modify just the email address of a user with key “73892” Yes Even if the user document is large, Couchbase has a key-based sub-document API, which allows you to modify a portion of the document.
Modify a group of users with keys “73892”, “47212”, and “90491” Yes This may require multiple key-value operations, but this still may be faster than using a SQL SELECT …​ WHERE …​ IN query.
Modify all addresses to use “OH” instead of “Ohio” No User’s state is likely a “secondary” attribute, not a key (multiple users can be from Ohio). This is a good use case for a SQL++ UPDATE

Because this endpoint only needs to add or change one single wishlist item, let’s use the key-value API instead.

Write a CreateOrEdit CRUD endpoint

Before we get to coding, it’s wise to think about whether CreateOrEdit should be a single endpoint, or split into a Create endpoint and an Edit endpoint. For this very simple wishlist app, there is no validation, authentication, or other system-wide cross cutting concerns. However, in a production system, “adding” data and “updating” data may very well follow different business rules and require different permissions. In that case, you may want to break the operations into two endpoints.

For now, let’s start with a single endpoint that uses an “upsert”:

Much like the SQL++ endpoint, this endpoint looks at Id to determine whether this is a “create” or an “update”.

The UpsertAsync function will either:

    • Create a new document with id.ToString() as the key
    • Change an existing document with a key of id.ToString()

In either case, once this endpoint finishes executing, you will end up with a JSON document like { “name” = “wishlist item name”} and a GUID as a key (technically all keys in Couchbase are strings, but we know it’s a GUID string).

Note: One subtle difference between the two APIs, is that the UPSERT statement will only mutate the one field (name), while the key-value method UpsertAsync will mutate the entire document (which is only name for now).

ASP.NET CRUD in action

Fire up your application from Visual Studio with CTRL+F5, and you should see some new endpoints show up in the OpenAPI / Swagger UI:

ASP.NET endpoints showing in Swagger UI

From an API consumer’s perspective, both /api/edit and /api/editWithSql endpoints will function the same. Try it once leaving the ID blank to create a new item, then try it again with a known ID (use /api/getall if you need to get an ID) and observe what happens and what changes.

For instance, I added a new item “Digital Photo Frame”, by leaving the ID blank (my .NET code generated “1c3de2e7-70ea-4ee2-803b-425bbf6251cb” for me), and I updated the item with ID of “2dab198b-1836-4409-9bdf-17275a2b2462” to have a name of “Skyline Chili 2XL T-Shirt”. Here are the results as seen in the Couchbase Capella UI:

Couchbase Capella UI showing ASP.NET project documents

What’s next?

The ASP.NET Core project is connected to Couchbase Capella, and it is now creating/updating (“upserting”) data with key-value (recommended) or SQL++ (not recommended for this specific situation).

In the next blog post, we’ll round out CRUD with “D” for “delete”.”

In the meantime, you should:


Posted by Matthew Groves

Matthew D. Groves is a guy who loves to code. It doesn't matter if it's C#, jQuery, or PHP: he'll submit pull requests for anything. He has been coding professionally ever since he wrote a QuickBASIC point-of-sale app for his parent's pizza shop back in the 90s. He currently works as a Senior Product Marketing Manager for Couchbase. His free time is spent with his family, watching the Reds, and getting involved in the developer community. He is the author of AOP in .NET, Pro Microservices in .NET, a Pluralsight author, and a Microsoft MVP.

Leave a reply