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

Под управлением базой данных в приложении понимается редактирование, вставка и удаления строк. Прилагаемое приложение позволяет редактировать содержимое базы данных в режиме "Что вижу, то и получаю" (технология WYSIWYG).
Исполняющие методы-события унифицированы для редактируемых визуальных элементов DataGridView. Процедура вставки новой строки и редактирование существующей включает практически одинаковые действия. Процесс удаления строк имеет логику отмены действия. Источники данных для элементов DataGridView - это объекты типа BindingSource, которые взаимодействуют с базой данных посредством одного экземпляра DataSet.
Программный код приложения управления базой данных содержит немного добавочных строк, основная логика построена на уже заложенной в DataGridView функциональности. В приложении программно реализованы отношения между таблицами и ограничения Foreign Key, Unique.
Формирование интерфейса DataGridView
Интерфейс приложения построен на трёх элементах DataGridView. В качестве источника данных для элементов отображения таблиц в приложении применены объекты типа BindingSource. Использование класса BindingSource обеспечивает приложению взаимосвязанные манипуляции с таблицами данных.
Первый элемент DataGridView отображает таблицу Types
типов продуктов. Второй элемент использует источник данных первого элемента и показывает временную таблицу выборки продуктов определенного типа. Третий элемент выводит всю таблицу продуктов Products
.
В свою очередь объекты BindingSource, получают данные из базы посредством одного экземпляра DataSet. Заполнение DataSet возможно двумя способами.
Метод формирования интерфейса элементов отображения таблиц. В таблицы добавляются столбцы типа DataGridViewComboBoxColumn для отображения имени типа вместо числового идентификатора.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
и сохраняется в базу данных.
UPDATE
. Программный код методов событий смотрите выше.
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.
Скачать исходник
DataGridViewControlDB-vs17.zip
- Размер: 3232 Кбайт
- Загрузки: 316