Чтение и запись данных на MS SQL Server с помощью OLE DB и ADO.NET

Временами у программистов возникает жгучее желание прочитать что-то из базы данных. Программисты становятся нервными и раздражительными, теряют сон и лихорадочно тыкают пальцами в клавиатуру. Ради всеобщего блага и мира во всем мире рассмотрим несложную работу с базой данных из C# с помощью ADO.NET и OLE DB. Хотя данный механизм поддерживает разные базы данных вроде Oracle, здесь и сейчас будем использовать MS SQL Server.

Две основных задачи при работе с базой данных

1. Считать данные по select в DataTable, для дальнейшей обработки или вывода на экран

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 хранятся типы данных базы данных.

1. Считать данные по select в DataTable, для дальнейшей обработки или вывода на экран

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[0].Value = "У всякой проблемы всегда есть решение — про­стое, удобное и, конечно, ошибочное";
someDbComm.Parameters[1].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("Изменения сохранены!");

Комментарии

Чтение и запись данных на MS SQL Server с помощью OLE DB и ADO.NET — Комментарии (7)

  1. OleDbConnection нужно для Command открыть сначала. Было бы хорошо, если бы в коде присутствовала эта строчка. Теперь получаю ошибку:
    ------------------
    An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

    Additional information: Incorrect syntax near '?'.
    ------------------
    Что там с параметром не так - непонятно! Лучше я вернусь к прежнему виду программ, где CommandText собирается прямо в программе. Возможно, он не нравится кому-то, но он простой и работоспособный!

    • Эта строчка присутствует в самом первом примере из статьи, дальше она считается самоочевидной - как вы собираетесь выполнять команду к БД не открыв соединение? Все статьи здесь рассчитаны на минимальное осмысление примеров, а не бездумное копирование.

      Не видя кода про ошибку ничего сказать невозможно, но судя по тексту ошибки можно предположить вы используете System.Data.SqlClient, а не System.Data.OleDb. Формат написания параметров в OleDB и Sql клиент различается, знаки вопроса в SqlClient не работают - подробнее например здесь. Статья посвящена исключительно OleDB.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *


*

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>