How to Connect .NET Web API with SQL Server using Entity Framework

Photo by Arif Riyanto on Unsplash

In today’s data-driven world, building web applications that interact with databases is a common requirement. .NET developers often find themselves working with SQL Server, a popular relational database management system, to store and retrieve data. One of the most efficient ways to interact with SQL Server in a .NET environment is through Entity Framework (EF). EF is an Object-Relational Mapping (ORM) framework that simplifies database interactions by allowing developers to work with objects instead of raw SQL queries. In this tutorial, we will explore how to connect a .NET Web API with SQL Server using Entity Framework in the Database-First approach.

Why Entity Framework?

Entity Framework offers several benefits, making it a popular choice among .NET developers for data access:

1. Abstraction: EF abstracts the database operations, enabling developers to work with objects and LINQ queries, promoting cleaner and more maintainable code.
2. Productivity: By handling repetitive CRUD operations, EF saves developers time and effort, allowing them to focus on business logic.
3. Flexibility: EF supports various database providers, allowing seamless switching between SQL Server, MySQL, SQLite, etc.
4. Migrations: EF supports database migrations, making it easier to evolve the database schema along with application changes.

Database-First vs. Code-First Approach

There are two main approaches to using Entity Framework: Database-First and Code-First.

1. Database-First Approach: The Database-First approach involves creating the database schema first, either manually or using a database design tool, and then generating the entity model from the existing database. This approach is beneficial when working with an existing database or when the database design is the primary concern.
2. Code-First Approach: The Code-First approach involves defining the entity classes and their relationships in code first, and then letting EF generate the database schema automatically based on these classes. This approach is suitable when the focus is on application development and not constrained by an existing database.

For this tutorial, we will use the Database-First approach.

So lets get the party started.

Step 1:

Setting up the Database Before starting, ensure you have SQL Server installed, and create a database named SampleDB. You can use the following SQL script to create the necessary table:

CREATE DATABASE SampleDB;

USE SampleDB;
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL
);
INSERT INTO Customers (CustomerId, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

Step 2:

Create a .NET Web API Project Let’s create a new .NET Web API project using Visual Studio or the .NET CLI:

dotnet new webapi -n DotnetWebApiWithEF

Step 3:

Install Entity Framework Core In the project directory, install the Entity Framework Core package using the following command:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Step 4:

Scaffold the Entity Model from the Database To scaffold the entity model from the existing database, use the EF CLI tool. Run the following command in the project directory:

dotnet ef dbcontext scaffold "Server=YOUR_SERVER_NAME;Database=SampleDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Models

Replace YOUR_SERVER_NAME with the name of your SQL Server instance.

Step 5:

Configure the DbContextIn the Models folder, you will find the generated SampleDBContext.cs file. This class represents the database context and allows interaction with the database.

// SampleDBContext.cs

using Microsoft.EntityFrameworkCore;

namespace DotnetWebApiWithEF.Models
{
    public partial class SampleDBContext : DbContext
    {
        public SampleDBContext(DbContextOptions<SampleDBContext> options)
            : base(options)
        {
        }
        public virtual DbSet<Customer> Customers { get; set; }
        // Additional DbSet properties for other tables can be added here
    }
}

Step 6:

Create the Web API Controller Now, let’s create a Web API controller that interacts with the database through the DbContextwe just created.

// Controllers/CustomerController.cs

using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using DotnetWebApiWithEF.Models;

namespace DotnetWebApiWithEF.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        private readonly SampleDBContext _context;
        public CustomerController(SampleDBContext context)
        {
            _context = context;
        }

        // GET: api/Customer
        [HttpGet]
        public ActionResult<IEnumerable<Customer>> GetCustomers()
        {
            return _context.Customers.ToList();
        }

        // GET: api/Customer/1
        [HttpGet("{id}")]
        public ActionResult<Customer> GetCustomer(int id)
        {
            var customer = _context.Customers.Find(id);
            if (customer == null)
            {
                return NotFound();
            }
            return customer;
        }

        // POST: api/Customer
        [HttpPost]
        public ActionResult<Customer> CreateCustomer(Customer customer)
        {
            if (customer == null)
            {
                return BadRequest();
            }

            _context.Customers.Add(customer);
            _context.SaveChanges();

            return CreatedAtAction(nameof(GetCustomer), new { id = customer.CustomerId }, customer);
        }
    }
}

Step 7:

Run the Web API Application Now, run the Web API application using the following command:

dotnet run

Once the application is running, you can open a web browser or use a tool like Postman to interact with the Web API. The API endpoints can be accessed using the base URL specified in the [Route] attribute of the CustomerController.

For example:

• To retrieve all customers: https://localhost:5001/api/Customer
• To retrieve a specific customer: https://localhost:5001/api/Customer/1
• To add new customer use POST request with the customer body with this urlhttps://localhost:5001/api/Customer

{
    "CustomerId": 2,
    "FirstName": "Jane",
    "LastName": "Smith",
    "Email": "jane.smith@example.com"
}

If you have reached this it means you have successfully connected to your Database.

Conclusion:

In this tutorial, we explored how to connect a .NET Web API with SQL Server using Entity Framework in the Database-First approach. We learned about the benefits of Entity Framework, the Database-First approach, and its advantages when working with an existing database. By following the step-by-step guide and using the provided code blocks, you should now have a functional Web API that interacts with SQL Server using Entity Framework.

Happy coding!

And that’s a wrap! If you’ve read this far, it means you liked this article. If that’s true, please leave a clap. I publish similar articles every week, so feel free to follow me for more.