BooksRating/praktika/Form1.cs

using System.Data;
using Npgsql;

namespace praktika
{
    public partial class Form1 : Form
    {
        public const string SchemaName = "dyomin_be";
        public const string AdminLogin = "admin";
        public const string AdminPassword = "admin";
        public const string ConnectionString =
            "Host=edu-pg.itiscaf.ru;Port=5432;Database=dbwork;Username=dyomin_be;Password=$vAx&y5zxQ$;Search Path=dyomin_be";

        public readonly TextBox loginTextBox = new();
        public readonly TextBox passwordTextBox = new();
        public readonly Button loginButton = new();
        public readonly Label loginStatusLabel = new();
        public readonly Panel loginPanel = new();

        public readonly Panel mainPanel = new();
        public readonly ComboBox tableComboBox = new();
        public readonly DataGridView dataGridView = new();
        public readonly Button refreshButton = new();
        public readonly Button addButton = new();
        public readonly Button saveButton = new();
        public readonly Button deleteButton = new();
        public readonly Button logoutButton = new();
        public readonly Label currentUserLabel = new();

        public readonly Dictionary<string, TableInfo> tables = new()
        {
            ["authors"] = new("id"),
            ["books"] = new("id"),
            ["tags"] = new("id"),
            ["book_tags"] = new("book_id", "tag_id") { AutoIncrementKeys = [] },
            ["comments"] = new("id"),
            ["users"] = new("id") { AdminOnly = true },
            ["v_book_catalog"] = new() { ReadOnly = true }
        };

        public DataTable? currentTable;
        public bool isAdmin;
        public string currentLogin = "";

        public Form1()
        {
            InitializeComponent();
            BuildUi();
            ShowLogin();
        }

