Руководство по работе с БД Firebird с использованием библиотеки ADO .Net 2.0
         

Автоматическое управление транзакциями


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

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

auto_commit

Допустимые значения: true, false

Значение по умолчанию: false

Включает режим автоматического управления транзакциями. По умолчанию false

auto_commit_level

Допустимые значения: Read Comitted, Repeatable Read, Snapshot

Значение по умолчанию: Repeatable Read

Задает уровень изоляции автоматических транзакций. По умолчанию Repeatable Read. В ADO .Net уровни изоляции транзакций определены в перечислении IsolationLevel

auto_commit_ddl

Значение по умолчанию: 0

Допустимые значения:

0 - Поддержка DDL запросов отключена



1- Выполнять DDL запрос в выделенной транзакции. Игнорируется если установлено свойство auto_commit

2- Выполнять CommitRetaining после DDL запроса. Игнорируется если установлено свойство auto_commit

Определяет режим выполнения DDL запросов

auto_commit_ddl_level

Допустимые значения: Read Comitted, Repeatable Read, Snapshot

Значение по умолчанию: Read Commited

Задает уровень изоляции автоматических транзакций для DDL запросов.

ПРИМЕЧАНИЕDDL запросы (CREATE/ALTER/DROP) позволяют управлять сущностями базы данных: таблицами, триггерами, хранимые процедуры и т.п.

Следующий пример демонстрирует включение режима автоматического управления транзакциями с уровнем изоляции RepeatableRead:

public void AutoCommitSessionTest() { OleDbConnectionStringBuilder builder = ConnectionProvider.GetConnectionStringBuilder(); builder.Add("auto_commit", true); builder.Add("auto_commit_level", Convert.ToInt32(System.Data.IsolationLevel.RepeatableRead));

OleDbConnection con = new OleDbConnection(builder.ToString()); con.Open();

OleDbCommand cmd = new OleDbCommand("select count(*) from employee", con); Assert.IsTrue((int)cmd.ExecuteScalar() > 0);

con.Close(); }



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


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

SAVEPOINT save_point_name

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

ROLLBACK TO SAVEPOINT save_point_name или COMMIT TO SAVEPOINT save_point_name

В следующем примере определяется одна точка сохранения между двумя командами:

public void SavePointTest() { OleDbConnection con1 = ConnectionProvider.CreateConnection(); con1.Open();

OleDbTransaction transaction = con1.BeginTransaction();

OleDbCommand cmd_insert = new OleDbCommand( "insert into country (country,currency) values (:country,:currency)", con1, transaction); cmd_insert.Parameters.AddWithValue(":country", "Russia"); cmd_insert.Parameters.AddWithValue(":currency", "Ruble"); Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

new OleDbCommand("SAVEPOINT AFTER_INSERT_POINT", con1, transaction).ExecuteNonQuery();

//delete country in using 3-level internal transaction context

OleDbCommand cmd_delete = new OleDbCommand( "delete from country where country=?", con1, transaction); cmd_delete.Parameters.AddWithValue("?", "Russia"); Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

new OleDbCommand("ROLLBACK TO SAVEPOINT AFTER_INSERT_POINT", con1, transaction).ExecuteNonQuery();

//check what record was not deleted but existing in database

OleDbCommand cmd_check = new OleDbCommand( "select count(*) from country where country=?", con1, transaction);

cmd_check.Parameters.AddWithValue("?", "Russia"); Assert.AreEqual(1, cmd_check.ExecuteScalar());

transaction.Rollback(); con1.Close(); }



Класс OleDbException


Для обработки ошибок Ole Db в Ado .Net есть свой класс OleDbException, который в отличие от стандартного класса Exception предоставляет дополнительную информацию:

Код ошибки OleDb

Коллекцию ошибок OleDbError

В общем случае обработка ошибок выглядит следующим образом:

try { OleDbConnection con = new OleDbConnection("Provider=LCPI.IBProvider;Data Source=Empty;"); con.Open(); con.Close(); } catch (OleDbException ole_ex) { foreach (OleDbError err in ole_ex.Errors) { Console.WriteLine("Message: " + err.Message); Console.WriteLine("Native Error: " + err.NativeError); Console.WriteLine("Source: " + err.Source); Console.WriteLine("SQL State : " + err.SQLState); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); }

В примере умышленно пропущены обязательные параметры строки подключения User ID и Password. В результате выполнения данного кода будет сгенерировано Ole Db исключение. В коллекции ошибок будет содержаться два объекта OleDbError. На экран будет выведено следующее:

Message: Неопределено обязательное свойство инициализации "Password". Native Error: 0 Source: LCPI.IBProvider.2 SQL State : Message: Неопределено обязательное свойство инициализации "User ID". Native Error: 0 Source: LCPI.IBProvider.2 SQL State :



