Sql

class · namespace z3n7 · source Db/Sql.cs

public class Sql : IDisposable

One open connection to SQLite (through the SQLite3 ODBC driver) or PostgreSQL (Npgsql). Dispose it to close the connection.

Remarks: The connection is opened in the constructor.

Constructors#

Sql#

public Sql(string dbPath, string dbPass)

source

Opens an SQLite database through the SQLite3 ODBC Driver.

public Sql(string hostname, string port, string database, string user, string password)

source

Opens a PostgreSQL connection with pooling.

public Sql(string connectionstring)

source

Opens a PostgreSQL connection from an Npgsql connection string.

public Sql(IDbConnection connection)

source

Wraps an existing connection and opens it if it is closed.

Properties#

ConnectionType#

public DatabaseType ConnectionType { get; }

source

SQLite for an ODBC connection, PostgreSQL for Npgsql, otherwise Unknown.

Methods#

AddRange#

public async Task AddRange(int range, string tableName = null)

source

Inserts rows with ids from the current maximum + 1 up to range, one statement per row.

CopyTableAsync#

public async Task<int> CopyTableAsync(string sourceTable, string destinationTable)

source

Creates destinationTable with the columns and primary key of sourceTable and copies all rows into it, within the same database.

Returns: Number of copied rows.

CreateParameter#

public IDbDataParameter CreateParameter(string name, object value)

source

Creates a command parameter of the right provider type; null becomes DBNull.

CreateParameters#

public IDbDataParameter[] CreateParameters(params (string name, object value)[] parameters)

source

Creates several parameters, see CreateParameter.

DbRead#

public string DbRead(string sql, string separator = "|")

source

Synchronous DbReadAsync with the default row separator.

DbReadAsync#

public async Task<string> DbReadAsync(string sql, string columnSeparator = "|", string rawSepararor = "\r\n")

source

Runs a query and returns every row as text.

DbWrite#

public int DbWrite(string sql, params IDbDataParameter[] parameters)

source

Synchronous DbWriteAsync.

DbWriteAsync#

public async Task<int> DbWriteAsync(string sql, params IDbDataParameter[] parameters)

source

Executes a non-query statement.

Returns: The affected row count. Errors are rethrown with the SQL text appended.

Dispose#

public void Dispose()

source

Closes and disposes the connection.

Get#

public async Task<string> Get(string toGet, string id, string tableName = null, string where = null)

source

Reads the first column of the first matching row.

Returns: The value as text, or null.

MigrateAllTablesAsync#

public static async Task<int> MigrateAllTablesAsync(Sql sourceDb, Sql destinationDb)

source

Copies every user table from one database to another of the other kind (PostgreSQL ↔ SQLite). Tables that already exist in the target are not recreated, rows are still inserted. A table that fails is skipped; the error goes to the debug output only.

Returns: Total number of copied rows.

Upd#

public async Task<int> Upd(string toUpd, object id, string tableName = null, string where = null, bool last = false)

source

Runs UPDATE … SET toUpd for the row id or the rows matching where.

Returns: Affected row count.

public async Task Upd(List<string> toWrite, string tableName = null, string where = null, bool last = false)

source

Runs Upd for each item, with ids 0, 1, 2 … in list order.