Доступ к БД из приложений

Рассмотрим организацию  совместной работы СУБД и приложения, передающего СУБД команды и получающего  от СУБД данные. Как правило, для этой цели необходима специальная библиотека-посредник между СУБД и приложением. Одной из такого рода библиотек является Microsoft ADO.NET (ActiveX Data Objects). Это библиотека классов (входящая в состав .NET Framework), позволяющая .NET-приложению взаимодействовать с MS SQL Server, Oracle и другими СУБД через стандартные интерфейсы.

Основной сценарий взаимодействия приложения с СУБД выглядит следующим  образом:

1. Создание подключения. 

2. Создание команды, содержащей SQL-запрос.

3. Открытие подключения. 

4. Выполнение команды. 

5. Обработка ошибок

6. Закрытие подключения.

7. Обработка результатов.

Подключение к БД

Для подключения к MS SQL Server служит класс System.Data.SqlClient.SqlConnection;

Для подключения к Oracle – System.Data.OracleClient.OracleConnection;

Для подключения к какой-либо СУБД по протоколу ODBC –  System.Data.Odbc.OdbcConnection;

Для подключения к какому-либо источнику данных (в т.ч. текстовому файлу или файлу Excel) - System.Data.OleDb.OleDbConnection.

Дальнейшее рассмотрение касается взаимодействия с MS SQL Server. Наиболее важные члены класса System.Data.SqlClient.SqlConnection показаны на диаграмме и описаны в таблице.

 

Конструктор
SqlConnection()	Инициализирует новый экземпляр  класса SqlConnection.
SqlConnection(String)	Инициализирует новый экземпляр  класса SqlConnection, получив строку подключения
Свойства
ConnectionString	Возвращает или задает строку, используемую для открытия базы данных SQL Server
ConnectionTimeout	Получает время ожидания при попытке  установки подключения, по истечении  которого попытка подключения завершается  и создается ошибка
ServerVersion	Получает строку, которая содержит версию экземпляра SQL Server, к которому подключен клиент
State	Отображает состояние SqlConnection во время  последней выполненной операции
Методы
BeginTransaction()	Начинает транзакцию базы данных
BeginTransaction(IsolationLevel)	Начинает транзакцию базы данных с  указанным уровнем изоляции
Close ()	Закрытие подключения к базе данных. Рекомендуется использовать этот метод  для закрытия любого открытого подключения
Dispose()	Освобождает все ресурсы, используемые объектом
Open ()	Открывает подключение к базе данных со значениями свойств, определяемыми  объектом ConnectionString

 

Практически работа с классом System.Data.SqlClient.SqlConnection происходит обычно следующим образом (пример на C#):

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

      string connectionString = @"data source=.\sqlexpress; initial catalog = study; integrated security = true;";

 

        //создание объекта System.Data.SqlClient.SqlConnection

        System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connectionString);

 

        //открытие подключения

        cn.Open();

 

        //выполнение команд

        //...

 

        //закрытие подключения

        cn.Close();

 

Примечание. В C#, в отличие от C++, имя пространства имён (namespace) присоединяется к имени типа при помощи точки. Сравните: System::Data::SqlClient:: SqlConnection и System.Data.SqlClient.SqlConnection. Для указания используемых по умолчанию пространств имён в C++ применяется директива using namespace NNN; в C# - using NNN.

Строка подключения

Строка подключения (Connection String) – набор всей необходимой для подключения информации, представленный в виде последовательности пар "ключевое слово — значение", разделенных символами точки с запятой. В приведенной ниже таблице представлены некоторые допустимые имена для значений ключевых слов в Connection String.