Компонент для управления свойствами


Решение было оформлено в фиде компонента, который поставляется в виде исходных текстов вместе с примерами к статье. Он реализован в виде словаря OleDbProperties индексированного по названию свойства, в котором присуствует фабричный метод GetPropertiesFor() (термин из []):

Фабричный метод обращается к поставщикам свойств, унаследованных от PropertyProviderBase. В компоненте реализовано три поставщика для объектов OleDbCommand, OleDbConnection и OleDbDataReader (см. )

Каждый из поставщиков возвращает свой набор свойств, соответствующий группам Ole Db:

поставщик для OleDbConnection возвращает свойства из наборов Data Source Information, Data Source и Initialization

поставщик для OleDbCommand и поставщик для OleDbDataReader возвращают свойства из группы Rowset с тем различием, что для команды можно установить свойства, а для OleDbDataReader-а их можно только читать.

Полный список свойств из группы Data Source Information, поддерживаемых IBProvider-ом доступен по этому адресу

Список свойств группы Initialization, которые могут быть заданны через параметры строки подключения здесь

Свойства набора рядов из группы Rowset описаны здесь



Пул подключений


Пул подключений позволяет более эффективно управлять таким ресурсом, как соединение с базой данных. Когда после завершения очередной операции с базой данных вы вызываете метод OleDbConnection.Close(), соединение с базой отправляется в пул и остается открытым там ещё некоторое время. По умолчанию это время - 60 секунд. Когда клиент инициирует новое соединение с базой, провайдер сначала запрашивает его из пула и только потом, если такого не нашлось, создает новый ресурс. Подходит соединение или нет, определяется по точному соответствию свойства ConnectionString с учетом регистра.

Настройка пула подключений осуществляется при помощи параметра строки подключения «Ole Db Services». Значение параметра – это битовая комбинация следующих флагов:



Флаг

Знач.

OLE DB сервисы

DBPROPVAL_OS_ENABLEALL -1 Используются все сервисы
DBPROPVAL_OS_RESOURCEPOOLING 1 Ресурсы должны помещаться в пул
DBPROPVAL_OS_TXNENLISTMENT 2 При необходимости сессии должны быть автоматически подключены к глобальной транзакции
DBPROPVAL_OS_AGR_AFTERSESSION 8 Поддержка операций за пределами сессии
DBPROPVAL_OS_CLIENTCURSOR 4 Поддержка клиентских курсоров на уровне OLE DB Services, если их не поддерживает управляемый провайдер
DBPROPVAL_OS_DISABLEALL 0 Все сервисы отключены

В примерах к статье класс OleDbServicesValues содержит константы для всех этих флагов. Для комбинации флагов можно использовать операцию побитового исключения (& ~) констант невостребованных сервисов из константы DBPROPVAL_OS_ENABLEALL.

Следующий пример тестирует производительность при использовании различных Ole Db сервисов:

public void OleDbServicesTest() { const int connection_count = 50;

//хранит результат работы по всем операциям

Dictionary<string, double> timeResults = new Dictionary<string, double>();

OleDbConnectionStringBuilder builder = ConnectionProvider.GetConnectionStringBuilderFromUDL();

//OLE DB SERVISES = Все сервисы включены

builder.OleDbServices = OleDbServicesValues.EnableAll; timeResults.Add( String.Format("OLE DB Services=EnableAll ({0})", builder.OleDbServices), DoConnections(builder, connection_count));


//OLE DB SERVISES = все сервисы отключены builder.OleDbServices = OleDbServicesValues.DisableAll; timeResults.Add( String.Format("OLE DB Services=DisableAll ({0})", builder.OleDbServices), DoConnections(builder, connection_count));

//OLE DB SERVICES = все включено, за исключением клиентских курсоров

//и работы за пределами сессии builder.OleDbServices = (OleDbServicesValues.EnableAll & ~ OleDbServicesValues.ClientCursor & ~ OleDbServicesValues.AggregationAfterSession); timeResults.Add( String.Format("OLE DB Services=\n"+ "\tEnableAll & \n" + "\t~ClientCursor & \n" + "\t~AggregationAfterSession ({0})", builder.OleDbServices), DoConnections(builder, connection_count));

foreach (string key in timeResults.Keys) Console.WriteLine(key + ". Seconds elapsed: " + timeResults[key]); }

/// <summary>

/// Открывает и закрывает много подключений, а так же стартует транзакции

/// </summary>

/// <param name="builder"></param>

/// <param name="cnt_connection"></param>

