Create, Read, Update, and Delete (CRUD) with ASP.NET Core

In computer programming, create, read, update, and delete (CRUD) are the four basic operations of persistent storage like database (e.g. MySQL, SQL Server, PostgreSQL). While a database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).

This tutorial will look at creating a simple CRUD application with ASP.NET Core. Here, we are going to use SQL Server and ASP.NET Core 5. We will create a CRUD for staff information. To do this, we will follow these steps;

1. Create a database
2. Create an ASP.NET Core application
3. Connecting the application to the database
4. Create a model for staff
5. Migration of the model to the database
6. Create controller
7. Create views for Create, Read, Update, and Delete

Create a database

To do this, launch the "Microsoft SQL Management Studio" application on your computer, and the interface should look like the one below.

Right-click the "Databases" node and select "New Database" and name it "personnel". Expand the "Databases" node to ensure the database you just created is in the node.

Create an ASP.NET Core application

Launch "Visual Studio" on your machine and click on create "New Project"

Search for ASP.NET Core from the page that appears, and then select "ASP.NET Core Web App (Model-View-Controller)" from the list and click "Next"

You will get to the page where you will need to name the application; you can call it "PersonnelInfo" or anything you would prefer for this tutorial. Click "Next" to get to the next step of the wizard. Click "Create" to create a new ASP.NET Core web application. At this point, you have successfully created an ASP.NET Core web application.

Once created, you will see the page below. Looking at the right, you will see the "Solution Explorer". The Solution Explorer shows all the files and folders in the application. From the explorer, you will see folders wwwroot, Controllers, Models, and View.

"wwwroot" is a folder that contains all the static files of the application. The static files include javascript files, css files, and image files.

"Controllers" is a folder containing all the controllers, while Models and Views contain the application models and views, respectively.

Connecting the application to the database

We already created the database and the application; now we need to connect both the application to the database. To do that, we need to first create a connection string. For the connection string, you need

Server = the name of the server that the database is installed on. You can get this from, or you can use "."

Database = name of the database you created, in this case, personnel.

User Id = the user name you are connecting to the database with

Password = the user password to the database

Once you have these details, you put them in a string like this

Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword; (please change the details accordingly to match your setup)

Now we will be adding this to the application. Open the "appsettings.json" from the "Solution Explorer" and add the following to it

"ConnectionStrings": {
    "DefaultConnection": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"
  },

The whole appsettings.json should look like the image below. Please ensure that you change the connection string information to match the information on your computer, or you will not be able to connect to the database.

The appsettings.json is a flat file that you can use to store configuration information for the application. That's why we put the connection string there. Other configurations can also be stored based on the structure of the application.

Now we consume the connection string from the application. To do this, we will need to create a DbContext. One of the very important classes in Entity Framework Core is the DbContext class. We use This class in our application code to interact with the underlying database. It is this class that manages the database connection and is used to retrieve and save data in the database. To create a DbContext, create a folder, name it "Data" and add a class "AddDbContext" to it. To add a folder to a project, right-click the project name from the solution explorer, select "Add" from the context menu and select "Folder" from the next one. To create a class, right-click the folder to add the class and select "Add" from the context menu and select "Class".

Open the newly created class "AppDbContext" and add the following code

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }
}

You will notice some red lines, and this is because we have not added EntityFrameworkCore, which is a library for ORM (i.e, an Object-Relational Mapper). To do this, right-click the project name from the "Solution Explorer" and click "Manage Nuget Packages"

This will open the page to manage the nugget libraries in the application. Go to the Browse tab and search for EntityFrameworkCore. Select the "Microsoft.EntityFrameworkCore" and ensure that you select version 5 and click "Install". Do the same for "Microsoft.EntityFrameworkCore.SqlServer" and "Microsoft.EntityFrameworkCore.Tools"

Now that you have installed the library, we will now go to AppDbContext to sort out the issues with the class. At the top of the class, add "using Microsoft.EntityFrameworkCore;". This will sort out the issues with the file. Now we will add the DbContext to the application. From the solution explorer, open the file Startup.cs and the following line in the ConfigureServices method.

