Data Access Concepts in .NET

This section provides an overview of the fundamental concepts and technologies used for data access within the .NET ecosystem. Understanding these concepts is crucial for building robust and efficient applications that interact with various data sources.

Key Data Access Technologies

The .NET Framework and .NET Core/.NET 5+ offer several powerful data access technologies, each with its own strengths and use cases:

1. ADO.NET

ADO.NET is a set of .NET classes that expose data access services to the .NET programmer. It provides a collection of command, connection, and data reader objects, along with data set objects, that can be used to interact with a data source. It's a low-level API that offers fine-grained control over data operations.

• Providers: For each type of data source (e.g., SQL Server, Oracle, OLE DB), ADO.NET provides a specific data provider.
• Connections: Establish a link to the data source.
• Commands: Execute SQL statements or stored procedures.
• DataReaders: Provide a forward-only, read-only stream of data.
• DataSets: Represent an in-memory cache of data, allowing for disconnected data manipulation.

Example snippet:

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "SELECT TOP 2 * FROM dbo.Products;";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"Product Name: {reader["ProductName"]}");
            }
        }
    }
}
            

2. Entity Framework (EF)

Entity Framework is an Object-Relational Mapper (ORM) that enables developers to work with relational data using domain-specific objects instead of the data that is typically stored in a database. EF abstracts away much of the complexity of direct database interaction, allowing developers to focus on business logic.

• Code-First, Database-First, Model-First: Different approaches to define your data model.
• LINQ to Entities: Allows you to query your data using Language Integrated Query (LINQ) syntax.
• Migrations: Tools to manage database schema changes over time.
• Change Tracking: EF automatically tracks changes made to entities.

Example snippet (LINQ query):

using (var context = new MyDbContext())
{
    var products = context.Products
                          .Where(p => p.Category == "Electronics")
                          .OrderBy(p => p.Price)
                          .ToList();

    foreach (var product in products)
    {
        Console.WriteLine($"Product: {product.Name}, Price: {product.Price:C}");
    }
}
            

3. Other Data Access Patterns

• Repository Pattern: Abstracts the data access layer, providing a cleaner separation of concerns.
• Unit of Work Pattern: Manages transactions and aggregates operations on multiple repositories.
• Dapper: A popular micro-ORM that offers excellent performance by mapping query results to objects with minimal overhead.