Ключевое слово	Значение по умолчанию	Описание
Async	false	Когда задано значение true, поддерживаются асинхронные операции. Распознаваемые значения: true, false, yes и no.
Connect Timeout -или- Connection Timeout	15	Продолжительность времени ожидания подключения к серверу (в секундах) перед прекращением попытки подключения  и генерацией ошибки.
Data Source -или- Server -или- Address -или- Addr -или- Network Address	Отсутствует	Имя или сетевой адрес экземпляра SQL Server, к которому осуществляется подключение. При задании локального экземпляра можно использовать имя (local).
Initial Catalog -или-  Database	Отсутствует	Имя базы данных.
Integrated Security -или- Trusted_Connection	false	Если задано значение false, в подключении должны быть заданы также параметры User ID и Password. Если задано значение true, для проверки подлинности используются текущие учетные данные Windows. Распознаваемые значения: true, false, yes, no и sspi (настоятельно рекомендуется), что  эквивалентно true.
Password -или-  Pwd	Отсутствует	Пароль для входа в SQL-сервер
User ID	Отсутствует	Учетная запись входа в SQL Server

 

Примеры строк подключения

       

//подключение к экземпляру SQL Server с именем sqlexpress; БД myDB; аутентификация Windows

        string connectionString = @"Data Source = localhost\sqlexpress; Initial Catalog = myDB; Integrated Security = True;";

      

 

 //подключение к неименованному экземпляру SQL Server на удалённом сервере; БД myDB2; аутентификация Sql Server по логину и паролю

        string connectionString = @"Data Source = 192.168.137.14; Initial Catalog = myDB2; User ID = MyUserID; Password = pa$$w0rd";

 

Примечание. Символ «@» перед открывающей кавычкой в языке C# означает, что строка должна интерпретироваться компилятором дословно: в частности, символы «\» (backslash) и «новая строка» не интерпретируются специальным образом, а просто сохраняются в строке.

Команды

За выполнение команды  MS SQL Server в ADO.NET отвечает класс System.Data.SqlClient.SqlCommand.

Конструктор
SqlCommand()	Инициализирует новый экземпляр  класса SqlCommand
SqlCommand(String)	Инициализирует новый экземпляр  класса SqlCommand с текстом запроса
SqlCommand(String, SqlConnection)	Инициализирует новый экземпляр  класса SqlCommand текстом запроса и  подключением SqlConnection
SqlCommand(String, SqlConnection, SqlTransaction)	Инициализирует новый экземпляр  класса SqlCommand с текстом запроса, подключением SqlConnection и транзакцией SqlTransaction
Свойства
CommandText	Возвращает или задает инструкцию Transact-SQL, имя таблицы или хранимую  процедуру, выполняемую для источника  данных
CommandTimeout	Возвращает или задает время ожидания перед завершением попытки выполнить  команду и созданием ошибки
CommandType	Возвращает или задает значение, указывающее, как будет интерпретироваться свойство CommandText (команда SQL или имя хранимой процедуры)
Connection	Получает или задает объект SqlConnection, используемый данным экземпляром класса SqlCommand
Parameters	Получает коллекцию параметров команды – объект SqlParameterCollection
Transaction	Получает или задает объект SqlTransaction, в рамках которого выполняется команда SqlCommand.
Методы
CreateParameter ()	Создает новый экземпляр параметра команды – объекта SqlParameter
ExecuteNonQuery ()	Выполняет для подключения инструкцию Transact-SQL и возвращает количество задействованных  в инструкции строк
ExecuteReader()	Отправляет CommandText в Connection и строит SqlDataReader
ExecuteScalar ()	Выполняет запрос и возвращает первый столбец первой строки результирующего  набора, возвращаемого запросом. Дополнительные столбцы и строки игнорируются
ExecuteXmlReader ()	Отправляет CommandText в Connection и строит объект XmlReader

Создание команды

Наиболее удобна перегрузка конструктора, принимающая текст  команды и объект подключения (поскольку  и то, и другое необходимо):

        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select * from T", cn);

 

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

cn.Open();

System.Data.SqlClient.SqlTransaction tran = cn.BeginTransaction();

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select * from T",

    cn, tran);

 

Объект System.Data.SqlClient.SqlTransaction создаётся не при помощи конструктора, а путём вызова метода BeginTransaction() класса System.Data.SqlClient.SqlConnection. При этом подключение должно быть уже открыто.

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

tran.Commit();

//или

tran.Rollback();

 

