Alpha

These pages are built from turborel-cs’s main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Running Queries

A connection runs one statement at a time, by QueryAsync. A query gives its columns and rows, a RowsOutcome; any other statement its completion, a DoneOutcome. The examples of this chapter and the next run in order against the server of Connecting, on a new database whose owner ANN has a schema of the same name.

A statement and its rows

SELECT ID, NAME, SALARY FROM STAFF ORDER BY ID
using Turborel.Data.Authentication;
using Turborel.Data.Session;
using Turborel.Data.Transport;

await using NativeConnection connection =
    await NativeConnection.ConnectAsync(new TcpTarget("localhost", 17435), "ann", new TrustMechanism());
connection.Autocommit = true;
await connection.QueryAsync("CREATE TABLE STAFF (ID INTEGER GENERATED ALWAYS AS IDENTITY, NAME VARCHAR(20), SALARY DECIMAL(9,2))");
Outcome inserted = await connection.QueryAsync("INSERT INTO STAFF (NAME, SALARY) VALUES ('Ann', 5200.00), ('Bob', 4100.50)");
if (inserted is DoneOutcome done)
{
    Console.WriteLine($"inserted {done.Completion.RowCount}, identities {string.Join(", ", connection.LastGenerated)}");
}

if (await connection.QueryAsync("SELECT ID, NAME, SALARY FROM STAFF ORDER BY ID") is RowsOutcome result)
{
    Console.WriteLine(string.Join(" | ", result.Columns.Select(c => $"{c.Name} {c.Type}")));
    foreach (IReadOnlyList<object?> row in result.Rows)
    {
        Console.WriteLine(string.Join(", ", row));
    }
}

Console.WriteLine($"server time told: {connection.ServerTime is not null}; in a transaction: {connection.InTransaction}");
inserted 2, identities 1, 2
ID ExactType { Precision = 10, Scale = 0 } | NAME CharacterType { Length = 20, Units = Characters, Varying = True } | SALARY ExactType { Precision = 9, Scale = 2 }
1, Ann, 5200.00
2, Bob, 4100.50
server time told: True; in a transaction: False

Each column has its name, if it has one, its data type and whether it is NOT NULL. Each value is of the type Appendix A gives its SQL type: an exact number a TurborelDecimal, which prints as SQL writes it, a character string a string, and SQL's null null.

With Autocommit set, each statement is its own transaction: the server commits it as it succeeds, and no COMMIT is sent. Without it, the first statement begins a transaction that lasts until COMMIT or ROLLBACK (Transactions).

What a completion tells

A statement's completion, and the connection after it, tell what the server granted the connection to be told:

Told By
The rows a statement changed, or a query returned once all were read Completed.RowCount, RowsAffected
The identity values an INSERT generated, in the order of its rows Completed.Generated, LastGenerated
The server's time for the statement: total, queued and locks, in microseconds Completed.Time, ServerTime
The log position of the commit it made Completed.Position, CommitPosition
The SQLSTATE of the warning it completed with Completed.SqlState, Warning

A query that returns no row, or a change of no row, completes with SQLCODE 100 and SQLSTATE 02000.

Rows as they come

QueryIntoAsync gives a query's rows to a sink, an IRowSink, a batch at a time as the server sends them, so no more than a batch is held. QueryManyAsync runs several statements in one request, each in turn, the first that fails stopping the rest:

using Turborel.Data.Authentication;
using Turborel.Data.Protocol;
using Turborel.Data.Session;
using Turborel.Data.Transport;

await using NativeConnection connection =
    await NativeConnection.ConnectAsync(new TcpTarget("localhost", 17435), "ann", new TrustMechanism());
string values = string.Join(", ", Enumerable.Range(1, 20000).Select(i => $"({i})"));
var printer = new Printer();
Streamed streamed = await connection.QueryIntoAsync($"VALUES {values}", printer);
Console.WriteLine($"{streamed.RowsSent} rows in {printer.Batches} batches, the last {printer.Last}");

