AdoScope

---

💡 What This Looks Like in Practice

Here’s how you can structure a clean, scoped unit of work across multiple repositories using AdoScope:

using IAdoScope scope = adoScopeFactory.Create();

repository1.DoSomething();
repository2.DoSomethingElse();
repository3.BulkInsert(records);
repository4.MarkAsProcessed(ids);

scope.Complete();

no manual transaction handling, no passing connections around, just clean and maintainable code.

---

📘 What is AdoScope?

AdoScope is a lightweight and flexible library for Dapper and ADO.NET that manages DbConnection and DbTransaction lifecycles, while providing clean, scoped transactions through an ambient context pattern.

It provides a minimal-effort Unit of Work pattern, inspired by DbContextScope for Entity Framework, but tailored for Dapper and raw ADO.NET.

It has been used in enterprise-scale applications and production systems, giving it a thorough shake-down in complex, high-volume scenarios.

No need to manually manage transactions, connection lifetimes, or implement repetitive unit of work classes. AdoScope wraps all of this with clean Dependency Injection (DI) support and allows:

• Transparent ambient scoping (no passing around context)
• Cross-database transaction coordination
• Distributed transactions (via MSDTC on supported platforms)
• Fine-grained control per scope or per context

---

✨ Features

• ✅ Lightweight ambient scope for ADO.NET
• ✅ Provider-agnostic (works with MSSQL, SQLite, PostgreSQL, etc.)
• ✅ Minimal effort Unit of Work via scoped transactions
• ✅ Nested scope support
• ✅ Support for multiple databases in the same transaction
• ✅ Distributed transaction support across databases
• ✅ Asynchronous-safe usage
• ✅ Configurable per context and per scope (transactional / non-transactional)
• ✅ Isolation level configuration per context or scope

---

🧩 What is a Context?

In Entity Framework, a DbContext represents a session with the database. It tracks changes to entities, handles LINQ queries, and manages both the connection and transaction lifecycle. It's tightly coupled to EF’s change tracking and object-relational mapping features.

In AdoScope, a context plays a simpler, but still important role. Since Dapper and raw ADO.NET do not provide entity tracking, a context in AdoScope is essentially just:

• A managed DbConnection
• An optional DbTransaction, depending on configuration

AdoScope handles the lifecycle of these objects for you, allowing each context to be used ambiently across repositories and services. This enables a Unit of Work pattern with minimal effort, without needing to manually pass around or manage connections and transactions.

So while an AdoScope context doesn't track entities, it does provide the other key half of what EF’s DbContext offers — structured and scoped connection and transaction management.

---

📦 Installation

Install-Package Promethix.Framework.Ado

---

🔧 Configuration (Dependency Injection)

Configuration Options

| Configuration Type | Option | Description | |--------------------------------|-------------------|------------------------------------------------------------------------------| | AdoContextExecutionOption | Transactional | Default. Wraps the context in a transaction. Recommended for most use cases.| | | NonTransactional| Executes commands immediately without a transaction. | | AdoContextGroupExecutionOption | Standard | Default. Creates a separate DbTransaction per context. Best-effort coordination without escalation — no automatic rollback across contexts. | | AdoContextGroupExecutionOption | Distributed | Wraps all contexts in a TransactionScope. If multiple database connections are involved, this may escalate to a distributed transaction, which requires MSDTC (or a compatible DTC service), ADO.NET provider support, and an OS that supports distributed transactions (e.g. Windows). |

---

DI Lifetime Guidance

• If IAdoScopeFactory and IAdoContextGroupFactory are registered as singleton (recommended), register AdoScopeOptionsBuilder and IAdoContextOptionsRegistry as singleton too.
• If you need scoped configuration objects, register the factories as scoped as well.
• Keep repositories/services that consume the ambient context as scoped.

This avoids ASP.NET Core lifetime validation errors caused by singleton services depending on scoped services.

---

.NET with SQLite (Appsettings Example)

DbProviderFactories.RegisterFactory("Microsoft.Data.Sqlite", SqliteFactory.Instance);

// Factories are singletons; their dependencies should be singletons too.
services.AddSingleton<IAmbientAdoContextLocator, AmbientAdoContextLocator>();
services.AddSingleton<IAdoScopeFactory, AdoScopeFactory>();
services.AddSingleton<IAdoContextGroupFactory, AdoContextGroupFactory>();

IConfigurationRoot configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json")
    .Build();

var adoScopeConfig = new AdoScopeConfigurationBuilder()
    .ConfigureScope(options => options.WithScopeConfiguration(configuration))
    .Build();

