Под управлением базой данных в приложении понимается редактирование, вставка и удаления строк. Прилагаемое приложение позволяет редактировать содержимое базы данных в режиме "Что вижу, то и получаю" (технология WYSIWYG).
Исполняющие методы-события унифицированы для редактируемых визуальных элементов DataGridView. Процедура вставки новой строки и редактирование существующей включает практически одинаковые действия. Процесс удаления строк имеет логику отмены действия. Источники данных для элементов DataGridView - это объекты типа BindingSource, которые взаимодействуют с базой данных посредством одного экземпляра DataSet.
Программный код приложения управления базой данных содержит немного добавочных строк, основная логика построена на уже заложенной в DataGridView функциональности. В приложении программно реализованы отношения между таблицами и ограничения Foreign Key, Unique.
Интерфейс приложения построен на трёх элементах 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 заполняется методом 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 и сохраняется в базу данных.
Метод записи изменённых и новых значений строки в базу данных, выполнение SQL запроса 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);
}
Первичные ключи таблиц базы данных имеют свойство 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