Connections and Transactions

Serenity uses the basic data access objects in .NET, like IDbConnection, DbCommand, etc.

It provides some basic helpers to create a connection, add parameters, execute queries, etc.

ISqlConnections Interface

The ISqlConnections is a factory interface to create a connection in a database-agnostic way.

public interface ISqlConnections : IConnectionStrings
{
    IDbConnection New(string connectionString, string providerName, ISqlDialect dialect);
    IDbConnection NewByKey(string connectionKey);
}

The New method in ISqlConnections creates a method by specifying the full connection string, the provider name, and the dialect instance that should be used.

The NewByKey method is for creating a connection by its key:

public class SomeClass
{
    private ISqlConnections sqlConnections;

    // assuming ISqlConnections is injected via dependency injection
    public SomeClass(ISqlConnections sqlConnections)
    {
        this.sqlConnections = sqlConnections ??
            throw new ArgumentNullException(nameof(sqlConnections));
    }

    public void SomeOperation()
    {
        using (var connection = sqlConnections.NewByKey("Northwind"))
        {
            // do something with the connection instance
            // as long as you use Serenity connection extension
            // methods to query the database, you don't have to
            // manually open the connection
        }
    }
}

The default implementation for ISqlConnections which is DefaultSqlConnections creates a connection by locating the connection definition from the Data section in the appsettings.json file:

  "Data": {
    "Default": {
      "ConnectionString": "...",
      "ProviderName": "System.Data.SqlClient"
    },
    "Northwind": {
      "ConnectionString": "...",
      "ProviderName": "System.Data.SqlClient"
    }
  }

The type of connection that should be created is specified in the ProviderName property. The provider names and their connection factories are mapped via the RegisterDataProviders method in the Startup.cs file:

public static void RegisterDataProviders()
{
    DbProviderFactories.RegisterFactory("System.Data.SqlClient", SqlClientFactory.Instance);
    DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);
    DbProviderFactories.RegisterFactory("Microsoft.Data.Sqlite", Microsoft.Data.Sqlite.SqliteFactory.Instance);

    // to enable FIREBIRD: add FirebirdSql.Data.FirebirdClient reference, set connections, and uncomment line below
    // DbProviderFactories.RegisterFactory("FirebirdSql.Data.FirebirdClient", FirebirdSql.Data.FirebirdClient.FirebirdClientFactory.Instance);
    // ...
}

As listed above, only the SQL Server and the SQLite connection factories are registered by default. If you want to use another type of server, you should uncomment the relevant line there, and add the NuGet reference for its client library in the project file.

The default implementations for the ISqlConnections and other related services are registered via an AddSqlConnections call. You may not see it in the Startup.cs file as it is indirectly called by the AddServiceHandlers method.

ConnectionExtensions.NewFor`<TClass>` extension method

If you don't want to memorize connection string keys, but instead reuse information on a row (in form of a ConnectionKey attribute), you may prefer this variant.

Looking on top of a Row class, you may spot the ConnectionKey attribute generated by Sergen:

[ConnectionKey("Northwind")]
public class CustomerRow
{
}

When you are going to query for customers, instead of hardcoding "Northwind", you may reuse this information from the CustomerRow:

using (var connection = sqlConnections.NewFor<CustomerRow>()) 
{
    return connection.List<CustomerRow>();
}

This corresponds to SqlConnections.NewByKey("Northwind").

Here we didn't have to open the connection, as the List extension method opens it automatically.

The class used with this method doesn't have to be a Row, any class with a ConnectionKey attribute would work, even though it would be a row type most of the time.

WrappedConnection

You may ask yourself, what is the point of using the ISqlConnections interface and its New and NewByKey methods instead of simply typing new SqlConnection()?

All the ISqlConnections methods return an IDbConnection object. You'd expect it to be a SqlConnection, FirebirdConnection, etc, but that's not exactly true.

The IDbConnection object they return is a WrappedConnection instance that wraps an underlying SqlConnection or FirebirdConnection etc.

