Приложение взаимодействия элемента визуального отображения таблиц DataGridView и связанного источника данных типа DataTable. Приложение позволяет работать с выбранной таблицей SQL базы данных, осуществлять создание и удаление строк, редактирование ячеек с записью в базу данных.
Термин связанный источник подразумевает, что изменения значений ячеек, добавление и удаление строк элемента DataGridView синхронно передаётся объекту DataTable. И наоборот, изменения в связанном DataTable тут же отображаются в таблице DataGridView.
Приложение имеет два элемента визуализации данных типа DataGridView:
- dgvDatabase для отображения списка таблиц базы данных;
- dgvTable для редактирования выбранной таблицы базы.
База данных в составе приложения
Исследуемая база данных содержит 3 различных таблицы. Для расширенного эксперимента в таблицах создано многообразие типов данных: int, float, bit, nvarchar, date. Столбцы имеют различные свойства инициализации: NULL, NOT NULL и значения по умолчанию.
Файл базы данных прикреплен к исходнику приложения. Подключение базы данных к SQL серверу можно осуществить с помощью среды программирования Visual Studio, как подключиться подробно описано на странице создание, подключение и отсоединение баз данных в Visual Studio. Подключить базу так же можно и в среде администрирования баз данных SQL Server Management Studio.
Инициализация приложения
Инициализация приложения и интерфейс обращения к базе данных практически аналогичен описанному в статье DataGridView - просмотр баз данных LocalDB. Во время инициализации приложения происходит получение списка таблиц базы с помощью представления информационной схемы INFORMATION_SCHEMA.TABLES. После запроса метаданные возвращаются в виде строк для каждой отдельной таблицы базы и отображаются в элементе dgvDatabase.
В списке таблиц ключевым является столбец с названием TABLE_NAME. При выборе пользователем любой ячейки элемента dgvDatabase считывается название таблицы из строки и данные загружаются в элемент DataGridView dgvTable для последующего редактирования. Программный код инициализации заключен в оболочку метода Init(), который вызывается в конструкторе формы.
У элемента DataGridView внизу таблицы отображается строка, помеченная слева звездочкой. Это строка для добавления новых записей. Когда пользователь щелкает на эту строку, в элемент управления добавляется новая строка DataGridView со значениями по умолчанию. Когда пользователь нажимает клавишу ESC, новая строка исчезает. Это встроенная в функционал DataGridView WYSIWYG визуализация создания новой строки. Но для того, чтобы строка реально записалась в базу данных, необходим дополнительный программный код.
В прикрепленном приложении новая строка вставляется в базу данных после редактирования любой ячейки и перевода фокуса на другие строки. При редактировании ячейки новая строка появляется в связанном источнике данных, в нашем случае в объекте DataTable. В событии DataGridView.RowValidated, возникающем при потере фокуса можно отследить создание новой строки посредством свойства DataRowState.Added связанного источника. Если проверка истинна, то значения строки записываются в базу данных. Для валидации заполнения новой строки предназначено событие DataGridView.RowValidating.
Вставка значений новой строки в событии DataGridView.RowValidated, возникающим после окончания валидации:
При создании новой строки требуются значения по умолчанию, и очень желательна их идентичность значениям в базе данных. Хотя DataColumn в составе DataTable имеет свойство DataColumn.DefaultValue, оно не заполняется при SQL запросах. Запросить из базы данных реальные значения по умолчанию столбцов не получится. Это связано с тем, что значения по умолчанию могут содержать не только величины, но и инициализирующие функции, например: GetDate(), NEW_ID(), которые определяют значения столбцов непосредственно во время вставки строки. Если запросить значения по умолчанию с помощью схемы INFORMATION_SCHEMA.COLUMNS, то результат вернется в виде строк вида "getdate()", "new_id()", "((0))", ("2022-01-20") и так далее.
Но получить значения столбцов по умолчанию все же можно. Необходимую процедуру можно осуществить различными способами:
- создать полную пустую временную копию таблицы, вставить в нее строку, считать значения, удалить таблицу.
- вставка строки в текущую таблицу во время транзакции, после считывания значений, откат транзакции.
Каждый из способов имеет свои достоинства и недоставки, но оба они позволяют получать реальные значения по умолчанию для столбцов с помощью самого механизма базы данных.
Для описываемого приложения написан метод GetDefaultValues(...) получения значений по умолчанию путем фиктивной вставки строки. Полученные default значения присваиваются соответствующим столбцам связанного источника данных DataTable. Ввод данных по умолчанию непосредственно в связанный источник освобождает от необходимости повторяющейся обработки события DefaultValuesNeeded.
Получение и присваивание default значений происходит в событии смены источника данных:
Фиктивная вставка строки в базу с пустыми значениями (не являющимися NULL) столбцов, исключая столбцы, у которых обнаружено значение по умолчанию;
Полученные default значения последней вставленной строки присваиваются одноименным столбцам связанного источника.
После вышеописанной процедуры, при вставке новой строки в DataGridView, в соответствующих столбцах появляются значения по умолчанию идентичные предусмотренным в базе данных. К сведению. Откат транзакции не возвращает назад значение IDENTITY (AUTO_INCREMENT) первичного ключа. Это прерогатива исключительно движка базы данных.
Программный код получения значений по умолчанию из базы данных:
public void GetDefaultValues(DataTable dtSource)
{
// Запрос информации о значениях по умолчанию.
string sq = "SELECT COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + dtSource.TableName + "' AND COLUMNPROPERTY(object_id('" + dtSource.TableName + "'), COLUMN_NAME, 'IsIdentity')=0;";
DataTable dtSchema = ConnectDB.SqlQuery(sq);
int count = 0;
List parameters = new();
List listnames = new();
List listvalues = new();
foreach (DataRow r in dtSchema.Rows)
{
if (r["COLUMN_DEFAULT"] == DBNull.Value)
{
listnames.Add("[" + r["COLUMN_NAME"] + "]");
SqlParameter parUri = new()
{
// Конвертирует в необходимый тип автоматически,
// без явного указания типа.
// DbType = ConvertToDBType.Convert(dataTable.Columns[colindex].DataType),
Direction = ParameterDirection.Input,
ParameterName = "Param" + count,
// Вставка пустого значения,
// разрешено вставлять даже если столбец NOT NULL.
Value = ""
};
parameters.Add(parUri);
listvalues.Add("@" + parUri.ParameterName);
count++;
}
}
string names = string.Join(',', listnames);
string values = string.Join(',', listvalues);
// =====
// Алгоритм 1
// Создать транзакцию
// Вставить строку в таблицу
// Прочитать значения по умолчанию
// Откатить транзакцию.
string sqlquery = "BEGIN TRANSACTION;";
sqlquery += "INSERT INTO " + dtSource.TableName + " (" + names + ") VALUES(" + values + ");";
string lastid = "SELECT IDENT_CURRENT('" + dtSource.TableName + "')";
sqlquery += "SELECT * FROM " + dtSource.TableName + " WHERE id=(" + lastid + ");";
sqlquery += "ROLLBACK;";
DataTable tableDefault = ConnectDB.SqlQuery(sqlquery, parameters);
// Добавляем данные по умолчанию непосредственно в связанный источник данных.
foreach (DataRow r in dtSchema.Rows)
{
foreach (DataColumn col in dtSource.Columns)
{
if ((string)r["COLUMN_NAME"] == col.ColumnName &&
r["COLUMN_DEFAULT"] != DBNull.Value)
{
object o = tableDefault.Rows[0][col.ColumnName];
col.DefaultValue = o;
}
}
}
}
Редактирование ячеек DataGridView
В загруженных строках dgvTable можно редактировать любую ячейку, кроме идентификатора. В приложении отслеживаются только изменения значений ячеек, далее отредактированное значение записывается в базу данных при потере фокуса ячейкой или после нажатия клавиши Enter.
Изменение значений своих ячеек элемент DataGridView сопровождает событием CellValueChanged. Редактируются ячейки, только существующие в базе, новые ячейки добавляются в базу данных во время вставки новой строки.
private void DgvTable_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
DataTable dtSource = (DataTable)dgvTable.DataSource;
// Обновляется запись в базе данных только после изменения.
// Редактируется ячейка строки, которая присутствует в связанном источнике DataTable, т.е.
// данная запись уже есть в базе данных.
// Новые значения вставляются после валидации новой строки.
if (dtSource.Rows.Count - 1 >= e.RowIndex &&
dtSource.Rows[e.RowIndex].RowState == DataRowState.Unchanged)
{
Update(dtSource, e.RowIndex, e.ColumnIndex);
}
}
Валидация вводимых значений
Движок DataGridView имеет встроенную функциональность проверки соответствия типа вводимого значения типу ячейки. При связанном источнике данных ячейки наследуют типы соответствующих столбцов DataTable.
Валидация введенного в ячейку значения происходит в событии DataGridView.CellValidating при потере фокуса ячейкой. В данном событии может находиться программный код оповещения пользователя о недопустимости ввода текущего значения. Если проигнорировать некорректность в этом событии, возникнет событие DataGridView.DataError, которое вернет первоначальное корректное значение.
Сочетание этих двух событий с диалоговыми окнами позволяют создать визуальную логику исправления ошибочного ввода. После успешной валидации значение ячейки можно записать в SQL базу данных.
Программный алгоритм валидации вводимых значений с диалоговым оповещением пользователя:
private void DgvTable_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
// Не очень удобно валидировать новую строку.
if (dgvTable.Rows[e.RowIndex].IsNewRow == true)
{
return;
}
DataTable dtSource = (DataTable)dgvTable.DataSource;
// Получаем тип значений редактируемого столбца.
Type type = dtSource.Columns[e.ColumnIndex].DataType;
// Непосредственно валидация введенного значения.
string? error = e.FormattedValue.ToString() switch
{
{ Length: 0 } when dtSource.Columns[e.ColumnIndex].AllowDBNull == false => "Ячейка не может быть пустой!",
{ Length: > 0 } when TryConvert(e.FormattedValue, type) == false => "Ячейка для значения " + type.Name + "!",
_ => null
};
// Если обнаружена некорректность ввода, оповещаем
// пользователя диалоговым окном.
if (error != null)
{
string action = "
Выйти - Да
Продолжить редактирование - Нет";
// Дополнительно выводим ошибку в текстовой метке.
string errormsg = "Ошибка! Строка " + (e.RowIndex + 1) + ", столбец " + (e.ColumnIndex + 1) + ": " + error;
dgvTable.Rows[e.RowIndex].ErrorText = errormsg;
labelErrorMsg.Text = errormsg;
// Если пользователь решил продолжить редактирование остаемся в ячейке.
if (MessageBox.Show(error + "
" + action, "Некорректные данные!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No)
{
e.Cancel = true;
}
else
{
// Если пользователь отменил ввод.
// Отменяем оповещения об ошибках.
dgvTable.Rows[e.RowIndex].ErrorText = "";
labelErrorMsg.Text = "";
// Отменяем все некорректные изменения, возврат к предыдущим значениям.
dgvTable.CancelEdit();
}
}
}
Определение типа вводимого значения
Вводимое в ячейку значение отслеживается в событии CellValidating(object sender, DataGridViewCellValidatingEventArgs e), где свойство e.FormattedValue содержит введенное пользователем значение. e.FormattedValue имеет тип object и поэтому неизвестен конкретный тип введенного значения. Например, e.FormattedValue содержит 5544 и нет сведений строка это или число. Проверка заключается в попытке конвертировать значение пользователя в тип, который должен содержать столбец.
Тип значения ячейки получаем из свойств столбцов связанного источника данных DataTable. Проверку соответствия типа производит метод приложения TryConvert(...). При успешной конвертации метод возвращает true, в противном случае false.
Программный код попытки конвертирования в заказанный тип. Применение метода показано в валидация вводимых значений.
public static bool TryConvert(object unknown, Type type)
{
try
{
object d = Convert.ChangeType(unknown, type);
}
catch (System.FormatException)
{
return false;
}
return true;
}
Удаление строк
В приложении можно удалять одну и более выделенных строк элемента dgvTable. Для процедуры удаления используется событие DataGridView.KeyDown с отслеживанием нажатия клавиши DELETE. Используя идентификаторы строк, осуществляется SQL запрос удаления выбранных записей.
Перед удалением открывается диалоговое окно, где запрашивается подтверждение либо отмена пользователем данного действия.
private void DgvTable_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Delete)
{
int numberrowsdelete = dgvTable.SelectedRows.Count;
DataTable dt = (DataTable)dgvTable.DataSource;
if (MessageBox.Show(
"Удалить строки?
Количество - " + numberrowsdelete,
"Удаление строк!",
MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
{
string? queryString = null;
foreach (DataGridViewRow row in dgvTable.SelectedRows)
{
queryString += "DELETE FROM " + dt.TableName + " WHERE Id=" + row.Cells["Id"].Value.ToString() + ";";
}
if (queryString != null)
ConnectDB.SqlQuery(queryString);
}
}
}
Метод вставки новой строки в базу данных
При визуальной вставке новой строки в элемент DataGridView автоматически создается новая строка в связанном источнике DataTable, но реально в базу данных ничего не записывается. Для вставки новой строки непосредственно в базу данных требуется дополнительный программный код.
В приложении за запись новых строк отвечает метод главной формы Insert(DataTable dtSource, DataRow row), который в качестве параметра получает новую строку. Данные новой строки записываются в SQL базу данных.
Программный код метода вставки строки в базу и синхронизации идентификаторов:
void Insert(DataTable dtSource, DataRow row)
{
List parameters = new();
List listnames = new();
List listvalues = new();
// Формирование имен и значений столбцов.
int count = 0;
foreach (DataColumn col in row.Table.Columns)
{
// данные вставляем в столбцы, кроме первичного идентификатора.
if (col.AutoIncrement == false)
{
listnames.Add("[" + col.ColumnName + "]");
SqlParameter parUri = new()
{
// Конвертирует в необходимый тип автоматически,
// без явного указания типа.
//DbType = ConvertToDBType.Convert(dataTable.Columns[colindex].DataType),
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);
// Фиксируем все добавления строк в источник данных DataTable.
row.AcceptChanges();
// SQL запрос на вставку, одновременно запрос на последний номер идентификатора для синхронизации Id.
// Иначе видимые в DataGridView идентификаторы могут не совпадать с идентификаторами записей в базе.
string querystring = "INSERT INTO " + row.Table.TableName + " (" + names + ") VALUES(" + values + ");";
querystring += "SELECT IDENT_CURRENT('" + row.Table.TableName + "');";
DataTable lastId = ConnectDB.SqlQuery(querystring, parameters);
// Синхронизируем первичные идентификаторы в базе данных и в DataGridView.
// Это гарантирует совпадение идентификаторов в виртуальной таблице
// с первоисточником в базе данных.
// Записываем настоящий идентификатор строки,
// полученный из базы данных.
dtSource.Columns["Id"]!.ReadOnly = false;
row["Id"] = lastId.Rows[0][0];
dtSource.Columns["Id"]!.ReadOnly = true;
}
Синхронизация первичных ключей базы данных и DataGridView
В случае с автоматическим приращением (IDENTITY (AUTO_INCREMENT)), при вставке новых строк не обеспечивается идентичность первичных идентификаторов записей базы данных и строк элемента управления DataGridView, что может вызвать нежелательные последствия.
При создании новой строки, в событии dgvTable.RowEnter, ячейке идентификатора элемента dgvTable временно присваивается значение -1. Метод Insert(DataTable dtSource, DataRow row) написан так, что одновременно со вставкой строки запрашивается последний вставленный идентификатор. Далее данный идентификатор присваивается ячейке идентификатора новой строки связанного источника, таким образом обеспечивается идентичность первичных ключей.
Программный код выделения новой строки отличающимся цветом и временным идентификатором:
private void DgvTable_RowEnter(object sender, DataGridViewCellEventArgs e)
{
if (dgvTable.Rows[e.RowIndex].IsNewRow == true)
{
// При фокусе на новой строке выделяем ее цветом.
dgvTable.Rows[e.RowIndex].DefaultCellStyle.BackColor = newRowColor;
// Первоначально идентификатор неизвестен для новой строки,
// после успешной вставки строки получаем идентификатор из базы.
// Если данный код удалить, то при многократном фокусировании и покидании
// фокуса новой строкой не на новую строку автоинкремент DataGridView увеличивает
// значение идентификатора и не сбрасывает его при возврате фокуса.
// В базе же данных свой отсчет автоинкремента не связанный с DataGridView.
dgvTable.Rows[e.RowIndex].Cells["Id"].Value = "-1";
}
}
Программный код синхронизации первичных ключей, полный код метода вставка новой строки:
void Insert(DataTable dtSource, DataRow row)
{
....
// SQL запросы на вставку и на последний номер идентификатора для синхронизации Id.
// Иначе видимые в DataGridView идентификаторы могут не совпадать с идентификаторами в базе.
string querystring = "INSERT INTO " + row.Table.TableName + " (" + names + ") VALUES(" + values + ");";
querystring += "SELECT IDENT_CURRENT('" + row.Table.TableName + "');";
DataTable lastId = ConnectDB.SqlQuery(querystring, parameters);
// Синхронизируем первичные идентификаторы в базе данных и источнике данных DataTable.
// Это гарантирует совпадение идентификаторов в DataGridView
// с первоисточником в базе данных.
// Записываем настоящий идентификатор строки,
// полученный из базы данных.
dtSource.Columns["Id"]!.ReadOnly = false;
row["Id"] = lastId.Rows[0][0];
dtSource.Columns["Id"]!.ReadOnly = true;
}
Вместо заключения
В данной статье описана работа в паре элемента DataGridView и объекта DataTable в качестве источника данных. Практический пример прикреплен к странице. DataTable предоставляет простой и удобный интерфейс для работы с таблицами баз данных.
Функциональность DataTable в паре с элементом управления DataGridView можно применять для редактирования таблиц источников данных. Простота работы с классом DataTable позволяет писать приложения обработки данных в кратчайшие сроки.
SQLDataGridViewDataTable-vs17.zip