69 lines
2.1 KiB
C#
69 lines
2.1 KiB
C#
using Microsoft.Data.Sqlite;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace trakker.Data
|
|
{
|
|
public class DataAccess(string connectionString)
|
|
{
|
|
private readonly string _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
|
|
|
|
/// <summary>
|
|
/// Creates a new, unopened SqliteConnection.
|
|
/// </summary>
|
|
protected SqliteConnection CreateConnection() => new(_connectionString);
|
|
|
|
/// <summary>
|
|
/// Opens and returns a SqliteConnection (synchronous).
|
|
/// </summary>
|
|
protected SqliteConnection OpenConnection()
|
|
{
|
|
var conn = CreateConnection();
|
|
conn.Open();
|
|
return conn;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Executes arbitrary, ad-hoc SQL against the database inside a transaction.
|
|
/// </summary>
|
|
/// <param name="sql">A SQL statement to execute. The caller is responsible for
|
|
/// ensuring the SQL is safe and properly parameterized to avoid SQL injection.</param>
|
|
/// <remarks>
|
|
/// This method is intended for one-off maintenance or administrative commands.
|
|
/// It does not return any result; if a scalar value is produced by the SQL,
|
|
/// the current implementation captures it but does not expose it to the caller.
|
|
/// </remarks>
|
|
public void Adhoc(string sql)
|
|
{
|
|
using var conn = OpenConnection();
|
|
using var tx = conn.BeginTransaction();
|
|
|
|
int? result = 0;
|
|
try
|
|
{
|
|
using (var cmd = conn.CreateCommand())
|
|
{
|
|
cmd.Transaction = tx;
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
using var idCmd = conn.CreateCommand();
|
|
idCmd.Transaction = tx;
|
|
result = (int?)idCmd.ExecuteScalar() ;
|
|
|
|
tx.Commit();
|
|
}
|
|
catch
|
|
{
|
|
tx.Rollback();
|
|
throw;
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|