Лекция № 13

Тема: «Обработка БД с помощью запросов»

План

1. Отображение данных из связанных таблиц

2. Работа с запросами. Параметры запросов.

3. Обновление и удаление данных.

4. Расчет общих показателей по таблице.

1. Отображение данных из связанных таблиц

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

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

Для отображения подчиненной таблицы нанесите сетку DataGridView и в качестве источника данных нужно указать не таблицу из БД (в этом случае не будет связи между данными), а раскрыть BindingSource главной таблицы и выбрать имя соединения. На первом рисунке видно, на таблицы otdely и sotr связаны по сполю id_otdel. На втором рисунке видно, что для подключения сетки к подчиненной таблице раскрыт компонент otdelyBindingSource (соответствует главной таблицы), и в нем отображается соединение otdelysotr. Именно это соединение и нужно выбрать в качестве источника данных.

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

Замечание. Обратите внимание, что после задания соединения с подчиненной таблицей на форме, как обычно, появится два компонента: BindingSource и TableAdapter. Для нашего примера будут созданы компоненты otdelysotrBindingSource и sotrTableAdapter. То есть имена у компонентов разные: BindingSource имеет имя соединения, а TableAdapter имеет имя подчиненной таблицы. Для простоты написания кода рекомендуется переименовать BindingSource по имени подчиненной таблицы: задать свойство Name = sotrBindingSource.

После настройки сеток можно выполнять любые операции с данными так же, как это было описано при работе с одной таблицей в предыдущих лекциях: добавление, редактирование, удаление записей, создание страничных форм, фильтрацию данных. Только для работы с главной таблицей нужно использовать компонент otdelyBindingSource и его свойства и методы. Для работы с подчиненной таблицей нужно использовать компонент sotrBindingSource и его свойства и методы (при условии, что его переименовали как указано в замечании выше).

Переключение между связанными и полными данными

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

Такая функция может быть реализована, например, с помощью группы переключателей RadioButton.

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

Для первого переключателя в событии CheckedChanged напишем код:

private void radioButton1_CheckedChanged(object sender, EventArgs e)

{

//включаем просмотр по отделам

//ссылаемся на BindingSource главной таблицы

sotrBindingSource.DataSource = otdelyBindingSource;

//ссылаемся на имя соединения

sotrBindingSource.DataMember = "otdelysotr";

}

Для второго переключателя в событии CheckedChanged напишем код:

private void radioButton2_CheckedChanged(object sender, EventArgs e)

{

//включаем просмотр всех записей

//ссылаемся на подключение к БД

sotrBindingSource.DataSource = sotrDataSet;

//ссылаемся на подчиненную таблицу

sotrBindingSource.DataMember = "sotr";

}

Замечание: обратите внимание, что таблицы otdely и sotr связаны по полю id_otdel. Если данные отображаются в связанном виде (по отделам), то при добавлении нового сотрудника в подчиненную таблицу, его поле id_otdel будет автоматически заполняться соответствующим значением из главной таблицы. Если же данные подчиненной таблицы отображаются без связывания (общим списком), то при добавлении нового сотрудника, его поле id_otdel остается пустым и при сохранении запись остается "потерянной", так как не принадлежит ни одному из отделов. Поэтому режим просмотра общим списком можно использовать для просмотра, изменения, удаления, но не для добавления данных (кнопку "Добавить" рекомендуется блокировать).

2. Работа с запросами. Параметры запросов

Любая таблица, которая указана при создании подключения БД, представляет собой запрос, отбирающий все поля и все записи таблицы. По умолчанию такой запрос вызывается методом Fill(), который обычно выполняется в событии Load() формы после настройки параметров соединения с БД. Чтобы увидеть текст запроса для нужной таблицы на панели "Источник данных" щелкните на кнопке "Изменить набор данных в конструкторе". Откроется окно со структурой таблиц (как показано на первом рисунке лекции). Если щелкнуть правой кнопкой на заголовке нужной таблицы и выбрать команду "Настроить", то откроется форма с текстом запроса, который соответствует методу Fill(). Можно внести изменения в текст. Но чаще всего приходится на основе таблиц создавать дополнительные запросы, для которых создаются дополнительные методы.

Создание нового запроса по таблице

Для создания запроса щелкните правой кнопкой по заголовку нужной таблицы и выберите команду "Добавить - Запрос". Запускается мастер создания запроса. На первом шаге укажите тип создаваемого запроса:

