Getting Started with Distributed SQL Transactions in the ScalarDB Cluster .NET Client SDK

The ScalarDB Cluster .NET Client SDK supports the distributed SQL transaction functionality of ScalarDB Cluster. The SDK includes transaction and manager abstractions for easier communication within a cluster.

note

Although we recommend using asynchronous methods, as in the following examples, you can use synchronous methods instead.

For details about distributed non-SQL transactions, see Getting Started with Distributed Transactions in the ScalarDB Cluster .NET Client SDK.

Install the SDK

Install the same major and minor version of the SDK as ScalarDB Cluster into the .NET project. You can do this by using the built-in NuGet package manager, replacing <MAJOR>.<MINOR> with the version that you're using:

dotnet add package ScalarDB.Net.Client --version '<MAJOR>.<MINOR>.*'

Get a transaction manager

First, you need to get a transaction manager object for distributed SQL transactions. To get the transaction manager object, you can use TransactionFactory as follows, replacing <HOSTNAME_OR_IP_ADDRESS> with the FQDN or the IP address, and <PORT> with the port number (60053 by default) of your cluster:

var scalarDbOptions = new ScalarDbOptions
                      {
                         Address = "http://<HOSTNAME_OR_IP_ADDRESS>:<PORT>",
                         HopLimit = 10
                      };
var factory = TransactionFactory.Create(scalarDbOptions);

using var manager = factory.GetSqlTransactionManager();

Execute SQL queries

To execute a SQL statement, you need a SqlStatement object, which can be created by using a builder as follows:

using ScalarDB.Net.Client.Builders;

// ...

var sqlStatement =
    new SqlStatementBuilder()
        .SetSql("SELECT * FROM order_service.statements WHERE item_id = :item_id")
        .AddParam("item_id", 2)
        .Build();

A single SQL statement can be executed directly by using the transaction manager as follows:

var resultSet = await manager.ExecuteAsync(sqlStatement);

The result from the ExecuteAsync method will contain records received from the cluster. The SDK has GetValue, TryGetValue, and IsNull extension methods to simplify using the records:

using ScalarDB.Net.Client.Extensions;

// ...

foreach (var record in resultSet.Records)
{
    // Getting an integer value from the "item_id" column.
    // If it fails, an exception will be thrown.
    var itemId = record.GetValue<int>("item_id");
            
    // Trying to get a string value from the "order_id" column.
    // If it fails, no exception will be thrown.
    if (record.TryGetValue<string>("order_id", out var orderId))
        Console.WriteLine($"order_id: {orderId}");
            
    // Checking if the "count" column is null.
    if (record.IsNull("count"))
        Console.WriteLine("'count' is null");
}

Execute SQL queries in a transaction

To execute multiple SQL statements as part of a single transaction, you need a transaction object.

You can create a transaction object by using the transaction manager as follows:

var transaction = await manager.BeginAsync();

You can also resume a transaction that has already been started as follows:

var transaction = manager.Resume(transactionIdString);

note

The Resume method doesn't have an asynchronous version because it only creates a transaction object. Because of this, resuming a transaction by using the wrong ID is possible.

The transaction has the same ExecuteAsync method as the transaction manager. That method can be used to execute SQL statements.

When a transaction is ready to be committed, you can call the CommitAsync method of the transaction as follows:

await transaction.CommitAsync();

To roll back the transaction, you can use the RollbackAsync method:

await transaction.RollbackAsync();