In the previous posting we saw how to create an API to get all the cars in our database. In this posting we’ll look at the remaining CRUD (Create Review Update Delete) operations.
As you may remember, we created a controller named CarController. ASP.NET will strip off the word Controller, leaving us with Car, which we will use to access the endpoints of our API.
An endpoint is just a URL that takes us to the operation we want.
We looked at GetAll, let’s take a look at Get. In this case, we have an id for the car we want, but we want all the details of that car. Simple!
First we need a method in our controller:
[HttpGet("{id}")]
public async Task<ActionResult<Car>> Get(int id)
{
var car = await _carRepository.Get(id);
if (car == null)
{
return NotFound();
}
return car;
}
Notice that next to the HttpGet attribute we indicate that the endpoint will take the id of the car we want
[HttpGet("{id}")]
This means we need to modify the URL to access the endpoint by adding the actual id of the desired record.
The first thing we do is call the repository, passing in the id.
public async Task<Car?> Get(int id)
{
var query = "select * from car where id=@id";
using var db = databaseConnectionFactory.GetConnection();
return await db.QuerySingleOrDefaultAsync<Car>(query, new {id});
}
In the Get method of the repo we create our query, get our connection and execute the query returning the value we retrieved (if any). This is very close to what we did previously.
Back in the controller, we check to ensure that we received a Car. If not, we return NotFound which is a shorthand way of returning a 404 message. Otherwise we return the Car as a Json object. You can see this in Postman:
We’ll issue a Get command passing in the URL, ending with the id of the car we want (in this case 4)
Notice that we get back a 200, indicating success. In the body of the returned Json we get back all the details of the Car. (If you decide to use DTOs you can get whatever subset of the information makes sense):
Post
Adding a Car to the database is quite similar. We need a method in the controller and one in the repo. Here is the controller method:
[HttpPost]
public async Task<ActionResult<Car>> Post([FromBody] Car car)
{
try
{
car = await _carService.Insert(car);
}
catch (Exception e)
{
return BadRequest(e);
}
return CreatedAtAction(nameof(Get), new { id = car.Id }, car);
}
Look at the attribute in the parameter ([FromBody]. This indicates to the API that the data needed to insert this Car will be in the body of the call. The alternative is FromQuery. You can, in fact, use both in one call.
Note: CreatedAction causes a return code of 201, which is what we want. Here’s the body we’ll insert:
When we click Send this data is sent to the API which returns 201 (created) and in the body of the returned data we see the new id assigned to this car
Service Class
Notice that this time, instead of calling the Repo directly, the method in the controller calls into a service class. A service class is a great way to get the logic out of the controller, where it does not belong, without putting it into the repo, where it also does not belong.
Here’s the top of the CarService
public class CarService : ICarService
{
private readonly ICarRepository _carRepository;
public CarService(ICarRepository carRepository)
{
_carRepository = carRepository;
}
public async Task<Car> Insert(Car car)
{
var newId = await _carRepository.UpsertAsync(car);
if (newId > 0)
{
car.Id = newId;
}
else
{
throw new Exception("Failed to insert car");
}
return car;
}
All the logic associated with this insert (e.g., making sure we get back a legitimate id from the repository, etc.) is encapsulated in the service.
This leaves the repository free to just talk to the database,
public async Task<int> UpsertAsync(Car car)
{
using var db = databaseConnectionFactory.GetConnection();
var sql = @"
DECLARE @InsertedRows AS TABLE (Id int);
MERGE INTO Car AS target
USING (SELECT @Id AS Id, @Name AS Name, @Model_Year AS Model_Year,
@Is_Deleted AS Is_Deleted, @Origin AS origin ) AS source
ON target.Id = source.Id
WHEN MATCHED THEN
UPDATE SET
Name = source.Name,
Model_Year = source.Model_Year,
Is_Deleted = source.Is_Deleted,
Origin = source.Origin
WHEN NOT MATCHED THEN
INSERT (Name, Model_Year, Is_Deleted, Origin)
VALUES (source.Name, source.Model_Year,
source.Is_Deleted, source.Origin)
OUTPUT inserted.Id INTO @InsertedRows
;
SELECT Id FROM @InsertedRows;
";
var newId = await db.QuerySingleOrDefaultAsync<int>(sql, car);
return newId == 0 ? car.Id : newId;
}
Rather than having an insert and an update method, we combine that logic into this upsert method. This is a common idiom for database manipulation.
Note: to make this work, be sure to fill in all the fields for a car (or at least as many as you want to have in the Database.
Delete
As noted earlier, we will implement a soft delete; that is, rather than actually removing the data from the database, we’ll just set the is_deleted column to true. This allows us to reverse the action, and make the row not-deleted by simply changing that value to false.
[HttpDelete(“{id}”)]
public async Task<IActionResult> Delete(int id)
{
try
{
await _carService.Delete(id);
}
catch (Exception e)
{
return BadRequest(e);
}
return NoContent();
}
As you would expect, the endpoint takes an id (the id of the car we want to delete). The controller then hands that off to the service, which calls the repository which, in turn, marks that id as deleted:
public async Task<int> DeleteAsync(int id)
{
using var db = databaseConnectionFactory.GetConnection();
var query = "UPDATE car SET Is_Deleted = 1 WHERE Id = @Id";
return await db.ExecuteAsync(query, new { Id = id });
}
If you are comfortable with SQL none of this will be very surprising. The key walkaway is:
Summary
In this post we saw that endpoints are just URLs with (potentially) data in the body of the request. The controller handles the URL and in our case passes the id or other data to the service. The service handles the (business) logic and then delegates talking to the database to the repository.
Book
This posting is excerpted from my forthcoming book Building APIs with .NET and C# to be released next year by Packt.
Paw Trust Meds: Paw Trust Meds – discount pet meds
Excellent beat ! I wish to apprentice while you amend your site, how can i subscribe for a blog site? The account helped me a acceptable deal. I had been tiny bit acquainted of this your broadcast provided bright clear concept
prednisone 1 mg daily: prednisone without prescription usa – prednisone for dogs
trusted ivermectin pharmacy: ivermectin tablets online – buy stromectol online usa
http://amoxilfast.com/# safe antibiotics online source
stromectol without prescription usa: Stromectol – buy stromectol online usa
generic ivermectin online pharmacy: ivermectin treatment tablets – stromectol delivery united states
buy stromectol online usa: ivermectin treatment tablets – parasite infection medication
Thanks for any other informative site. The place else may I am getting that type of information written in such an ideal means? I have a mission that I am simply now operating on, and I’ve been on the look out for such info.
https://amoxilfast.shop/# safe antibiotics online source
fast delivery amoxicillin usa: amoxil without prescription usa – amoxil 500mg tablets usa
trusted antibiotic pharmacy usa: safe antibiotics online source – safe antibiotics online source
prednisone pack: steroid tablets usa pharmacy – prednisone 5mg coupon
Hello, Neat post. There is a problem with your website in web explorer, would check this? IE still is the marketplace chief and a good element of other people will leave out your excellent writing because of this problem.
10mg prednisone daily: anti-inflammatory medication online – over the counter prednisone pills
prednisone 5mg price: trusted steroid medication source – buy prednisone mexico
http://ivermectinfast.com/# ivermectin oral tablets usa
amoxicillin 250 mg price in india: buy amoxil online usa – fast delivery amoxicillin usa
prednisone acetate: steroid tablets usa pharmacy – prednisone online india
ivermectin tablets online: ivermectin tablets online – ivermectin tablets online
buy prednisone canadian pharmacy: trusted steroid medication source – buy prednisone online without a prescription
https://ivermectinfast.shop/# ivermectin fast
amoxil: buy amoxicillin without prescription – fast delivery amoxicillin usa
prednisone 50 mg canada: prednisone for allergies and asthma – prednisone 2.5 mg daily
http://amoxilfast.com/# fast delivery amoxicillin usa
What’s up, after reading this amazing paragraph i am as well happy to share my experience here with friends.
https://teletype.in/@avtobloggerua/yBdxoskAM8B
enlarged clitorious from steroids
References:
http://wiki.0-24.jp/index.php?detailcorn6
https://stericarepharmacy.com/# prednisone 15 mg tablet
I’ve been exploring for a bit for any high-quality articles or blog posts on this sort of area . Exploring in Yahoo I finally stumbled upon this web site. Studying this information So i’m glad to exhibit that I’ve a very good uncanny feeling I discovered just what I needed. I most surely will make sure to do not omit this website and give it a look on a continuing basis.
antibiotics online pharmacy usa: safe antibiotics online source – amoxil 500mg tablets usa
ivermectin fast: stromectol delivery united states – ivermectin fast
https://ivermectinfast.com/# ivermectin fast
https://amoxilfast.shop/# amoxil without prescription usa
safe antibiotics online source: trusted antibiotic pharmacy usa – trusted antibiotic pharmacy usa
https://stericarepharmacy.shop/# prednisone buy online nz
What’s up, this weekend is pleasant designed for me, for the reason that this point in time i am reading this enormous educational article here at my house.
https://teletype.in/@avtobloggerua/53x_9oqGO2V
http://ivermectinfast.com/# stromectol without prescription usa
https://ivermectinfast.shop/# ivermectin fast
amoxil 500mg tablets usa: amoxil – amoxil without prescription usa
http://stericarepharmacy.com/# prednisone over the counter uk
generic ivermectin online pharmacy: trusted ivermectin pharmacy – stromectol without prescription usa
ivermectin fast: ivermectin tablets online – Stromectol
buy stromectol online usa: ivermectin oral tablets usa – buy stromectol online usa
cheap generic prednisone: prednisone 2 mg – prednisone cream over the counter
https://stericarepharmacy.com/# prednisone 50 mg tablet cost
ivermectin 50ml: parasite infection medication – generic ivermectin online pharmacy
ivermectin fast: ivermectin oral tablets usa – ivermectin fast
prednisone 5mg over the counter: prednisone for allergies and asthma – prednisone 10mg prices
amoxil: where to buy amoxicillin over the counter – safe antibiotics online source
Hi there friends, good article and pleasant urging commented at this place, I am truly enjoying by these.
https://abank.com.ua/zolote-marmuruvannya-alyuminii-proty.html