connections_and_transactions.md

Connections and Transactions

Serenity uses simple ADO.NET data access objects, like SqlConnection, DbCommand etc.

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

SqlConnections Class

[namespace: Serenity.Data, assembly: Serenity.Data]

This class contains static functions to create a connection, and control it in a database agnostic way.

SqlConnections.NewByKey method

public static IDbConnection NewByKey(string connectionKey)

Use this method to get a new IDbConnection for a connection string defined in application configuration file (e.g. app.config or web.config).

using (var connection = SqlConnections.NewByKey("Default")) 
{
    // ...
}

Try to always wrap connections in a using block...

This reads connection string with "Default" key from web.config, and creates a new connection using ProviderName information that is also specified in connection setting. For example, if ProviderName is "System.Data.SqlClient" this creates a new SqlConnection object.

You usually don't have to open connections explicitly as they are automatically opened when needed (as long as you use Serenity extensions).

SqlConnections.NewFor< TClass > 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 ConnectionKey attribute generated by Sergen:

[ConnectionKey("Northwind")]
public sealed class CustomerRow : Row, IIdRow, INameRow
{
}

When you are going to query for customers, instead of hardcoding "Northwind", you may reuse this information from a 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 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 most of the time.

SqlConnections.New method

public static IDbConnection New(string connectionString, string providerName)

You may sometimes want to create a connection that doesn't exist in your configuration file.

using (var connection = SqlConnections.New(
    "Data Source=(localdb)\v11.0; Initial Catalog=Northwind; 
     Integrated Security=true", "System.Data.SqlClient")) 
{
    // ...
}

Here we have to specify connection string and the provider name like "System.Data.SqlClient".

You might be asking yourself "why this method instead of simply typing new SqlClient()?", see next topic for advantages of these.

WrappedConnection

All methods we saw so far returns an IDbConnection object. You'd expect it to be a SqlConnection, FirebirdConnection etc, but thats not exactly true.

The IDbConnection object you receive is a Serenity specific WrappedConnection object that actually contains 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 returned IDbConnection instances, they'll act just like the underlying connections, but you should prefer SqlConnections methods to create connections, otherwise you might lose some of these listed features.

UnitOfWork and IUnitOfWork

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

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

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

You might say then Commit 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 success.

Another scenario is about uploading files. This time we are updating some File entity, and let's say we replace an old file with uploaded new file. If we again hurry and delete old file before transaction outcome is clear, and transaction fails eventually, we'll end up with a file entity without an actual old file in disk. So, we should actually delete file and replace it with the new file in OnCommit event, and remove uploaded file in 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 connection wrapped inside IUnitOfWork
    // this will automatically run in transaction context
    uow.Connection.Insert(task);
   
    uow.OnCommit += () => {
       // send e-mail for this task now, this method will only
       // be called if transaction commits successfully
    };
    
    uow.OnRollback += () => {
       // optional, do something else if it fails
    };       
}