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.
Hello there, You’ve performed an incredible job. I will certainly digg it and for my part recommend to my friends. I am confident they’ll be benefited from this web site.
May I just say what a comfort to discover a person that truly understands what they are talking about over the internet. You actually know how to bring a problem to light and make it important. A lot more people need to check this out and understand this side of your story. I was surprised that you’re not more popular given that you surely have the gift.
Greate pieces. Keep writing such kind of information on your page. Im really impressed by it.
Hey there, You’ve performed a fantastic job. I will definitely digg it and individually recommend to my friends. I’m sure they’ll be benefited from this website.
Can I simply say what a comfort to discover someone who actually knows what they are discussing online. You certainly know how to bring an issue to light and make it important. More people have to look at this and understand this side of your story. I was surprised you aren’t more popular given that you definitely have the gift.
https://svenskapharm.click/# rödcederolja apotek
Hey there, You’ve performed a great job. I’ll definitely digg it and personally recommend to my friends. I’m sure they’ll be benefited from this website.
https://svenskapharm.click/# covid utslag barn
Svenska Pharma: Svenska Pharma – hur stavas yoghurt
Currently it sounds like WordPress is the preferred blogging platform out there right now. (from what I’ve read) Is that what you are using on your blog?
Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.
Greate pieces. Keep posting such kind of information on your page. Im really impressed by your blog.
Right now it appears like WordPress is the best blogging platform available right now. (from what I’ve read) Is that what you’re using on your blog?
Hello there, You have performed an incredible job. I will definitely digg it and for my part suggest to my friends. I’m confident they will be benefited from this site.
Currently it looks like Movable Type is the top blogging platform available right now. (from what I’ve read) Is that what you’re using on your blog?
Currently it sounds like WordPress is the best blogging platform out there right now. (from what I’ve read) Is that what you’re using on your blog?
online mexican pharmacy: online mexican pharmacies – online pharmacies
Viagra homme prix en pharmacie sans ordonnance: п»їViagra sans ordonnance 24h – Viagra homme prix en pharmacie sans ordonnance
May I simply just say what a relief to find a person that truly understands what they’re talking about on the web. You certainly realize how to bring an issue to light and make it important. A lot more people ought to look at this and understand this side of the story. It’s surprising you’re not more popular because you definitely have the gift.
https://pharmaciefr.click/# SildГ©nafil 100 mg prix en pharmacie en France
https://healthspherepharmacy.com/# indian pharmacy
mexican pharmacy: mexican online pharmacy – mexican pharmacy online
May I simply say what a relief to find somebody that really understands what they are talking about on the web. You definitely realize how to bring a problem to light and make it important. More and more people must read this and understand this side of the story. It’s surprising you are not more popular given that you surely possess the gift.
Right now it looks like Drupal is the top blogging platform available right now. (from what I’ve read) Is that what you are using on your blog?
Hey there, You have done an incredible job. I’ll certainly digg it and for my part recommend to my friends. I am sure they’ll be benefited from this web site.
Greate post. Keep writing such kind of information on your page. Im really impressed by your site.
http://northbridgepharm.com/# pharmacy in canada
Greate pieces. Keep posting such kind of info on your site. Im really impressed by your blog.
May I simply say what a relief to uncover an individual who actually knows what they are discussing over the internet. You certainly know how to bring an issue to light and make it important. A lot more people have to read this and understand this side of your story. It’s surprising you are not more popular since you surely possess the gift.
At this time it seems like Expression Engine is the top blogging platform available right now. (from what I’ve read) Is that what you are using on your blog?
Wow that was strange. I just wrote an very long comment but after I clicked submit my comment didn’t appear. Grrrr… well I’m not writing all that over again. Anyway, just wanted to say wonderful blog!
Currently it appears like Expression Engine is the best blogging platform available right now. (from what I’ve read) Is that what you are using on your blog?
Hi there, You have done an excellent job. I’ll definitely digg it and for my part suggest to my friends. I’m sure they’ll be benefited from this site.
Greate post. Keep writing such kind of information on your page. Im really impressed by it.
Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.
https://northbridgepharm.shop/# NorthBridge Pharmacy
Greate post. Keep writing such kind of info on your blog. Im really impressed by your blog.
Excellent post. Keep writing such kind of info on your site. Im really impressed by your site.
Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.
Aw, this was a really good post. Finding the time and actual effort to make a superb article… but what can I say… I hesitate a lot and don’t seem to get nearly anything done.
Aw, this was an exceptionally nice post. Finding the time and actual effort to make a top notch article… but what can I say… I put things off a whole lot and don’t manage to get nearly anything done.
Right now it appears like BlogEngine is the preferred blogging platform out there right now. (from what I’ve read) Is that what you’re using on your blog?
Wow that was odd. I just wrote an really long comment but after I clicked submit my comment didn’t show up. Grrrr… well I’m not writing all that over again. Regardless, just wanted to say wonderful blog!
п»їlegitimate online pharmacies india: HealthSphere Pharmacy – online pharmacies
This post creates a very interesting discussion because the ideas are presented in a clear and balanced way that makes the content pleasant and simple for different readers to follow online.
在线购买他达拉非片用于肛交XXX色情
Aw, this was an extremely good post. Spending some time and actual effort to generate a top notch article… but what can I say… I procrastinate a lot and never manage to get nearly anything done.
I like how this post explains the topic clearly without making the discussion feel too difficult to understand for readers.
casino en ligne neosurf
Excellent article. Keep posting such kind of information on your blog. Im really impressed by your blog.
May I just say what a relief to find an individual who genuinely knows what they are discussing on the internet. You definitely understand how to bring an issue to light and make it important. More and more people need to look at this and understand this side of your story. It’s surprising you’re not more popular given that you certainly possess the gift.
At this time it looks like BlogEngine is the best blogging platform available right now. (from what I’ve read) Is that what you are using on your blog?