Build a Complete ASP NET Core Web API Tutorial Without Entity Framework

Welcome to this comprehensive tutorial on creating an ASP.NET Core Web API without using Entity Framework. Entity Framework is a popular choice for database access in ASP.NET Core, but sometimes you might want to use other ORMs or access data using different methods. This guide will walk you through the process of creating a simple API that interacts with a database using ADO.NET, showcasing alternative methods for handling database operations in ASP.NET Core.

By the end of this tutorial, you'll have a solid understanding of how to create and configure an ASP.NET Core Web API for database interaction without Entity Framework. You'll also gain insights into using ADO.NET for database operations, which can be beneficial in scenarios where you need fine-grained control over your data access or want to use other ORMs like Dapper.

Project Setup and Configuration

Before diving into the core functionality, let's set up a new ASP.NET Core Web API project and configure it for database interaction.

First, create a new Web API project using the dotnet new command, followed by webapi and your project name:

Creating the Web API Project

dotnet new webapi -n DatabaseInteractionApi

Change into your new project directory and add a database provider package, such as System.Data.SqlClient, for interacting with SQL Server:

Adding a Database Provider Package

dotnet add package System.Data.SqlClient

Next, configure the application's database connection settings in the appsettings.json file:

Configuring the Database Connection

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=(localdb)\\mssqllocaldb;Initial Catalog=DatabaseInteractionDb;Integrated Security=True;Connect Timeout=30;Encrynption=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }
}

"Finally, create a database and tables for storing sample data in your database. For this tutorial, we'll create a Products table with columns for Id, Name, and Price.

Now that your project is set up and configured, let's move on to creating and using CRUD API operations without Entity Framework.

Creating CRUD Operations using ADO.NET

In this section, we'll create simple CRUD (Create, Read, Update, Delete) operations using ADO.NET in an ASP.NET Core Web API. This approach allows you to interact with your database directly, giving you fine-grained control over your data access.

To start, create a new service class called ProductService.cs inside a new folder named Services. This class will handle database operations using ADO.NET.

Now, let's dive into each CRUD operation and see how to implement them using ADO.NET.

Create (Insert) Operation

Implementing the insert operation involves creating an Insert method in your ProductService class that takes a product as a parameter and executes an INSERT SQL command to add the product to the database.

Here's a simple example of how to implement the insert operation using ADO.NET:

Next, let's create the Read operation to retrieve products from the database.

Read (Get) Operation

Implement the read operation by creating a GetProducts method in your ProductService class. This method should execute a SELECT SQL command to fetch all products from the database and return them as a list:

 products = new List();

            while (await reader.ReadAsync())
            {
                products.Add(new Product
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Price = reader.GetDecimal(2)
                });
            }

            return products;
        }
    }
}

Update Operation

To implement the update operation, create an Update method in your ProductService class that accepts a Product object as a parameter. This method should execute an UPDATE SQL command to modify the specific product in the database:

Here's an example of how to implement the update operation:

Delete Operation

To implement the delete operation, create a Delete method in your ProductService class that takes a product's Id as a parameter. This method should execute a DELETE SQL command to remove the specified product from the database:

In conclusion, by following this tutorial, you've gained valuable insights into creating an ASP.NET Core Web API that interacts with a database using ADO.NET, showcasing an alternative method for handling database operations without relying on Entity Framework. This approach can be beneficial when you need fine-grained control over your data access or want to use other ORMs like Dapper. Happy coding, and may your APIs thrive!