.NET Data Access

Learn how to interact with databases and other data sources in your .NET applications.

Accessing and manipulating data is a fundamental aspect of most applications. The .NET ecosystem provides a rich set of tools and frameworks to facilitate seamless data access. This section explores the primary approaches and technologies available for managing data in .NET.

ADO.NET

ADO.NET is a set of classes that expose data access services to the .NET Framework. It is the foundational technology for data access in .NET and provides a mechanism to connect to data sources, retrieve data, and manage data updates. Key components include:

Here's a simple example of fetching data using ADO.NET:

C# 
using System;
using System.Data;
using System.Data.SqlClient;

public class DataAccessExample
{
    private const string ConnectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

    public static void GetCustomerData(int customerId)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            string query = "SELECT CustomerName, Email FROM Customers WHERE CustomerID = @ID;";
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@ID", customerId);

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Name: {reader["CustomerName"]}, Email: {reader["Email"]}");
                    }
                }
                else
                {
                    Console.WriteLine("No customer found.");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Entity Framework Core

Entity Framework Core (EF Core) is a modern, cross-platform, open-source version of the popular Entity Framework data access technology. It is an Object-Relational Mapper (ORM) that enables .NET developers to work with databases using domain-specific objects, eliminating the need for most of the data-access code they typically need to write. EF Core supports LINQ queries, which allows you to query data using familiar C# or VB.NET syntax.

EF Core provides two main approaches for defining your model:

Tip: EF Core is generally recommended for new applications due to its higher level of abstraction and productivity benefits over raw ADO.NET.

Example using EF Core:

C# 
using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;");
    }
}

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

public class EFCoreExample
{
    public static void GetProducts(decimal minPrice)
    {
        using (var context = new ApplicationDbContext())
        {
            var expensiveProducts = context.Products
                                        .Where(p => p.Price > minPrice)
                                        .OrderBy(p => p.Name)
                                        .ToList();

            foreach (var product in expensiveProducts)
            {
                Console.WriteLine($"- {product.Name} (${product.Price})");
            }
        }
    }
}

Data Providers

Both ADO.NET and EF Core rely on data providers to communicate with different types of data stores. .NET supports providers for a wide range of databases:

When using EF Core, you install the appropriate EF Core provider package (e.g., Microsoft.EntityFrameworkCore.SqlServer, Npgsql.EntityFrameworkCore.PostgreSQL).

Transactions

Transactions are crucial for ensuring data consistency when performing multiple database operations. If one operation fails, the entire set of operations can be rolled back to a consistent state. ADO.NET provides SqlTransaction (or its equivalents for other providers) for managing transactions.

EF Core also supports transactions, often implicitly through DbContext operations, or explicitly using DbContext.Database.BeginTransaction().

C# 
using System;
using System.Data.SqlClient;

public class TransactionExample
{
    private const string ConnectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

    public static void TransferFunds(int fromAccountId, int toAccountId, decimal amount)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            SqlTransaction transaction = null;

            try
            {
                transaction = connection.BeginTransaction();

                // Decrease balance from source account
                string decreaseSql = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount";
                SqlCommand cmdDecrease = new SqlCommand(decreaseSql, connection, transaction);
                cmdDecrease.Parameters.AddWithValue("@Amount", amount);
                cmdDecrease.Parameters.AddWithValue("@FromAccount", fromAccountId);
                cmdDecrease.ExecuteNonQuery();

                // Increase balance to destination account
                string increaseSql = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount";
                SqlCommand cmdIncrease = new SqlCommand(increaseSql, connection, transaction);
                cmdIncrease.Parameters.AddWithValue("@Amount", amount);
                cmdIncrease.Parameters.AddWithValue("@ToAccount", toAccountId);
                cmdIncrease.ExecuteNonQuery();

                // Commit the transaction
                transaction.Commit();
                Console.WriteLine("Funds transferred successfully.");
            }
            catch (Exception ex)
            {
                // Rollback the transaction if any error occurs
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                Console.WriteLine($"Transaction failed: {ex.Message}");
            }
        }
    }
}

Asynchronous Operations

Modern .NET applications leverage asynchronous programming to improve scalability and responsiveness, especially for I/O-bound operations like database access. ADO.NET and EF Core provide asynchronous methods (e.g., ExecuteReaderAsync, SaveChangesAsync) that should be used in asynchronous contexts.

C# 
using System;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

public class AsyncDataAccessExample
{
    public static async Task GetProductCountAsync(ApplicationDbContext context)
    {
        int count = await context.Products.CountAsync();
        Console.WriteLine($"Total products: {count}");
    }
}
Note: Always use asynchronous methods when performing database operations in ASP.NET Core applications and other I/O-intensive scenarios to prevent blocking threads.