- запрос возвращающий несколько строк или колонок;

- запрос, возвращающий одно значение (если вы рассчитываете только одно значение count, max, min, avg, sum). Если нужно одновременно рассчитать несколько значений, то выбирайте первый тип;

- запрос на обновление данных;

- запрос на удаление данных;

- запрос на добавление данных.

На следующем шаге нужно будет ввести текст запроса. При этом мастер предлагает обычно некоторую заготовку, которую нужно заменить на нужную команду. Для облегчения написания запроса можно нажать на кнопку "Построитель запросов" и построить запрос с помощью конструктора.

При этом флажками вы отмечаете поля, которые учувствуют в запросе, ниже в сетке для каждого поля задается сортировка и условия отбора. Под сеткой отображается текст запроса, формируемый в соответствии с вашими параметрами. С помощью кнопки "Выполнить запрос" можно проверить, как он работает.

Например, на рисунке мы делаем запрос, который отбирает id, ФИО и дату рождения сотрудников с сортировкой по фамилии.

На следующем шаге мастера необходимо выбрать способ возвращения результат запроса в программу и указать имя метода для вызова запроса.

Обратите внимание, что первый флажок отвечает за метод заполнения TableAdapter, и мастер предлагает создать метод с именем FillBy(). Данный метод будет заполнять исходную таблицу, заменяя данные на форме. Например, если вызвать метод с помощью команды:

//выполняем метод, аргумент - имя таблицы sotr из БД

sotrTableAdapter.FillBy(sotrDataSet.sotr);

то в сетке на форме останутся заполненными только поля id, fam, imya, otch, datar. Остальные будут пустыми, так как колонки для них есть, а самих полей в запросе нет.

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

Например, будет создан метод ShowFIO() для создания нового набора с полями id, ФИО и дата рождения.

После завершения работы мастера в окне со структурой таблицы отобразится список новых методов для выполнения запроса.

Например. Пусть нужно отобрать данные в соответствии с новым запросом не изменяя данных в основной таблице и сетке.

//описываем переменную (экземпляр класса) для хранения таблицы

DataTable tbl = new DataTable();

//выполняем метод (запрос) и записываем результат в переменную

tbl=sotrTableAdapter.ShowFIO();

//далее с переменной tbl выполняем нужные действия

Использование параметров в запросах

Часто при создании запросов в них нужно указать значения, которые при выполнении программы могут изменяться. Например, в запросе select можно указать такое значение в условии отбора, в запросе update можно указать такое значение для обновления поля и т.д. Такие неопределенные значения называются параметрами и для их задания в тексте запроса используется символ "?"

Пример.

Отберем ФИО сотрудников с нужным номером отдела. Создадим новый запрос и зададим для него текст:

select fam, imya, otch from sotr where id_otdel=?

Для запроса зададим имя метода ShowFIOByOtdel. В результате будет создан метод ShowFIOByOtdel(int id_otdel). Как видно, метод имеет входной параметр, с помощью которого вы укажите номер нужного отдела.

//описываем переменную

DataTable tbl=new DataTable;

//отбираем ФИО сотрудников отдела № 1

tbl=sotrTableAdapter.ShowFIOByOtdel(1);

Если нужно задать несколько параметров, то в тексте запроса указывается несколько знаков "?". В результате созданный для запроса метод вызова будет иметь несколько входных параметров, которые указывают при вызове метода.

Пример.

Отберем всех сотрудников с заданным годом рождения. Так как у нас нет года рождения, а есть дата рождения, то год зададим как диапазон дат (от и до). Создадим новый запрос и зададим для него текст:

select * from sotr where datar>=? and datar<=?

Для запроса зададим имя метода ShowSotrData. В результате будет создан метод ShowFIOByOtdel(DateTime datar, DateTime datar1). Как видно, метод имеет 2 входных параметра, с помощью которых вы укажите начало и конец нужного года.

//описываем переменную

DataTable tbl=new DataTable;

//отбираем сотрудников 1995 г.р. (с 01.01.1995 до 31.12.1995)

tbl = sotrTableAdapter.ShowSotrData(Convert.ToDateTime("01.01.1995"), Convert.ToDateTime("31.12.1995"));

3. Обновление и удаление данных

Запросы на обновление/удаление данных сразу обращаются к таблице БД и не требуют вызова дополнительных методов для синхронизации данных, как это делается при работе через BindingSource.

