Добавление значений в таблицу sql. Sql-запросы на вставку, модификацию и удаление данных. Примеры реализации запросов

Временами у программистов возникает жгучее желание прочитать что-то из базы данных. Программисты становятся нервными и раздражительными, теряют сон и лихорадочно тыкают пальцами в клавиатуру. Ради всеобщего блага и мира во всем мире рассмотрим несложную работу с базой данных из 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()

Надо особо отметить, что 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("Изменения сохранены!");

Общий синтаксис:

а) вставка одной новой строки в таблицу:

INSERT <таблица> [(столбцы)]

VALUES (значения)

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

INSERT <таблица> [(столбцы)]