DataGridView управление базой данных

Управление базой данных

Под управлением базой данных в приложении понимается редактирование, вставка и удаления строк. Прилагаемое приложение позволяет редактировать содержимое базы данных в режиме "Что вижу, то и получаю" (технология WYSIWYG).

Исполняющие методы-события унифицированы для редактируемых визуальных элементов DataGridView. Процедура вставки новой строки и редактирование существующей включает практически одинаковые действия. Процесс удаления строк имеет логику отмены действия. Источники данных для элементов DataGridView - это объекты типа BindingSource, которые взаимодействуют с базой данных посредством одного экземпляра DataSet.

Программный код приложения управления базой данных содержит немного добавочных строк, основная логика построена на уже заложенной в DataGridView функциональности. В приложении программно реализованы отношения между таблицами и ограничения Foreign Key, Unique.

Формирование интерфейса DataGridView

Интерфейс приложения построен на трёх элементах DataGridView. В качестве источника данных для элементов отображения таблиц в приложении применены объекты типа BindingSource. Использование класса BindingSource обеспечивает приложению взаимосвязанные манипуляции с таблицами данных.

Первый элемент DataGridView отображает таблицу Types типов продуктов. Второй элемент использует источник данных первого элемента и показывает временную таблицу выборки продуктов определенного типа. Третий элемент выводит всю таблицу продуктов Products.

В свою очередь объекты BindingSource, получают данные из базы посредством одного экземпляра DataSet. Заполнение DataSet возможно двумя способами.