Однако, результаты работы таких запросов не отображаются на форме. Это связано с тем, что данные из БД считываются методом Fill() только один раз в событии Load() формы. Все последующие изменения, выполненные не через компоненты на форме, "не видны". Поэтому, после выполнения запросов на обновление/удаление обязательно нужно считывать данные из БД заново методом Fill().

Пример. Обновим оклад сотрудников на произвольный процент. Для обновления данных добавляем запрос с типом update и вводим текст запроса:

update sotr set oklad=oklad+oklad*?/100

Для запроса зададим имя метода UpdSotrOklad. В результате будет создан метод UpdSotrOklad(decimal oklad). Как видно, метод имеет входной параметр, с помощью которого вы укажите процент для изменения оклада.

//изменяем оклады сотрудников на 20%

sotrTableAdapter.UpdSotrOklad(20);

//обновляем данные в сетке, считывая заново данные из БД

//для этого вызываем метод загрузки данных из БД, как в событии Load формы

sotrTableAdapter.Fill(sotrDataSet.sotr);

Пример. Удалим сотрудников заданного отдела. Для удаления данных добавляем запрос с типом delete и вводим текст запроса:

delete sotr where id_otdel=?

Для запроса зададим имя метода DelSotrOtdel. В результате будет создан метод DelSotrOtdel(int id_otdel). Как видно, метод имеет входной параметр, с помощью которого вы укажите номер отдела для удаления.

//удаляем сотрудников отдела № 5

sotrTableAdapter.DelSotrOtdel(5);

//обновляем данные в сетке, считывая заново данные из БД

//для этого вызываем метод загрузки данных из БД, как в событии Load формы

sotrTableAdapter.Fill(sotrDataSet.sotr);

4. Расчет общих показателей по таблице

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

Для расчетов таких показателей можно применять запросы вида:

select функция(поле) as имя, функция(поле) as имя, ...

from таблица [where условие]

В качестве функций можно использовать такие:

count(*) – количество записей в Таблице;

count(поле) – количество записей с непустыми значениями в поле

sum(поле) – сумма значений в поле

max(поле) – максимальное значение в поле

min(поле) – минимальное значение в поле

avg(поле) – среднее значение в поле.

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

select sum(oklad) as sumoklad, avg(oklad) as sroklad from sotr

Для запроса зададим имя метода CalcAll. В результате будет создан метод CalcAll() без параметров.

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

void schet()

{

//описываем переменную

DataTable tbl=new DataTable;

//отбираем расчетные данные

tbl = sotrTableAdapter.CalcAll();

//выводим результаты в надписях (при условии, что они есть на форме)

//запрос вернул одну строку в числами

//общаемся к числу, указывая номер первой строки (нумерация с 0)

//и имя колонки в запросе

label1.Text=tbl.Rows[0]["sumoklad"].ToString();

label2.Text=tbl.Rows[0]["sroklad"].ToString();

}

Данную процедуру можно вызвать в таких событиях как перемещение по записям в сетке, добавление, изменение, удаление данных в сетке.

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

select sum(oklad) as sumoklad, avg(oklad) as sroklad from sotr where id_otdel=?

Для запроса зададим имя метода CalcOtdel. В результате будет создан метод CalcOtdel(int id_otdel). Как видно, метод имеет входной параметр, с помощью которого вы укажите номер отдела для расчета.

//отбираем расчетные данные

//передаем в метод расчета номер активного отдела

//из набора данных otdelyBindingSource

tbl = sotrTableAdapter.CalcOtdel(Convert.ToInt32((otdelyBindingSource.Current as DataRowView)["id_otdel"]));

//выводим результаты в надписях (при условии, что они есть на форме)

//запрос вернул одну строку в числами

//общаемся к числу, указывая номер первой строки (нумерация с 0)

//и имя колонки в запросе

label3.Text=tbl.Rows[0]["sumoklad"].ToString();

label4.Text=tbl.Rows[0]["sroklad"].ToString();

Вопросы для самоконтроля

1. Как отобразить на форме данные из связанных таблиц?

2. Как создать и вызвать запрос на выборку по таблице?

3. Как создать запрос на выборку с параметром и вызвать его на выполнение?

4. Как создать и вызвать запрос на обновление/удаление? Как обновить данные на форме после таких запросов?

5. Как создать запрос на выполнение расчетов по таблице? Как его вызвать в программе?