Stop Hardcoding Your .NET Data Provider

There is a piece of advice I have been giving for a long time. I first wrote about it around 2010, before .NET Core existed:

Do not hardcode your database provider into your application unless the application is deliberately a database-specific application.

That sounds obvious now. It was less obvious then, when a lot of .NET code looked like this:

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);

There is nothing technically wrong with that code. SqlConnection works. SqlCommand works. If the application is a SQL Server application and will remain one, this is a reasonable choice.

The problem begins when the application is supposed to support more than one database, or when a reusable library quietly assumes that every database behaves like SQL Server.

At that point, the provider type is no longer an implementation detail. It has leaked through the data-access boundary and become part of the application.

That is exactly the problem ADO.NET was designed to solve.

ADO.NET already has the contract

The useful part of ADO.NET is not SqlConnection. It is the abstraction underneath it:

• DbConnection
• DbCommand
• DbParameter
• DbDataReader
• DbTransaction
• DbProviderFactory

The concrete provider supplies the implementation. The application uses the contract.

A provider-agnostic connection flow is therefore not mysterious:

DbProviderFactory factory = ...;

await using DbConnection connection = factory.CreateConnection()
    ?? throw new InvalidOperationException("The provider did not create a connection.");

connection.ConnectionString = connectionString;
await connection.OpenAsync();

await using DbCommand command = connection.CreateCommand();

var parameterName = "customer_id";
command.CommandText = $"select name from customer where customer_id = @{parameterName}";

DbParameter parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = customerId;
command.Parameters.Add(parameter);

await using DbDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var name = reader.GetString(0);
}

The @ in that first SQL string is deliberately limited to named-parameter providers, not presented as universal syntax. In a real provider-agnostic implementation, the marker comes from detected provider metadata. A positional provider such as OleDb or Access must use ?, and the parameter order becomes part of the contract.

There is no SqlConnection in the object-creation path. There is no NpgsqlConnection, OracleConnection, or MySqlConnection either.

That is the first half of the solution.

Why bother?

Because the provider is infrastructure, not application policy.

If a reusable data-access library directly references SqlConnection, then the library is coupled to that provider's assembly, release cycle, configuration conventions, and behavioral assumptions. Moving to Microsoft.Data.SqlClient, upgrading Npgsql, adding Oracle support, or serving a second tenant with a different database becomes a library change.

With the provider behind DbProviderFactory, the composition root selects the implementation and the data-access layer consumes the contract. A routine provider upgrade can then be a package and deployment change rather than a source change and rebuild of every library that talks to the database. The application still pins the provider version, and a provider that changes behavior still needs testing. Switching providers is localized to registration and dialect/capability handling.

The same separation works in the other direction. A data-access library can ship an upgrade without taking ownership of the provider package or forcing every consumer onto one vendor's release schedule. Provider selection remains an application or deployment decision.

That does not make upgrades magically risk-free. A provider can change defaults, remove APIs, or expose a database behavior that needs a new dialect rule. Those changes still need tests. The difference is that the dependency is isolated: the application-facing data-access contract does not have to change just because the provider assembly did.

It also makes the same library usable in more than one environment. Development can use SQLite, integration tests can use a fake or containerized provider, production can use SQL Server or PostgreSQL, and a multi-tenant service can select a provider per tenant without embedding those choices in every query path.

Finding the provider at runtime

.NET Framework: providerName in configuration

In classic .NET Framework applications, a connection string entry commonly carried both the connection string and a providerName:

<connectionStrings>
  <add name="ApplicationDatabase"
       connectionString="..."
       providerName="System.Data.SqlClient" />
</connectionStrings>

The important idea was not the XML. The important idea was that the provider identity was configuration, not source code.

Once you have the provider name, ADO.NET can resolve the factory:

var factory = DbProviderFactories.GetFactory(providerName);

.NET Core and modern .NET

There is no machine-wide provider configuration model in .NET Core. In current .NET, the provider is normally a NuGet dependency and the application registers its factory during startup.

{
  "Database": {
    "ProviderName": "Npgsql",
    "ConnectionString": "Host=localhost;Database=orders;Username=app;Password=..."
  }
}

The composition root can map the configured invariant name to the matching factory:

var factories = new Dictionary<string, DbProviderFactory>(StringComparer.OrdinalIgnoreCase)
{
    ["Npgsql"] = NpgsqlFactory.Instance,
    ["Microsoft.Data.SqlClient"] = SqlClientFactory.Instance
};

var providerName = configuration["Database:ProviderName"]
    ?? throw new InvalidOperationException("Database provider is not configured.");

if (!factories.TryGetValue(providerName, out var factory))
{
    throw new InvalidOperationException($"Unsupported database provider '{providerName}'.");
}