This helps Serenity provide some features like auto-open, dialect support, default transactions, unit of work pattern, overriding connections for testability, etc.

You may not notice these details while working with the returned IDbConnection instances. They'll act just like the underlying connections.

But you should prefer the methods of ISqlConnections to create connections. Otherwise, you might lose some of these listed features.

Setting Database Dialect for Connections

Serenity tries to auto-determine the dialect for a connection by using the "providerName" in the appsettings.json file for the connection definition.

See SQL Dialects for the full reference of the dialect types, the built-in dialects, and how they affect query generation.

Sometimes, the dialect determined automatically using the "providerName" may not work for you, or you may want to use a dialect like SqlServer2000 or SqlServer2005 for some connections.

Even though it is possible to set a default global dialect, it doesn't override the automatic detection:

SqlSettings.DefaultDialect = SqlServer2005Dialect.Instance;

Because the provider name for "Northwind" and "Default" connections is System.Data.SqlClient, Serenity will automatically set their dialects to SqlServer2012, even if you override the global dialect.

It is possible to set the dialect in the connection definition in the appsettings.json file:

{
  "Data": {
    "Default": {
      "ConnectionString": "...",
      "ProviderName": "System.Data.SqlClient",
      "Dialect": "SqlServer2012"
    }
  }
}

For the dialects provided by Serenity, it is enough to only specify the dialect class name.

If you defined a custom dialect, you may need to use the full name of the class and the assembly name:

{
  "Data": {
    "Default": {
      "ConnectionString": "...",
      "ProviderName": "System.Data.SqlClient",
      "Dialect": "MyProject.MyNamespace.MyCustomSqlDialect, MyProject.Web"
    }
  }
}

UnitOfWork and IUnitOfWork

UnitOfWork is a simple object that just contains a transaction reference. It has two extra events that we can attach to which are OnCommit and OnRollback.

Let's say we are creating tasks, and some e-mails should be sent in case these tasks are saved to the database successfully.

If we hurry and send these e-mails before the transaction is committed, we might end up with e-mails that are sent for non-existent tasks in case the transaction fails. So we should only send the e-mail if the transaction is committed successfully, e.g. in the `OnCommit`` event.

You might say then Commit the transaction first and send e-mails right after, but what if our Create Task service call is just a step of a larger operation, so we are not controlling the transaction and it should be committed after all steps are successful?

Another scenario is about uploading files. This time we are updating an item that contains files, and let's say we replace an old file with the uploaded new file. If we again hurry and delete the old file before the transaction outcome is clear, and the transaction fails eventually, we'll end up with a file entity without an actual old file on the disk. So, we should delete the file and replace it with the new file in the OnCommit event, and remove the uploaded file in the OnRollback event.

void SomeBatchOperation() 
{
    using (var connection = sqlConnections.NewByKey("Default"))
    using (var uow = new UnitOfWork(connection))
    {
        // here we are in a transaction context
        // create several tasks in transaction
        CreateATask(new TaskRow { ... });
        CreateATask(new TaskRow { ... });
        //...
        
        // commit the transaction
        // if any exception occurs here or at prior
        // lines transaction will rollback
        // and no e-mails will be sent
        uow.Commit();
    }
}

void CreateATask(IUnitOfWork uow, TaskRow task)
{
    // insert task using the connection wrapped inside IUnitOfWork
    // this will automatically run in the current transaction context
    uow.Connection.Insert(task);
   
    uow.OnCommit += () => {
       // send e-mail for this task now, this method will only
       // be called if the transaction commits successfully
    };
    
    uow.OnRollback += () => {
       // optional, do something else if it fails
    };
}

## TransactionlessUnitOfWork

Some methods are written to accept an `IUnitOfWork` so they can take part in whatever transaction the caller started. If you call such a method but don't actually need a real transaction — for example you're only reading, or you already manage the transaction yourself — you can still satisfy the signature by passing a [`TransactionlessUnitOfWork`](../../api/dotnet/Serenity.Net.Services/Serenity.Data/TransactionlessUnitOfWork.md). It implements `IUnitOfWork` but never begins a database transaction.

```cs
using (var connection = sqlConnections.NewByKey("Default"))
{
    // no real transaction is started
    using var uow = new TransactionlessUnitOfWork(connection);

    // OnCommit() fires here ...
    uow.Commit();
}
// OnRollback fires if Dispose() is called without a commit