Если же явная транзакция не создаётся, то каждая SQL-инструкция в рамках команды выполняется как отдельная автоматическая транзакция. Вызывать Commit() или Rollback() в этом случае не требуется.

Выполнение команд

Способ выполнения зависит  от ожидаемого результата:

● Если ожидается получение результата в виде таблицы, то используется метод ExecuteReader();

● Если ожидается получение скалярного результата, то используется метод ExecuteScalar();

● Если ожидается получение XML-документа, то используется метод ExecuteXmlReader ();

● Если команда не возвращает значения, то используется метод ExecuteNonQuery ().

Выборка единственного (скалярного) значения

Метод ExecuteScalar() возвращает значение типа object, которое упаковывает настоящее значение результата. Для распаковки применяется операция приведения типа к простому типу (чаще всего int, double, string или Date).

Пример.

string connectionString = @"data source=.\sqlexpress; initial catalog = NorthWind; integrated security = true;";

System.Data.SqlClient.SqlConnection cn =

    new System.Data.SqlClient.SqlConnection(connectionString);

System.Data.SqlClient.SqlCommand cmd =

    new System.Data.SqlClient.SqlCommand("select count(*) from Orders",

        cn);

cn.Open();

//выполнение скалярной  команды

int result = (int)cmd.ExecuteScalar();

cn.Close();

//вывод результата на  консоль

System.Console.WriteLine("Количество заказов: {0}", result);

 

Взаимодействие объектов в данном примере схематически показано на диаграмме.

Выборка набора результатов

Метод ExecuteReader() класса SqlCommand возвращает объект типа SqlDataReader, который позволяет просматривать строки и столбцы набора результатов в режиме только чтения в прямом направлении.

Свойства
HasRows	Возвращает значение, указывающее, содержит ли объект SqlDataReader хотя бы одну строку
Item[String]	Индексатор. Возвращает значение заданного столбца при указании имени столбца
FieldCount	Возвращает количество столбцов в текущей строке
Методы
Read()	Перемещает SqlDataReader к следующей записи. Возвращает false, если следующей записи не существует, иначе – true

 

Работать с объектом SqlDataReader следует до закрытия подключения. Если получена хотя бы одна строка (на что указывает истинность значения свойства HasRows), то для получения очередной строки можно вызывать метод Read(), а для чтения значений в строке – индексатор.

Пример использования SqlDataReader.

string connectionString = @"data source=.\sqlexpress; initial catalog = Study; integrated security = true;";

//создание объекта System.Data.SqlClient.SqlConnection

System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connectionString);

 

//открытие подключения

cn.Open();

 

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select StudentID, FIO, GroupID from Student", cn);

 

//выполнение команды

System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

 

if (reader.HasRows) {

    //чтение очередной строки

    while (reader.Read()) {

        //вывод значений на консоль

        System.Console.WriteLine("{0} {1} {2}", reader["StudentID"], reader["FIO"], reader["GroupID"]);

    }

}

 

//закрытие подключения

cn.Close();

 

При вызове индексатора (в данном примере – reader["StudentID"]) ему передаётся номер или (чаще) имя столбца, возвращённого запросом. Возвращаемое значение представляет собой объект, который следует распаковывать, как и в случае с возвращаемым значением ExecuteScalar(), например:

int StudentID = (int)reader["StudentID"];

string FIO = (string)reader["FIO"];

Изменение данных в БД

Метод ExecuteNonQuery() класса SqlCommand возвращает количество строк, затронутых последней инструкцией команды. Как правило, при помощи данного метода выполняются DML- и DDL-инструкции.

Пример.

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

string connectionString = @"data source=.\sqlexpress; initial catalog = tempdb; integrated security = true;";

//создание объекта System.Data.SqlClient.SqlConnection

System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connectionString);

 

//открытие подключения

cn.Open();

 

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("insert into T(n,m) values (10, 10)",

    cn);

 

//выполнение команды

int cnt = (int)cmd.ExecuteNonQuery();

//закрытие подключения

 

cn.Close();