/// <returns>Сколько секунд выполнялось</returns> private double DoConnections(OleDbConnectionStringBuilder builder, int cnt_connection) { DateTime startTime = DateTime.Now;

for (int i = 1; i <= cnt_connection; i++) { OleDbConnection con = new OleDbConnection(builder.ToString()); con.Open(); OleDbTransaction trans = con.BeginTransaction(); trans.Commit(); con.Close(); }

return DateTime.Now.Subtract(startTime).TotalSeconds; }

Наиболее производительным будет вариант при использовании только пула ресурсов и автоматического подключения транзакций – это соответствует битовой маске OleDbServicesValues.EnableAll & ~OleDbServicesValues.ClientCursor &~ OleDbServicesValues.AggregationAfterSession (параметр “OLE DB Services =-13”) .

Чуть медленнее будет работать при использовании всех Ole Db сервисов. И наконец производительность значительно падает (примерно в 20 раз) при полностью выключенных сервисах.

Подробнее об управлении пулом подключений в MSDN - здесь.


Событие InfoMessage


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



Свойства объектов Ole Db


Раньше в библиотеке ADO у объектов Connection, Command и Recordset существовал набор свойств Properties, при помощи которого можно было устанавливать и считывать свойства соответствующих Ole Db интерфейсов: IDBProperties, ISessionProperties, ICommandProperties, IRowsetIndex. По непонятным мне причинам разработчики ADO .Net исключили чтение/запись свойств напрямую, лишив нас возможности получения расширенной информации об источнике данных, а так же тонкой настройки поведения провайдера.

В результате исследования библиотеки ADO .Net было найдено решение, которое позволило применять следующий код:

public void ReadPropertiesTest() { OleDbConnection con = ConnectionProvider.CreateConnection(); con.Open();

//OleDbConnection properties

OleDbProperties properties = OleDbProperties.GetPropertiesFor(con); Assert.AreNotEqual(0, properties.Count); PrintProperties(properties);

//OleDbCommand properties

OleDbCommand cmd = new OleDbCommand("select * from country", con, con.BeginTransaction());

properties = OleDbProperties.GetPropertiesFor(cmd); Assert.AreNotEqual(0, properties.Count); PrintProperties(properties);

//Property from Rowset ole db group can be changed

properties["Use Bookmarks"].Value = true; Assert.IsTrue(Convert.ToBoolean(properties["Use Bookmarks"].Value));

//OleDbDataReader properties

using (OleDbDataReader rdr = cmd.ExecuteReader()) { properties = OleDbProperties.GetPropertiesFor(rdr); PrintProperties(properties); }

con.Close(); }

private void PrintProperties(OleDbProperties properties) { foreach (OleDbProperty prop in properties.Values) Console.WriteLine((prop.Required ==true ? "[r] " : "") + prop.Name + "=" + prop.ValueString);

}



появилось новое пространство имен


В Net Framework 2 появилось новое пространство имен System.Transaction, которое предоставляет поддержку распределенных транзакций. IBProvider поддерживает распределенные транзакции за счет расширения COM+ Microsoft Transaction Server (MTS). Распределенные транзакции позволяют нам преодолеть границы базы данных и, к примеру, выполнять действия с различными БД в контексте одной распределенной транзакции.

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

