ASP.NET Web API Backend: Part 2
In the first part of this tutorial, we created an ASP.NET Web API back-end with a RESTful API controller for products that has endpoints for Create, Read, Update, and Delete, commonly known as CRUD commands.
We tested out the functionality of these endpoints using Postman, a free developer tool for testing APIs, and saw that by following conventions, the ASP.NET framework takes care of routing requests to the correct endpoint.
In this tutorial, we’ll add a SQL Server database to our project to store data!
You can download the finished version of this project: part 2 on our GitHub page.
About Relational Databases
The core functionality of most applications is moving data around in a database. A database is a lot like a collection of spreadsheets, but a lot stricter about what can go where, as explained in the earlier lesson on relational databases.
It’s always important to be careful whenever making changes to a database. The database is kind of like the ‘human exclusion zone’ in one of those Amazon warehouses where only robots are allowed to move around. That’s because the application depends on data to be exactly where it’s supposed to be. Typically, developers don’t interact with the database or its data directly, but instead write code which does.
There are many varieties of databases that you can use with an ASP.NET application, though SQL Server tends to be the default choice since it’s also a Microsoft technology. It’s free and open source, so unless you have a specific need for using something else, just use SQL Server if you are working with C# and ASP.NET.
If you haven’t already, download SQL Server and install it on your computer. This is not a small download but hey, you’re going to be a developer right? Once you’ve installed SQL Server, you may be wondering, ‘ok where is it’? Well it’s not really a program so you won’t find it anywhere. It’s a database engine which is constantly running in the background of your computer – and no it won’t slow your computer down, it’s super light. If you were curious, you could open up Services by typing that into your start menu and find that SQL Server is indeed running.
Now that you have SQL Server installed, one other tool that is helpful to have is SQL Server Management Studio (SSMS). Download this for free too. This tool will allow you to connect to your databases, local or remote, and peer into what is happening. You can also directly manipulate data, export tables, run SQL commands, and a lot more. Visual Studio has its own database explorer, but SSMS is a bit more powerful.
Another powerful thing is the Nano ASP.NET Boilerplate. With Nano you can generate fully featured projects with all of the tedious work like authentication, architecture, and infrastructure already done for you. Create unlimited projects that implement all the modern best practices with the detailed documentation. The boilerplate comes with an admin UI provided in Vue, React, and Razor pages saving you a ton of effort on both the back-end and front-end.
SQL and ORMs
Sending commands using Structured Query Language, or SQL, is how anything happens in a database. Want to select data? Use the Select command. Want to write data? Use the Insert Into Table commands.
In our code we could initialize a database connection and write SQL commands with values passed in from our code. For example, to create a new product we could create a SQL command that inserts the values received from a CreateProductRequest into a table called Products. This is how you would do it in a programming 101 class. In practice though, ASP.NET developers would tell you that writing direct SQL is madness! ASP.NET provides power tools for that like Entity Framework.
What about the database and the tables themselves? In a programming 101 class, you would probably use a management tool like SSMS to create a database along with a table called Products within that database and set up the columns. The programming 101 class is there to teach you theory, while this tutorial is here to show you the practical approach used by the industry today.
The problem with manually creating a database is that anytime the code is redistributed to another developer in your team or saved it to source control, the database design (schema) won’t exist. The developer would have to manually recreate your database design, known as a schema, which could be very large and complex. The solution therefore, is to define the database design in the code. This is known as code-first design, and it’s one of the critical features provided by Entity Framework.
Using an ORM to interact with the database is the practical approach. ORM stands for Object Relational Mapper. It is a mechanism which serves as the bridge between your application and database. It allows you to define and manipulate your database with code. It serves as a translator, preventing us from having to write messy raw SQL commands among other benefits.
Entity Framework is the most popular ORM for ASP.NET. Every web application framework has ORMs made for it. For ASP.NET, Entity Framework is the best ORM so let’s get on with adding it to our project.
Entity Framework
We can expand the functionality of our application by adding new packages from NuGet. NuGet is an online marketplace of extensions known as packages or libraries; usually free. If you have ever worked with JavaScript anything before, it’s similar to Node Package Manager (NPM). A nice thing about Visual Studio is that NuGet is seamlessly integrated with it. Whenever you want to add new libraries to extend the capabilities of your project, its as easy as opening up NuGet and selecting the ones you want to install. They will be added as dependencies to the project.
Right click your project, select Manage NuGet Packages, and search for Entity Framework. There are three packages that we need to install:
- Microsoft.EntityFrameworkCore.Design
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.SqlServer
Why are there three separate libraries for Entity Framework? Because even though you’ll almost always need all three of these to accomplish 90% of the use cases, it’s better to provide the community with granular control!
SqlServer is the one that does most of the actual ORM tasks. Tools and Design are used to generate migrations, which we will learn about next.
Once these are installed, we they will appear under the project dependencies list. Whenever you share your project code with another developer, these dependencies will get added automatically.
Adding Entities and DbContext
First thing we need for Entity Framework is to define a table of contents for our database. This is known as a Database Context. Create a new folder in your project called Infrastructure. You can name it anything but infrastructure is descriptive because applications often have a group of items that facilitate functionality but aren’t particular to the business logic of the application.
Within the infrastructure folder, create a class called ApplicationDbContext. This also can be called anything but it needs to inherit from DbContext which is the special class provided by Entity Framework that makes it function as table of contents for the database. Create the constructor as show here and then we will add DbSets for our entity classes.
namespace SimpleWebApiP2.Infrastructure
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
}
}Create another folder called Domain. This is not a required name but it’s fairly common to put entity classes inside a folder called Domain. An entity is a class that represents a business object or data model that corresponds to a database table or a real-world concept in your application domain.
Create a new class within the domain folder called Product. Add the following properties:
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}Properties are declared as public or private, usually public unless they are to be used internally by the class for some kind of calculated property. They are also declared with the type like string, integer, boolean, etc., also a getter and setter which are shorthand methods that enable read and write access.
Frameworks like ASP.NET and libraries Entity Framework are convention-based, meaning we can save ourselves a bit of work by just following the expected way of doing things. One convention is that having a property named Id will be automatically treated as the primary key. A primary key is a column that every entity table must have to uniquely identity each row resource, usually with an integer or GUID (globally unique identifier) type.
With our entity class created, we can now add it as a DbSet to include it in our database table of content. Anytime you add a new entity, you should add a new DbSet to the DbContext; otherwise Entity Framework won’t know about it.
namespace SimpleWebApiP2.Infrastructure
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
// -- add DbSets here for new application entities
public DbSet<Product> Products { get; set; }
}
}Now we need to hook our Entity Framework mechanism into the web application itself. So the way we do that is the same way we hook anything into an ASP.NET application, by registering it as a service.
Program.cs is the top level class in our application and it’s where we can register services. Dependency Injection is a larger topic to explain, but in summary, for your application to know about anything, it must be registered in the service container. The service container is like the orchestrator of the whole song and dance your application is performing.
Here we add Entity Framework, and pass in some configuration. We also specify that ApplicationDbContext is our database context class, like a table of contents.
// register db context
builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
The configuration for Entity Framework is specifying that we want to use SqlServer as the database technology, and we point to the app setting (appsettings.json) which contains the connection string. The UseSqlServer directive comes from one of the packages we installed earlier, EntityframeworkCore.SqlServer. Entity Framework has database connection adapters for all the popular databases, not just SQL Server, so we need to specify which.
The connection string is the setting that provides the location for the database, whether that be a local database as in this example, or a remote one. We need to add that setting in ConnectionStrings and name it something like DefaultConnection.
"ConnectionStrings": {
"DefaultConnection": "Data Source=(localdb)\\mssqllocaldb;Database=SimpleWebApiDb;Trusted_Connection=True;MultipleActiveResultSets=true"
},The practice is to avoid hard coding application settings like this one so that they can be easily changed. When the app is published to a server in the cloud, these settings are typically replaced by the cloud provider’s platform or on the fly during the publish process. In this example, the localdb connection would get replaced with an online database connection when published.
This is all we need to do to add Entity Framework and database to our application. If we run the application now though, nothing will happen because we need create migrations. Migrations are what actually initialize and make changes to a database. These migrations are explicitly controlled by the developer.
Migrations
Migrations are classes that manage the state of the database schema. They start from a blank slate and at each iteration, add new changes to the schema. This is part of the whole code-first development idea. We want to manage everything about the database with code so that when we distribute it, deploy it, or commit it to source control, the database is created the same every time.
We don’t have to actually write these migration classes; instead we use CLI commands to generate them automatically. This functionality is provided by the EntityframeworkCore.Tools and Design packages we installed earlier.
Whenever we create a new migration, the design tool will look at the previous state of the database versus the current state, and generate the code needed to create whenever new columns, tables, etc. to bring it current based on any new entity DbSets or properties we added/removed from before. If a database doesn’t exist when migrations are applied, Entity Framework will create one.
Now that you have an idea about what migrations are, let’s create one. Start by creating a new folder in your project called Migrations. You don’t need to name it Migrations but by convention, Entity Framework will place migrations in a folder named Migrations unless you specify otherwise.
Open up the Package Manager Console which can usually be found as a tab next to the terminal or from the Tools menu, select NuGet Package Manager, then Package Manager Console.
Type the command add-migation with the name of the migration, which we will call initial, then press enter.
add-migration initialThe Entity Framework Design tools will generate a new class within the migrations folder called initial prefixed with the date and time stamp. This class will contain up and down methods, which represent the changes that would be applied to the database to bring it current (up) or roll it back to a previous version (down).
namespace SimpleWebApiP2.Migrations
{
/// <inheritdoc />
public partial class initial : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Products",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(max)", nullable: false),
Description = table.Column<string>(type: "nvarchar(max)", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Products", x => x.Id);
});
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Products");
}
}
}The first time you generate migrations, another class will be created called model snapshot. This is just a reference that Entity Framework uses to keep track of changes and typically you don’t need to worry about this.
Generating a migration does not actually apply it to the database though. For that we need to run another command in the Package Manager Console. Type update-database and run the command. This will tell Entity Framework to check for any pending migrations we have, in this case the initial migration, and run the code in the up method. After we run the update-database command we should see a new database in the database explorer window, or SSMS if you are using that tool. The database will contain one table called Products and have columns for ID, Name, and Description, exactly as we had defined in our model!
update-databaseAnytime anything changes about the DbContext, for example if we add new properties to the Product entity class, or add completely new entities, we will need to create a new migration so that those changes are actually applied to the database. Entity Framework will only run migrations which are pending, not any that have already been applied. It will run the migrations in the order they are named, which is why they are prefixed by a time and date stamp. If you run your application with changes that have not been applied to the database, it will usually result in an error message being thrown. So always remember to apply your migrations and it’s a good practice to create migrations in tiny increments.
Modify the API Controllers
That was quite a dive into databases and Entity Framework, but now we can get back to our API code and finally do something with data!
All database operations are facilitated by the ApplicationDbContext class so we need to inject this into our controller. To do that, create a constructor for the API controller and pass in the context. We also need a read only property for the context object within the controller.
A constructor is a method that is named the same as the class. It runs whenever an object of the class is instantiated. We can bring in functionality from other classes by passing them in as parameters. This will create an instance of them which we can use in the current class.
private readonly ApplicationDbContext _context;
public ProductsController(ApplicationDbContext context)
{
_context = context;
}Let’s start with the CreateProduct method since we don’t have any products in our database yet. Replace the code we had earlier with this:
// create
[HttpPost]
public IActionResult CreateProduct(CreateProductRequest request)
{
var newProduct = new Product()
{
Name = request.Name,
Description = request.Description,
};
_context.Products.Add(newProduct);
_context.SaveChanges();
return Ok(newProduct);
}Create a new object for the new product, and populate its properties with those from the request. By using the new() keyword, we are creating a new instance of a product.
Using the database context object, we can call the Add method on products and pass in this new product. This will queue up Entity Framework to create a new product in the database. Calling SaveChanges will actually write the changes to the database.
In our response we can replace the message we had earlier with an object, the newly created product. The Id will be automatically assigned by the database and the value will already be populated when we return it because Entity Framework tracks those changes.
In Postman create a few new products, remember to send up the name and the description in the message body as json.
Modify the GetProducts method with the following code
// full list
[HttpGet]
public IActionResult GetProducts()
{
var response = _context.Products.ToList();
return Ok(response);
}Use the database context once again and call the ToList method on products. This time our context will return a list of products. Pass that list into the OK() response from the API.
While we are at it, update the GetProduct method with this new code that returns an actual item from the database. This time use the Where method on products and pass a lambda expression to search products that have a matching Id. Then use the FirstOrDefault to return the matching entity.
// single by Id
[HttpGet("{id}")]
public IActionResult GetProduct(int id)
{
var response = _context.Products.Where(x => x.Id == id).FirstOrDefault();
return Ok(response);
}Open up Postman to test out the new methods. Try the get list of products and also the get by Id. Use the Id of one of the products in the request.
Next modify the UpdateProduct method with the following code:
// update
[HttpPut("{id}")]
public IActionResult UpdateProduct(UpdateProductRequest request, int id)
{
var productInDb = _context.Products.Where(x => x.Id == id).FirstOrDefault();
if (productInDb == null) {
return BadRequest("Product not found");
}
productInDb.Name = request.Name;
productInDb.Description = request.Description;
_context.SaveChanges();
return Ok(productInDb);
}First we need to find the existing product in the database using the Id just like we did before. If a product is not found, we can return a BadRequest response along with a message. If we do find a product, we’ll update its properties with the new values from the request and call SaveChanges on the database context. Notice how Entity Framework will track any changes to an entity object. Return the update product object as a response.
In DeleteProduct we will once again check that the entity exists. If it does, call the Remove method on the database context and pass the entity to be removed. Save the changes and return the id of the deleted product.
Go back to Postman now to test the remaining methods. All should be working as expected.
Conclusion
We’ve added a lot of functionality to our application. In the first part of the tutorial we saw how to create API endpoints in ASP.NET. Now we have gone a step further in this lesson by adding a database with Entity Framework as an ORM.
We learned about code-first design and used it to map out a database schema which is managed in code. Then we created a migration to initialize the database. The API endpoints now interact with the database, creating, reading, updating, and deleting data. These operations known as CRUD are the core of almost every application!
Don’t forget to check out the Nano ASP.NET Boilerplate for when your skills in ASP.NET get a little better. It will save you months of research on best practices and show you the way to build scalable applications.
In the next lesson, we’ll make our data model a bit more complex with related data and see how to manage that with Entity Framework.