Accessing Data in ASP.NET Core

This tutorial explores various strategies for accessing and managing data within your ASP.NET Core applications. We'll cover popular data access technologies and best practices to ensure efficient and secure data operations.

1. Entity Framework Core (EF Core)

Entity Framework Core is a modern, cross-platform, open-source Object-Relational Mapper (ORM) that enables .NET developers to work with a database using domain-specific objects, eliminating the need for most of the data-access code they typically need to write.

1.1. Installation

To start using EF Core, you need to install the appropriate NuGet packages. For SQL Server, you would typically use:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

1.2. Defining Models

Define your application's entities as plain C# classes (POCOs).

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

1.3. DbContext

Create a derived DbContext class and properties for each entity class that represents a table in your database.

using Microsoft.EntityFrameworkCore;

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

    public DbSet Products { get; set; }
}

1.4. Migrations

Use EF Core Migrations to incrementally update your database schema as your model changes.

dotnet ef migrations add InitialCreate
dotnet ef database update

1.5. Performing CRUD Operations

Interact with your database context to perform Create, Read, Update, and Delete operations.

// Add a new product
var newProduct = new Product { Name = "Laptop", Price = 1200.00M };
_context.Products.Add(newProduct);
await _context.SaveChangesAsync();

// Get all products
var allProducts = await _context.Products.ToListAsync();

// Update a product
var productToUpdate = await _context.Products.FindAsync(1);
if (productToUpdate != null)
{
    productToUpdate.Price = 1150.00M;
    await _context.SaveChangesAsync();
}

// Delete a product
var productToDelete = await _context.Products.FindAsync(2);
if (productToDelete != null)
{
    _context.Products.Remove(productToDelete);
    await _context.SaveChangesAsync();
}
Tip: Use dependency injection to register your DbContext in Startup.cs (or Program.cs in .NET 6+) and inject it into your controllers or services.

2. Dapper

Dapper is a simple object mapper for .NET. It's an alternative to full ORMs like Entity Framework and is known for its performance and flexibility when dealing with complex queries.

2.1. Installation

dotnet add package Dapper
dotnet add package Dapper.Contrib # Optional: For basic CRUD operations

2.2. Executing Queries

Use Dapper's extension methods to execute SQL queries directly.

using Dapper;
using System.Data.SqlClient; // For SQL Server

// Assuming you have a connection string
string connectionString = "Your_Connection_String";

using (var connection = new SqlConnection(connectionString))
{
    // Querying for a list of products
    var products = connection.Query("SELECT Id, Name, Price FROM Products");

    // Querying for a single product
    var singleProduct = connection.QueryFirstOrDefault("SELECT Id, Name, Price FROM Products WHERE Id = @Id", new { Id = 1 });

    // Executing a non-query command (e.g., INSERT, UPDATE, DELETE)
    var affectedRows = connection.Execute("UPDATE Products SET Price = @Price WHERE Id = @Id", new { Price = 1250.00M, Id = 1 });
}
Note: Dapper requires you to write your SQL queries manually. This gives you more control but also means you're responsible for SQL injection prevention (e.g., by using parameterized queries).

3. ADO.NET

ADO.NET is the foundational data access technology in the .NET Framework and .NET Core. It provides a set of classes for interacting with data sources, including relational databases.

3.1. Basic Usage

using System.Data.SqlClient; // For SQL Server

string connectionString = "Your_Connection_String";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "SELECT Id, Name, Price FROM Products WHERE Id = @Id";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@Id", 1);
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                var product = new Product
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Price = reader.GetDecimal(2)
                };
                // Process the product
            }
        }
    }
}

While ADO.NET offers direct control, it's often more verbose than ORMs or micro-ORMs like Dapper. It's typically used when performance is absolutely critical or for very specific, low-level data operations.

Choosing the Right Data Access Technology