public void TransactionScopeTest() { //TransactionScrope автоматически свяжет локальные транзакции с распределенной

//В данном контексте будет две локальных транзакции на каждое подключение и одна

//распределенная.

using (TransactionScope scope = new TransactionScope()) { //автоматически будет создана локальная транзакция

OleDbConnection con1 = ConnectionProvider.CreateConnection(); con1.Open();

//insert command

OleDbCommand cmd_insert = new OleDbCommand( "insert into country (country,currency) values (:country,:currency)",con1); cmd_insert.Parameters.AddWithValue("country", "Russia"); cmd_insert.Parameters.AddWithValue("currency", "Rouble"); Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

//автоматически будет создана ещё одна локальная транзакция OleDbConnection con2 = ConnectionProvider.CreateConnection(); con2.Open();

cmd_insert.Connection = con2; cmd_insert.Parameters["country"].Value = "Latvia"; cmd_insert.Parameters["currency"].Value = "Lat"; Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

//Фиксация распределенной транзакции //Для всех локальных транзакций будет вызван метод Commit()

scope.Complete();

//Если ранее распределенная транзакция не была завершена для всех локальных //транзакций будет вызван метод Rollback при вызове IDispose.Dispose()



}

//TransactionScope будет использовать локальную транзакцию

//т.к. все команды выполняются в одном контексте

using (TransactionScope scope = new TransactionScope()) { OleDbConnection con1 = ConnectionProvider.CreateConnection(); con1.Open();

//проверяем, что в предыдущем контексте были добавлены записи

OleDbCommand cmd_select = new OleDbCommand( "select count(*) from country where country=:country", con1); cmd_select.Parameters.Add("country", OleDbType.BSTR);

//удаляем записи OleDbCommand cmd_delete = new OleDbCommand( "delete from country where country=:country", con1); cmd_delete.Parameters.Add("country", OleDbType.BSTR);

cmd_select.Parameters["country"].Value = "Russia"; cmd_delete.Parameters["country"].Value = "Russia"; Assert.AreEqual(1, cmd_select.ExecuteScalar()); Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

cmd_select.Parameters["country"].Value = "Latvia"; cmd_delete.Parameters["country"].Value = "Latvia"; Assert.AreEqual(1, cmd_select.ExecuteScalar()); Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

scope.Complete(); //commit

} //rollback }


Управление транзакциями через SQL


Помимо управления транзакциями через Ole Db интерфейсы, IBProvider осуществляет специальную поддержку SQL – запросов для управления транзакциями: SET TRANSACTION, COMMIT, COMMIT RETAIN, ROLLBACK и для Firebird 2 – ROLLBACK RETAIN. Данный метод позволяет указывать специфические параметры контекста транзакции, которые не стандартизированы в OLE DB, но поддерживаются в Firebird. Следующий пример демонстрирует управление транзакциями через SQL:

public void SQLTransactionTest() { OleDbConnection con1 = ConnectionProvider.CreateConnection(); con1.Open();

OleDbCommand cmd = new OleDbCommand( "SET TRANSACTION READ ONLY WAIT ISOLATION LEVEL READ COMMITTED", con1); cmd.ExecuteNonQuery();

cmd.CommandText = "select count(*) from employee"; Assert.AreNotEqual(0, cmd.ExecuteScalar());

//подтверждение транзакции с последующим использованием её контекста

cmd.CommandText = "COMMIT RETAIN"; cmd.ExecuteNonQuery();

cmd.CommandText = "select count(*) from employee"; Assert.AreNotEqual(0, cmd.ExecuteScalar());

cmd.CommandText = "COMMIT"; cmd.ExecuteNonQuery();

con1.Close(); }



Уровни изоляции транзакций


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

IBProvider поддерживает три уровня изоляции транзакций: Read Committed, Repeatable Read и Snapshot. Чтобы задать уровень изоляции для транзакции, необходимо в метод OleDbConnection.BeginTransaction() передать допустимое значение из перечисления System.Data.IsolationLevel.



Вложенные транзакции


Одной из замечательных возможностей, которые поддерживает IBProvider - это использование вложенных транзакций. Уровень вложенности транзакций не ограничен. Для того чтобы включить использование вложенных транзакций, необходимо установить свойство инициализации nested_trans = true. Следующий пример демонстрирует применение вложенных транзакций:

public void InternalTransactionTest() { OleDbConnectionStringBuilder builder = ConnectionProvider.GetConnectionStringBuilderFromUDL(); //включить вложенные транзакции

builder.Add("nested_trans", true);

OleDbConnection con1 = new OleDbConnection(builder.ToString()); con1.Open();

//основная транзакция

OleDbTransaction trans = con1.BeginTransaction();

//добавить новую запись

OleDbCommand cmd_insert = new OleDbCommand( "insert into country (country,currency) values (:country,:currency)", con1, trans);

cmd_insert.Parameters.AddWithValue(":country", "Russia"); cmd_insert.Parameters.AddWithValue(":currency", "Ruble"); Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

//запустить вложенную транзакцию

OleDbTransaction internal_transaction = trans.Begin();

//запустить вложенную транзакцию внутри вложенной

OleDbTransaction internal_transaction2 = internal_transaction.Begin();

//удалить запись во вложенно транзакции третьего уровня

OleDbCommand cmd_delete = new OleDbCommand( "delete from country where country=?", con1, internal_transaction2); cmd_delete.Parameters.AddWithValue("?", "Russia"); Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

//откатить вложенную транзакцию третьего уровня

internal_transaction2.Rollback();

//проверить что запись не была удалена в тразакции второго уровня

OleDbCommand cmd_check = new OleDbCommand( "select count(*) from country where country=?", con1, internal_transaction);

cmd_check.Parameters.AddWithValue("?", "Russia"); Assert.AreEqual (1, cmd_check.ExecuteScalar());

//удалить запись

cmd_delete.Transaction = internal_transaction; Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

//зафиксировать изменения

internal_transaction.Commit();

//проверить в основной транзакции что запись удалена из БД

cmd_check.Transaction = trans; Assert.AreEqual(0, cmd_check.ExecuteScalar());

trans.Commit(); con1.Close(); }