Building an ASP NET Core Web API Example with Database Access Without Entity Framework

Asp.NET Core Web API is a popular choice for building backend services, and while Entity Framework is a common database management tool, it's not the only option. In this guide, we'll explore creating an Asp.NET Core Web API with a database without using Entity Framework, focusing on Dapper, a simple, fast, and practical micro-ORM.

Asp.net Core web API Tutorial: C# web API .Net Core Example

Dapper is a lightweight, open-source ORM that helps interact with databases using plain SQL. It's an excellent alternative when you want to have more control over your database interactions and avoid the overhead that sometimes comes with Entity Framework.

Setting Up the Project

To get started, create a new Asp.NET Core Web API project with the following command:

How to call Stored Procedures in ASP.NET Core

dotnet new webapi -n NoEfWebApi

Adding the Database Context

First, let's add a SQLite database for simplicity. In the appsettings.json file, add the following connection string:

Data Access in ASP.NET Core using EF Core (Database First)

"ConnectionStrings": {
  "DefaultConnection": "Data Source=blogging.db"
}

Next, create a new class DatabaseContext.cs to handle database operations.

Setting Up Dapper

Install the Dapper package via NuGet to start using it in the project:

a web api with asp net core, net 6 0 build a web api with asp net core

dotnet add package Dapper

Now, let's configure Dapper with the DefaultConnection from appsettings.json.

Finally, create a new model class, say Blog.cs, to represent the data in the database.

Creating the API Controller

Implementing Web APIs: Connect & Fetch Data Like a Pro 🌍🚀

Create a new API controller, BlogController.cs, to handle CRUD operations using Dapper.

Getting All Blogs

Advanced Architecture for ASP.NET Core Web API

Create ASP.NET Core Identity SQL Database Asp Dot Net Core Web API - Asp .Net core Web API - Part 2

Code First Approach in Entity Framework in Asp.net MVC with Example - Tutlane

8 Buenas prácticas para el diseño de APIs: | Miguel Angel Durán García

a diagram that shows the types of apis and their use cases

the diagram shows how to use apis for security and data storage, as well as other

Let's start by creating an action to retrieve all blogs from the database.

Creating a New Blog

Next, create an action to insert a new blog into the database.

Updating and Deleting Blogs

Following the same pattern, create actions to update and delete blogs.

Here's an example of the CreateAsync method:

```csharp [HttpPost] public async Task CreateAsync(Blog blog) { using (var connection = new SqlConnection(_config.GetConnectionString("DefaultConnection"))) { await connection.ExecuteAsync("INSERT INTO Blogs (Url, Name, Description) VALUES (@Url, @Name, @Description)", blog); } return CreatedAtAction(nameof(GetBlog), new { id = blog.Id }, blog); } ```

Migrating the Database

To create the Blogs table in the SQLite database, run the following script:

```sql CREATE TABLE Blogs ( Id INTEGER PRIMARY KEY AUTOINCREMENT, Url NVARCHAR(200) NOT NULL, Name NVARCHAR(50) NOT NULL, Description NVARCHAR(255) ); ```

Seeding the Database

To add initial data to the database, create a method in the DatabaseContext class to seed the data:

```csharp public async Task InitAsync() { using var connection = new SqlConnection(_config.GetConnectionString("DefaultConnection")); var blogs = new List { new Blog { Url = "https://example.com", Name = "Test Blog", Description = "A test blog" } }; await connection.ExecuteAsync("INSERT INTO Blogs (Url, Name, Description) VALUES (@Url, @Name, @Description)", blogs); } ```

Finally, call this method in the Configure method of the Startup.cs file:

```csharp public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DatabaseContext context) { context.InitAsync().Wait(); // ... } ```