services.AddDbContext<AppDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

The method should look like this. Now we have successfully connected the application to the database.

Create a model for staff

Now we will create a staff model, which is the object that will be used for the CRUD. The object will contain basic information about the staff like id, last_name, first_name, address, designation, and staff_number. To create a model, we will add a class to the Models folder from the solution explorer and name it Staff. In the staff class, add the following code

public class Staff
{
    [Key]
    [Required]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { set; get; }
    [Required]
    public string LastName { set; get; }
    [Required]
    public string FirstName { set; get; }
    [Required]
    public string Address { set; get; }
    [Required]
    public string Designation { set; get; }
    [Required]
    public string StaffNo { set; get; }
}

Now we need to add the Staff model to the DbContext so that the ORM can recognize it as a database object. To this, navigate to AppDbContext class in the "Data" folder. Open the class and add the following line of code.

Migration of the model to the database

We need to create a table corresponding to the model we created above in our database to be able to store persistent data. We can manually create the table from the "SQL Server management studio" or use migration to automatically create them from visual studio. To do this, we will need to open the "Package Manager Console". From the menu bar, click Tools, click on "Nuget Package Manager" and then "Package Manager Console"

The package manager console will look something like the image below. Here, we will run the commands to automatically create the database tables.

To do this, we will create a migration and then use it to create the database table. Type "Add-Migration InitialCreate" into the command window to create a migration. Then run "Update-Database" to use the migration to create the table on the database. Now, go to the SQL Server management studio and expand the personnel database; also expand the "Tables" node to ensure that the database is successfully created.

Well done if you have gotten so far, you have successfully created a database, ASP.NET Core web application, connected the application to a database, created a model, and migrated the model into the database. At this point, we will create the controller where we will have most of the logic for the application.

Create controller

Add a controller to the Controllers folder in the application and name it "StaffController"

This will create a scaffolding of the controller and make the class look like this.

public class StaffController : Controller
{
    public IActionResult Create()
    {
        return View();
    }
    public IActionResult List()
    {
        return View();
    }
    public IActionResult Edit(int Id)
    {
        return View();
    }
    public IActionResult Delete(int Id)
    {
        return View();
    }
}

The four methods in the controller returns views. We will now create the corresponding views for the methods. In the views folder, add a folder called Staff and create a view named Create.

On the view, using HTML and CSS, create a form with inputs for LastName, FirstName, Address, Designation, and StaffNo.

The code in the Create view should look like this.

<div class="row">
    <div class="col-md-12">
        <form method="post" action="@Url.Action("Post","Staff")">
            <div class="form-group">
                <label>Last name</label>
                <input type="text" class="form-control" name="LastName" placeholder="last name" required>
            </div>
            <div class="form-group">
                <label>First name</label>
                <input type="text" class="form-control" name="FirstName" placeholder="first name" required>
            </div>
            <div class="form-group">
                <label>Staff No</label>
                <input type="text" class="form-control" name="StaffNo" placeholder="staff no" required>
            </div>
            <div class="form-group">
                <label>Designation</label>
                <input type="text" class="form-control" name="Designation" placeholder="designation" required>
            </div>
            <div class="form-group">
                <label>Address</label>
                <textarea class="form-control" name="Address" placeholder="address" required></textarea>
            </div>
            <button class="btn btn-success" type="submit">Add Staff</button>
        </form>
    </div>
</div>

We can see that on the form tag, we have an attribute action mapped to a "Post" method on the "StaffController". So for this form to post data, we will need to add another method "Post" to the StaffController. Before then, we will need to initialize the DbContext on the StaffController to be able to use it. Add the code below at the top of the StaffController.

AppDbContext _context;

public StaffController(AppDbContext context)
{
    _context = context;
}

Then the "Post" method will look like this

public IActionResult Post([FromBody] Staff staff)
{
    //add staff to the context
    _context.Staffs.Add(staff);
    _context.SaveChanges();
    return RedirectToAction("List");
}