        public void BuildUi()
        {
            Text = "PostgreSQL CRUD";
            Width = 1100;
            Height = 700;
            StartPosition = FormStartPosition.CenterScreen;
            MinimumSize = new Size(900, 550);

            loginPanel.Dock = DockStyle.Fill;
            var loginBox = new TableLayoutPanel
            {
                Width = 360,
                Height = 210,
                Anchor = AnchorStyles.None,
                ColumnCount = 1,
                RowCount = 7,
                Padding = new Padding(20)
            };

            loginBox.Location = new Point((loginPanel.Width - loginBox.Width) / 2, (loginPanel.Height - loginBox.Height) / 2);

            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 24));
            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 36));
            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 24));
            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 36));
            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 36));
            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 24));
            loginBox.RowStyles.Add(new RowStyle(SizeType.Absolute, 36));

            loginBox.Controls.Add(new Label { Text = "Логин", Dock = DockStyle.Fill }, 0, 0);
            loginTextBox.Dock = DockStyle.Fill;
            loginBox.Controls.Add(loginTextBox, 0, 1);

            loginBox.Controls.Add(new Label { Text = "Пароль", Dock = DockStyle.Fill }, 0, 2);
            passwordTextBox.Dock = DockStyle.Fill;
            passwordTextBox.UseSystemPasswordChar = true;
            loginBox.Controls.Add(passwordTextBox, 0, 3);

            loginButton.Text = "Войти";
            loginButton.Dock = DockStyle.Fill;
            loginButton.Click += LoginButton_Click;
            loginBox.Controls.Add(loginButton, 0, 4);

            loginBox.Controls.Add(new Label { Text = "Админ: admin / admin", Dock = DockStyle.Fill }, 0, 5);
            loginStatusLabel.ForeColor = Color.DarkRed;
            loginStatusLabel.Dock = DockStyle.Fill;
            loginBox.Controls.Add(loginStatusLabel, 0, 6);

            loginPanel.Controls.Add(loginBox);
            Controls.Add(loginPanel);

            mainPanel.Dock = DockStyle.Fill;
            mainPanel.Visible = false;

            var root = new TableLayoutPanel
            {
                Dock = DockStyle.Fill,
                ColumnCount = 1,
                RowCount = 2,
                Padding = new Padding(10)
            };
            root.RowStyles.Add(new RowStyle(SizeType.Absolute, 42));
            root.RowStyles.Add(new RowStyle(SizeType.Percent, 100));

            var toolbar = new FlowLayoutPanel
            {
                Dock = DockStyle.Fill,
                FlowDirection = FlowDirection.LeftToRight,
                WrapContents = false
            };

            currentUserLabel.AutoSize = true;
            currentUserLabel.Padding = new Padding(0, 8, 16, 0);
            toolbar.Controls.Add(currentUserLabel);

            tableComboBox.Width = 180;
            tableComboBox.DropDownStyle = ComboBoxStyle.DropDownList;
            tableComboBox.SelectedIndexChanged += TableComboBox_SelectedIndexChanged;
            toolbar.Controls.Add(tableComboBox);

            refreshButton.Text = "Обновить";
            refreshButton.Click += RefreshButton_Click;
            toolbar.Controls.Add(refreshButton);

            addButton.Text = "Добавить";
            addButton.Click += AddButton_Click;
            toolbar.Controls.Add(addButton);

            saveButton.Text = "Сохранить";
            saveButton.Click += SaveButton_Click;
            toolbar.Controls.Add(saveButton);

            deleteButton.Text = "Удалить";
            deleteButton.Click += DeleteButton_Click;
            toolbar.Controls.Add(deleteButton);

            logoutButton.Text = "Выйти";
            logoutButton.Click += LogoutButton_Click;
            toolbar.Controls.Add(logoutButton);

            dataGridView.Dock = DockStyle.Fill;
            dataGridView.AllowUserToAddRows = false;
            dataGridView.AllowUserToDeleteRows = false;
            dataGridView.AutoGenerateColumns = true;
            dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView.MultiSelect = true;

            root.Controls.Add(toolbar, 0, 0);
            root.Controls.Add(dataGridView, 0, 1);
            mainPanel.Controls.Add(root);
            Controls.Add(mainPanel);

            AcceptButton = loginButton;
        }

        public void LoginButton_Click(object? sender, EventArgs e)
        {
            loginStatusLabel.Text = "";
            var login = loginTextBox.Text.Trim();
            var password = passwordTextBox.Text;

            if (login == AdminLogin && password == AdminPassword)
            {
                isAdmin = true;
                currentLogin = login;
                OpenMain();
                return;
            }

            try
            {
                using var connection = new NpgsqlConnection(ConnectionString);
                connection.Open();

                using var command = new NpgsqlCommand(
                    $"select count(*) from {Q(SchemaName)}.{Q("users")} " +
                    $"where login = @login and password_hash = crypt(@password, password_hash)",
                    connection);
                command.Parameters.AddWithValue("login", login);
                command.Parameters.AddWithValue("password", password);

                var exists = Convert.ToInt32(command.ExecuteScalar()) > 0;
                if (!exists)
                {
                    loginStatusLabel.Text = "Неверный логин или пароль";
                    return;
                }

                isAdmin = false;
                currentLogin = login;
                OpenMain();
            }
            catch (Exception ex)
            {
                loginStatusLabel.Text = ex.Message;
            }
        }

        public void OpenMain()
        {
            loginPanel.Visible = false;
            mainPanel.Visible = true;
            currentUserLabel.Text = isAdmin ? $"Админ: {currentLogin}" : $"Пользователь: {currentLogin}";
            AcceptButton = saveButton;

            tableComboBox.Items.Clear();
            foreach (var table in tables.Where(x => isAdmin || !x.Value.AdminOnly).Select(x => x.Key))
            {
                tableComboBox.Items.Add(table);
            }

            if (tableComboBox.Items.Count > 0)
            {
                tableComboBox.SelectedIndex = 0;
            }
        }

        public void ShowLogin()
        {
            mainPanel.Visible = false;
            loginPanel.Visible = true;
            loginTextBox.Text = "";
            passwordTextBox.Text = "";
            loginStatusLabel.Text = "";
            dataGridView.DataSource = null;
            currentTable = null;
            AcceptButton = loginButton;
            loginTextBox.Focus();
        }

        public void TableComboBox_SelectedIndexChanged(object? sender, EventArgs e)
        {
            LoadCurrentTable();
        }

        public void RefreshButton_Click(object? sender, EventArgs e)
        {
            LoadCurrentTable();
        }

        public void AddButton_Click(object? sender, EventArgs e)
        {
            if (currentTable is null || SelectedTable().ReadOnly)
            {
                return;
            }

            currentTable.Rows.Add(currentTable.NewRow());
        }

        public void DeleteButton_Click(object? sender, EventArgs e)
        {
            if (currentTable is null || SelectedTable().ReadOnly)
            {
                return;
            }

            foreach (DataGridViewRow gridRow in dataGridView.SelectedRows)
            {
                if (gridRow.DataBoundItem is DataRowView rowView)
                {
                    rowView.Row.Delete();
                }
            }
        }

        public void SaveButton_Click(object? sender, EventArgs e)
        {
            if (currentTable is null)
            {
                return;
            }

            dataGridView.EndEdit();
            if (BindingContext is { } context && context[currentTable] is { } manager)
            {
                manager.EndCurrentEdit();
            }

            try
            {
                var tableName = SelectedTableName();
                var table = SelectedTable();

                if (table.ReadOnly)
                {
                    throw new InvalidOperationException("Эту таблицу нельзя редактировать");
                }

                using var connection = new NpgsqlConnection(ConnectionString);
                connection.Open();
                using var transaction = connection.BeginTransaction();

                foreach (DataRow row in currentTable.Rows.Cast<DataRow>().Where(x => x.RowState == DataRowState.Deleted).ToList())
                {
                    DeleteRow(connection, transaction, tableName, table, row);
                }

                foreach (DataRow row in currentTable.Rows.Cast<DataRow>().Where(x => x.RowState == DataRowState.Added).ToList())
                {
                    InsertRow(connection, transaction, tableName, table, row);
                }

                foreach (DataRow row in currentTable.Rows.Cast<DataRow>().Where(x => x.RowState == DataRowState.Modified).ToList())
                {
                    UpdateRow(connection, transaction, tableName, table, row);
                }

                transaction.Commit();
                LoadCurrentTable();
                MessageBox.Show("Сохранено", "Готово", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка сохранения", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void LogoutButton_Click(object? sender, EventArgs e)
        {
            isAdmin = false;
            currentLogin = "";
            tableComboBox.Items.Clear();
            ShowLogin();
        }

        public void LoadCurrentTable()
        {
            if (tableComboBox.SelectedItem is not string tableName)
            {
                return;
            }

            try
            {
                var table = tables[tableName];
                var orderBy = table.PrimaryKeys.Count > 0
                    ? " order by " + string.Join(", ", table.PrimaryKeys.Select(Q))
                    : "";

                using var connection = new NpgsqlConnection(ConnectionString);
                connection.Open();
                using var command = new NpgsqlCommand($"select * from {Q(SchemaName)}.{Q(tableName)}{orderBy}", connection);
                using var reader = command.ExecuteReader();

                currentTable = new DataTable(tableName);
                currentTable.Load(reader);
                PrepareTable(tableName, currentTable);
                dataGridView.DataSource = currentTable;

                dataGridView.ReadOnly = table.ReadOnly;
                addButton.Enabled = !table.ReadOnly;
                saveButton.Enabled = !table.ReadOnly;
                deleteButton.Enabled = !table.ReadOnly;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка загрузки", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void PrepareTable(string tableName, DataTable dataTable)
        {
            var table = tables[tableName];
            foreach (DataColumn column in dataTable.Columns)
            {
                column.ReadOnly = false;
                column.AllowDBNull = true;
            }

            foreach (var key in table.PrimaryKeys)
            {
                if (dataTable.Columns.Contains(key))
                {
                    dataTable.Columns[key]!.ReadOnly = table.AutoIncrementKeys.Contains(key);
                }
            }
        }

        public void InsertRow(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, TableInfo table, DataRow row)
        {
            var columns = currentTable!.Columns
                .Cast<DataColumn>()
                .Select(x => x.ColumnName)
                .Where(x => !table.AutoIncrementKeys.Contains(x) && row[x] != DBNull.Value)
                .ToList();

            if (columns.Count == 0)
            {
                throw new InvalidOperationException("Нет данных для добавления");
            }

            var columnList = string.Join(", ", columns.Select(Q));
            var valueList = string.Join(", ", columns.Select((_, index) => $"@p{index}"));
            using var command = new NpgsqlCommand(
                $"insert into {Q(SchemaName)}.{Q(tableName)} ({columnList}) values ({valueList})",
                connection,
                transaction);

            AddParameters(command, columns, row, DataRowVersion.Current, 0);
            command.ExecuteNonQuery();
        }

        public void UpdateRow(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, TableInfo table, DataRow row)
        {
            var columns = currentTable!.Columns
                .Cast<DataColumn>()
                .Select(x => x.ColumnName)
                .Where(x => !table.PrimaryKeys.Contains(x))
                .ToList();

            if (columns.Count == 0)
            {
                return;
            }

            var setSql = string.Join(", ", columns.Select((column, index) => $"{Q(column)} = @p{index}"));
            var whereSql = BuildWhere(table, row, DataRowVersion.Original, columns.Count);
            using var command = new NpgsqlCommand(
                $"update {Q(SchemaName)}.{Q(tableName)} set {setSql} where {whereSql}",
                connection,
                transaction);

            AddParameters(command, columns, row, DataRowVersion.Current, 0);
            AddParameters(command, table.PrimaryKeys, row, DataRowVersion.Original, columns.Count);
            command.ExecuteNonQuery();
        }

        public void DeleteRow(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, TableInfo table, DataRow row)
        {
            var whereSql = BuildWhere(table, row, DataRowVersion.Original, 0);
            using var command = new NpgsqlCommand(
                $"delete from {Q(SchemaName)}.{Q(tableName)} where {whereSql}",
                connection,
                transaction);

            AddParameters(command, table.PrimaryKeys, row, DataRowVersion.Original, 0);
            command.ExecuteNonQuery();
        }

        public static string BuildWhere(TableInfo table, DataRow row, DataRowVersion version, int offset)
        {
            if (table.PrimaryKeys.Count == 0)
            {
                throw new InvalidOperationException("Для CRUD нужен первичный ключ");
            }

            return string.Join(" and ", table.PrimaryKeys.Select((key, index) => $"{Q(key)} = @p{offset + index}"));
        }

        public static void AddParameters(NpgsqlCommand command, List<string> columns, DataRow row, DataRowVersion version, int offset)
        {
            for (var i = 0; i < columns.Count; i++)
            {
                var value = row[columns[i], version];
                command.Parameters.AddWithValue($"p{offset + i}", value is DBNull ? DBNull.Value : value);
            }
        }

        public string SelectedTableName()
        {
            return tableComboBox.SelectedItem as string
                ?? throw new InvalidOperationException("Таблица не выбрана");
        }

        public TableInfo SelectedTable()
        {
            return tables[SelectedTableName()];
        }

        public static string Q(string identifier)
        {
            return "\"" + identifier.Replace("\"", "\"\"") + "\"";
        }

        public sealed class TableInfo
        {
            public TableInfo(params string[] primaryKeys)
            {
                PrimaryKeys = primaryKeys.ToList();
                AutoIncrementKeys = primaryKeys.ToList();
            }

            public List<string> PrimaryKeys { get; init; }
            public List<string> AutoIncrementKeys { get; init; }
            public bool AdminOnly { get; init; }
            public bool ReadOnly { get; init; }
        }
    }
}