Building a .NET Core API: A Comprehensive Guide to API Development with SQL Server

dot_net_core_web_api

Building a .NET Core API: A Comprehensive Guide to API Development with SQL Server

1. Introduction

.NET Core API is a powerful framework designed for building modern, scalable web applications and services. It operates across different platforms (Windows, macOS, and Linux), making it an excellent choice for diverse development environments. With features such as modular architecture and built-in dependency injection, .NET Core API simplifies the development process while ensuring high performance.

2. Key Features

  • Cross-Platform: Runs seamlessly on multiple operating systems.
  • Lightweight and Fast: Optimized for performance and resource usage.
  • Modular Architecture: Include only necessary components to streamline applications.
  • Built-in Dependency Injection: Simplifies service management and enhances maintainability.
  • Support for Asynchronous Programming: Facilitates handling multiple requests simultaneously.

3. Project Structure

SampleApi/
│
├── Controllers/
│   └── ProductsController.cs
│
├── Models/
│   └── Product.cs
│
├── Data/
│   ├── ApplicationDbContext.cs
│   ├── IProductRepository.cs
│   └── ProductRepository.cs
│
├── appsettings.json
├── Program.cs
└── Startup.cs

4. Database Table Structure

To create a table for the Product model in SQL Server, use the following SQL script:

CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL
);

5. Sample Data Insertion

To populate the Products table with sample data, you can run the following SQL statements:

INSERT INTO Products (Name, Price) VALUES ('Product A', 19.99);
INSERT INTO Products (Name, Price) VALUES ('Product B', 29.99);
INSERT INTO Products (Name, Price) VALUES ('Product C', 39.99);
INSERT INTO Products (Name, Price) VALUES ('Product D', 49.99);
INSERT INTO Products (Name, Price) VALUES ('Product E', 59.99);

After executing the above SQL commands, the Products table will contain the following data:

Id Name Price
1 Product A 19.99
2 Product B 29.99
3 Product C 39.99
4 Product D 49.99
5 Product E 59.99

6. Setting Up Your Environment

Before creating a .NET Core API, ensure that you have the .NET SDK installed on your machine. You can download it from the official .NET website.

7. Creating a New Project

  1. Open your command line interface (CLI).
  2. Run the following command to create a new web API project: 
    dotnet new webapi -n SampleApi
cd SampleApi

8. Defining a Model

Create a simple model class to represent your data. For instance, create a file named Product.cs in the Models folder:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

9. Creating a DbContext

Create a DbContext class in the Data folder to manage database interactions:

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions options) : base(options) { }

    public DbSet Products { get; set; }
}

10. Creating a Repository

Implement a repository pattern to abstract database operations. Create an interface IProductRepository.cs:

using System.Collections.Generic;

public interface IProductRepository
{
    IEnumerable GetAllProducts();
    Product GetProductById(int id);
}

Then, implement the repository in ProductRepository.cs:

using System.Collections.Generic;
using System.Linq;

public class ProductRepository : IProductRepository
{
    private readonly ApplicationDbContext _context;

    public ProductRepository(ApplicationDbContext context)
    {
        _context = context;
    }

    public IEnumerable GetAllProducts()
    {
        return _context.Products.ToList();
    }

    public Product GetProductById(int id)
    {
        return _context.Products.Find(id);
    }
}

11. Creating a Controller

Now, create a controller to handle incoming requests. Update ProductsController.cs to use the repository:

using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("api/[controller]")]
publicHere's the continuation of the HTML article without CSS:

```html
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductRepository _productRepository;

    public ProductsController(IProductRepository productRepository)
    {
        _productRepository = productRepository;
    }

    [HttpGet]
    public ActionResult> GetAll()
    {
        return Ok(_productRepository.GetAllProducts());
    }

    [HttpGet("{id}")]
    public ActionResult GetById(int id)
    {
        var product = _productRepository.GetProductById(id);
        if (product == null)
        {
            return NotFound();
        }
        return Ok(product);
    }
}

12. Setting Up Connection String

Configure the database connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=SampleDb;User Id=your_username;Password=your_password;"
  }
}

13. Dependency Injection

Register the DbContext and repository in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    services.AddScoped();
    services.AddControllers();
}

14. Running Your API

To run your API, use the following command in the CLI:

dotnet run

Your API should now be running at http://localhost:5000/api/products.

15. Conclusion

In this guide, you learned how to create a .NET Core API with database integration, dependency injection, and basic CRUD operations. This foundational knowledge can be expanded upon as you build more complex applications.

Tags: , , , ,