Let's complete the code of the List method so that it will pull all the staff that already exists in the database. Update the List method to look like this.

//get all staff in the database
public IActionResult List()
{
    var staff = _context.Staffs.ToList();
    return View(staff);
}

Let's add the List view, as we did before, a view List to the Staff folder in the Views folder on the solution explorer. Add the following code to the List view.

@using PersonnelInfo.Models;
@model List<Staff>
@{
}
<a href="@Url.Action("Create","Staff")">Create</a>
<div class="row">
    <div class="col-md-12">
        <div class="table-responsive">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>Staff No</th>
                        <th>Last name</th>
                        <th>First name</th>
                        <th>Designation</th>
                        <th>Address</th>
                        <th></th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                    @foreach(var staff in Model)
                    {
                    <tr>
                        <td>@staff.StaffNo</td>
                        <td>@staff.LastName</td>
                        <td>@staff.FirstName</td>
                        <td>@staff.Designation</td>
                        <td>@staff.Address</td>
                        <td>
                            <a class="btn btn-sm btn-primary" href="@Url.Action("Edit","Staff",new {id=staff.Id})">Edit</a>
                        </td>
                        <td>
                            <a class="btn btn-sm btn-danger" href="@Url.Action("Delete","Staff",new {id=staff.Id})">Delete</a>
                        </td>
                    </tr>
                    }
                </tbody>
            </table>
        </div>
    </div>
</div>

We have successfully created the Create and Read functionalities on the app. We will now move on to the Update functionality.

Add an Edit view in the same folder as the others and create a form like the one below.

@using PersonnelInfo.Models;
@model Staff
@{
}
<div class="row">
    <div class="col-md-12">
        <form method="post" action="@Url.Action("Update","Staff")">
            <input type="hidden" name="Id" value="@Model.Id" />
            <div class="form-group">
                <label>Last name</label>
                <input type="text" class="form-control" name="LastName" placeholder="last name" value="@Model.LastName" required>
            </div>
            <div class="form-group">
                <label>First name</label>
                <input type="text" class="form-control" name="FirstName" placeholder="first name" value="@Model.FirstName" required>
            </div>
            <div class="form-group">
                <label>Staff No</label>
                <input type="text" class="form-control" name="StaffNo" placeholder="staff no" value="@Model.StaffNo" required>
            </div>
            <div class="form-group">
                <label>Designation</label>
                <input type="text" class="form-control" name="Designation" placeholder="designation" value="@Model.Designation" required>
            </div>
            <div class="form-group">
                <label>Address</label>
                <textarea class="form-control" name="Address" placeholder="address" required>@Model.Address</textarea>
            </div>
            <button class="btn btn-success" type="submit">Update Staff</button>
        </form>
    </div>
</div>

Now modify the Edit method on the StaffController class to look like this

public IActionResult Edit(int Id)
{
    var staff = _context.Staffs.FirstOrDefault(e => e.Id == Id);
    return View(staff);
}

We will need a function to update the changed data. Let's add the "Update" method to the StaffController

public IActionResult Update(Staff staff)
{
    //get the existing staff
    var old_staff = _context.Staffs.FirstOrDefault(e => e.Id == staff.Id);
    //update with new staff information
    _context.Entry(old_staff).CurrentValues.SetValues(staff);
    _context.SaveChanges();
    return RedirectToAction("List");
}

The only implementation remaining for this tutorial is Delete. To achieve this, modify the delete function to

public IActionResult Delete(int Id)
{
    //get the staff with the Id
    var staff = _context.Staffs.FirstOrDefault(e => e.Id == Id);
    //remove the staff from the database
    _context.Staffs.Remove(staff);
    _context.SaveChanges();
    return RedirectToAction("List");
}

Finally, we are done with coding, and it's now time to test our application. Run the application by clicking the

You will get the homepage of the application

In the address bar, just add  /staff/List

Click the "Create" to add a new Staff; use the "Edit" button to edit the details of an existing staff while the delete button will delete staff from the database.