Work with Web API and Class Library projects in Visual Studio Code

In the previous article you learned to setup Visual Studio Code for ASP.NET Core development. We created ASP.NET Core MVC project and used scaffolding to create CRUD pages. In this article you will create a Web API that exposes the CRUD functionality to its client apps. You will also wrap the EF Core code inside a class library project.

The example discussed in this article is similar to the previous example in that it performs CRUD operations on the Employees table. However, I am going to make the following changes to the example :

• We will now use SQL Server database for storing the data
• We will create a Class Library that encapsulates the data access code
• CRUD operations will be performed via a simple repository
• The Class Library project is referenced inside a Web API project
• Web API project expose the CRUD actions to the client apps
• A .sln file is created that contains these two projects

Let's get started. First, we will create a blank solution file named EmployeeSolution. To create the solution, open Visual Studio Code and then the Terminal window. Issue the following .NET CLI command in the terminal window :

dotnet new sln --output EmployeeSolution    

This command will create a folder named EmployeeSolution and then will create EmployeeSolution.sln file inside that folder. There is also a variant of this command :

dotnet new sln --name EmployeeSolution

This command won't create any folder. It will simply create a blank solution file with the specified name.

Now open the EmployeeSolution folder in VS Code using File | Open Folder menu option. Opening the EmployeeSolution folder will automatically trigger the Solution Explorer extension and the blank EmployeeSolution.sln will loaded as shown below:

As you can see, there is a + icon for adding a new project to the solution. We will add a Class Library project named EmployeeClassLibrary. So, click on the + icon to reveal the available project templates.

When you select the Class Library project template, you will be asked for a project name. Enter EmployeeClassLibrary and complete the process to add it in the solution.

Follow the same steps to add a new Web API project named EmployeeWebApi into the solution, this time picking Web API as the project template.

After adding these two projects your Solution Exploer and the physical folder should resemble the following:

Next, open the NuGet Gallery extension using the command palette and install the following NuGet packages in the EmployeeClassLibrary project.

• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

You can install a NuGet package for a particular project by checking the required checkbox :

Now, add DataAccess folder to the EmployeeClassLibrary project and create the Employee.cs file in it. The Employee class looks like this :

public class Employee
{
    [Required]
    public Guid EmployeeID { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public DateOnly HireDate { get; set; }
}    

Then add AppDbContext.cs class file and write the following code in it:

public class AppDbContext : DbContext
{
    var connectionString = @"Data Source=.;
    Initial Catalog=CodeDb;
    User ID=sa;Password=*****;
    encrypt=false";

    public AppDbContext() : base() 
    { 

    } 
    protected override void OnConfiguring
    (DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString);
    }
   
    public AppDbContext (DbContextOptions
    <AppDbContext> options)
        : base(options)
    {
    }
    public DbSet<Employee> Employees 
        { get; set; } = default!;
}

This code begins by declaring a hardcoded connection string. We are trying to connect to CodeDb database using certain credentials. Make sure to change the connection string -- server, database name, and credentials -- as per your SQL Server setup. This value is used in the UseSqlServer() method to set the SQL Server connection string. And there is also an empty constructor. These three pieces of code are required only for creating the EF Core migrations. Once the database table is created we will comment them and pick the connection string from appsettings.json of the Web API project. The second constructor is needed to support DI.

Then add a new interface called IEmployeeRepository in the DataAccess folder and write the following code in it.

public interface IEmployeeRepository
{
    List<Employee> SelectAll();
    Employee SelectByID(Guid id);
    void Insert(Employee emp);
    void Update(Employee emp);
    void Delete(Guid id);
}    

This is a simple repository interface with five methods -- SelectAll(), SelectByID(), Insert(), Update(), and Delete(). The method signatures are self explanatory.

Now create EmployeeRepository class as the concrete implementation of IEmployeeRepository.

public class EmployeeRepository : IEmployeeRepository
{
    private readonly AppDbContext db;

    public EmployeeRepository(AppDbContext db)
    {
        this.db = db;
    }
    public List<Employee> SelectAll()
    {
        return db.Employees.ToList();
    }
    public Employee SelectByID(Guid id)
    {
        return db.Employees.Find(id);
    }
    public void Insert(Employee emp)
    {
        db.Employees.Add(emp);
        db.SaveChanges();
    }
    public void Update(Employee emp)
    {
        db.Employees.Update(emp);
        db.SaveChanges();
    }
    public void Delete(Guid id)
    {
        db.Employees.Remove
        (db.Employees.Find(id));
        db.SaveChanges();
    }
}

