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.Client --version '<MAJOR>.<MINOR>.*'

Create a settings file

Create a scalardb-options.json file and add the following, replacing <HOSTNAME_OR_IP_ADDRESS> with the FQDN or the IP address, and <PORT> with the port number (60053 by default) of your cluster:

{
  "ScalarDbOptions": {
    "Address": "http://<HOSTNAME_OR_IP_ADDRESS>:<PORT>",
    "HopLimit": 10
  }
}

For details about settings files and other ways to configure the client, see Client configuration.

Get a transaction manager

You need to get a transaction manager object for distributed SQL transactions. To get the transaction manager object, you can use TransactionFactory as follows:

// Pass the path to the settings file created in the previous step.
var factory = TransactionFactory.Create("scalardb-options.json");

using var manager = factory.GetSqlTransactionManager();

Execute SQL queries

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

using ScalarDB.Client.Builders.Sql;

// ...

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 value of the specific column can be retrieved in the following manner:

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");
}

For details about which type should be used in GetValue<T> and TryGetValue<T>, see How ScalarDB Column Types Are Converted to and from .NET Types.

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();

Get Metadata

You can retrieve ScalarDB's metadata with the Metadata property as follows:

// namespaces, tables metadata
var namespaces = (await manager.Metadata.GetNamespacesAsync()).ToList();

foreach (var ns in namespaces)
{
    Console.WriteLine($"Namespace: {ns.Name}");
    
    foreach (var tbl in await ns.GetTablesAsync())
    {
        Console.WriteLine($"  Table: {tbl.Name}");

        Console.WriteLine($"  Columns:");
        foreach (var col in tbl.Columns)
            Console.WriteLine($"    {col.Name} [{col.DataType}]");

        Console.WriteLine($"  PartitionKey:");
        foreach (var col in tbl.PartitionKey)
            Console.WriteLine($"    {col.Name}");

        Console.WriteLine($"  ClusteringKey:");
        foreach (var col in tbl.ClusteringKey)
            Console.WriteLine($"    {col.Name} [{col.ClusteringOrder}]");

        Console.WriteLine($"  Indexes:");
        foreach (var index in tbl.Indexes)
            Console.WriteLine($"    {index.ColumnName}");

        Console.WriteLine();
    }
}

// users metadata
foreach (var user in await manager.Metadata.GetUsersAsync())
{
    Console.WriteLine($"User: {user.Name} [IsSuperuser: {user.IsSuperuser}]");

    foreach (var ns in namespaces)
    {
        Console.WriteLine($"  Namespace: {ns.Name}");

        Console.WriteLine($"  Privileges:");
        foreach (var privilege in await user.GetPrivilegesAsync(ns.Name))
            Console.WriteLine($"    {privilege}");
    }

    Console.WriteLine();
}