DbProviderFactories.RegisterFactory(providerName, factory);

For a provider that is a compile-time dependency, the BCL also accepts its factory type:

DbProviderFactories.RegisterFactory("Npgsql", typeof(NpgsqlFactory));

For a provider that is not a compile-time dependency, use a trusted assembly-qualified factory type name:

var factoryTypeName = configuration["Database:FactoryType"]
    ?? throw new InvalidOperationException("Database factory type is not configured.");

DbProviderFactories.RegisterFactory("Npgsql", factoryTypeName);

The assembly still has to be resolvable by normal .NET probing, which usually means it ships with the application. A path-based loader can go further and load a provider DLL from a file under the application directory.

For applications that support multiple providers or tenants, keyed DI is another option. AddKeyedSingleton and GetRequiredKeyedService require .NET 8 or later. The important architectural point is that the composition root knows the provider; the rest of the data-access code receives the ADO.NET contract.

For longer-lived applications, current .NET also provides DbDataSource, which gives a provider a shared place for pooling and prepared-statement setup. Register it once and open short-lived connections from the shared instance:

builder.Services.AddSingleton<DbDataSource>(_ =>
    factory.CreateDataSource(connectionString));

Npgsql's NpgsqlDataSource is a richer provider-specific version of the same idea. DbProviderFactory remains the portable construction boundary; DbDataSource is the shared-resource boundary.

Loading a provider from a registry

Sometimes the provider is not known when the application is built. A multi-tenant service may keep each tenant's provider name, factory type, and connection string in a central registry. The registry itself still needs a bootstrap provider, but the target provider can be loaded from the registry record.

Here is the essential loader pattern, simplified from pengdows.crud's provider loader:

static DbProviderFactory LoadFactory(
    string providerName,
    string assemblyName,
    string factoryTypeName)
{
    var assembly = Assembly.Load(assemblyName);
    var factoryType = assembly.GetType(factoryTypeName, throwOnError: true)!;

    var factory =
        factoryType.GetProperty("Instance", BindingFlags.Public | BindingFlags.Static)
            ?.GetValue(null) as DbProviderFactory
        ?? factoryType.GetField("Instance", BindingFlags.Public | BindingFlags.Static)
            ?.GetValue(null) as DbProviderFactory
        ?? throw new InvalidOperationException(
            $"Factory type '{factoryTypeName}' did not expose DbProviderFactory.Instance.");

    DbProviderFactories.RegisterFactory(providerName, factory);
    return factory;
}

The registration belongs at startup or DI composition time. Do not repeat it for every command or lookup.

If the assembly comes from a configured file path, use Assembly.LoadFrom only after resolving the path against the application base directory. Resolve symlinks before checking containment, and reject paths or symlink targets that escape that directory. This is a path-safety rail, not a sandbox: provider code executes inside the application process with its permissions.

The registry values must be trusted deployment data. A database row that controls assembly loading is effectively code-loading configuration.

DataSourceInformation: the missing layer

Resolving a provider tells us how to create ADO.NET objects. It does not tell us every rule of the database behind the connection.

ADO.NET exposes a standard metadata path through GetSchema("DataSourceInformation"):

static void ReadMetadata(DbConnection connection)
{
    DataTable schema = connection.GetSchema(
        DbMetaDataCollectionNames.DataSourceInformation);

    if (schema.Rows.Count == 0)
    {
        // This provider does not publish the standard row; probe instead.
        return;
    }

    DataRow row = schema.Rows[0];

    string product = row[DbMetaDataColumnNames.DataSourceProductName] as string ?? "";
    string version = row[DbMetaDataColumnNames.DataSourceProductVersion] as string ?? "";
    string markerFormat = row[DbMetaDataColumnNames.ParameterMarkerFormat] as string ?? "";
    string markerPattern = row[DbMetaDataColumnNames.ParameterMarkerPattern] as string ?? "";
    string namePattern = row[DbMetaDataColumnNames.ParameterNamePattern] as string ?? "";
    int maxNameLength = row[DbMetaDataColumnNames.ParameterNameMaxLength] is int n ? n : 0;
}

The problem is that providers fill this table inconsistently. Some report useful values. Some leave fields empty. Some expose a base product but not a meaningful version. Some require a product-specific query before you can distinguish PostgreSQL from a compatible engine.

In this model, 0 means the provider did not report a limit. Unknown limits should be treated as unknown, not as zero capacity.

A real data-access layer therefore does three things:

1. Reads the standard metadata when it is available.
2. Runs product and version probes when the metadata is incomplete.
3. Normalizes the result into an immutable object containing facts and capability flags.

The shape is small and boring by design. This is trimmed from pengdows.crud's DataSourceInformation:

