Abstract Factory Pattern for Database Access in C# 14

Build flexible, pluggable, and testable data access layers for multiple databases.

Why Use Abstract Factory for Database Providers?

Many applications today need to support multiple database backends—either for flexibility, migration, multi-tenancy, or simply unit testing. It’s not unusual for one customer to use SQL Server and another to use PostgreSQL. Abstract Factory enables you to define a contract for a set of related data operations (repositories, connections, transactions, etc.), and plug in different implementations without changing the calling code.

Using this pattern gives you,

Database-agnostic logic in your business layer
Clear boundaries between abstraction and implementation
Easier testing using in-memory or mock databases
Centralized creation logic for managing data-related components

This fits naturally into clean architecture and SOLID design. You decouple your app from infrastructure concerns, allowing it to evolve without breaking the core logic.

Real-World Example Structure

Imagine you’re building a data access layer for an e-commerce platform. You need repositories like.

ICustomerRepository
IOrderRepository

But the implementation may change.

SQL Server (production)
PostgreSQL (alternative)
InMemory (for tests)

Let’s create.

A repository factory interface
SQL Server and PostgreSQL implementations
Client code that doesn’t care what database it’s using

Step 1. Define the Abstract Interfaces.

Repository Interfaces

public interface ICustomerRepository
{
    string GetCustomerNameById(int id);
}

public interface IOrderRepository
{
    string GetOrderStatus(int orderId);
}

These interfaces define the behavior of your business-level repositories. They don’t care about what database is underneath—they just return business data.

Abstract Factory Interface

public interface IRepositoryFactory
{
    ICustomerRepository CreateCustomerRepository();
    IOrderRepository CreateOrderRepository();
}

This is the abstract factory: it defines the family of related objects (repositories) that must be created together. Each database provider will implement this interface differently.

Step 2. SQL Server Implementation.

public class SqlServerCustomerRepository : ICustomerRepository
{
    public string GetCustomerNameById(int id)
    {
        // Simulated data access
        return $"[SQL Server] Customer #{id} - John Doe";
    }
}

public class SqlServerOrderRepository : IOrderRepository
{
    public string GetOrderStatus(int orderId)
    {
        return $"[SQL Server] Order #{orderId} - Delivered";
    }
}
public class SqlServerRepositoryFactory : IRepositoryFactory
{
    public ICustomerRepository CreateCustomerRepository() => new SqlServerCustomerRepository();
    public IOrderRepository CreateOrderRepository() => new SqlServerOrderRepository();
}

This implementation encapsulates everything related to SQL Server. You can inject SQL-specific services (e.g., SqlConnection) if needed.

Step 3. PostgreSQL Implementation.

public class PostgresCustomerRepository : ICustomerRepository
{
    public string GetCustomerNameById(int id)
    {
        return $"[PostgreSQL] Customer #{id} - Jane Smith";
    }
}

public class PostgresOrderRepository : IOrderRepository
{
    public string GetOrderStatus(int orderId)
    {
        return $"[PostgreSQL] Order #{orderId} - Processing";
    }
}
public class PostgresRepositoryFactory : IRepositoryFactory
{
    public ICustomerRepository CreateCustomerRepository() => new PostgresCustomerRepository();
    public IOrderRepository CreateOrderRepository() => new PostgresOrderRepository();
}

This version is for PostgreSQL, showing how easily you can plug in a new backend.

Step 4. Client Code Using Factory.

public class DataService
{
    private readonly ICustomerRepository _customerRepo;
    private readonly IOrderRepository _orderRepo;

    public DataService(IRepositoryFactory factory)
    {
        _customerRepo = factory.CreateCustomerRepository();
        _orderRepo = factory.CreateOrderRepository();
    }

    public void DisplayData()
    {
        Console.WriteLine(_customerRepo.GetCustomerNameById(101));
        Console.WriteLine(_orderRepo.GetOrderStatus(501));
    }
}

This service depends only on the abstract factory and repository interfaces. The client does not know or care what database is used.

Main Program Example

class Program
{
    static void Main()
    {
        IRepositoryFactory factory;

        // Choose the provider (can be based on config/env)
        factory = new SqlServerRepositoryFactory();
        // factory = new PostgresRepositoryFactory();

        var service = new DataService(factory);
        service.DisplayData();
    }
}

Switching between databases is now a single-line change—this could easily be moved to config or dependency injection.

Real-World Benefits

This pattern is widely used in repository-based architectures, onion architecture, and clean architecture. It plays well with

ASP.NET Core’s DI container (register a factory per environment)
Automated unit testing (inject a fake or in-memory factory)
Microservices where data backends may vary per service or customer

It also encourages interface-first design, which makes the codebase more adaptable and team-friendly.

Bonus: InMemory Factory for Unit Testing

public class InMemoryCustomerRepository : ICustomerRepository
{
    public string GetCustomerNameById(int id) => $"[InMemory] Customer #{id} - Test User";
}

public class InMemoryOrderRepository : IOrderRepository
{
    public string GetOrderStatus(int orderId) => $"[InMemory] Order #{orderId} - Mocked";
}

public class InMemoryRepositoryFactory : IRepositoryFactory
{
    public ICustomerRepository CreateCustomerRepository() => new InMemoryCustomerRepository();
    public IOrderRepository CreateOrderRepository() => new InMemoryOrderRepository();
}

You can now run your app in test mode with,

var factory = new InMemoryRepositoryFactory();

Summary

The Abstract Factory Pattern provides a powerful, clean, and flexible way to build database-independent applications. It allows you to,

Isolate database-specific logic
Swap out backends with minimal effort
Keep business logic free of infrastructure concerns
Add testability via mock/in-memory implementations

Using C# 14, this pattern feels even more elegant, especially when combined with dependency injection, functional initialization, and immutable configuration.