void BindDataSource(DataSet ds, DataGridView dgvParent, DataGridView dgvDetail, DataGridView dgvChild)
{
    // === Создание функционального интерфейса элемента DataGridView ===

    // Источники данных для DataGridView.
    BindingSource parent = new();
    BindingSource detail = new();
    BindingSource child = new();

    // Редактируемая таблица типов, родительская для таблицы Product.
    parent.DataSource = ds;
    parent.DataMember = Constants.ParentTableName;
    // Заголовок с названием текущей таблицы.
    groupBoxParent.Text = "Таблица """ + Constants.ParentTableName + """";

    // Таблица просмотра продуктов выбранного типа.
    detail.DataSource = parent;
    // Отображаем таблицу в соответствии с выбранным внешним ключом.
    detail.DataMember = "FK_Product_Type";
    groupBoxDetail.Text = "Продукты выбранного типа";

    // Редактируемая таблица Product
    child.DataSource = ds;
    child.DataMember = Constants.ChildTableName;

    // Связывание источников данных с элементами управления
    // для отображения соответствующих таблиц.
    dgvParent.DataSource = parent;
    dgvDetail.DataSource = detail;
    dgvChild.DataSource = child;

    // Видимость столбцов в завизимости от конфигурации решения.
    // Невидимость столбца идентификаторов.
    // Для удобства созданы порядковые номера в заголовках строк.
    // Идентификаторы внешних ключей скрываются,
    // остаются только названия типов.
#if DEBUG == false
    dgvParent.Columns["Id"].Visible = false;
    dgvDetail.Columns["Id"].Visible = false;
    dgvChild.Columns["Id"].Visible = false;

     dgvDetail.Columns["TypeId"].Visible = false;
     dgvChild.Columns["TypeId"].Visible = false;
#endif

    //===Таблица выбранного типа  ===

    // Вставка столбца НазваниеТипа в таблицу выбранного типа
    DataGridViewComboBoxColumn colbox2 = new();
    colbox2.Name = "TypeName";

    // Таким кодом активизируется связь между родительской таблицей и
    // дочерней для отображения в элементах DataGridView.
    colbox2.DataSource = parent;
    // Так связи не будет.
    // colbox.DataSource = ds.Tables["Type"];
    colbox2.DisplayMember = "Name";
    colbox2.ValueMember = "Id";
    colbox2.DataPropertyName = "TypeId";
    colbox2.DisplayIndex = 1;
    colbox2.FlatStyle = FlatStyle.Flat;

    // Если закомментировать строку ниже у ячеек DataGridViewComboBoxColumn
    // появится кнопка вызова списка.
    colbox2.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;

    dgvDetail.Columns.Add(colbox2);

    // ====

    // === Таблица Product ===

    // Отображение названия таблицы.
    groupBoxChild.Text = "Таблица """ + Constants.ChildTableName + """";

    // Добавление способа установки типа продукта по имени.
    DataGridViewComboBoxColumn colbox = new();
    colbox.Name = "TypeName";

    // Таким кодом активизируется связь между родительской таблицей и
    // дочерней в текущем DataGridView.
    colbox.DataSource = parent;
    colbox.DisplayMember = "Name";
    colbox.ValueMember = "Id";
    colbox.DataPropertyName = "TypeId";
    colbox.DisplayIndex = 1;
    colbox.FlatStyle = FlatStyle.Flat;
    colbox.SortMode = DataGridViewColumnSortMode.Automatic;

    // Если закомментировать строку ниже у ячеек DataGridViewComboBoxColumn
    // появится кнопка вызова списка.
    colbox.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;

    dgvChild.Columns.Add(colbox);

    // ===
}

Наполнение DataSet данными

В приложении есть два варианта получения данных из базы в объект DataSet. Оба способа работают одинаково, но второй позволяет немного упростить программный код.

Первый способ, классический, DataSet заполняется методом SqlDataAdapter.Fill(...). Таким способом объект DataSet не получает из базы свойства IDENTITY (AUTO_INCREMENT) первичных ключей, названия таблиц, значения NOT NULL и другое. Для соответствия имен таблиц исходным в базе данных дополнительно необходима карта сопоставления названий таблиц.

Второй способ основан на получение данных через SqlDataReader в виде списка таблиц. В этом случае таблицы получают не только данные, но и основную информацию о своих схемах, кроме отношений и ограничений Foreign Key. Возможно в будущем более полная информация будет передаваться в таблицы: современные базы данных выдают любые схемо-сведения .

public static DataSet FillDataAdapter(string querystring, List? parameters = null)
{
    using SqlConnection connection = new(ConnectionString);
    SqlDataAdapter adapter = GetAdapter(connection, querystring, parameters);

    DataSet dataSet = new("Catalog");

    // Карта сопоставления названий таблиц набора DataSet и базы данных.
    adapter.TableMappings.Add("Table", Constants.ParentTableName);
    adapter.TableMappings.Add("Table1", Constants.ChildTableName);
    adapter.Fill(dataSet);

    return dataSet;
}


public static DataSet FillDataReader(string querytext, List? parameters = null)
{
    using SqlConnection connection = new(ConnectionString); // C# 9.0
    using SqlCommand command = new(querytext, connection); // C# 9.0

    if (parameters != null)
    {
        foreach (SqlParameter p in parameters)
        {
            command.Parameters.Add(p);
        }
    }

    connection.Open();

    List tables = new();

    // Получаем многие данные таблиц, в т.ч. и название таблиц автоматически.
    // Получаем: свойства автоинкремент первичного ключа, свойства столбцов 
    // NOT NULL и др.
    SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

    while (reader.IsClosed == false)
    {
        DataTable table = new();
        table.Load(reader);

        tables.Add(table);
    }

    DataSet dataSet = new DataSet("Catalog");

    // Добавление таблиц в коллекцию набора.
    foreach (DataTable dt in tables)
    {
        dataSet.Tables.Add(dt);
    }

    return dataSet;
}


public static void SaveData(DataTable dt, string querystring, List? parameters = null)
{
    using SqlConnection connection = new(ConnectionString);
    SqlDataAdapter adapter = GetAdapter(connection, querystring, parameters);

    // Для обновления передаётся таблица.
    // adapter.Update обновляет базу данных по таблично.
    // Чтобы не передавать в данный метод два параметра:
    // DataSet и название таблицы, передаётся один параметр DataTable
    // с названием таблицы внутри.
    int i = adapter.Update(dt);
    // Звук успешного обновления - для режима тестирования.
#if DEBUG
    if (i > 0) Console.Beep(3000, 100);
#endif
}

// Метод формирует разный код в зависимости от
// способа получения данных.
void SetRelationConstraint(DataSet ds, bool isdataadapter)
{
    DataTable parentTable = ds.Tables[Constants.ParentTableName];
    DataTable childTable = ds.Tables[Constants.ChildTableName];

    // Определение отношений между таблицами.
    // В базе данных эти отношения не определены.
    DataColumn parentColumn = parentTable.Columns["Id"];
    DataColumn childColumn = childTable.Columns["TypeId"];
    . . .

    // Код необходим только при заполнении DataSet с помощью SqlDataAdapter.
    // Если заполнение DataSet построить на DataTable этот код не требуется.
    if (isdataadapter == true)
    {
        // Установка свойства автонумерации идентификаторов.
        DataColumn columnId = parentColumn;
        columnId.AutoIncrement = true;
        columnId.ReadOnly = true;
        DataColumn columnId2 = childTable.Columns["Id"];
        columnId2.AutoIncrement = true;
        columnId2.ReadOnly = true;

        // Вручную устанавливаем запрет NULL.
        // Если заполнение DataSet построить на DataTable эта строка не потребуется.
        parentTable.Columns["Name"].AllowDBNull = false;
        childTable.Columns["SKU"].AllowDBNull = false;
    }
}

Вставка новой строки

Метод события DataGridView.RowEnter() в приложении начинает процедуру создания новой строки. Данное событие генерируется в момент получения фокуса любой строкой элемента управления DataGridView. Строки в приложении могут добавляться только по одной штуке.

При получении фокуса самой нижней строкой, предназначенной для формирования новой строки, в ячейку идентификатора записывается значение, которое гарантированно не совпадает с любым значением первичного ключа. Далее в необходимые ячейки вводятся значения.

После окончания редактирования новой строки перевод фокуса на любую другую строку вызывает событие DataGridView.RowValidated(), новая строка помечается как AddedRow и далее записывается в базу данных.

Одновременно с записью новой строки из базы данных запрашивается ее идентификатор, которым перезаписывается временное значение первичного ключа. Это очень важное мероприятие: без синхронизации первичных ключей таблиц элементов DataGridView и таблиц базы данных возможно нежелательное удаление или перезаписывание важной информации.

private void RowEnter(object sender, DataGridViewCellEventArgs e)
{
    // === Программный код участия в создании новой строки.

    DataGridView dgv = (DataGridView)sender;

    // Пока новая строка не добавлена в таблицу источника данных,
    // её неизвестен идентификатор.
    if (dgv.Rows[e.RowIndex].IsNewRow == true)
    {
        dgv.Rows[e.RowIndex].Cells["Id"].Value = "-1";
    }
}

private void RowValidated(object sender, DataGridViewCellEventArgs e)
{
    // Запись изменений в базу данных. Запросы UPDATE, INSERT,
    // исключая DELETE.

    DataGridView dgv = (DataGridView)sender;
    BindingSource bs = (BindingSource)dgv.DataSource;
    // Получение ссылки на редактируемую часть источника данных. 
    DataSet ds = (DataSet)bs.DataSource;
    string tablename = bs.DataMember;
    // Сохранение в базу данных изменений и вставок строк.
    SaveModifications(ds, tablename);

    // Погашение текста и иконки ошибок связанных с ячейками.
    foreach (DataGridViewCell cell in dgv.Rows[e.RowIndex].Cells)
    {
        cell.ErrorText = null;
    }
}

// Логика записи UPDATE, INSERT в базу данных.
void SaveModifications(DataSet ds, string tablename)
{
    DataTable? dt = ds.Tables[tablename];

    if (dt != null && ds.HasChanges() == true)
    {
        if (ds.HasChanges(DataRowState.Added) == true) InsertRow(dt);
        if (ds.HasChanges(DataRowState.Modified) == true) UpdateRow(dt);
    }
}

// Метод записи SQL запроса INSERT в базу данных.
void InsertRow(DataTable dt)
{
    string tablename = dt.TableName;
    // Получаем ссылку на новую строку.
    DataRow? row = dt.AsEnumerable().FirstOrDefault(r => r.RowState == DataRowState.Added);
    if (row == null) return;

    List parameters = new();
    List listnames = new();
    List listvalues = new();

    // Формирование списков имен и значений столбцов.
    int count = 0;
    foreach (DataColumn col in row.Table.Columns)
    {
        // В столбец со свойством идентификатора IDENTITY 
        //  значения записывать нельзя.
        if (col.AutoIncrement == false)
        {
            listnames.Add("[" + col.ColumnName + "]");

            SqlParameter parUri = new()
            {
                // Конвертирует в необходимый тип автоматически,
                // без явного указания типа.
                //DbType = ,
                Direction = ParameterDirection.Input,
                ParameterName = "Param" + count,
                Value = row[col.ColumnName]
            };
            parameters.Add(parUri);
            count++;

            listvalues.Add("@" + parUri.ParameterName);
        }
    }

    string names = string.Join(',', listnames);
    string values = string.Join(',', listvalues);

    // Ожидаемый идентификатор вставленной строки.
    SqlParameter lastId = new()
    {
        Direction = ParameterDirection.Output,
        ParameterName = "Identity",
        Size = 8
    };
    parameters.Add(lastId);

    // Одновременно со вставкой строки получаем ее идентификатор
    // с целью синхронизации ключей элемента DataGridView и базы данных.
    string queryString = "INSERT INTO " + tablename + " (" + names + ") VALUES(" + values + ");";
   
    // Функция IDENT_CURRENT( 'table_or_view' ) возвращает
    // последнее значение идентификатора для указанной таблицы.
    queryString += "SET @Identity = IDENT_CURRENT('" + tablename + "');";
    //queryString += "SELECT @Identity=IDENT_CURRENT ('Product');";
    ConnectDatabase.SaveData(dt, queryString, parameters);

    // Идентификатор новой строки получает актуальное значение.
    dt.Columns["Id"]!.ReadOnly = false;
    row["Id"] = lastId.Value;
    dt.Columns["Id"]!.ReadOnly = true;

    // Принятие изменений после присвоения настоящего идентификатора строки.
    // иначе логика SqlDataAdapter.Update() будет думать что это модификация строки и 
    // без необходимости перезапишет эту строку в базе данных.
    row.AcceptChanges();
}

Редактирование строки

Логическая процедура похожа на последовательность вставки строки: получение фокуса строки - событие RowEnter(), редактирование ячеек, перевод фокуса - событие RowValidated().

Редактирование строк заключается в изменении значений заполненных ячеек или добавлении значений в пустые ячейки таблиц элемента управления DataGridView.

После окончания редактирования строки переводом фокуса вызывается событие DataGridView.RowValidated(), отредактированная строка помечается как ModifiedRow и сохраняется в базу данных.

void UpdateRow(DataTable dt)
{
    string tablename = dt.TableName;
    // Получение ссылки на изменённую строку.
    DataRow? row = dt.AsEnumerable().FirstOrDefault(r => r.RowState == DataRowState.Modified);
    if (row == null) return;

    List parameters = new();
    List listnamesvalues = new();
    object id = row["Id"];
    int count = 0;
    foreach (DataColumn col in row.Table.Columns)
    {
        string columnname = col.ColumnName;
        object value = row[col.ColumnName];
        // Идентификатор нельзя перезаписывать.
        if (col.AutoIncrement == false)
        {
            SqlParameter parUri = new()
            {
                // Конвертирует в необходимый тип автоматически,
                // без явного указания типа.
                //DbType = ,
                Direction = ParameterDirection.Input,
                ParameterName = "Param" + count,
                Value = row[col.ColumnName]
            };
            parameters.Add(parUri);
            count++;

            // Полуфабрикат [имя]=значение,... для строки запроса.
            listnamesvalues.Add("[" + col.ColumnName + "]=@" + parUri.ParameterName);
        }
    }

    // Идентификатор модифицированной строки 
    // передаётся посредством параметра.
    SqlParameter Id = new()
    {
        Direction = ParameterDirection.Input,
        ParameterName = "Identity",
        Value = id
    };
    parameters.Add(Id);

    string namesvalues = string.Join(',', listnamesvalues);
    string queryString = "UPDATE " + tablename + " SET " + namesvalues + " WHERE Id=@Identity";
    ConnectDatabase.SaveData(dt, queryString, parameters);
}

Удаление строк

Чтобы удалить строки из базы, необходимо их выделить и нажать клавишу DELETE. Программная последовательность удаления строк включает три события:

DataGridView.KeyDown() – обеспечивает диалоговый интерфейс подтверждения или отмены процедуры удаления строк.

DataGridView.UserDeletingRow() – программная логика отмены процедуры удаления строк.

DataGridView.RowsRemoved() – удаляемые строки уже помечены как DeletedRow и передаются в метод удаления из базы данных.

private void DGVKeyDown(object sender, KeyEventArgs e)
{
    // === Интерфейс диалога удаления и отмены удаления строк ===

    DataGridView dgv = (DataGridView)sender;
    BindingSource bs = (BindingSource)dgv.DataSource;
    DataSet ds = (DataSet)bs.DataSource;
    string tablename = bs.DataMember;

    if (e.KeyCode == Keys.Delete)
    {
        if (ds != null)
        {
            int numberrowsdelete = dgv.SelectedRows.Count;
            DataTable? dt = ds.Tables[tablename];

            if (dt != null)
            {
                // Внешний флаг удаления строк для переноса решения в другой метод.
                // По умолчанию запрещаем удаление строк.
                _deleteRows = false;

                if (MessageBox.Show(
                    "Удалить строки?
Количество - " + numberrowsdelete,
                    "Удаление строк!",
                    MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
                {
                    // Удаление разрешено.
                    _deleteRows = true;
                }
            }
        }
    }
    . . .
}

private void UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
    // === Логика отмены удаления строк ===
    // true - отмена действия, false - продолжения действия.
    e.Cancel = !_deleteRows;
}

private void RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
{
    // === Непосредственное удаление строк ===

    DataGridView dgv = (DataGridView)sender;
    BindingSource bs = (BindingSource)dgv.DataSource;
    DataSet ds = (DataSet)bs.DataSource;
    string tablename = bs.DataMember;
    DataTable? dt = ds.Tables[tablename];

    if (dt != null)
    {
        DeleteRows(dt);
    }
}

void DeleteRows(DataTable dt)
{
    string tablename = dt.TableName;

    // Получение строк помеченных для удаления.
    DataRow? row = dt.AsEnumerable().FirstOrDefault(r => r.RowState == DataRowState.Deleted);
    if (row != null)
    {
        // Получение идентификатора удаленной строки.
        string? id = row[0, DataRowVersion.Original].ToString();
        if (id == null) return;

        // Удаляем строку из базы по её идентификатору.
        string queryString = "DELETE FROM " + tablename + " WHERE Id=" + id + ";";
        ConnectDatabase.SaveData(dt, queryString);
    }
}

Порядковые номера строк

Первичные ключи таблиц базы данных имеют свойство IDENTITY (для MySQL AUTO_INCREMENT) и равными шагами увеличиваются после вставки новой строки. Но равномерность нарушается при удалении строк и для пользователей не очень логично выглядят такие значения.

Для комфорта пользователей в строковых заголовочных ячейках вставляются порядковые номера строк, а столбцы с идентификаторами скрываются. Обновление порядковых номеров происходит после подключения источника данных и при изменениях его содержимого.

В таблице элемента DataGridView, связанным с источником данных, изменить значения в заголовках строк можно только после завершения привязки. Поэтому вычисление и вставка номеров происходит в событии DataGridView.DataBindingComplete().

private void DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
    // Это событие возникает при подключении источника данных и
    // при изменении его содержимого.
    // При элементе DataGridView связанным с источником данных,
    // изменить значения в заголовках строк можно 
    // только после завершения привязки.

    DataGridView dgv = (DataGridView)sender;

    foreach (DataGridViewRow row in dgv.Rows)
    {
        // Порядковый номер не ставится в новой строке, ещё не добавленной в источник данных.
        if (row.IsNewRow == false)
            row.HeaderCell.Value = (row.Index + 1).ToString();
    }
}

Оповещение о несохраненных данных

При изменениях в ячейках редактируемой строки таблиц возникает событие DataGridView.CellValueChanged(). В теле метода данного события отредактированные, но не сохраненные ячейки помечаются иконкой с выплывающей подсказкой. Такое состояние может возникать в случаях редактирования пользователем нескольких ячеек одной строки, без потери фокуса строкой. Как только редактируемая строка потеряет фокус, несохранённые значения запишутся в базу данных (см. выше), а иконки оповещения исчезнут.

private void CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    // Заголовки строк не редактируются.
    if (e.ColumnIndex >= 0)
    {
        DataGridView dgv = (DataGridView)sender;

        // Не предназначено для новых строк (которые внизу) элемента DataGridView.
        if (dgv.Rows[e.RowIndex].IsNewRow == false)
        {
            // Оповещение пользователя о несохраненных значениях в ячейках.
            dgv.Rows[e.RowIndex].
                Cells[e.ColumnIndex].ErrorText = "Есть несохраненные изменения!";
        }
    }
}

private void RowValidated(object sender, DataGridViewCellEventArgs e)
{
    . . .

    // Погашение текста и иконки ошибок связанных с ячейками.
    foreach (DataGridViewCell cell in dgv.Rows[e.RowIndex].Cells)
    {
        cell.ErrorText = null;
    }
}

Оповещение об ошибках

Ошибки, возникающие в процессе редактирования таблиц DataGridView предупреждаются событием DataGridView.DataError(). В методе размещается простейший код диалогового оповещения для выбора дальнейших действий пользователем. Событие подключено ко всем редактируемым элементам DataGridView, вычисления владельца происходит по параметру sender.

private void DataError(object sender, DataGridViewDataErrorEventArgs e)
{
    DataGridView dgv = (DataGridView)sender;
    BindingSource bs = (BindingSource)dgv.DataSource;
    string tablename = bs.DataMember;

    // Если нажать кнопку Ок ошибочное значение можно изменить,
    // нажатие на кнопку Отмена(Cancel) возвращает предыдущее
    // корректное значение.
    if (MessageBox.Show("Таблица " + tablename + "
Ошибка: ячейка " +
        e.RowIndex + "x" + e.ColumnIndex + "
" +
        e.Exception.Message, "Внимание", 
		MessageBoxButtons.OKCancel, 
		MessageBoxIcon.Warning) == DialogResult.OK)
    {
        e.Cancel = true;
    }
}

Исходник приложения управления базой данных

Исходник написан на языке C#, среда программирования MS Visual Studio 2022, .NET 6. В решении приложения находится скрипт для создания базы данных. Скрипт можно также скачать на странице Relations Tables в DataSet.