CRUD (create, read, update, delete) operations are common operations in all web applications. In this article we will cover the step by step process of implementing a CRUD operation int asp.net core.
Our scenario is a simple customer transaction system. We will use use .Net Core, Entity Framework, SQLite to implement APIs to add, update, read, delete customers for an e-commerce mobile application. Let’s Start
1. Installation
For .Net Core installation, you can follow the instructions on Microsoft’s documentation and install the appropriate package for your operating system. After installing this setup, you will be able to use the .Net CLI commands we use below.
In this example we are going to use SQLite as our database. You can easy install the database following the instruction from the SQLite download page.
Postman is a very useful tool for webapi tests. I recommend use it not only in this example, but in all webapi tests in general. You can install by following the instructions from the official site.
After these processes, we are ready to create our project.
2. Creating a Project
Now that every is setup it is time that we create a project. Open the command line and create a new .Net Core WebApi project with the command
“dotnet new webapi -n AspnetCoreCRUD”.
The variable after the “-n” parameter in this command is the name of the project. You can write any name you want here.
3. Adding dependencies
Given that we need to communicate with a SQLite database we will need to add a dependency. We need to install the following package.
“Microsoft.EntityFrameworkCore.Sqlite”. You can install this package with the Nuget package manager or through the Nuget Package Manager from the Visual Studio. Adding it through command line you add follow the simple steps.
- Access the root folder of the project. In our case the project is located in c:\Projects so cd C:\Projects\AspnetCodeCRUD.
- Run the following nuget package command.
4.Database and Table creation
Now that we have create the project and added the dependencies, we need to create a database so that we can use it in our project. You can create the database using a
command “sqlite3 <databaseName>” by going to the main directory of our project on the command line.
Once we created the database we need to create the tables that will store the data. To perform this task, we need to switch to the sqlite command line with the “sqlite3” command in the main directory of our project.
We use the command “.open <databasepath> .db” to select the database where we will create our table. This command corresponds to the “use” command in standard databases. Our command for this project will be “.open AspnetCoreCRUD.db“. After opening our database file, we create our Customer table with the following commands. Since there is no space reserved for boolean values in SQLite database, we will keep the user’s activity status as an integer value.
CREATE TABLE "Customer" (
"PKCustomerId"INTEGER UNIQUE,
"FirstName"TEXT NOT NULL,
"LastName"TEXT NOT NULL,
"Email"TEXT NOT NULL,
"IsActive"INTEGER NOT NULL,
"CreatedOn"TEXT NOT NULL,
"ModifiedOn"TEXT NOT NULL,
PRIMARY KEY("PKCustomerId" AUTOINCREMENT)
);
5.DBContext Creation
Now that we have created the database it is time that we make our hands dirty and start add some code to our project. The first class we are going to add is the DBContext. The DBContext represents a session to the database that will be used to query and persist data in the database.
Create a folder named “Data” in the main directory of our project and create a class named AspnetCRUDContext. The class will inherit from DbContext class. The following is the layout of the class.
public class AspnetCoreCRUDContext: DbContext
{
public AspnetCoreCRUDContext ()
{
}
}
5.DataBase Configuration
Now it is time to add a configuration to the database that will specify the connection string to our database. The following entries have to be added the the appsettings.json
{
"ConnectionStrings": {
"SQLiteConnection": "Data Source = AspnetCoreCRUD.db"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
The appsettings.json file is a file where you can keep your in-app settings. It offers the possibility to configure according to different working environments.
With the setting we created, we indicate to our project that we will use our context class for sqlite. We make this definition with the following line of code in the ConfigureServices method in the Startup class.
public void ConfigureServices (IServiceCollection services)
{
services.AddDbContext <AspnetCoreCRUDContext> (opt => opt.UseSqlite (Configuration.GetConnectionString ("SQLiteConnection")));
services.AddControllers ();
}
Make sure that the parameter we have given inside the GetConnectionString method is the same as the setting name we created in the ConnectionString object. You can make other database connections with SQLite with the commands
opt => opt.UseSqlServer and
opt => opt.UseMysql after installing the related dependencies.
6.Creating Entity
We must have a model representing customers for customer transactions that we will implement in our scenario. We call these entity classes. First of all, we create a folder named “Models” in the main directory of our project. Then we add our entity class named Customer.cs into this folder. Our Customer Entity will be as follows.
public class Customer
{
public Customer()
{
}
[Key]
public int PKCustomerId{get;set;}
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public int IsActive { get; set; }
public DateTime CreatedOn { get; set; }
public DateTime ModifiedOn { get; set; }
}
With the [Key] attribute, we specify that the PKCustomerId value is the Primary Key of this table. We could do this in the context class we created above.
The properties in the entities must match the field names in the table they represent. This is the default behavior of Entities.
In order to use the Entities we have created and to do database operations on them, we need to define them in the Context class. After the definition is made, our AspnetCoreCRUDContext class will be as follows.
public class AspnetCoreCRUDContext : DbContext
{
public AspnetCoreCRUDContext()
{
}
public DbSet<Customer> Customer { get; set; }
}
If we do not map the parameter we specified as DbSet to any table while configuring, Entity Framework will assume that this entity has a table in its own name and will perform its operations accordingly. So in this case, we need to have a table named Customer in our database. So the field we define as dbSet actually represents our table in the database
6.Creating The Controller
We installed dependencies, set up our database, created our entity class, and it’s time to perform CRUD operations. For this, we first create a class called CustomerController in the Controllers folder in the main directory of our project. The entity to be processed according to general standards must have a controllers named in its name. When you combine this distinction with rest standards, legible and manageable codes emerge. The main draft of our CustomerController class will be as follows.
using Microsoft.AspNetCore.Mvc;
namespace AspnetCoreCRUD.Controllers
{
public class CustomerController: ControllerBase
{
public CustomerController ()
{
}
}
}
In Web API projects, controller classes must be derived from the ControllerBase class. In this way, there is no need to inherit the Controller class, which contains methods that enable view return in MVC projects.
using Microsoft.AspNetCore.Mvc;
namespace AspnetCoreCRUD.Controllers
{
public class CustomerController: ControllerBase
{
public CustomerController ()
{
}
}
}
6.Creating Customer Operations
First of all, we will create an endpoint where we can create customers. This endpoint needs a request to create a customer. We create a folder called “Requests” in the main directory of our project and put this request into it. Our “CreateCustomerRequest.cs” file will be as follows.
public class CreateCustomerRequest
{
public CreateCustomerRequest ()
{
}
public string FirstName {get; set; }
public string LastName {get; set; }
public string Email {get; set; }
}
Our Customer table also has IsActive, CreatedOn and ModifiedOn fields, but we don’t need this request. We will assume that the first customer will be active and assign the created date as the system date. Now we can create the endpoint that accepts this request. While creating the Endpoints, we will follow the Rest Standards. The final version of our class will be as follows.
public class CustomerController: ControllerBase
{
public CustomerController ()
{
}
[HttpPost]
public IActionResult Post (CreateCustomerRequest request)
{
}
}
You should notice that we do not set any route here.
To create a record according to Rest standards, we need to use “Post”, one of the Http methods. The table to be registered depends on which controller is used. We should assume that a method marked as HttpPost will create a new record. This will make it easier to understand for employees who join our project later.
Now, let’s create a new customer. For this, we first need to inject the Context class. The Constructor method will be as follows.
private readonly AspnetCoreCRUDContext _dbContext;
public CustomerController (AspnetCoreCRUDContext dbContext)
{
_dbContext = dbContext;
}
You have noticed that unlike traditional dependency injection structures, we do not register the AspnetCoreCRUDContext class. In fact, we are registering our context class with the AddDbContext method we call in the Startup class.
We fill in our method of creating Customer. As I mentioned above, we assign IsActive, CreatedOn and ModifiedOn values for business reasons. The body of our method is as follows.
[HttpPost]
public IActionResult Post([FromBody]CreateCustomerRequest request)
{
var customer = new Customer
{
FirstName = request.FirstName,
LastName = request.LastName,
Email = request.Email,
IsActive = 1,
CreatedOn = DateTime.Now,
ModifiedOn = DateTime.Now
};
_dbContext.Customer.Add(customer);
_dbContext.SaveChanges();
return Ok();
}
With the [FromBody] attribute, we specify that the request model we expect should be mapped from the requesting body.
Now we can test our method. First of all, we run our application. In the command line, we go to the main directory of our project and run the “dotnet run” command. According to the command output, we see that our application has started to run on the http: // localhost: 5000 port.
You can change the port information on which the application specified here will run through the “launchSettings.json” file under the Properties directory. The content of the launchSettings.json file as default is like this.
{
"$schema": "http://json.schemastore.org/launchsettings.json",
"iisSettings": {
"windowsAuthentication": false,
"anonymousAuthentication": true,
"iisExpress": {
"applicationUrl": "http://localhost:32984",
"sslPort": 44355
}
},
"profiles": {
"IIS Express": {
"commandName": "IISExpress",
"launchBrowser": true,
"launchUrl": “customer”,
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
}
},
"AspnetCoreCRUD": {
"commandName": "Project",
"launchBrowser": true,
"launchUrl": “customer”,
"applicationUrl": "https://localhost:5001;http://localhost:5000",
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
}
}
}
}
We open the Postman application, select the Post method and enter our endpoint (http://localhost:5000/Customer). Here, we do not enter any method names in accordance with the rest standards I mentioned above. Requesting “Post” indicates that it is doing the create process. Request body’s as follows.
{
"FirstName": "Jhon",
"LastName": "Doe",
"Email": "[email protected]"
}
In the image above, we see that the transaction returned 200 response codes. Now let’s see if the record is actually added.
We connect to our database via the command line, then select our database and run the select query.
As you can see, our record has been successfully added. According to the first assignment we made, the CreatedOn and ModifiedOn fields have the same value.
Now, let’s create an endpoint where we can read the customer we recorded. For this, we create a method that brings users according to their emails. This method will retrieve the mail address given through the querystring, query it from the database and return the matching records. When the user is not found, we return 404 as HttpStatus code. This is again a behavior that needs to be done according to the Rest standards. Our method is as follows.
[HttpGet]
public IActionResult Get ([FromQuery] string email)
{
var customer = _dbContext.Customer.FirstOrDefault (x => x.Email == email);
if (customer == null)
return NotFound ();
return new OkObjectResult (customer);
}
We use the [FromQuery] attribute in this method instead of the [FromBody] attribute we use in the method that creates Customer. We need to get the parameters we expect in the methods we use with HttpGet via the querystring [FromQuery] attribute is exactly here. In methods that take long parameters, instead of specifying just one parameter, we can also map a request model directly.
We call the method we created through Postman.
As seen in the image above, when we query the record we just added with the given e-mail address, we see that the matching record is returned.
It’s time to update the record we added. The update method should be “HttpPut” according to rest standards. It should ensure that the method it adds on HttpPut will update one or more records. This rule also binds the method’s route value to a rule. In our scenario, the customer id to be updated should be specified in the url.
(http://localhost:5000/Customer/{id})
However, we need to create a request class that holds the new values of the fields to be updated. Let’s create our UpdateCustomerRequest class inside our Requests folder. Our class will be as follows.
public class UpdateCustomerRequest
{
public UpdateCustomerRequest()
{
}
public string FirstName { get; set; }
public string LastName { get; set; }
public int IsActive{get; set;}
}
According to this requested, we only allow our customer’s FirstName, LastName and IsActive values to be updated. Of course, we will assign the ModifiedOn value ourselves according to the business situation. Now let’s create our method. The body of our method will be as follows.
[HttpPut("{id}")]
public IActionResult Put([FromRoute] int id,[FromBody]UpdateCustomerRequest request)
{
var customer = _dbContext.Customer.FirstOrDefault(x => x.PKCustomerId == id);
if (customer == null)
return NotFound();
customer.FirstName = request.FirstName;
customer.LastName = request.LastName;
customer.IsActive = request.IsActive;
customer.ModifiedOn = DateTime.Now;
_dbContext.SaveChanges();
return Ok();
}
In case the user to be updated cannot find, we inform the client with the Http 404 status as above. Here we can send an extra error message and make the situation more meaningful.
The definition of [HttpPut (“{id}”)] shows that the method expects an id via the url. We specify that we will get this id over the route with the [FromRoute] attribute in the method parameters.
Let’s try our method now. The request body is as follows
{
"FirstName": "John updated",
"LastName": "Doe updated",
"IsActive": 0
}
As you can see above, our method returns 200. Now let’s check if it is updated on the database side.
With the update process, you can see that the ModifiedOn value is separated from the CreatedOn value.
With the update process, you can see that the ModifiedOn value is separated from the CreatedOn value.
As the last process, we have to delete the user I created. Here, deleting a record from the database is not a preferred scenario in real world applications. Instead, a flag checks whether the user data is deleted. This is necessary for the data parties to be able to view consistency and historical reports, but we will completely delete our record from the database to show how these operations were performed.
We do not need any request class for this operation. According to Rest standards, we will take from the url which user should be deleted and perform the operation with the HttpDelete method.
Our method is as follows.
[HttpDelete("{id}")]
public IActionResult Delete([FromRoute] int id)
{
var customer = _dbContext.Customer.FirstOrDefault(x => x.PKCustomerId == id);
if (customer == null)
return NotFound();
_dbContext.Customer.Remove(customer);
_dbContext.SaveChanges();
return Ok();
}
As in our previous method, we specify that we will get the id of the record we want to delete from the url with the [FromRoute] parameter. Let’s call our method and check the results.
We were able to see 200 status codes in the image. Now let’s see if we can successfully delete the record from the database.
Yes, it is no longer available in customer database with id #2.
Final Toughts
We learned how to perform CRUD operations with SQLite and Entity Framework in a simple way. It is not suitable to perform database operations through the controller in real world applications. At the same time, it is not a preferred method to directly inject the DbContext class. Instead, you can try using Generic Repository and Unit of Work patterns for Service classes and database operations.