System.Console.WriteLine("Вставлено строк: {0}", cnt);

Параметры команд

В предыдущем примере значения, передаваемые с командой, были встроены в саму команду. Если значения получены из внешнего источника (и безопасность значений не гарантирована), то этого  делать ни в коем случае нельзя, поскольку  путём простой вставки строки в команду легко изменить её смысл. Вместо этого параметры следует  передавать СУБД отдельно – для  этого в классе SqlCommand имеется коллекция параметров Parameters.

Обычно параметры добавляются  сразу со значением:

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("insert into T(n,m) values (@n, @m)", cn);

cmd.Parameters.AddWithValue("n", 11);

cmd.Parameters.AddWithValue("m", 11);

 

//выполнение команды

int cnt = (int)cmd.ExecuteNonQuery();

В этом случае инструкция передаётся SQL Server следующим образом:

exec sp_executesql N'insert into T(n,m) values (@n, @m)',N'@n int,@m int',@n=12,@m=12

 

При этом изменить смысл команды  невозможно.

Обработка ошибок

Необходимая часть работы с БД – рассмотрение  возможных ошибок и программирование реакции на них. Большинство ошибок при выполнении DQL- и DML-инструкций сводится к следующим видам:

● ошибки подключения и  аутентификации;

● недостаток привилегий;

● неверная структура инструкции;

● нарушение ограничений  целостности данных;

● конфликты параллельно выполняемых транзакций.

Естественно, в первую очередь  следует заботиться о предотвращении ошибок. Если ошибка всё же возникла, то, в зависимости от серьёзности ошибки, произойдет следующее:

● для ошибок уровня серьёзности менее 11 СУБД передаёт информационное сообщение, доступное через событие InfoMessage объекта подключения;

● для ошибок уровня от 11 до 20 генерируется ошибка, но подключение  не закрывается. ADO.NET создаёт исключение, которое должно быть обработано;

● для ошибок уровня более 20 генерируется ошибка, и подключение  закрывается.

Рассмотрим несколько  типичных ошибок и приёмы их обработки.

Ошибки подключения и аутентификации

Если параметры строки подключения неверны либо сервер БД по какой-либо причине недоступен, то при выполнении метода Open() класса SqlConnection возникнет исключение типа SqlException.

try {

    SqlConnection cn = new SqlConnection(@"Data Source = wrong name; Initial Catalog = master; Integrated Security = true; Connect Timeout = 5");

    cn.Open();

}

catch (SqlException ex) {

    Console.WriteLine("Не удалось подключиться к БД");

}

 

Если же сервер отказал  в авторизации, то также возникнет  исключение SqlException с сообщением «Login failed for user …».

В данном примере перехватываются любые исключения SqlException, и пользователю выдаётся сообщение общего характера об ошибке подключения.

Если неверен формат самой строки подключения, то исключение типа ArgumentException возникнет в момент присваивания неверной строки свойству ConnectionString объекта SqlConnection.

Нарушение ограничений  целостности данных

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

При попытке вставки строки в таблицу студент с неверным значением внешнего ключа возникает  ошибка с сообщением «The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Student_Group". The conflict occurred in database "Study", table "dbo.Groups", column 'GroupID'. The statement has been terminated».

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

SqlConnection cn = new SqlConnection(@"Data Source = localhost\sqlexpress; Initial Catalog = study; Integrated Security = true; ");

try {

    cn.Open();

}

catch (SqlException ex) {

    Console.WriteLine("Не удалось подключиться к БД");

}

try {

    SqlCommand cmd = new SqlCommand(@"insert into Student(FIO, GroupID) values('John', 'Wrong Group')", cn);

    cmd.ExecuteNonQuery();

    cn.Close();

}

catch (SqlException ex) {

    if (ex.Message.Contains("FK_Student_Group")) {

        //обработка ошибки внешнего ключа

        Console.WriteLine("Указан неверный код группы");

    }

    else {

        //обработка других возможных ошибок

    }

    cn.Close();

}

 

Заметим, что закрытие подключения  следует выполнить в любом  случае.