App in C# (.NET)

• Initializing a database connection
• Creating tables
• Adding data
• Retrieving data with a Select
  • Processing execution results
• Scan queries

This page contains a detailed description of the code of a test app that uses the C# (.NET) SDK YDB.

Note

The article is being updated.

Initializing a database connection

To interact with YDB, create an instance of the driver, client, and session:

• The YDB driver lets the app and YDB interact at the transport layer. The driver must exist throughout the YDB access lifecycle and be initialized before creating a client or session.
• The YDB client runs on top of the YDB driver and enables the handling of entities and transactions.
• The YDB session contains information about executed transactions and prepared queries, and is part of the YDB client context.

App code snippet for driver initialization:

public static async Task Run(
    string endpoint,
    string database,
    ICredentialsProvider credentialsProvider)
{
    var config = new DriverConfig(
        endpoint: endpoint,
        database: database,
        credentials: credentialsProvider
    );
    using var driver = new Driver(
        config: config
    );
    await driver.Initialize();
}

App code snippet for creating a session:

using var tableClient = new TableClient(driver, new TableClientConfig());

Creating tables

Creating tables to be used in operations on a test app. This step results in the creation of DB tables of the series directory data model:

• Series
• Seasons
• Episodes

Once the tables are created, the method for getting information about data schema objects is called and the result of its execution is output.

To create tables, use the session.ExecuteSchemeQuery method with a DDL (Data Definition Language) YQL query.

var response = await tableClient.SessionExec(async session =>
{
    return await session.ExecuteSchemeQuery(@"
        CREATE TABLE series (
            series_id Uint64,
            title Utf8,
            series_info Utf8,
            release_date Date,
            PRIMARY KEY (series_id)
        );
        CREATE TABLE seasons (
            series_id Uint64,
            season_id Uint64,
            title Utf8,
            first_aired Date,
            last_aired Date,
            PRIMARY KEY (series_id, season_id)
        );
        CREATE TABLE episodes (
            series_id Uint64,
            season_id Uint64,
            episode_id Uint64,
            title Utf8,
            air_date Date,
            PRIMARY KEY (series_id, season_id, episode_id)
        );
    ");
});
response.Status.EnsureSuccess();

Adding data

Adding data to the created tables using an UPSERT statement of YQL. A data update request is sent within a single request to the server with transaction auto-commit mode enabled.

Code snippet for inserting and updating data:

var response = await tableClient.SessionExec(async session =>
{
    var query = @"
        DECLARE $id AS Uint64;
        DECLARE $title AS Utf8;
        DECLARE $release_date AS Date;
        UPSERT INTO series (series_id, title, release_date) VALUES
            ($id, $title, $release_date);
    ";
    return await session.ExecuteDataQuery(
        query: query,
        txControl: TxControl.BeginSerializableRW().Commit(),
        parameters: new Dictionary<string, YdbValue>
            {
                { "$id", YdbValue.MakeUint64(1) },
                { "$title", YdbValue.MakeUtf8("NewTitle") },
                { "$release_date", YdbValue.MakeDate(DateTime.UtcNow) }
            }
    );
});
response.Status.EnsureSuccess();

PRAGMA TablePathPrefix adds a specified prefix to the database table paths. It uses standard file system path concatenation: i.e., it supports parent folder referencing and requires no trailing slash. For example:

PRAGMA TablePathPrefix = "/cluster/database";
SELECT * FROM episodes;

For more information about PRAGMA YQL, see the YQL documentation.

Retrieving data with a Select

Retrieving data using a SELECT statement in YQL. Handling the retrieved data selection in the app.

To execute YQL queries, use the Session.executeDataQuery() method. The SDK lets you explicitly control the execution of transactions and configure the transaction execution mode using the TxControl class. In the code snippet below, a transaction with the SerializableRW mode and an automatic commit after executing the request is used. The values of the request parameters are passed in the form of a dictionary name-value in the parameters argument.

var response = await tableClient.SessionExec(async session =>
{
    var query = @"
        DECLARE $id AS Uint64;
        SELECT
            series_id,
            title,
            release_date
        FROM series
        WHERE series_id = $id;
    ";
    return await session.ExecuteDataQuery(
        query: query,
        txControl: TxControl.BeginSerializableRW().Commit(),
        parameters: new Dictionary<string, YdbValue>
            {
                { "$id", YdbValue.MakeUint64(id) }
            },
    );
});
response.Status.EnsureSuccess();
var queryResponse = (ExecuteDataQueryResponse)response;
var resultSet = queryResponse.Result.ResultSets[0];

Processing execution results

The result of query execution (resultset) consists of an organized set of rows. Example of processing the query execution result:

foreach (var row in resultSet.Rows)
{
    Console.WriteLine($"> Series, " +
        $"series_id: {(ulong?)row["series_id"]}, " +
        $"title: {(string?)row["title"]}, " +
        $"release_date: {(DateTime?)row["release_date"]}");
}

Scan queries

Making a scan query that results in a data stream. Streaming lets you read an unlimited number of rows and amount of data.

public void executeScanQuery()
{
  var scanStream = TableClient.ExecuteScanQuery(@$"
    SELECT series_id, season_id, COUNT(*) AS episodes_count
    FROM episodes
    GROUP BY series_id, season_id
    ORDER BY series_id, season_id;
  ");
  while (await scanStream.Next())
  {
    scanStream.Response.EnsureSuccess();
    var resultSet = scanStream.Response.Result.ResultSetPart;
    if (resultSet != null)
    {
      foreach (var row in resultSet.Rows)
      {
        Console.WriteLine($"> ScanQuery, " +
          $"series_id: {(ulong?)row["series_id"]}, " +
          $"season_id: {(ulong?)row["season_id"]}, " +
          $"episodes_count: {(ulong)row["episodes_count"]}");
      }
    }
  }
}