MSDN Documentation

Core Data Access in .NET

This tutorial provides a comprehensive guide to accessing data within your .NET Core applications. We'll explore various strategies, from traditional ADO.NET to modern ORM solutions like Entity Framework Core.

Understanding Data Access Layers

A well-structured data access layer (DAL) is crucial for maintaining clean, maintainable, and testable code. It abstracts the complexities of data storage and retrieval, allowing your application's business logic to focus on its core responsibilities.

Key principles of a good DAL include:

• Abstraction: Hiding the underlying data source details.
• Encapsulation: Bundling data and the methods that operate on that data.
• Reusability: Providing common data access operations.
• Testability: Enabling easy unit testing of data operations.

ADO.NET Fundamentals

ADO.NET is the foundational data access technology in .NET. It provides a set of classes for connecting to data sources, executing commands, and retrieving results. While powerful, it can be verbose for common tasks.

Key ADO.NET objects:

• SqlConnection / NpgsqlConnection / MySqlConnection: Establishes a connection to the database.
• SqlCommand / NpgsqlCommand / MySqlCommand: Represents a SQL statement or stored procedure to execute.
• SqlDataReader / NpgsqlDataReader / MySqlDataReader: Provides a forward-only, read-only stream of data.
• SqlDataAdapter / NpgsqlDataAdapter / MySqlDataAdapter: Bridges the gap between a DataSet and a data source.
• DataSet: An in-memory representation of data, often used with DataAdapter for disconnected scenarios.

Example: Reading Data with ADO.NET

using System;
using System.Data;
using Microsoft.Data.SqlClient; // Or your specific provider

public class CustomerRepository
{
    private readonly string _connectionString;

    public CustomerRepository(string connectionString)
    {
        _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
    }

    public string GetCustomerName(int customerId)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand("SELECT Name FROM Customers WHERE CustomerID = @CustomerID", connection);
            command.Parameters.AddWithValue("@CustomerID", customerId);

            connection.Open();
            var reader = command.ExecuteReader();

            if (reader.Read())
            {
                return reader.GetString(0);
            }
            return null;
        }
    }
}
            

Entity Framework Core (EF Core)

Entity Framework Core is Microsoft's modern, cross-platform Object-Relational Mapper (ORM). It simplifies data access by allowing you to work with data as .NET objects, rather than writing raw SQL queries. EF Core supports various database providers for SQL Server, PostgreSQL, MySQL, SQLite, and more.

Key EF Core Concepts:

• DbContext: Represents a session with the database and allows you to query and save data.
• DbSet: Represents a collection of all entities in the context, or that can be queried from the database, of a given type.
• Entities: Plain Old CLR Objects (POCOs) that represent tables in your database.
• Migrations: Tools for incrementally updating your database schema to match your entity model.

Example: Using EF Core

First, define your entity and DbContext:

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace with your actual connection string and provider
        optionsBuilder.UseSqlServer("YourConnectionStringHere");
    }
}
            

Then, use it in your application:

using (var context = new MyDbContext())
{
    var customer = context.Customers.FirstOrDefault(c => c.CustomerId == 1);
    if (customer != null)
    {
        Console.WriteLine($"Customer Name: {customer.Name}");
    }
}
            

Choosing the Right Approach

The choice between ADO.NET and EF Core (or other ORMs) depends on your project's needs:

• ADO.NET: Ideal for scenarios where you need fine-grained control over SQL, performance optimization through raw SQL, or working with very simple data structures.
• EF Core: Excellent for rapid development, complex object models, and when you want to minimize direct SQL interaction. It significantly reduces boilerplate code.

Best Practices

• Always use parameterized queries to prevent SQL injection.
• Dispose of database connections and commands properly (using using statements).
• Implement connection pooling for better performance.
• Consider asynchronous data operations (e.g., await connection.OpenAsync()) for responsive applications.
• Use migrations effectively for database schema management with EF Core.

Continue to the next section for exploring web development concepts in .NET Core.