public sealed class DataSourceInformation
{
    public required string ProductName { get; init; }
    public required string ProductVersion { get; init; }
    public required string ParameterMarker { get; init; }
    public required string ParameterMarkerPattern { get; init; }
    public required Regex ParameterNamePattern { get; init; }
    public required string QuotePrefix { get; init; }
    public required string QuoteSuffix { get; init; }
    public required int ParameterNameMaxLength { get; init; }
    public required int MaxParameterLimit { get; init; }
    public required bool SupportsNamedParameters { get; init; }
    public required bool SupportsRepeatedNamedParameters { get; init; }
    public required bool SupportsMerge { get; init; }
    public required bool SupportsInsertOnConflict { get; init; }
    public required bool SupportsOnDuplicateKey { get; init; }
    public required bool IsFallbackDialect { get; init; }
}

This is the useful abstraction. Code can ask what the connected database supports instead of switching on a database-name enum:

if (!info.SupportsRepeatedNamedParameters)
{
    // Allocate distinct parameter names for repeated values.
}

if (info.MaxParameterLimit > 0 &&
    info.MaxParameterLimit < requestedParameterCount)
{
    // Split the command or batch the operation.
}

The parameter helper is equally small:

static string MakeParameterName(DataSourceInformation info, string name)
{
    if (!info.SupportsNamedParameters)
    {
        // With ?, binding order equals parameter-add order. Add parameters in SQL order.
        return info.ParameterMarker; // positional: ?, for example
    }

    if (!info.ParameterNamePattern.IsMatch(name))
    {
        throw new ArgumentException("Invalid parameter name.", nameof(name));
    }

    if (info.ParameterNameMaxLength > 0 &&
        name.Length > info.ParameterNameMaxLength)
    {
        throw new ArgumentException("Parameter name is too long.", nameof(name));
    }

    return info.ParameterMarker + name;
}

This is the idea behind a dialect's parameter-name logic. It also explains why a generic implementation cannot blindly write @p0. Oracle commonly uses :p0; positional providers use ?; Npgsql supports named forms and native positional $1; and ODP.NET binds by position by default unless BindByName is enabled.

Capability flags are not necessarily mutually exclusive:

if (info.SupportsInsertOnConflict)
{
    // Default preference order; a specific operation may choose differently.
    // Build INSERT ... ON CONFLICT for this operation.
}
else if (info.SupportsOnDuplicateKey)
{
    // Build INSERT ... ON DUPLICATE KEY UPDATE.
}
else if (info.SupportsMerge)
{
    // Build a MERGE-based implementation.
}
else
{
    // Use a transaction containing an insert/update decision.
}

PostgreSQL 15+ supports both MERGE and ON CONFLICT; the correct choice depends on the operation being generated. Query the capability required by the SQL you are building, not a database name as a shortcut.

Identifier quoting follows the same rule. Keep it in one helper rather than scattering concatenation through the application:

static string QuoteIdentifier(DataSourceInformation info, string name)
{
    if (string.IsNullOrEmpty(info.QuoteSuffix))
    {
        return name;
    }

    return info.QuotePrefix + name.Replace(
        info.QuoteSuffix,
        info.QuoteSuffix + info.QuoteSuffix,
        StringComparison.Ordinal) + info.QuoteSuffix;
}

Composite identifiers, reserved words, and provider-specific rules may require a richer dialect helper, but the ownership is the same: SQL generation needs facts about the connected database.

Detection happens once, then the facts are reused

Database detection should not be repeated for every command. It belongs to context initialization.

The broad flow is:

configuration
    -> provider name
    -> DbProviderFactory
    -> DbConnection
    -> open connection
    -> ADO.NET metadata and product probes
    -> database dialect
    -> immutable datasource facts
    -> commands generated with the right rules

Your data-access layer owns the detected dialect and metadata. Query builders, repositories, transactions, and higher-level libraries all use the same answer.

If the provider loads but the engine is not recognized, the layer can use a conservative SQL-92 fallback, expose that it is operating in fallback mode, and provide a compatibility warning. Loading a provider does not mean that every database behind that provider is fully supported.

The point is not to hide the database

I am not interested in pretending that all relational databases are interchangeable. They are not.

The point is to put the differences where they belong. Configuration chooses the provider. DbProviderFactory creates provider objects. The open connection tells you what is behind it. Metadata and probes become immutable facts. A dialect turns those facts into correct SQL behavior.

That is how you support multiple databases without hardcoding your data provider into every layer of the application.

That's a lot of code to own: provider loading, metadata normalization, dialects, capability detection, fallback handling, connection modes for file-based databases like SQLite, and the edge cases around them.

Or, if you don't want to handcode all this... use pengdows.crud.