В предыдущих разделах мы рассматривали работу по получению данных с заранее созданных таблиц. Теперь пора разобрать, каким же образом мы можем создавать/удалять таблицы, добавлять новые записи и удалять старые. Для этих целей в SQL существуют такие операторы, как: CREATE - создает таблицу, ALTER - изменяет структуру таблицы, DROP - удаляет таблицу или поле, INSERT - добавляет данные в таблицу. Начнем знакомство с данной группой операторов из оператора INSERT .
Как видно из названия, оператор INSERT используется для вставки (добавления) строк в таблицу базы данных. Добавление можно осуществить несколькими способами:
Итак, чтобы добавить новую строку в таблицу, нам необходимо указать название таблицы, перечислить названия колонок и указать значение для каждой колонки с помощью конструкции INSERT INTO название_таблицы (поле1, поле2 ...) VALUES (значение1, значение2 ...) . Рассмотрим на примере.
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES ("6", "1st Street", "Los Angeles", "Harry Monroe", "USA")
Также можно изменять порядок указания названий колонок, однако одновременно нужно менять и порядок значений в параметре VALUES .
В предыдущем примере при использовании оператора INSERT мы явно отмечали имена столбцов таблицы. Используя данный синтаксис, мы можем пропустить некоторые столбцы. Это значит, что вы вводите значение для одних столбцов но не предлагаете их для других. Например:
INSERT INTO Sellers (ID, City, Seller_name) VALUES ("6", "Los Angeles", "Harry Monroe")
В данном примере мы не указали значение для двух столбцов Address и Country . Вы можете исключать некоторые столбцы из оператора INSERT INTO , если это позволяет производить определение таблицы. В этом случае должно соблюдаться одно из условий: этот столбец определен как допускающий значение NULL (отсутствие какого-либо значения) или в определение таблицы указанное значение по умолчанию. Это означает, что, если не указано никакое значение, будет использовано значение по умолчанию. Если вы пропускаете столбец таблицы, которая не допускает появления в своих строках значений NULL и не имеет значения, определенного для использования по умолчанию, СУБД выдаст сообщение об ошибке, и это строка не будет добавлена.
В предыдущей примерах мы вставляли данные в таблицы, прописывая их вручную в запросе. Однако оператор INSERT INTO позволяет автоматизировать этот процесс, если мы хотим вставлять данные из другой таблицы. Для этого в SQL существует такая кострукция как INSERT INTO ... SELECT ... . Данная конструкция позволяет одновременно выбирать данные из одной таблицы, и вставить их в другую. Предположим мы имеем еще одну таблицу Sellers_EU с перечнем продавцов нашего товара в Европе и нам нужно их добавить в общую таблицу Sellers . Структура этих таблиц одинакова (то же количество колонок и те же их названия), однако другие данные. Для этого мы можем прописать следующий запрос:
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU
Нужно обратить внимание, чтобы значение внутренних ключей не повторялись (поле ID ), в противном случае произойдет ошибка. Оператор SELECT также может включать предложения WHERE для фильтрации данных. Также следует отметить, что СУБД не обращает внимания на названия колонок, которые содержатся в операторе SELECT , для нее важно только порядок их расположения. Поэтому данные в первом указанном столбце, что были выбраны из-за SELECT , будут в любом случае заполнены в первый столбец таблицы Sellers , указанной после оператора INSERT INTO , независимо от названия поля.
Часто при работе с базами данных возникает необходимость в создании копий любых таблиц, с целью резервирования или модификации. Чтобы сделать полную копию таблицы в SQL предусмотрен отдельный оператор SELECT INTO . Например, нам нужно создать копию таблицы Sellers , нужно будет прописать запрос следующим образом:
SELECT * INTO Sellers_new FROM Sellers
В отличие от предыдущей конструкции INSERT INTO ... SELECT ... , когда данные добавляются в существующую таблицу, конструкция копирует данные в новую таблицу. Также можно сказать, что первая конструкция импортирует данные, а вторая - экспортирует. При использовании конструкции SELECT ... INTO ... FROM ... следует учитывать следующее:
Общий синтаксис:
а) вставка одной новой строки в таблицу:
INSERT <таблица> [(столбцы)]
VALUES (значения)
б) Вставка в таблицу одной или более строк, взятых из источника, указанного в подзапросе:
INSERT <таблица> [(столбцы)]
Правила:
Количество элементов в разделе «Столбцы» должно совпадать с количеством элементов в разделе «Значения»; типы данных должны совпадать либо допускать неявное преобразование;
В случае, когда раздел «Столбцы» отсутствует, раздел «Значения» должен содержать столько же значений, сколько всего столбцов в редактируемой таблице, кроме того, значения должны перечисляться в том же порядке, в каком объявлены столбцы таблицы; поэтому рекомендуется всегда явно указывать список столбцов, в которые будут добавляться новые значения;
В случае использования синтаксиса (б) подзапрос SELECT может быть адресован либо к этой же таблице, либо к другой таблице базы данных, либо к другой базе данных на сервере;
Если для некоторого столбца таблицы задано значение по умолчанию, и требуется в добавляемой записи использовать именно это значение, то в разделе «Значения» для этого столбца необходимо написать ключевое слово DEFAULT.
Примеры :
INSERT INTO detail (detail_name)
VALUES ("Подшипник")
INSERT INTO supply (supplier_id, supply_quantity, supply_cost,
supply_date, detail_id)
VALUES (4, 177, 453.45, "25.09.2009", 1)
INSERT supplier (supplier_type, supplier_name)
VALUES (DEFAULT, "Смирнов С. С.")
Общий синтаксис:
UPDATE <таблица>
SET <столбец> = <значение> [, <столбец> = <значение>]
Примеры :
SET weight = 210
SET material_name = "Олово"
WHERE material_id = 2
SET department_id = NULL,
delivery_date = delivery_date + 1 /*увеличение даты поставки на день
WHERE delivery_id = 1 OR department_id > 3
SET detail_name = detail_name + "!!!"
WHERE detail_name LIKE "_а%" AND weight BETWEEN 6 AND 10
Общий синтаксис:
DELETE<таблица>
<условие>
Примеры :
WHERE detail_id IN (2, 5, 8)
WHERE weight IS NULL
Задание:
Заполните базу данных средствами ManagementStudio. Для этого следует воспользоваться командой «Открыть таблицу» контекстного меню таблицы в «Обозревателе объектов» (рис. 4.1).
SELECT <список столбцов>
FROM <таблица(-ы) источник>
Разделы SELECT и FROM обязательно должны присутствовать в каждом запросе; остальные разделы могут отсутствовать или присутствовать частично.
Наиболее простые запросы:
1. Выбрать название и вес деталей
Рис. 5.1. Результаты выполнения запроса №1
2. Выбрать всю информацию из таблицы материалов
Рис. 5.2. Результаты выполнения запроса №2
Уникальность DISTINCT
3. Выбрать уникальные коды поставщиков из таблицы поставок
Рис. 5.3. Результаты выполнения запроса №3
Ограничение WHERE
4. Выбрать количество и даты поставки детали с кодом 1
Рис. 5.4. Результаты выполнения запроса №4
5. Выбрать названия поставщиков с кодами 1, 4 и 6
Рис. 5.5. Результаты выполнения запроса №5
6. Выбрать всю информацию о поставках, сделанных до 1.10.2009
Рис. 5.6. Результаты выполнения запроса №6
7. Выбрать всю информацию о деталях, не начинающихся на букву «В» (в любом регистре) и чей вес меньше 50
Рис. 5.7. Результаты выполнения запроса №7
8. Выбрать название и код материала для деталей с весом от 5 до 10 г или имеющих в названии букву «н» в третьей позиции
Рис. 5.8. Результаты выполнения запроса №8
9. Выбрать названия поставщиков длиной не больше 15-и символов
Рис. 5.9. Результаты выполнения запроса №9
10. Выбрать месяца и годы поставок деталей
Рис. 5.10. Результаты выполнения запроса №10
Сортировка ORDER BY
11. Упорядочить поставки сначала по коду поставщика, а затем по дате поставки
Рис. 5.11. Результаты выполнения запроса №11
12. Выбрать названия поставщиков с кодами 4, 6, 8, 9, упорядоченных по алфавиту в обратном порядке
Рис. 5.12. Результаты выполнения запроса №12
Агрегация и группировка GROUP BY
Рис. 5.13. Результаты выполнения запроса №13
14. Определить средний вес деталей из материала с кодом 2
Рис. 5.14. Результаты выполнения запроса №2
15. Из поставок, совершенных до 1.10.2009, выбрать самую крупную поставку и самую мелкую
Рис. 5.15. Результаты выполнения запроса №15
16. Для поставщиков с кодами в диапазоне 5 - 8 посчитать суммарное количество поставленных ими деталей
Рис. 5.16. Результаты выполнения запроса №16
Рис. 5.17. Результаты выполнения запроса №17
Ограничение на группировки HAVING
18. Выбрать материалы, для которых суммарный вес выполненных из них деталей не больше 20
Рис. 5.18. Результаты выполнения запроса №18
19. Из поставок 2008-го года выбрать детали, поставлявшиеся более одного раза
Рис. 5.19. Результаты выполнения запроса №19
Преобразование типов CAST
20. Получить сведения о датах поставок в текстовом виде
Рис. 5.20. Результаты выполнения запроса №20
21. Получить сведения из таблицы деталей в виде строк «Деталь X имеет вес Y»
Рис. 5.21. Результаты выполнения запроса №21
Альтернативы CASE
22. Разделить детали на легкие (весом до 20), средние (между 20 и 50) и тяжелые
Рис. 5.22. Результаты выполнения запроса №22
Обработка NULL-значений
23. Получить сведения о деталях и их весах, причем если у детали вес не задан, то вместо NULL написать -100
Рис. 5.23. Результаты выполнения запроса №23
Функция существования EXISTS
24. Выбрать название и код материала только тех деталей, которые когда-либо поставлялись
Рис. 5.24. Результаты выполнения запроса №24
25. Выбрать названия тех материалов, из которых не изготовлена ни одна деталь
Рис. 5.25. Результаты выполнения запроса №25
Подзапросы
26. Получить сведения о самой последней (по дате) поставке
Рис. 5.26. Результаты выполнения запроса №26
27. Получить все поставки деталей из материала с кодом 2
Рис. 5.27. Результаты выполнения запроса №27
28. Для каждого поставщика получить сведения о самой первой (по дате) его поставке
Рис. 5.28. Результаты выполнения запроса №28
29. Для каждого поставщика получить его имя и дату последнего заказа
Рис. 5.29. Результаты выполнения запроса №29
Объединение таблиц JOIN
30. Получить таблицу вида: название детали, название материала, из которого выполнена эта деталь
Рис. 5.30. Результаты выполнения запроса №30
31. Получить таблицу вида: поставщик, название детали, количество и дата поставки для деталей, у которых задан вес
Рис. 5.31. Результаты выполнения запроса №31
32. Выбрать всю информацию о тех деталях, которые когда-либо поставлялись
Рис. 5.32. Результаты выполнения запроса №32
Рис. 5.33. Результаты выполнения запроса №33
34. Выбрать названия материалов и сделанных из них деталей
Рис. 5.34. Результаты выполнения запроса №34
35. Получить все данные о поставщиках, поставках и деталях
Рис. 5.35. Результаты выполнения запроса №35
Объединение таблиц UNION
36. Получить таблицу названий и весов деталей, причем последняя строка таблицы должна содержать итоги в виде суммарного веса всех деталей
Рис. 5.36. Результаты выполнения запроса №36
37. Получить таблицу из двух полей, где первое поле – название детали, материала, поставщика или дата поставки, а второе поле – длина строки из первого поля
Рис. 5.37. Результаты выполнения запроса №37
Инструкция SQL INSERT INTO и INSERT SELECT используются для вставки новых строк в таблицу. Существует два способа использования инструкций:
Синтаксис :
INSERT INTO имя_таблицы VALUES (значение1, значение2, значение3,...); имя_таблицы: имя таблицы. значение1, значение2,.. : значения первого столбца, второго столбца,... для новой записи
Синтаксис :
INSERT INTO имя_таблицы (столбец1, столбец2, столбец3,..) VALUES (значение1, значение2, значение3,...); имя_таблицы: имя таблицы. столбец1: имя первого столбцы, второго столбца... значение1, значение2,.. : значения первого столбца, второго столбца,... для новой записи
Запросы :
Способ 1 (вставка только значений ):
INSERT INTO Student VALUES ("5","HARSH","WEST BENGAL","8759770477","19");
Результат :
После использования INSERT INTO SELECT таблица Student теперь будет выглядеть следующим образом:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | 9455123451 | 18 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
4 | SURESH | Delhi | 9156768971 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
5 | HARSH | WEST BENGAL | 8759770477 | 19 |
Способ 2 (вставка значений только в указанные столбцы ):
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ("5","PRATIK","19");
Результат :
Таблица Student теперь будет выглядеть следующим образом:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | 9455123451 | 18 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
4 | SURESH | Delhi | 9156768971 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
5 | PRATIK | null | null | 19 |
Обратите внимание, что для столбцов, значения для которых не указаны, задается null .
Можно использовать инструкцию MySQL INSERT SELECT для копирования строк из одной таблицы и их вставки в другую.
Использование этого оператора аналогично использованию INSERT INTO . Разница в том, что оператор SELECT применяется для выборки данных из другой таблицы. Ниже приведены различные способы использования INSERT INTO SELECT :
Синтаксис :
INSERT INTO первая_таблица SELECT * FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы.
Мы использовали инструкцию SELECT для копирования данных из одной таблицы и инструкцию INSERT INTO для их вставки в другую.
Синтаксис :
INSERT INTO первая_таблица(имена_столбцов1) SELECT имена_столбцов2 FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы. имена_столбцов1: имена столбцов, разделенные запятой(,) для таблицы 1. имена_столбцов2: имена столбцов, разделенные запятой(,) для таблицы 2.
Мы использовали инструкцию SELECT для копирования данных только из выбранных столбцов второй таблицы и инструкцию MySQL INSERT INTO SELECT для их вставки в первую таблицу.
Синтаксис :
Таблица 2: LateralStudent
ROLL_NO | NAME | ADDRESS | PHONE | Age |
7 | SOUVIK | DUMDUM | 9876543210 | 18 |
8 | NIRAJ | NOIDA | 9786543210 | 19 |
9 | SOMESH | ROHTAK | 9687543210 | 20 |
Запросы :
Способ 1 (вставка всех строк и столбцов ):
INSERT INTO Student SELECT * FROM LateralStudent;
Результат :
Этот запрос вставит все данные таблицы LateralStudent в таблицу Student . После применения SQL INSERT INTO SELECT таблица Student будет выглядеть следующим образом:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | 9455123451 | 18 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
4 | SURESH | Delhi | 9156768971 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
7 | SOUVIK | DUMDUM | 9876543210 | 18 |
8 | NIRAJ | NOIDA | 9786543210 | 19 |
9 | SOMESH | ROHTAK | 9687543210 | 20 |
Способ 2 (вставка отдельных столбцов ):
INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;
Результат :
Этот запрос вставит данные из столбцов ROLL_NO , NAME и Age таблицы LateralStudent в таблицу Student . Для остальных столбцов таблицы Student будет задано значение null . После применения SQL INSERT SELECT таблица будет выглядеть следующим образом:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | 9455123451 | 18 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
4 | SURESH | Delhi | 9156768971 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
7 | SOUVIK | Null | null | 18 |
8 | NIRAJ | Null | null | 19 |
9 | SOMESH | Null | null | 20 |
Результат :
Этот запрос выберет только первую строку из таблицы LateralStudent для вставки в таблицу Student . После применения INSERT SELECT таблица будет выглядеть следующим образом:
ROLL_NO | NAME | ADDRESS | PHONE | Age |
1 | Ram | Delhi | 9455123451 | 18 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
4 | SURESH | Delhi | 9156768971 | 18 |
3 | SUJIT | ROHTAK | 9156253131 | 20 |
2 | RAMESH | GURGAON | 9562431543 | 18 |
7 | SOUVIK | DUMDUM | 9876543210 | 18 |
Данная публикация представляет собой перевод статьи «SQL INSERT INTO Statement » , подготовленной дружной командой проекта
Запросы на изменение используются для добавления (INSERT INTO), удаления (DELETE) и обновления (UPDATE) записей в таблицах.
Команда INSERT INTO может быть использована для добавления одной или нескольких записей в конец таблицы.
Синтаксис команды INSERT INTO имеет следующий вид:
INSERT INTO Название_Таблицы [(Название_Полей)] VALUES (Значения_данных);
Например, поместим в таблицу "Группы" запись, содержащую данные (1, БФ-16а, 1, 1) в соответствующие столбцы (КодГруппы, Название, Курс, Семестр).
Для этого запрос в формате:
INSERT INTO Группы (КодГруппы, Название, Курс, Семестр) VALUES (1, БФ-16а, 1, 1);
введем на бланке запроса, выполнив команду "Конструктор запросов" на вкладке "Создание", в режиме "Режим SQL" из меню Вид.
Сохраняем запрос с именем "Добав_1_записи". В результате в «Области переходов» появится несвязанный объект - «Добав_1_записи» (Рис.1.).
Рис. 1.
После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Добав_1_записи» в пустой таблице появится первая запись (Рис. 2.)
Рис. 2.
Команда UPDATE предназначена для изменения уже существующих записей в столбцах таблицы, что позволяет обновлять значения в заданных столбцах таблицы.
Синтаксис команды UPDATE имеет следующий вид:
UPDATE Название_Таблицы SET Название_Столбца = Новое_Значение WHERE Условие_Отбора;
В предложении SET указываются имена столбцов, данные в которых надо изменить. Оператор WHERE не является обязательным, но при его помощи можно указать только на те строки столбцов (таблиц), которые необходимо обновить.
Создадим запрос на изменение записи в таблице "Группы" базы данных sql_training_st.mdb.
Например, изменим уже существующую запись для первого ключевого поля id в столбце "Название" таблицы "Группы".
Запрос в формате:
UPDATE Группы SET Название ="БФ-16б" WHERE ID=1;
Сохраняем запрос с именем "Изменение_записи_1". В результате в «Области переходов» появится объект - «Изменение_записи_1» (Рис. 3.).
Рис. 3.
После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Изменение_записи_1» появится окно диалога, в котором необходимо ввести значение параметра ID=1 и нажать кнопку ОК. После этих действий в таблице "Группы" изменится запись в поле "Название" с БФ-16а на БФ-16б (Рис. 4.).
Рис. 4.
Команда DELETE предназначена для удаления записей из таблицы.
Формат запроса на удаление записей:
DELETE FROM Название_Таблицы WHERE Условие_Отбора;
Создадим запрос на изменение записи в таблице "Группы" базы данных sql_training_st.mdb.
Например, удалим существующую запись для первого ключевого поля id во всех столбцах (полях) таблицы "Группы".
Для этого запрос в формате:
DELETE FROM Группы WHERE ID=1;
создаем на бланке запроса, выполнив команду "Конструктор запросов" на вкладке "Создание", в режиме "Режим SQL" из меню Вид.
Сохраняем запрос с именем "Удаление_записи_1". В результате в «Области переходов» появится объект - «Удаление_записи_1» (Рис. 5.).
Рис. 5.
После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Удаление_записи_1» появится окно диалога, в котором необходимо ввести значение параметра ID=1 и нажать кнопку ОК. После этих действий откроется окно диалога "Подтвердите удаление записей". После подтверждения в таблице "Группы" будет удалены данные во всех полях, т.е. удалена запись (Рис. 6.).
Рис. 6.
Следует отметить, что для удаления данных в конкретных полях таблицы целесообразно воспользоваться командой UPDATE и заменить значения в требуемых полях на Null. Если необходимо удалить таблицу в целом, то следует использовать оператор DROP TABLE (синтаксис оператора: DROP TABLE Название_Таблицы;).
Восстановим первую запись в таблице "Группы", выполнив команду «Добав_1_записи», в результате восстановим первую запись (Рис. 7.).
Рис. 7.
Временами у программистов возникает жгучее желание прочитать что-то из базы данных. Программисты становятся нервными и раздражительными, теряют сон и лихорадочно тыкают пальцами в клавиатуру. Ради всеобщего блага и мира во всем мире рассмотрим несложную работу с базой данных из C# с помощью ADO.NET и OLE DB. Хотя данный механизм поддерживает разные базы данных вроде Oracle, здесь и сейчас будем использовать MS SQL Server.
Две основных задачи при работе с базой данных
2. Выполнение sql-команды, что-то делающей на сервере (insert, update, delete, вызов функции или хранимой процедуры
3. Последовательное чтение из select-выборки строки за строкой. В основном используется в веб-приложениях, в настольных проще скачать сразу всю выборку в оперативную память, экономия от чтения только нужных строк незначительна.
4. Редкий случай. Автоматическое обновление таблицы в базе на основании изменений в DataTable (как правило редактируемой через визуальный интрфейс). В реальной жизни данные обычно читаются через сложный запрос с кучей join или представление, так что автоматическая синхронизация не подходит.
Основные классы, используемые для этих целей: OleDbConnection - соединение с базой, создаем со строкой, содержащей параметры соеднинения, открываем, закрываем, OleDbCommand - создаем с экземпляром соединения и sql-командой, если нужно просто выполнить update или получить единичное значение, то хватит этого класса, OleDbDataAdapter - создается с OleDbCommand, специализируется на разовом чтении наборов строк в DataTable, может автоматически создавать колонки DataTable на основании выборки, переносить изменения из DataTable в таблицу в базе, OleDbDataReader - последовательное чтение строк в DataTable по одной за раз (он работает внутри OleDbDataAdapter), DataTable / DataSet - основной контейнер для данных. В перечислении OleDbType хранятся типы данных базы данных.
Using System.Data; using System.Data.OleDb; // строка соединения, используется система пользователей Windows (Integrated Security=SSPI;) string connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=databaseName;Data Source=serverName"; // альтернативная строка соединения с иcпользованием аутентификации MS SQL Server // connString = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=databaseName;Connect Timeout=20;Data Source=serverName;Uid=userName;Pwd=userPassword;" OleDbConnection dbConn = new OleDbConnection(connString); dbConn.Open(); someDataTable = new DataTable(); OleDbDataAdapter dbAdapter = new OleDbDataAdapter("select COLUMN1, COLUMN2 from TEST_TABLE ORDER BY COLUMN2", dbConn); // внутренняя структура пустой таблицы будет создана автоматически на основании прочитанных данных, если структура таблицы уже задана (например через типизированный DataSet), то данные будут писаться в столбцы с совпадающими именами или добавятся новые столбцы dbAdapter.Fill(someDataTable); // альтернативный вариант для заполнения таблицы в DataSet // dbAdapter.Fill(someDataSet, "someDataTable"); dbConn.Close();
2. Выполнение sql-команды, что-то делающей на сервере (insert, update, delete, вызов функции или хранимой процедуры .
Особенно стоить отметить проблемы с датами. Реализация дат в.Net крайне кривая - изначально даты не могут быть пустыми, а реальной жизни они пустые сплошь и рядом. Самое правильное решение - использовать специальный класс даты, исправляющий косяки программистов из Microsoft. Более ленивые разработчики держат все даты в коде строками и конвертируют их в DateTime только при необходимости, например при записи в базу или DataTable. Nullable DateTime не спасает, так как пустая дата в интерфейсе должна выглядеть пустой строкой, а при записи в базу как DBNull.Value - и банальный null в коде не конвертируется в эти значения без дополнительных плясок с бубном.
Первый вариант подразумевает банальное склеивание строки запроса. Считается плохой практикой и особенно опасен в веб-приложениях, так как уязвим для хакерских атак. Проблема с пустыми датами так просто не решается. Кроме того при работе с датами появляется дополнительная проблема - разные форматы строк даты в зависимости от региональный настроек.Net Framework, среды разработки и sql-сервера. Выглядеть это может сюрреалистически - один и тот же запрос работает в SQL Managment Studio, но вылетает при выполнении из кода. Частично спасает особый формат строки дат, Тем не менее так нередко делают в маленьких программках для внутреннего пользования, о существовании которых внешний мир никогда не узнает.
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (" + intVariable", " + stringVariable" , " + dateTimeVariable.ToString("yyyyMMdd") +")"; dbCommand.ExecuteNonQuery();
Правильный вариант подразумевает создание команды с набором строго типизированных параметров. Считайте, что предыдущего абзаца не было.
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (?, ?, ?)"; dbCommand.Parameters.Add("INT_COLUMN", OleDbType.Integer).Value = intVariable; dbCommand.Parameters.Add("VARCHAR_COLUMN", OleDbType.VarChar).Value = stringVariable; if (stringDate == "") { dbCommand.Parameters.Add("DATETIME_COLUMN", OleDbType.DateTime).Value =DBNull.Value; } else { dbCommand.Parameters.Add("DATETIME_COLUMN", OleDbType.DateTime).Value = Convert.ToDateTime(stringDate); } dbCommand.ExecuteNonQuery();
Хранимая процедура вызывается точно так же, разнообразия ради другой вариант записи значений в параметры (он не связан именно с хранимой процедурой):
OleDbCommand someDbComm = new OleDbCommand("someStoredProcedure", this.dbConn); someDbComm.CommandType = CommandType.StoredProcedure; someDbComm.Parameters.Add("@parameter1", OleDbType.VarChar); someDbComm.Parameters.Add("@parameter2", OleDbType.VarChar); someDbComm.Parameters.Value = "У всякой проблемы всегда есть решение - простое, удобное и, конечно, ошибочное"; someDbComm.Parameters.Value = "Генри Луис Менкен"; someDbComm.ExecuteNonQuery();
Расширенный вариант описания параметра с указанием размера поля и привязкой к конкретной колонке таблицы.
DbCommand.Parameters.Add("VARCHAR_COLUMN", OleDbType.VarChar, 100, "VARCHAR_COLUMN").Value = stringVariable;
Если нам не нужна привязка параметра команды к определенному полю DataTable, то лучше всего не указывать размер вообще, так например если строка будет меньше указанной длинны Varchar, то добрый.Net фреймворк добавит в строку пробелов до указанной длины, испортив передаваемые на сервер данные.
Значение единичного поля читается методом ExecuteScalar()
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN = ?"; dbCommand.Parameters.Add("INT_COLUMN", OleDbType.Integer).Value = intVariable; int result = Convert.ToInt32(dbCommand.ExecuteScalar());
Надо особо отметить, что ExecuteScalar возвращает Object и если запрос не вернул вообще ничего, то результат будет null и конвертация в нормальный тип данных вылетит с ошибкой. Если возможна ситуация, когда в ответ мы ничего не получим, то надо делать так:
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN = ?"; dbCommand.Parameters.Add("INT_COLUMN", OleDbType.Integer).Value = intVariable; object resultObj = dbCommand.ExecuteScalar() int result = -1; // значение по умолчанию, означающее пустой результат if(resultObj != null) { result = Convert.ToInt32(dbCommand.ExecuteScalar()); }
3. Последовательное чтение из select-выборки строки за строкой
Чтение одной строки (несколько читаются в цикле);
OleDbCommand dbCommand = new OleDbCommand(select PERSON_ID, NAME, SURNAME from TEST_TABLE, dbConn); OleDbDataReader dbReader = dbCommand.ExecuteReader(); dbReader.Read(); string name = Convert.ToString(dbReader["NAME"]); string surname = Convert.ToString(dbReader["SURNAME"]); dbReader.Close();
4. Редкий случай. Автоматическое обновление таблицы в базе на основании изменений в DataTable (как правило редактируемой через визуальный интерфейс).
Необходимо прописать для DbAdapter четыре команды на каждый возможный случай - select, insert, update, delete.
DbAdapter.InsertCommand = new OleDbCommand("insert into TEST_TABLE (NAME, FAMIL, AGE) values (?, ?, ?)", dbConnection); dbAdapter.InsertCommand.Parameters.Add("NAME", OleDbType.VarChar, 100, "NAME"); dbAdapter.InsertCommand.Parameters.Add("FAMIL", OleDbType.VarChar, 100, "FAMIL"); // для типов данных с неизменной длинной указанная в команде длинна игнорируется dbAdapter.InsertCommand.Parameters.Add("AGE", OleDbType.Integer, 100, "AGE"); // добавляем команду обновления dbAdapter.UpdateCommand = new OleDbCommand("update TEST_TABLE set NAME = ?, FAMIL = ?, AGE = ? where ID = ?", dbConnection); dbAdapter.UpdateCommand.Parameters.Add("NAME", OleDbType.VarChar,100, "NAME"); dbAdapter.UpdateCommand.Parameters.Add("FAMIL", OleDbType.VarChar, 100, "FAMIL"); dbAdapter.UpdateCommand.Parameters.Add("AGE", OleDbType.Integer, 100, "AGE"); dbAdapter.UpdateCommand.Parameters.Add("ID", OleDbType.Integer, 100, "ID"); // добавляем команду удаления dbAdapter.DeleteCommand = new OleDbCommand("delete from TEST_TABLE where ID = ?", dbConnection); dbAdapter.DeleteCommand.Parameters.Add("ID", OleDbType.Integer, 100, "ID"); try { // переносит все изменения из DataTable в таблицу в базе данных dbAdapter.Update(table); } catch (Exception error) { MessageBox.Show("Ошибка при сохранении данных! " + error.Message); return; } MessageBox.Show("Изменения сохранены!");