Since there is no underlying transaction, the OnCommit/OnRollback events are just callbacks: OnCommit is raised when Commit() is called, and OnRollback is raised when the instance is disposed without a Commit(). Use it with care — pass it only where a method requires an IUnitOfWork but you're sure you don't want a transaction.

Connection extensions

The static ConnectionExtensions class adds helpers on top of any IDbConnection:

NewFor<TClass>() — covered above; creates a connection using the [ConnectionKey] attribute on a row/type.
EnsureOpen() — opens the connection if it isn't already open. A connection returned by ISqlConnections cannot be reopened after it has been closed, so this throws if the connection was already opened once.
GetCurrentActualTransaction() — returns the underlying IDbTransaction of the current transaction, if any. WrappedConnection tracks the active transaction so it can be retrieved without knowing it from elsewhere.
SetCommandTimeout(int?) — sets a default command timeout on the connection (only works with wrapped connections, which implement IHasCommandTimeout).
GetLogger() — returns the connection's logger if it implements IHasLogger, otherwise null.

using (var connection = sqlConnections.NewByKey("Default"))
{
    connection.EnsureOpen();
    connection.SetCommandTimeout(60);

    using var transaction = connection.BeginTransaction();
    // ...
}

Registering SQL connections

The connection services are registered through dependency injection. In a typical template these are registered for you when you call AddServiceHandlers (which calls AddEntities, which in turn calls AddSqlConnections), but you can also register them explicitly:

services.AddSqlConnections();

This registers the default implementations:

Interface	Default implementation
`ISqlConnections`	`DefaultSqlConnections`
`IConnectionStrings`	`DefaultConnectionStrings`
`ISqlDialectMapper`	`DefaultSqlDialectMapper`

IConnectionStrings/IConnectionString represent the configured connection strings, each carrying its key, the raw connection string, provider name and ISqlDialect. DefaultConnectionStrings reads them from the Data section of appsettings.json through ConnectionStringOptions/ConnectionStringEntry. See IConnectionStrings, IConnectionString, ConnectionStringOptions and ConnectionStringEntry.

You can add or override connections from code with the overload that takes a setup action — useful when the details come from environment variables or a secret store rather than appsettings.json:

services.AddSqlConnections(connectionStrings =>
{
    connectionStrings["Reporting"] = new ConnectionStringEntry
    {
        ConnectionString = "Server=...;Database=Reporting;...",
        ProviderName = "Microsoft.Data.SqlClient",
        Dialect = "SqlServer2012"
    };
});

Transaction settings

When a service endpoint action takes an IUnitOfWork, the ServiceEndpoint base class creates a UnitOfWork with an isolation level. By default this is IsolationLevel.Unspecified and the transaction starts immediately. You can change the isolation level and defer starting the transaction with the TransactionSettings attribute on the endpoint class or action method:

[ServiceAuthorize]
[ConnectionKey(typeof(OrderRow))]
public class OrderEndpoint : ServiceEndpoint
{
    [HttpPost]
    [TransactionSettings(IsolationLevel.ReadCommitted)]
    public SaveResponse Create(IUnitOfWork uow, SaveRequest request)
    {
        // runs inside a ReadCommitted transaction
    }
}

You can also set a global default through the TransactionSettings options:

{
  "TransactionSettings": {
    "IsolationLevel": "ReadCommitted",
    "DeferStart": false
  }
}

Using connections in service endpoints

The ServiceEndpoint base class uses everything above automatically: it resolves ISqlConnections, creates a connection with NewByKey using the [ConnectionKey] on the endpoint class, and injects it into actions that take an IDbConnection or IUnitOfWork parameter, committing or rolling back the transaction for you. See Service Endpoints for the details and the manual equivalent.