Лекция № 14
Тема: "Обработка данных в отношении многие-ко-многим"
План
1. Отображения связанных таблиц в одном наборе данных
2. Обработка связанного набора данных с помощью запросов
3. Работа с таблицами в отношении «многие-ко-многим»
1. Отображения связанных таблиц в одном наборе данных
Для оптимизации скорости обработки и экономии места на диске, данные в БД «нормализуют», что приводит к появлению нескольких таблиц, связанных друг с другом отношениями разных типов: один-к-одному, один-ко-многим, многие-ко-многим. Такое разбиение никоим образом не должно усложнять доступ к информации пользователю. Он должен видеть на экране информацию в обычном виде. Т.е. необходимо каким-то образом «собрать» информацию из нескольких таблиц в одном наборе данных и вывести для пользователя.
Для решения поставленной задачи будем использовать запросы по нескольким таблицам. Такой запрос в общем виде имеет вид:
SELECT список полей FROM таблица1
INNER JOIN таблица2
ON таблица1.поле = таблица2.поле
[WHERE условие]
В списке полей перечисляются в нужном порядке поля из двух связываемых таблиц. Если в таблицах имеются поля одинаковыми именами, то их указывают так:
таблица.имя_поля
В разделе ON указывают, по каким полям две таблицы связаны между собой.
Если нужно связать больше таблиц в одном запросе, то запрос записывается так:
SELECT список полей FROM таблица1
INNER JOIN (таблица2 INNER join таблица3
ON таблица2.поле = таблица3.поле)
ON таблица1=таблица2
[WHERE условие]
Например. Пусть имеются в БД sotr имеет 3 таблицы:
Таблицы vyrabotka хранит информацию о количестве произведенной продукции каждым сотрудником по датам. Например:
Видно, что сотрудники с номерами 1, 2 и 3 в указанные даты производили определенное количество продукции. Однако, такое представление информации для пользователя не удобно, так как он не знает, какие именно сотрудники соответствуют номерам 1, 2, 3. Более целесообразно вывести на экран поля id_vyrab, data, kolvo из таблицы vyrabotka, и поля fam, imya, otch из таблицы sotr. Для этого воспользуемся запросом вида:
SELECT id_vyrab, data, kolvo, fam, imya, otch
FROM sotr INNER JOIN Vyrabotka
ON sotr.id_sotr =
Vyrabotka.id_sotr
Результат выполнения запроса представлен на рисунке:
Такая форма представления данных более информативна, так как показывает не номер сотрудника, а его ФИО. Но сам набор данных строится на основании запроса по нескольким таблицам.
Для создания таблицы на основе указанного запроса в Visual Studio на панели ""Источники данных" щелкните на кнопке "Изменить набор данных в конструкторе"". В результате откроется окно со структурой всех таблиц БД и связями между ними. Далее щелкните правой кнопкой на пустом месте в окне со структурами таблиц, выберите команду "Добавить - TableAdapter", введите текст запроса. В результате будет создана отдельная виртуальная таблица, которую можно подключить на форму как обычную. Для удобства работы с таблицей рекомендуется в ее свойстве Name задать имя, отличное от стандартного.
Например, на рисунке мы создали новый TableAdpater, указали текст запроса и переименовали таблицу в "SotrVyrab"
С помощью компонента DataGridView подключите эту таблицу на форму. В результате в одной сетке отобразятся колонки из разных таблиц:
2. Обработка связанного набора данных с помощью запросов
Связанный набор данных можно только просматривать, но нельзя изменять (добавлять, редактировать, удалять) записи теми способами, которые были рассмотрены в предыдущих лекциях, так как при изменении, набор данных "ничего не знает" о составляющих его таблицах и никаких изменений в них не записывает. Можно поменять значения полей, добавить или удалить записи, но при следующем запуске программы все изменения пропадут.
Для внесения изменений в связанный набор данных нужно использовать запросы:
- Insert - для добавления новой записи;
- Update - для обновления текущей записи;
- Delete - для удаления текущей записи.
Замечание. Не забывайте, что после выполнения любого из указанных запросов набор данных обязательно обязательно должен быть считан заново методом Fill(), чтобы информация в сетке корректно обновлялась.
Добавления данных в таблицу
Для добавления данных используется запрос вида:
insert into имя_таблицы (поле1, поле2, поле3, ...) values (знач1, знач2, знач3, ...)
В первых скобках перечисляются поля таблицы, которые будут заполнены. Во вторых скобках перечисляются значения, которые попадут в соответствующие поля таблицы. Обязательно порядок и тип значений должен совпадать с порядком полей таблицы из первых скобок. Поле-счетчик в первых скобках можно не указывать, так как оно заполняется автоматически.
Например:
//добавим в таблицу vyrabotka новую запись
//введем выработку за очередную дату для первого сотрудника
insert into vyrabotka (data, kolvo, id_sotr) values ('17.05.2019', 52, 1)
Для создания запроса на добавление для связанного набора данных SotrVyrab вызовите его контекстное меню и выполните команду "Добавить - Запрос". Выберите тип запроса "INSERT" и введите текст запроса:
insert into Vyrabotka (data, kolvo, id_sotr) values (?, ?, ?)
На следующем шаге укажите имя для метода. В результате внутри набора данных SotrVyrab будет создан метод с тремя параметрами, с помощью которых можно будет задать значения для полей новой записи.
Например, на форме имеются компоненты вида:
Дата задается с помощью DateTimePicker, количество - с помощью TextBox, сотрудник выбирается из ComboBox. Пусть метод для запроса INSERT имеет имя
AddVyrabotka(data, kolvo, id_sotr)
Для кнопки напишем код вызова метода добавления новой записи:
private void button1_Click(object sender, EventArgs e)
{
//вызываем метод и через параметры передаем в него дату (тип DateTime,
//количество (тип int) и номер сотрудника (тип int)
//для записи даты из DateTimePicker методом ToString выделяем только дату,
//а затем полученное значение конвертируем в DateTime
sotrVyrabTableAdapter.AddVyrabotka(Convert.ToDateTime(dateTimePicker1.Value.ToString("dd/MM/yyyy")),
Convert.ToInt32(textBox1.Text),
Convert.ToInt32(comboBox1.SelectedValue));
//обновляем связанный набор для отображения изменений в сетке
sotrVyrabTableAdapter.Fill(sotrDataSet.SotrVyrab);
}
Изменение данных в таблице
Для изменения данных используется запрос вида:
update имя_таблицы set поле1=знач1, поле2=знач2, поле3=знач3, ... where условие
Так как нужно обновлять только текущую запись, то условие where записывается только для ключевого поля-счетчика.
Например:
//обновим запись с номером 1 произвольными значениями
update vyrabotka set data='17.05.2019', kolvo=5, id_sotr=1 where id_vyrab=1
Для создания запроса на обновление для связанного набора данных SotrVyrab вызовите его контекстное меню и выполните команду "Добавить - Запрос". Выберите тип запроса "UPDATE" и введите текст запроса:
update Vyrabotka set data=?, kolvo=?, id_sotr=? where id_vyrab=?
На следующем шаге укажите имя для метода. В результате внутри набора данных SotrVyrab будет создан метод с четырьмя параметрами, с помощью которых можно будет задать новые значения для полей текущей записи и ее номер для условия обновления.
Например, на форме значение даты и количества будем задавать непосредственно в сетке dataGridView1, а нового сотрудника будем выбирать из созданного ранее списка comboBox1.
Пусть метод для запроса UPDATE имеет имя
UpdVyrabotka(data, kolvo, id_sotr, id_vyrab)
Для кнопки напишем код вызова метода изменения текущей записи:
private void button2_Click(object sender, EventArgs e)
{
//вызываем метод и передаем в него 4 параметра:
//1 - значение поля Дата текущей записи (тип DateTime)
//2 - значение коля Kolvo текущей записи (тип int)
//3 - номер сотрудника из выпадающего списка (тип int)
//4 - значение ключевого поля текущей записи (тип int)
sotrVyrabTableAdapter.UpdVyrabotka(Convert.ToDateTime((sotrVyrabBindingSource.Current as DataRowView)["data"]),
Convert.ToInt32((sotrVyrabBindingSource.Current as DataRowView)["kolvo"]),
Convert.ToInt32(comboBox1.SelectedValue),
Convert.ToInt32((sotrVyrabBindingSource.Current as DataRowView)["id_vyrab"]));
//сохраняем текущую позицию в наборе данных перед обновлением
int pos = sotrVyrabBindingSource.Position;
//обновляем связанный набор для отображения изменений в сетке
sotrVyrabTableAdapter.Fill(sotrDataSet.SotrVyrab);
//восстанавливаем позицию текущей записи после обновления
sotrVyrabBindingSource.Position = pos;
}
Удаление данных в таблице
Для удаления данных используется запрос вида:
delete from имя_таблицы where условие
Так как нужно обновлять только текущую запись, то условие where записывается только для ключевого поля-счетчика.
Например:
//удалим запись с номером 1
delete from vyrabotka where id_vyrab=1
Для создания запроса на удаление для связанного набора данных SotrVyrab вызовите его контекстное меню и выполните команду "Добавить - Запрос". Выберите тип запроса "DELETE" и введите текст запроса:
delete from vyrabotka where id_vyrab=?
На следующем шаге укажите имя для метода. В результате внутри набора данных SotrVyrab будет создан метод с одним параметром, с помощью которого можно указать значен ие ключевого поля удаляемой записи.
Например, пользователь в сетке будет выбирать ненужную запись и удалять ее.
Пусть метод для запроса DELETE имеет имя
DelVyrabotka(id_vyrab)
Для кнопки напишем код вызова метода удаления текущей записи:
private void button3_Click(object sender, EventArgs e)
{
//выдаем запрос на подтверждение операции
//если пользователь ответил НЕТ, выходим без удаления
if (MessageBox.Show("Удалить текущую запись о выработке?",
"Подтвердите удаление",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.No) return;
//вызываем метод для удаления записи
//в качестве параметра передаем значение ключевого поля (тип int)
sotrVyrabTableAdapter.DelVyrabotka(Convert.ToInt32((sotrVyrabBindingSource.Current as DataRowView)["id_vyrab"]));
//обновляем связанный набор для отображения изменений в сетке
sotrVyrabTableAdapter.Fill(sotrDataSet.SotrVyrab);
}
3. Работа с таблицами в отношении «многие-ко-многим»
В реальных базах данных, разрабатываемых для разных предметных областей, часто встречается ситуация, когда таблицы связываются отношением "многие-ко-многим". Такая связь предполагает наличие в БД специальной промежуточной таблицы, через которую связываются две основные.
Например, добавим в нашу исходную БД sotr новую таблицу. Структура БД представлена на рисунке:
Схема показывает, что таблицы product и sotr связаны между собой отношением "многие-ко-многим" через таблицу vyrabotka. То есть:
- один сотрудник может произвести много наименования продукции;
- одно наименование продукции могут производить многие сотрудники.
Таблицы vyrabotka хранит информацию о наименовании и количестве произведенной продукции каждым сотрудником по датам. Например:
Видно, что сотрудники с номерами 1, 2 и 3 в указанные даты производили определенное количество продукции с номерами 1, 2 и 3. Однако, такое представление информации для пользователя не удобно, так как он не знает, какие именно сотрудники соответствуют номерам 1, 2, 3, и какие именно наименования продукции соответствуют номерам 1, 2, 3. Более целесообразно вывести на экран поля id_vyrab, data, kolvo из таблицы vyrabotka, поля fam, imya, otch из таблицы sotr, поле product из таблицы product. Для этого воспользуемся запросом вида:
SELECT id_vyrab, data, product, kolvo, fam, imya, otch
FROM sotr INNER JOIN (product INNER JOIN Vyrabotka ON product.id_product = Vyrabotka.id_product)
ON sotr.id_sotr = Vyrabotka.id_sotr;
Результат выполнения запроса представлен на рисунке:
Такая форма представления данных более информативна, так как показывает не номер сотрудника, а его ФИО. Но сам набор данных строится на основании запроса по нескольким таблицам.
Для создания таблицы на основе указанного запроса в Visual Studio на панели ""Источники данных" щелкните на кнопке "Изменить набор данных в конструкторе"". В результате откроется окно со структурой всех таблиц БД и связями между ними. Далее щелкните правой кнопкой на пустом месте в окне со структурами таблиц, выберите команду "Добавить - TableAdapter", введите текст запроса. В результате будет создана отдельная виртуальная таблица, которую можно подключить на форму как обычную. Для удобства работы с таблицей рекомендуется в ее свойстве Name задать имя, отличное от стандартного.
Например, на рисунке мы создали новый TableAdpater, указали текст запроса и переименовали таблицу в "SotrProduct"
С помощью компонента DataGridView подключите эту таблицу на форму. В результате в одной сетке отобразятся колонки из разных таблиц:
Мы опять получили в сетке связанный набор данных по нескольким таблицам. Принцип работы с ним такой же, как и рассмотренный выше: создаются отдельные запросы INSERT, UPDATE и DELETE, и вызываются через соответствующие методы с передачей параметров.
Особенное внимание уделите запросу INSERT, в котором нужно обязательно указать значения ключевых полей их двух основных таблиц. Для нашего примера, при добавлении новой записи в таблицу vyrabotka обязательно указывают значение id_sotr из таблицы sotr (кто произвел) и значение id_product из таблицы product (что произвели).
Например. Реализуем функцию добавления новой записи в таблицу vyrabotka.
Для создания запроса на добавление для связанного набора данных SotrProduct вызовите его контекстное меню и выполните команду "Добавить - Запрос". Выберите тип запроса "INSERT" и введите текст запроса:
insert into Vyrabotka (data, kolvo, id_sotr, id_product) values (?, ?, ?, ?)
На следующем шаге укажите имя для метода. В результате внутри набора данных SotrVyrab будет создан метод с четырьмя параметрами, с помощью которых можно будет задать значения для полей новой записи.
Например, на форме имеются компоненты вида:
Дата задается с помощью DateTimePicker, продукция - с помощью списка ComboBox, количество - с помощью TextBox, сотрудник - с помощью ComboBox. Пусть метод для запроса INSERT имеет имя
AddVyrabotka(data, kolvo, id_sotr, id_product)
Для кнопки напишем код вызова метода добавления новой записи:
private void button1_Click(object sender, EventArgs e)
{
//вызываем метод и через параметры передаем в него дату (тип DateTime,
//количество (тип int), номер сотрудника(тип int), номер продукции (тип int)
//для записи даты из DateTimePicker методом ToString выделяем только дату,
//а затем полученное значение конвертируем в DateTime
sotrProductTableAdapter.AddVyrabotka(Convert.ToDateTime(dateTimePicker1.Value.ToString("dd/MM/yyyy")),
Convert.ToInt32(textBox1.Text),
Convert.ToInt32(comboBox2.SelectedValue),
Convert.ToInt32(comboBox1.SelectedValue));
//обновляем связанный набор для отображения изменений в сетке
sotrProductTableAdapter.Fill(sotrDataSet.SotrProduct);
}
Операции обновления и удаления выполняются аналогично рассмотренным выше.
Вопросы для самоконтроля
1. Как отобразить данные из связанных таблиц в одном наборе данных?
2. Как реализовать операцию добавления записи в связанный набор данных?
3. Как реализовать операцию изменения текущей записи в связанном наборе данных?
4. Как реализовать операцию удаления текущей записи в связанном наборе данных?