Database Agent - C Sharp

Read and write an external database server.

Logic Type	Available
Generic logic	✅
Aggregator logic	❌

Database Agent Configuration

See: Agent Configuration

A Database Agent Configuration defines an external database server that will be allowed to be accessed from LOC runtime.

The Database Agent requires a configuration reference name so that it can access data on the database server. The reference name can be added to a logic while creating or editing a data process.

Import and Usage

The agent can be used without using additional namespaces:

public static class Logic
{

    public static async Task Run(Context ctx)
    {
        var dbClient = await DatabaseAgent.Acquire("db-config-ref");
        var result = await dbClient.Query("SELECT * FROM table1*;");
    }

    public static async Task HandleError(Context ctx, Exception error)
    {
        // ... same
    }
}

Class Reference

Type

• DatabaseAgent

Method: Acquire Database Client

public async static Task<DatabaseClient> Acquire(string name) {}

Parameter	Description
name	Database Agent Configuration reference name

Acquire a Database client using a configuration reference name. Throws an error if the configuration cannot be found.

Database Client

Type

• Public static class DatabaseClient

Method: Select Query

public async Task<QueryResults> Query(string rawSql, IEnumerable<object>? parameters = null) {}

Parameter	Type	Description
rawSql	string	SQL prepared statement
parameters	IEnumerable<object>?	Values to be inserted into the prepared statement

Execute a select query and returns a QueryResults object.

Always Use Prepared Statements

It is strongly recommended to query a database using prepared statements. For example, the following code works but is vulnerable to SQL injection attack:

// bad practice
var result = await db.Query(
    $"SELECT * FROM table1 WHERE col_1 = {value1} AND col_2 = {value2};"
);

Instead, separate parameters into the params array:

var result = await db.Query(
    "SELECT * FROM table1 WHERE col_1 = ? AND col_2 = ?;",
    new List<object>
    {
        value1,
        value2
    }
);

Prepared Statement Placeholders

Supported Database	Placeholder
MySQL/Oracle	?
Microsoft SQL Server	@P1, @P2...
PostgreSQL	$1, $2...

Also be noted that values passed by Query or Execute may not be read as intended types in the database. You may have to add additional conversion it using SQL itself:

SELECT * FROM table1 WHERE id = (CAST((@P1) AS INTEGER));

QueryResults

Results from select query.

Property	Type	Description
Columns	List<QueryResultColumn>	Column descriptions
Rows	List<Dictionary<string, object?>>	Columns names and row values

If a column in the result has the name of ColName and type of ColType, the value can be accessed as Rows[index]["ColName"]["ColType"].

info

PostgreSQL returns lowered-case column names, which makes it rows[index].colname.

QueryResultColumn

Property	Type	Description
Name	string	Column name
Type	string	Column data type

info

The content of Type here may be different with the ColType in each element of Rows.

Method: Action Query

public async Task<ulong> Execute(string rawSql, IEnumerable<object>? parameters = null) {}

Parameter	Type	Description
rawSql	string	SQL prepared statement
parameters	IEnumerable<object>?	Values to be inserted into the prepared statement

Execute an action query (insert, update, delete, etc.). Returns the number of affected rows.

Method: Begin Transaction

public async Task BeginTransaction() {}

Begin a transaction. All action queries will only take effect when commitTransaction() is called.

Modify Transaction Isolation Level

Advanced users can modify the transaction isolation level with the following SQL statement:

--MySQL, MS SQL Server
SET TRANSACTION ISOLATION LEVEL <LEVEL>

--PostgreSQL, Oracle
SET TRANSACTION <LEVEL>

The default level setting is READ COMMITTED.

Method: Commit Transaction

public async Task CommitTransaction() {}

Commit a transaction.

Method: Rollback Transaction

public async Task RollbackTransaction() {}

Rollback a transaction. The database may throw an error if CommitTransaction() is already called.

Method: Release Connection

public async Task Release() {}

The LOC runtime will also try to close unreleased connection when the task completes execution.

Examples

Query From Table

var dbClient = await DatabaseAgent.Acquire("db-config-ref");

var result = await dbClient.Query(
    "SELECT * FROM table1 WHERE col_1 = ? AND col_2 = ?;",
    new List<object>{ value1, value2 }
);

var rows = result.Rows;

foreach (var row in rows)
{
    // if col_1 is integer type
    int value1 = ((JsonNode) row["col_1"])["Integer"].GetValue<int>();

    // if col_2 is text type
    string value2 = ((JsonNode) row["col_2"])["Text"].GetValue<string>();

    // ...
}

Insert Data Into Table

var dbClient = await DatabaseAgent.Acquire("db-config-ref");

await dbClient.Execute(
    "INSERT INTO table1 (col_1, col_2) VALUES (?, ?);",
    new List<object>{ value1, value2 }
);

Error Handling with Transaction

var dbClient = await DatabaseAgent.Acquire("db-config-ref");

try
{
    await dbClient.BeginTransaction();

    // a series of SQL action queries to be commited

    await dbClient.CommitTransaction();
}
catch (Exception e)
{
    try
    {
        await dbClient.RollbackTransaction();
    }
    catch (Exception e)
    {
        // error occurred after commit
    }
}
finally
{
    await dbClient.Release();
}