Travel_Agency/TravelAgencyWinForms/Data/Db.cs

using System.Data;
using Npgsql;
using TravelAgencyWinForms.Models;

namespace TravelAgencyWinForms.Data;

public static class Db
{
    private const string DefaultConnectionString = "Host=edu-pg.itiscaf.ru;Port=5432;Database=dbwork;Username=shchetnikov_id;Password=#10j$bn0&22;Search Path=shchetnikov_id;Include Error Detail=true";

    public static string ConnectionString
    {
        get
        {
            var localPath = Path.Combine(AppContext.BaseDirectory, "connection.txt");
            if (File.Exists(localPath))
            {
                var text = File.ReadAllText(localPath).Trim();
                if (!string.IsNullOrWhiteSpace(text))
                    return text;
            }

            return DefaultConnectionString;
        }
    }

    public static NpgsqlConnection CreateConnection()
    {
        return new NpgsqlConnection(ConnectionString);
    }

    public static DataTable Query(string sql, params NpgsqlParameter[] parameters)
    {
        using var connection = CreateConnection();
        using var command = new NpgsqlCommand(sql, connection);
        if (parameters.Length > 0)
            command.Parameters.AddRange(parameters);
        using var adapter = new NpgsqlDataAdapter(command);
        var table = new DataTable();
        adapter.Fill(table);
        return table;
    }

    public static int Execute(string sql, params NpgsqlParameter[] parameters)
    {
        using var connection = CreateConnection();
        connection.Open();
        ApplyAppContext(connection);
        using var command = new NpgsqlCommand(sql, connection);
        if (parameters.Length > 0)
            command.Parameters.AddRange(parameters);
        return command.ExecuteNonQuery();
    }

    public static object? Scalar(string sql, params NpgsqlParameter[] parameters)
    {
        using var connection = CreateConnection();
        connection.Open();
        using var command = new NpgsqlCommand(sql, connection);
        if (parameters.Length > 0)
            command.Parameters.AddRange(parameters);
        var value = command.ExecuteScalar();
        return value == DBNull.Value ? null : value;
    }

    public static void ResetIdentitySequences()
    {
        try
        {
            Execute("CALL sp_reset_identity_sequences();");
        }
        catch
        {
        }
    }

    public static bool TestConnection(out string message)
    {
        try
        {
            using var connection = CreateConnection();
            connection.Open();
            using var command = new NpgsqlCommand("select current_database(), current_schema();", connection);
            using var reader = command.ExecuteReader();
            if (reader.Read())
                message = $"Подключение выполнено. База: {reader.GetString(0)}, схема: {reader.GetString(1)}";
            else
                message = "Подключение выполнено.";
            return true;
        }
        catch (Exception ex)
        {
            message = ex.Message;
            return false;
        }
    }

    public static void SetAppContext(string login)
    {
        try
        {
            using var connection = CreateConnection();
            connection.Open();
            using var command = new NpgsqlCommand("CALL sp_set_app_context(@login);", connection);
            command.Parameters.AddWithValue("login", login);
            command.ExecuteNonQuery();
        }
        catch
        {
        }
    }

    public static void ApplyAppContext(NpgsqlConnection connection)
    {
        if (string.IsNullOrWhiteSpace(CurrentUser.Login))
            return;

        try
        {
            using var command = new NpgsqlCommand("CALL sp_set_app_context(@login);", connection);
            command.Parameters.AddWithValue("login", CurrentUser.Login);
            command.ExecuteNonQuery();
        }
        catch
        {
        }
    }
}