Ran ran = await connection.QueryManyAsync("VALUES 1; SELECT * FROM NOWHERE; VALUES 3");
Console.WriteLine($"{ran.Outcomes.Count} ran, then SQLSTATE {ran.Failure?.SqlState}: {ran.Failure?.Message}");

sealed class Printer : IRowSink
{
    public int Batches { get; private set; }

    public object? Last { get; private set; }

    public void Columns(IReadOnlyList<Column> columns) => Console.WriteLine($"{columns.Count} column");

    public void Rows(IReadOnlyList<IReadOnlyList<object?>> rows) => (Batches, Last) = (Batches + 1, rows[^1][0]);
}
1 column
20000 rows in 8 batches, the last 20000
1 ran, then SQLSTATE 2A000: table ANN.NOWHERE is not defined

With ADO.NET

A TurborelCommand runs a statement on a TurborelConnection, and a TurborelDataReader reads a query's rows a batch at a time, as they come. A command of parameters is prepared and executed, its query's rows fetched through a cursor as the reader reads them; one prepared by PrepareAsync is executed again with no Prepare. Outside a transaction each statement is its own, under autocommit. Each column is read as .NET's type of its data type, the type GetFieldType names (Appendix A: Types):

using Turborel.Data;

await using var connection = new TurborelConnection("host=localhost:17435;user=ann");
await connection.OpenAsync();

await using (var create = new TurborelCommand(
    "CREATE TABLE TEAM (ID INTEGER PRIMARY KEY, NAME VARCHAR(20), JOINED DATE, PAY DECIMAL(9,2))", connection))
{
    await create.ExecuteNonQueryAsync();
}

await using var insert = new TurborelCommand("INSERT INTO TEAM VALUES (?, ?, ?, ?)", connection);
TurborelParameter id = insert.Parameters.AddWithValue("id", 0);
TurborelParameter name = insert.Parameters.AddWithValue("name", "");
TurborelParameter joined = insert.Parameters.AddWithValue("joined", DateOnly.MinValue);
TurborelParameter pay = insert.Parameters.AddWithValue("pay", 0m);
await insert.PrepareAsync();
foreach ((int i, string n, DateOnly j, decimal p) in new[]
{
    (1, "Ann", new DateOnly(2021, 3, 1), 5200.00m),
    (2, "Bob", new DateOnly(2022, 7, 15), 4100.50m),
    (3, "Cy", new DateOnly(2024, 1, 2), 3900.00m),
})
{
    (id.Value, name.Value, joined.Value, pay.Value) = (i, n, j, p);
    Console.WriteLine($"inserted {await insert.ExecuteNonQueryAsync()} row");
}

await using var select = new TurborelCommand("SELECT ID, NAME, JOINED, PAY FROM TEAM WHERE PAY > ? ORDER BY ID", connection);
select.Parameters.AddWithValue("least", 4000);
await using (TurborelDataReader reader = await select.ExecuteReaderAsync())
{
    for (int c = 0; c < reader.FieldCount; c++)
    {
        Console.WriteLine($"{reader.GetName(c)}: {reader.GetDataTypeName(c)}, read as {reader.GetFieldType(c).Name}");
    }

    while (await reader.ReadAsync())
    {
        Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDateTime(2):yyyy-MM-dd} {reader.GetDecimal(3)}");
    }
}

await using var count = new TurborelCommand("SELECT COUNT(*) FROM TEAM", connection);
Console.WriteLine($"members: {await count.ExecuteScalarAsync()}");
inserted 1 row
inserted 1 row
inserted 1 row
ID: INTEGER, read as Int32
NAME: CHARACTER VARYING(20), read as String
JOINED: DATE, read as DateTime
PAY: DECIMAL(9,2), read as Decimal
1 Ann 2021-03-01 5200.00
2 Bob 2022-07-15 4100.50
members: 3

ExecuteNonQueryAsync returns the rows a statement changed, ExecuteScalarAsync the first column of the first row. A command of no parameters, of a server that grants multi-statement, may hold several statements, each result read in turn by the reader's NextResultAsync (TurborelCommand).

See also

Statements and Cursors and Values in the API Reference.