var adoContextConfig = new AdoContextConfigurationBuilder()
    .AddAdoContext<SqliteDbContext>(options =>
        options.WithNamedContext("SqliteDbContext", configuration))
    .Build();

// Register the built options as singletons (they are created once at startup)
services.AddSingleton(adoScopeConfig);
services.AddSingleton<IAdoContextOptionsRegistry>(adoContextConfig);

.NET with MSSQL (Appsettings Example)

DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);

services.AddSingleton<IAmbientAdoContextLocator, AmbientAdoContextLocator>();
services.AddSingleton<IAdoScopeFactory, AdoScopeFactory>();
services.AddSingleton<IAdoContextGroupFactory, AdoContextGroupFactory>();

IConfigurationRoot configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json")
    .Build();

var adoScopeConfig = new AdoScopeConfigurationBuilder()
    .ConfigureScope(options => options.WithScopeConfiguration(configuration))
    .Build();

var adoContextConfig = new AdoContextConfigurationBuilder()
    .AddAdoContext<MyDbContext>(options =>
        options.WithNamedContext("MyDbContext", configuration))
    .Build();

services.AddSingleton(adoScopeConfig);
services.AddSingleton<IAdoContextOptionsRegistry>(adoContextConfig);

appsettings.json Example

{
  "AdoScopeOptions": {
    "ScopeExecutionOption": "Standard"
  },
  "AdoContextOptions": {
    "SqliteDbContext": {
      "ProviderName": "Microsoft.Data.Sqlite",
      "ConnectionString": "Data Source=mydatabase.db",
      "ExecutionOption": "Transactional"
    },
    "MyDbContext": {
      "ProviderName": "Microsoft.Data.SqlClient",
      "ConnectionString": "Server=localhost;Database=MyDb;Trusted_Connection=True;",
      "ExecutionOption": "Transactional"
    }
  }
}

.NET Framework Example (Ninject + Fluent Configuration)

// Register AdoScope services
kernel.Bind<IAmbientAdoContextLocator>().To<AmbientAdoContextLocator>().InSingletonScope();
kernel.Bind<IAdoScopeFactory>().To<AdoScopeFactory>().InSingletonScope();
kernel.Bind<IAdoContextGroupFactory>().To<AdoContextGroupFactory>().InSingletonScope();

// Register the ADO.NET provider for MSSQL
DbProviderFactory sqlFactory = DbProviderFactories.GetFactory("Microsoft.Data.SqlClient");
kernel.Bind<DbProviderFactory>().ToConstant(sqlFactory);

// Configure AdoScope globally (application-wide scope behavior)
var adoScopeConfig = new AdoScopeConfigurationBuilder()
    .ConfigureScope(options =>
    {
        options.WithScopeExecutionOption(AdoContextGroupExecutionOption.Standard);
    })
    .Build();

// Configure multiple AdoContexts using fluent API
var adoContextConfig = new AdoContextConfigurationBuilder()
    .AddAdoContext<PrimaryDbContext>(options =>
    {
        options.WithNamedContext("PrimaryDbContext")
            .WithConnectionString(ConfigurationManager.ConnectionStrings["PrimaryDb"].ConnectionString)
            .WithProviderName("Microsoft.Data.SqlClient")
            .WithExecutionOption(AdoContextExecutionOption.Transactional)
            .WithDefaultIsolationLevel(IsolationLevel.ReadCommitted);
    })
    .AddAdoContext<AuditDbContext>(options =>
    {
        options.WithNamedContext("AuditDbContext")
            .WithConnectionString(ConfigurationManager.ConnectionStrings["AuditDb"].ConnectionString)
            .WithProviderName("Microsoft.Data.SqlClient")
            .WithExecutionOption(AdoContextExecutionOption.Transactional)
            .WithDefaultIsolationLevel(IsolationLevel.ReadCommitted);
    })
    .Build();

// Register configurations into DI container
kernel.Bind<AdoScopeOptionsBuilder>().ToConstant(adoScopeConfig).InSingletonScope();
kernel.Bind<IAdoContextOptionsRegistry>().ToConstant(adoContextConfig).InSingletonScope();

---

🧪 Usage

1. Define Your Context

public class MyDbContext : AdoContext { }
public class SqliteDbContext : AdoContext { }

---

2. Create a Repository

public class MyRepository : IMyRepository
{
    private readonly IAmbientAdoContextLocator locator;

    public MyRepository(IAmbientAdoContextLocator locator)
    {
        this.locator = locator;
    }

    private IDbConnection Connect