The EmployeeRepository class is quite straightforward. We don't do much complex logic or error checking but you can add that if you want.

This completes our EmployeeClassLibrary project. We will now create the SQL Server database and the Employees table.

Open the terminal and change the working folder to EmployeeClassLibrary. Then issue these commands one-by-one:

dotnet ef migrations add Initial
dotnet ef database update    

The Migrations folder will be created in the EmployeeClassLibrary project. And Employees table will be created in the database. Note that if the database mentioned in the connection string doesn't exist, it will also be created. The following figure shows the Employees table in the SSMS.

Before we move further, comment the hardcoded connection string, empty constructor, and the OnConfiguring() method from AppDbContext class. That code is no longer needed since database table has been created. So, now you will have :

public class AppDbContext : DbContext
{
    public AppDbContext 
    (DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }
    public DbSet<Employee> Employees 
        { get; set; } = default!;
}

Next, we would like to add a project reference for EmployeeClassLibrary in the Web API project so that we can use the repository and data access code. To do this, right click on the EmployeeWebApi project in the Solution Explorer and select Add Project Refrerence from the shortcut menu.

Then pick EmployeeClassLibrary from the list:

You will find that this entry is now shown under Dependencies | Projects folder:

Also notice from the figure that Microsoft.AspNetCore.OpenApi and Swashbuckle.AspNetCore NuGet packages are already added for you as a part of the default Web API project template. These packages are needed for Swagger integration (we will use that later in this article).

Now, create ApiControllers folder in the Web API project and add EmployeesController.cs file to it. This is going to be our Web API controller that houses a few API actions. Note that the default Web API project template uses minimal API but we want to create controller based API.

The completed EmployeesController class is shown below :

[ApiController]
[Route("api/[controller]")]
public class EmployeesController : ControllerBase
{
    private readonly IEmployeeRepository repository;

    public EmployeesController
    (IEmployeeRepository repository)
    {
        this.repository = repository;
    }

    [HttpGet]
    public IActionResult Get()
    {
        return Ok(repository.SelectAll());
    }

    [HttpGet("{id}")]
    public IActionResult Get(Guid id)
    {
        return Ok(repository.SelectByID(id));
    }

    [HttpPost]
    public IActionResult Post(Employee emp)
    {
        repository.Insert(emp);
        return CreatedAtAction("Get", 
        new { id = emp.EmployeeID }, emp);
    }

    [HttpPut("{id}")]
    public IActionResult Put(Guid id, Employee emp)
    {
        repository.Update(emp);
        return NoContent();
    }

    [HttpDelete("{id}")]
    public IActionResult Delete(Guid id)
    {
        repository.Delete(id);
        return NoContent();
    }
}

I won't go into much details of this code in this article since our focus area is VS Code development. There are plenty of articles on Web APIs and Minimal APIs that you can read from the Categories menu on the left. It is suffice to say that EmployeesController exposes CRUD functionality to the client apps with the help of the EmployeeRepository class we created earlier.

Now open appsettings.json file from the Web API project and place the database connection string there as shown below:

"ConnectionStrings": {
    "AppDb" : "Data Source=.;
    Initial Catalog=CodeDb;
    User ID=sa;
    Password=*****;
    encrypt=false"
  }    

This is the same connection string we used for creating EF Core migrations. However, instead of hardcoding it we store it in the configuration file in AppDb key. Make sure to change it as per your SQL Server setup.

Next, open Program.cs from the Web API project add the following code in it (many of these statements will be already there as a part of the default template code).

var builder = WebApplication.CreateBuilder(args);
var connStr = builder.Configuration.
GetConnectionString("AppDb");
builder.Services.AddDbContext<AppDbContext>
    (options => options.UseSqlServer(connStr));
builder.Services.AddScoped
<IEmployeeRepository, EmployeeRepository>();

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.MapControllers();
app.Run();

This code registers AppDbContext and EmployeeRepository with the DI container. Notice how the database connection string is being supplied to AppDbContext. We also wire Swagger UI so that we can test our APIs.

This completes our Web API project. To test the Web API, press F5 or click on Run | Start Debugging menu option. This will launch a browser window and load the Swagger UI as shown below.

You can test the CRUD operations using Swagger UI as you normally do. The following figure shows both the projects in VS Code Solution Explorer.

You can play with debugger features of VS Code such as breakpoints and step-by-step code execution. Read more about debugging in VS Code here.

That's it for now! Keep coding!!