Использование «LCPI ADO.NET Data Provider for OLE DB» с Firebird SQL Server

---

Введение

В этом документе будут рассмотрены базовые вопросы использования «LCPI ADO.NET Data Provider for OLE DB», созданного для устранения ограничений «стандартного ADO.NET провайдера для OLE DB» из .NET Framework и полного раскрытия технологического потенциала Firebird SQL Server и IBProvider-a.

Большая часть этой статьи применима и к InterBase SQL Server.

Подготовка системы

Visual Studio

Для создания кода этой статьи будет использоваться Visual Studio 2013 Community Edition.

Firebird

Для основного набора примеров будет использоваться Firebird 2.5. В некоторых случаях – FB 3.0.

Позаботьтесь о том, чтобы в System32 и SysWOW64 находились модули серверного клиента – fbclient.dll.

Тестовые базы данных

Для основного набора примеров будет использоваться стандартная база данных «employee.fdb» из дистрибутива Firebird 2.5.

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

Установка ADO.NET провайдера

1. Скачиваем EXE-инсталлятор ADO.NET провайдера с сайта www.ibprovider.com.
2. Запускаем.
3. Выбираем «установить все».
4. Указываем основные сборки для .NET Runtimes – 3.5 и 4.5.1.

5. Жмем «Далее» вплоть до страницы запуска установки. Запускаем установку.

Установка IBProvider-а

1. Скачиваем MSI-инсталляторы IBProvider-a для 32-битной и 64-битной Windows.
2. Устанавливаем оба пакета с использованием конфигурации по-умолчанию.

Подготовка проекта

Для всех примеров будет использоваться консольное приложение на C# для FW 4.5.1.

Добавление ссылок на сборки ADO.NET провайдера

В проект нужно будет добавить ссылки на сборки ADO.NET провайдера. Есть несколько способов это сделать.

Первый способ. Через UI Visual Studio.

1. Открываем меню со свойствами проекта и выбираем «Add->Reference»:

2. Находим и выбираем сборки:
   • «LCPI ADO.NET Data Provider for OLE DB [NET 4.5.1]»
   • «LCPI Instrumental Library for .NET 4.5.1»

Второй способ. Через прямое редактирование csproj файла. Можно подключать сборки с учетом конфигурации проекта:

<Reference Include="lcpi.data.oledb.net4_5_1.debug, Version=1.0.0.2412, Culture=neutral, PublicKeyToken=ff716095e8002e7e, processorArchitecture=MSIL"
           Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' " />

<Reference Include="lcpi.data.oledb.net4_5_1, Version=1.0.0.2412, Culture=neutral, PublicKeyToken=ff716095e8002e7e, processorArchitecture=MSIL"
           Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' " />

<Reference Include="lcpi.lib.net4_5_1.debug, Version=1.0.0.1174, Culture=neutral, PublicKeyToken=ff716095e8002e7e, processorArchitecture=MSIL"
           Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' " />

<Reference Include="lcpi.lib.net4_5_1, Version=1.0.0.1174, Culture=neutral, PublicKeyToken=ff716095e8002e7e, processorArchitecture=MSIL"
           Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' " />

Третий способ. Ну и последний, пожалуй, самый простой способ – подключить к проекту NUGET-пакет «lcpi.data.oledb». Пакет можно найти на www.nuget.org.

Пространства имен

Компоненты ADO.NET провайдера находятся в пространстве имен «lcpi.data.oledb». Добавим в начало cs-файлов следующую строку:

using lcpi.data.oledb;

Дополнительно, для удобства, нужно открыть доступ к пространству имен с общими для всех ADO.NET провайдеров конструкциями – «System.Data»:

using System.Data;

Работа с подключением к базе данных

Управление подключением осуществляется с помощью объекта класса OleDbConnection.

Подключение базы данных

Для подключения к базе данных нужно:

1. Создать объект OleDbConnection.
2. Указать строку подключения.
3. Вызвать метод Open.

static void Test_001()
{
 const string cn_str=
  "provider=LCPI.IBProvider.3;"
  +"location=localhost:d:\\database\\employee.fdb;"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 var cn=new OleDbConnection();

 cn.ConnectionString=cn_str;

 cn.Open();
}//Test_001

Строку подключения можно передавать прямо в конструктор класса OleDbConnection:

var cn=new OleDbConnection(cn_str);

Параметры приведенной строки подключения разделяются на две группы:

1. Параметры ADO.NET провайдера: provider.
2. Параметры OLE DB провайдера: location, user id, password, dbclient_library.

ADO.NET провайдер обрабатывает следующие параметры:

Имя	Назначение
Provider	Идентификатор OLE DB провайдера.
File Name	Файл с параметрами подключения базы данных.

У OLE DB провайдера (IBProvider-a) настроек подключения гораздо больше. Полный список можно посмотреть в документации. Вот основные:

Имя	Назначение
Location	Расположение базы данных.
User ID	Имя пользователя.
Password	Пароль пользователя.
dbclient_library	Имя или файловый путь к серверному клиенту (gds32.dll, fbclient.dll, ibclient64.dll).
dbclient_library_64	Имя или файловый путь к 64-битному серверному клиенту. Учитывается только в 64-битных процессах.
ctype	Кодовая страница подключения.
ctype_none	Кодовая страница для текстовых данных без указания кодовой страницы (NONE).
auto_commit	Разрешение использовать автоматические транзакции.
nested_trans	Разрешение на создание «вложенных» транзакций.
named_param_prefix	Префикс именованных параметров. По умолчанию — «:».

Кроме того, есть свойства, которые обрабатываются как ADO.NET провайдером, так и OLE DB провайдером. Например:

Имя	Назначение
OLE DB Services	Конфигурация взаимодействия с OLE DB провайдером. В частности – использование пула подключений.
Persist Security Info	Конфигурация доступа к значениям параметров аутентификации после подключения к базе данных.

Для упрощения процесса формирования строки подключения из кода программы, в ADO.NET провайдере реализован специальный компонент – OleDbConnectionStringBuilder.

Значения свойств можно указывать через «индексатор»:

static void Test_003()
{
 var cnsb=new OleDbConnectionStringBuilder();

 cnsb["provider"]="LCPI.IBProvider.3";

 cnsb["location"]="localhost:d:\\database\\employee.fdb";

 cnsb["user id"]="SYSDBA";

 cnsb["password"]="masterkey";

 cnsb["dbclient_library"]="fbclient.dll";

 var cn=new OleDbConnection(cnsb.ConnectionString);

 cn.Open();
}//Test_003

Или через свойства класса OleDbConnectionStringBuilder:

static void Test_004()
{
 var cnsb=new OleDbConnectionStringBuilder();

 cnsb.Provider="LCPI.IBProvider.3";

 cnsb.Location="localhost:d:\\database\\employee.fdb";

 cnsb.UserID="SYSDBA";

 cnsb.Password="masterkey";

 cnsb.IBProvider.dbclient_library="fbclient.dll";

 var cn=new OleDbConnection(cnsb.ConnectionString);

 cn.Open();
}//Test_004

По-умолчанию, после подключения к базе данных из строки подключения (OleDbConnection.ConnectionString) будет исключено свойство с паролем:

static void Test_005()
{
 const string cn_str=
  "provider=LCPI.IBProvider.3;"
  +"location=localhost:d:\\database\\employee.fdb;"
  +"user id=SYSDBA;"
  +"password=masterkey;"
  +"dbclient_library=fbclient.dll";

 var cn=new OleDbConnection(cn_str);

 Console.WriteLine("before connect:");
 Console.WriteLine(cn.ConnectionString);

 cn.Open();

 Console.WriteLine("");
 Console.WriteLine("after connect:");
 Console.WriteLine(cn.ConnectionString);
}//Test_005

Это поведение зависит от значения свойства «Persist Security Info»:

• False указывает исключать из строки подключения параметры, связанные с безопасностью. Это значение по-умолчанию.
• True оставляет в строке подключения значения свойств, относящихся к безопасности.

Использование файла с параметрами инициализации

Параметры инициализации подключения можно сохранить во UDL-файле и указать в строке подключения путь к этому файлу:

1. Создайте пустой файл с расширением UDL — «test.udl».
2. Выберите этот файл в проводнике Windows и нажмите Enter.
3. Откроется диалог «Data Links» для настройки параметров подключения.
4. Переключитесь на страницу «Поставщик данных» и выберите «LCPI OLE DB Provider for InterBase [v3]»:

5. Нажмите «Далее >>» или щелкните на вкладке «Соединение».
6. Заполните поля с основными параметрами соединения:

7. На странице «Дополнительно» можно указать расширенные параметры соединения. Например, имя серверного клиента (fbclient.dll):

8. Теперь можно вернуться на страницу «Соединение» и проверить параметры подключения, нажав кнопку «Проверить подключение». Если все было указано правильно, то появится диалог с информацией о типе и версии сервера базы данных:

9. Закрываем диалоги («Закрыть», «OK»). Разрешаем сохранение пароля в открытом виде:

10. Пишем следующий код:

static void Test_UDL()
{
 var cn=new OleDbConnection("file name=test.udl");

 cn.Open();
}//Test_UDL

Здесь главное позаботиться о том, чтобы тестовый процесс нашел «test.udl». Лучше всего держать этот файл в одном каталоге с EXE программы.

Отключение от базы данных

Есть несколько способов завершения работы с подключением к базе данных.

Первый – с помощью метода OleDbConnection.Close. После вызова метода Close объект подключения остается работоспособным и с ним можно продолжать работать. Например, снова вызвать метод Open:

static void Test_006__close()
{
 var cn=new OleDbConnection("provider=LCPI.IBProvider.3;"
                            +"location=localhost:d:\\database\\employee.fdb;"
                            +"user id=SYSDBA;\n"
                            +"password=masterkey;\n"
                            +"dbclient_library=fbclient.dll");

 cn.Open();

 cn.Close();

 cn.Open();

 cn.Close();
}//Test_006__close

Второй метод, это вызов метода OleDbConnection.Dispose. Он освобождает подключение и полностью деинициализирует объект подключения. Если вы попробуете продолжить с ним работать, то получите исключение ObjectDisposedException:

static void Test_006__dispose()
{
 var cn=new OleDbConnection("provider=LCPI.IBProvider.3;"
                            +"location=localhost:d:\\database\\employee.fdb;"
                            +"user id=SYSDBA;\n"
                            +"password=masterkey;\n"
                            +"dbclient_library=fbclient.dll");

 cn.Open();

 cn.Dispose();

 try
 {
  cn.Open();
 }
 catch(ObjectDisposedException e)
 {
  Console.WriteLine("ERROR: {0} - {1}",e.Source,e.Message);
 }
}//Test_006__dispose

Ну и последний, третий, способ – это вообще ничего не вызывать. Сборщик мусора рано и поздно доберется до объекта подключения и заставит его освободить все ресурсы, которые представляют собой указатели на объекты OLE DB.

Во всех случаях, ADO.NET провайдер не инициирует фактическое отключение от базы данных. Он просто освобождает указатели на объекты OLE DB провайдера.

Реальное отключение произойдет только после освобождения последней ссылки на OLE DB подключение.

Если соединение с базой данных было создано через пул подключений (это режим по-умолчанию) или на COM-объект источника данных OLE DB остались внешние ссылки, то подключение останется активным.

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

Пул подключений (в упрощенном виде) представляет собой отображение строк подключений на объекты подключений. Вся механика спрятана в стандартных сервисных компонентах OLE DB. ADO.NET провайдер может только либо разрешить использование пула подключений, либо запретить его использование. Включение/выключение пула подключений осуществляется через свойство инициализации “OLE DB Services”.

По-умолчанию, как уже отмечалось ранее, пул подключений разрешен:

static void Test_007()
{
 const string c_sql
  ="select CURRENT_CONNECTION from RDB$DATABASE";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;"
  +"location=localhost:d:\\database\\employee.fdb;"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"auto_commit=true";

 var cn=new OleDbConnection(c_cn_str);

 for(uint n=1;n!=6;++n)
 {
  cn.Open();
  Console.WriteLine("CN{0}_ID: {1}",n,(new OleDbCommand(c_sql,cn)).ExecuteScalar());
  cn.Close();
 }//for n
}//Test_007

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

static void Test_008()
{
 const string c_sql
  ="select CURRENT_CONNECTION from RDB$DATABASE";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;"
  +"location=localhost:d:\\database\\employee.fdb;"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"auto_commit=true";

 var cnsb=new OleDbConnectionStringBuilder(c_cn_str);

 cnsb.OleDbServices=cnsb.OleDbServices&(~1);

 var cn=new OleDbConnection(cnsb.ConnectionString);

 for(uint n=1;n!=6;++n)
 {
  cn.Open();
  Console.WriteLine("CN{0}_ID: {1}",n,(new OleDbCommand(c_sql,cn)).ExecuteScalar());
  cn.Close();
 }//for n
}//Test_008

Создание и удаление базы данных

Для создания и подключения новой базы данных нужно:

1. Cформировать строку подключения, в которой будут указаны параметры новой базы данных и параметры соединения с ней.
2. Установить эту строку в OleDbConnection.
3. Вызвать метод OleDbConnection.CreateDatabase.

Допустимые параметры новой базы данных описаны в документации IBProvider-a.

Рассмотрим пример создания новой базы данных с указанием:

1. SQL диалекта базы данных.
2. Размера страницы.
3. Кодовой страницы по-умолчанию.
4. Правила сортировки (collate) кодовой страницы по-умолчанию.

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

static void Test_009()
{
 const string c_sql
  ="select CURRENT_CONNECTION from RDB$DATABASE";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\ADO_NET_TEST_009.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"auto_commit=true";

 var cnsb=new OleDbConnectionStringBuilder(c_cn_str);

 cnsb["IBP_NEWDB: Database Dialect"]=3;
 cnsb["IBP_NEWDB: Database Page Size"]=8*1024;
 cnsb["IBP_NEWDB: Default Charset"]="WIN1251";
 cnsb["IBP_NEWDB: Default Charset Collation"]="PWX_CYRL";

 var cn=new OleDbConnection(cnsb.ConnectionString);

 cn.CreateDatabase();

 var rd=new OleDbCommand("select MON$DATABASE_NAME, MON$SQL_DIALECT, MON$PAGE_SIZE\n"
                         +"from MON$DATABASE",cn).ExecuteReader();
 rd.Read();

 Console.WriteLine("DATABSE  : {0}",rd["MON$DATABASE_NAME"]);
 Console.WriteLine("DIALECT  : {0}",rd["MON$SQL_DIALECT"]);
 Console.WriteLine("PAGE_SIZE: {0}",rd["MON$PAGE_SIZE"]);

 cn.Close();
}//Test_009

После вызова метода CreateDatabase, объект подключения переходит в открытое состояние и позволяет работать с базой данных так же, как и после выполнения метода Open.

Удаление базы данных выполняется с помощью метода OleDbConnection.DropDatabase. Пример удаления базы данных, созданной в предыдущем примере:

static void Test_010()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\ADO_NET_TEST_009.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 var cn=new OleDbConnection(c_cn_str);

 cn.Open();

 cn.DropDatabase();
}//Test_010

Работа с транзакциями

Для работы с транзакциями в ADO.NET провайдере определен компонент OleDbTransaction.

Старт транзакции

Транзакция создается через вызов метода OleDbConnection.BeginTransaction. Доступна пара перегруженных вариантов этого метода.

Первый, без параметров, создает транзакцию с уровнем изоляции ReadCommitted:

OleDbTransaction BeginTransaction();

Второй вариант этого метода позволяет указать уровень изоляции новой транзакции:

OleDbTransaction Begin(IsolationLevel isolationLevel);

IBProvider поддерживает три уровня изоляции транзакции:

1. IsolationLevel.ReadCommitted
2. IsolationLevel.RepeatableRead
3. IsolationLevel.Serializable

Завершение транзакции

Завершение транзакции осуществляется одним из методов OleDbTransaction: Commit или Rollback. Commit фиксирует изменения, Rollback откатывает изменения.

После завершения транзакции, объект OleDbTransaction становится бесполезным – не предусмотрено способа активизировать его заново.

У завершенной транзакции OleDbTransaction.Connection содержит пустую ссылку.

Завершение транзакции с продолжением

Помимо «жесткого» завершения транзакции, в OleDbTransaction предусмотрена еще одна пара методов с сохранением её активности после коммита или отката: CommitRetaining, RollbackRetaining.

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

Одной из уникальных возможностей IBProvider-a является моделирование «вложенных» транзакций с помощью точек сохранения. По-умолчанию этот механизм отключен. Для включения нужно указать в строке подключения «nested_trans=true». Это позволит вызывать метод OleDbTransaction.Begin, который будет возвращать управляющий объект «вложенной» транзакции. Для «вложенной» транзакции так же доступны методы Commit, Rollback, CommitRetaining, RollbackRetaining.

Стоит отметить, что ADO.NET провайдер по-умолчанию отклоняет коммит родительской транзакции, если у неё есть активная дочерняя транзакция.

static void Test_011()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"nested_trans=true";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr1=cn.BeginTransaction(IsolationLevel.RepeatableRead))
  {
   using(var tr2=tr1.Begin())
   {
    try
    {
     tr1.Commit(); //throw!
    }
    catch(Exception e)
    {
     Console.WriteLine("ERROR: {0} - {1}",e.Source,e.Message);
    }
   }//using tr2
  }//using tr1
 }//using cn
}//Test_011

Для изменения этого поведения в строке подключения нужно указать «NetProv: NestedTransRules=AllowImplicitCommit». Или настроить свойство OleDbConnectionStringBuilder.NetProvider.NestedTransRules (допустимые значения определены в перечислении OleDbPropertiesValues.NetProvider. NestedTransRules). Это заставит ADO.NET провайдер, при коммите родительской транзакции, осуществить неявный коммит всех вложенных транзакций:

static void Test_012()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"nested_trans=true";

 var cnsb=new OleDbConnectionStringBuilder(c_cn_str);

 cnsb.NetProvider.NestedTransRules
  =OleDbPropertiesValues.NetProvider.NestedTransRules.AllowImplicitCommit;

 using(var cn=new OleDbConnection(cnsb.ConnectionString))
 {
  cn.Open();

  using(var tr1=cn.BeginTransaction(IsolationLevel.RepeatableRead))
  {
   using(var tr2=tr1.Begin())
   {
    try
    {
     tr1.Commit();

     Console.WriteLine("tr1.Commit - OK");
    }
    catch(Exception e)
    {
     Console.WriteLine("ERROR: {0} - {1}",e.Source,e.Message);
    }
   }//using tr2
  }//using tr1
 }//using cn
}//Test_012

Автоматические транзакции

Помимо явного управления стартом/завершением транзакций, IBProvider предоставляет возможность автоматического формирования транзакционного контекста для операций с базой данных. Достаточно указать в строке подключения «auto_commit=true» и IBProvider будет самостоятельно стартовать и завершать транзакцию для каждой операции с базой данных.

Уровень изоляции автоматической транзакции определяется через свойство строки подключения «auto_commit_level». По-умолчанию используется уровень изоляции «Repeatable Read».

Параллельные транзакции

Одной из отличительных особенностей Firebird и InterBase является возможность одновременного создания нескольких, независимых транзакций в рамках одного подключения. IBProvider предоставляет поддержку для этой возможности. Однако в ADO.NET технологии это не поддерживается — повторный вызов метода OleDbConnection.BeginTransaction, при наличии предыдущей незавершенной транзакции, будет завершаться ошибкой:

static void Test_013()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"nested_trans=true";

 OleDbConnection  cn=null;
 OleDbTransaction tr1=null;

 try
 {
  cn=new OleDbConnection(c_cn_str);

  cn.Open();

  tr1=cn.BeginTransaction();

  try
  {
   var tr2=cn.BeginTransaction(); //throw!
  }
  catch(Exception e)
  {
   Console.WriteLine("ERROR: {0} - {1}",e.Source,e.Message);
  }
 }
 finally
 {
  if(!Object.ReferenceEquals(tr1,null))
   tr1.Dispose();

  if(!Object.ReferenceEquals(cn,null))
   cn.Dispose();
 }//finally
}//Test_013

Для решения этой проблемы на уровне компоненты OleDbConnection добавлен метод CloneSession. Он создает новый объект OleDbConnection, привязанный к существующему объекту OLE DB подключения и новому объекту OLE DB сессии (который, фактически, и управляет транзакцией). Схематично это выглядит так:

static void Test_014()
{
 const string c_sql
  ="select CURRENT_CONNECTION || ' - ' || CURRENT_TRANSACTION from RDB$DATABASE";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 var cn1=new OleDbConnection(c_cn_str);

 cn1.Open();

 var tr1=cn1.BeginTransaction();

 var cn2=cn1.CloneSession();

 var tr2=cn2.BeginTransaction();

 Console.WriteLine("cn1: {0}",
                   (new OleDbCommand(c_sql,cn1,tr1).ExecuteScalar()));

 Console.WriteLine("cn2: {0}",
                   (new OleDbCommand(c_sql,cn2,tr2).ExecuteScalar()));

 tr1.Commit();
 tr2.Commit();

 cn1.Dispose();
 cn2.Dispose();
}//Test_014

В этом примере были созданы два объекта «cn1» и «cn2», привязаные к одному и тому же подключению базы данных (id: 1346). И созданы две независимые транзакции (3698 и 3699), принадлежащие этому подключению.

«Параллельность» доступна только для транзакций первого уровня. Не существует возможности создания двух активных «параллельных» транзакций второго (и далее) уровня с общей родительской транзакцией.

Распределенные транзакции

ADO.NET провайдер поддерживает пару способов присоединения к распределенной транзакции, созданной средствами .Net Framework: явный и неявный.

Для явного присоединения предназначен метод OleDbConnection.EnlistTransaction:

static void Test_enlist()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tscope=new System.Transactions.TransactionScope())
  {
   cn.EnlistTransaction(System.Transactions.Transaction.Current);

   using(var cmd=cn.CreateCommand())
   {
    cmd.CommandText="insert into COUNTRY (COUNTRY,CURRENCY) values ('Neptun','Fish')";

    cmd.ExecuteNonQuery();
   }//using cmd

   tscope.Complete();
  }//using ts

  using(var tr=cn.BeginTransaction())
  {
   Console.WriteLine
    ("Count: {0}.",
     (new OleDbCommand("select count(*) from COUNTRY where COUNTRY='Neptun'",cn,tr)).ExecuteScalar());

   Console.WriteLine
    ("Delete: {0}.",
      (new OleDbCommand("delete from COUNTRY where COUNTRY='Neptun'",cn,tr)).ExecuteNonQuery());

   tr.Commit();
  }//using tr
 }//using cn
}//Test_enlist

Неявное присоединение к распределенной транзакции осуществляется при создании подключения к БД в «контексте» активного объекта TransactionScope:

static void Test_enlist__implicit()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

  using(var tscope=new System.Transactions.TransactionScope())
  {
   using(var cn=new OleDbConnection(c_cn_str))
   {
    cn.Open();

    using(var cmd=cn.CreateCommand())
    {
     cmd.CommandText="insert into COUNTRY (COUNTRY,CURRENCY) values ('Neptun','Fish')";

     cmd.ExecuteNonQuery();
    }//using cmd
   }//using cn

   tscope.Complete();
  }//using ts

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   Console.WriteLine
    ("Count: {0}.",
     (new OleDbCommand("select count(*) from COUNTRY where COUNTRY='Neptun'",cn,tr)).ExecuteScalar());

   Console.WriteLine
    ("Delete: {0}.",
      (new OleDbCommand("delete from COUNTRY where COUNTRY='Neptun'",cn,tr)).ExecuteNonQuery());

   tr.Commit();
  }//using tr
 }//using cn
}//Test_enlist__implicit

Автоматическое присоединение к распределенной транзакции можно запретить, модифицировав свойство инициализации «OLE DB Services»:

static void Test_enlist__disable_implicit()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 var cnsb=new OleDbConnectionStringBuilder(c_cn_str);

 cnsb.OleDbServices=cnsb.OleDbServices&(~2);

 using(var tscope=new System.Transactions.TransactionScope())
 {
  using(var cn=new OleDbConnection(cnsb.ConnectionString))
  {
   cn.Open();

   using(var cmd=cn.CreateCommand())
   {
    cmd.CommandText="insert into COUNTRY (COUNTRY,CURRENCY) values ('Neptun','Fish')";

    try
    {
     cmd.ExecuteNonQuery();
    }
    catch(Exception e)
    {
     Console.WriteLine("{0}\n\n{1}",e.Source,e.Message);
    }
   }//using cmd
  }//using cn

  tscope.Complete();
 }//using ts
}//Test_enlist__disable_implicit

Выполнение запросов к базе данных

Для работы с запросами в ADO.NET провайдере определен компонент OleDbCommand. Запросы выполняются в рамках открытого подключения и, как правило, активной транзакции. Существует пара способов создания объекта команды.

Первый способ – это явное конструирование объекта:

var cmd=new OleDbCommand();

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

Второй способ – через вызов метода OleDbConnection.CreateCommand:

var cmd=cn.CreateCommand();

Полученный объект команды будет привязан к объекту подключения «cn».

Текст запроса указывается через свойство CommandText или передается в конструктор OleDbCommand.

Выполнение запроса выполняется через вызов одного из методов: ExecuteScalar, ExecuteReader или ExecuteNonQuery. Все зависит от типа запроса и способа получения результата. Формально, все запросы можно выполнять с помощью метода ExecuteReader.

ExecuteScalar

Метод OleDbCommand.ExecuteScalar возвращает единственное значение первой колонки первой строки. Остальные результаты игнорируются. Этот метод полезен для запросов, которые, к примеру, вычисляют количество записей в таблице — соответственно возвращают только одно значение:

static void Test_015__ExecuteScalar()
{
 const string c_sql="select count(*) from employee";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 object n=null;

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    n=cmd.ExecuteScalar();
   }//using cmd

   tr.Commit();
  }//using tr
 }//using cn

 string s;

 if(Object.ReferenceEquals(n,null))
  s="null";
 else
 if(n.Equals(DBNull.Value))
  s="DBNull";
 else
  s=n.ToString();

 Console.WriteLine("n: {0}",s);
}//Test_015__ExecuteScalar

В случае пустого результирующего множества, то есть с нулевым количеством рядов, ExecuteScalar вернет null:

static void Test_016__ExecuteScalar__empty_result()
{
 const string c_sql="select 1 from RDB$DATABASE where 1=0";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 object n=null;

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    n=cmd.ExecuteScalar();
   }//using cmd

   tr.Commit();
  }//using tr
 }//using cn

 string s;

 if(Object.ReferenceEquals(n,null))
  s="null";
 else
 if(n.Equals(DBNull.Value))
  s="DBNull";
 else
  s=n.ToString();

 Console.WriteLine("n: {0}",s);
}//Test_016__ExecuteScalar__empty_result

Если запрос вообще не возвращает множество (например, это update-запрос), то ExecuteScalar так же возвращает null:

static void Test_017__ExecuteScalar__no_result()
{
 const string c_sql="update employee set first_name=null where 1=0";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 object n=null;

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    n=cmd.ExecuteScalar();
   }//using cmd

   tr.Commit();
  }//using tr
 }//using cn

 string s;

 if(Object.ReferenceEquals(n,null))
  s="null";
 else
 if(n.Equals(DBNull.Value))
  s="DBNull";
 else
  s=n.ToString();

 Console.WriteLine("n: {0}",s);
}//Test_017__ExecuteScalar__no_result

ExecuteReader

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

Переход на следующую запись выполняется методом OleDbDataReader.Read. Он возвращает:

• true, если выбрана очередная запись результирующего множества.
• false, если записей больше не осталось.

Обратите внимание, что позиционирование на первую запись множества не осуществляется – нужно вызвать метод OleDbDataReader.Read.

После завершения работы с объектом OleDbDataReader, желательно вызывать его метод Close или Dispose.

static void Test_018()
{
 const string c_sql="select country from country";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    using(var reader=cmd.ExecuteReader())
    {
     int n=0;

     while(reader.Read())
     {
      ++n;

      if(n>1)
       Console.Write(", ");

      Console.Write("{0}",reader.GetString(0));
     }//while

     Console.WriteLine("");
    }//using reader
   }//using cmd

   tr.Commit();
  }//using tr
 }//using cn
}//Test_018

Проверить доступность записей можно проверить с помощью свойства OleDbDataReader.HasRows:

static void Test_019__HasRows()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand("select country from country",cn,tr))
   {
    using(var reader=cmd.ExecuteReader())
    {
     Console.WriteLine("1. HasRows: {0}",reader.HasRows);
    }//using reader
   }//using cmd

   using(var cmd=new OleDbCommand("select country from country where 1=0",cn,tr))
   {
    using(var reader=cmd.ExecuteReader())
    {
     Console.WriteLine("2. HasRows: {0}",reader.HasRows);
    }//using reader
   }//using cmd

   tr.Commit();
  }//using tr
 }//using cn
}//Test_019__HasRows

С помощью ExecuteReader можно выполнять запросы, которые не возвращают результирующее множество. В этом случае все равно возвращается объект OleDbDataReader. Единственным осмысленным использованием этого объекта будет чтение значения свойства OleDbDataReader.RecordsAffected. В нем будет указано количество записей, затронутых запросом.

static void Test_020()
{
 const string c_sql="update COUNTRY set CURRENCY=upper(CURRENCY) where 1=0";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    using(var reader=cmd.ExecuteReader())
    {
     Console.WriteLine("RecordsAffected: {0}",reader.RecordsAffected);
    }//using reader
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_020

ExecuteNonQuery

OleDbCommand.ExecuteNonQuery предназначен для выполнения запросов, которые не возвращают результирующее множество – insert, update, delete, DDL запросы. Метод возвращает количество рядов, затронутых командой.

static void Test_021()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values('Mars', 'Snickers')";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_021

Команды с параметрами

В реальной работе с базой данных, как правило, используются запросы с параметрами. Это позволяет однократно подготовить запрос и многократно выполнять его для разных наборов значений. ADO.NET провайдер в паре с IBProvider-ом предоставляют полную поддержку для параметризованных запросов:

• Можно использовать именованные и неименованные (позиционные) параметры.
• Можно самостоятельно формировать описания параметров и генерировать их автоматически.
• Поддерживаются IN, OUT и IN/OUT параметры.
• Предоставлена возможность конфигурирования типа OUT-значений параметров.
• Поддерживается использование параметров в скриптах (команда с несколькими SQL-запросами).

В общем, для комфортной работы с параметризованными SQL-запросами есть все.

Для работы с параметрами на уровне класса OleDbCommand определено свойство Parameters. Это свойство возвращает объект класса OleDbParameterCollection, обслуживающий коллекцию параметров команды. Кроме того, по аналогии с классическим ADODB, на уровне того же класса OleDbCommand реализованы два нестандартных «индексатора» для доступа к параметрам по имени и по целочисленному индексу.

Неименованные параметры в тексте запроса обозначаются маркером ‘?’:

insert into COUNTRY (COUNTRY, CURRENCY) values( ?, ?);

В следующей паре примеров будут продемонстрированы прямолинейные способы выполнения этого запроса с самостоятельным формированием описаний параметров запроса:

static void Test_022а()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(?, ?)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd.Parameters.Add(new OleDbParameter(/*name*/null,
                                          /*type*/OleDbType.VarWChar,
                                          /*size. not defined*/0,
                                          ParameterDirection.Input)).Value="Mars";

    cmd.Parameters.Add(new OleDbParameter(/*name*/null,
                                          /*type*/OleDbType.VarWChar,
                                          /*size. not defined*/0,
                                          ParameterDirection.Input)).Value="Snickers";

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_022а

//------------------------------------------------------------------------
static void Test_022b()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(?, ?)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd.Parameters.Add(/*name*/null,
                       /*type*/OleDbType.VarWChar,
                       /*size. not defined*/0,
                       ParameterDirection.Input).Value="Mars";

    cmd.Parameters.Add(/*name*/null,
                       /*type*/OleDbType.VarWChar,
                       /*size. not defined*/0,
                       ParameterDirection.Input).Value="Snickers";

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_022b

Этот код можно сократить, заставив ADO.NET провайдер самостоятельно определить тип параметров и (по-умолчанию) указывать IN-направление:

static void Test_023()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(?, ?)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd.Parameters.AddWithValue(/*name*/null,"Mars");

    cmd.Parameters.AddWithValue(null,"Snickers");

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_023

OLE DB провайдер может предоставить описания параметров. Для получения сгенерированных описаний параметров небходимо вызвать метод OleDbParameterCollection.Refresh:

static void Test_024()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(?, ?)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd.Parameters.Refresh();

    cmd.Parameters[0].Value="Mars";

    cmd.Parameters[1].Value="Snickers";

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_024

Ну и последний способ – это просто присвоить значения через «индексаторы» параметров команды. OleDbCommand самостоятельно запросит описания параметров у OLE DB провайдера:

static void Test_025()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(?, ?)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd[0].Value="Mars";

    cmd[1].Value="Snickers";

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_025

С неименованными (или позиционными – кому как больше нравится) параметрами есть одна серьезная проблема – их нужно определять строго в том порядке, в каком они следуют в тексте команды. И, поскольку нет имени, не получится дважды использовать один и тот же параметр в запросе. Есть, конечно, небольшое преимущество – обращение к параметру по целочисленному индексу все-таки эффективнее обращения по его имени. Но в целом, гораздо надежнее давать параметрам имена.

Маркер именованного параметра представляет собой префикс и имя. По-умолчанию, в качестве префикса предлагается использовать двоеточие – ‘:’. Префикс можно поменять через свойство инициализации «named_param_prefix».

Как и неименованные параметры, именованные параметры можно определять самостоятельно или запрашивать у OLE DB провайдера. Перепишем последний пример с использованием именованных параметров. Для разнообразия, настроим IBProvider для использования префикса ‘@’ (в стиле MSSQL).

static void Test_026()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(@country, @currency)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"named_param_prefix='@'";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd["country"].Value="Mars";

    cmd["currency"].Value="Snickers";

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_026

Если необходимо сохранить префикс параметра в его имени, то в строке подключения нужно указать «named_param_rules=1»:

static void Test_027()
{
 const string c_sql
  ="insert into COUNTRY (COUNTRY, CURRENCY) values(@country, @currency)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;"
  +"named_param_prefix='@';"
  +"named_param_rules=1";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd["@country"].Value="Mars";

    cmd["@currency"].Value="Snickers";

    Console.WriteLine("RecordsAffected: {0}",cmd.ExecuteNonQuery());
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_027

В предыдущих примерах рассматривались запросы с IN-параметрами, которые передают свои значения серверу базы данных. Есть еще OUT-параметры, через которые получают результат работы запроса.

OUT-параметры присутствуют не только в запросах для выполнения хранимых процедур, но и в запросах с секцией «RETURNING». Например, запрос «INSERT … RETURNING …» позволяет перечитать и получить в OUT-параметрах значения колонок добавленной записи.

В «INSERT … RETURNING …» имена OUT-параметров назначаются сервером и, скорее всего, будут совпадать с именами перечитываемых колонок. Это не всегда удобно, поэтому IBProvider расширяет этот запрос до конструкции «INSERT … RETURNING … INTO …», предоставляя возможность явного указания имен OUT-параметров для возвращаемых значений.

Рассмотрим использование запроса «INSERT … RETURNING … INTO …» на примере добавления новой записи в таблицу EMPLOYEE стандартной базы данных employee.fdb. У этой таблицы есть триггер «BEFORE INSERT», генерирующий значение первичного ключа. Вот это значение и будет получено через OUT-параметр:

static void Test_insert_returning_into()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   const string c_sql
    ="insert into EMPLOYEE (FIRST_NAME,LAST_NAME,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY)\n"
    +"values (:FIRST_NAME,:LAST_NAME,:HIRE_DATE,:DEPT_NO,:JOB_CODE,:JOB_GRADE,:JOB_COUNTRY,:SALARY)\n"
    +"returning EMP_NO into :NEW_ID";

   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd["first_name"].Value  ="Agent";
    cmd["last_name"].Value   ="Smith";
    cmd["hire_date"].Value   =DateTime.Now;
    cmd["dept_no"].Value     ="000";
    cmd["job_code"].Value    ="CEO";
    cmd["job_grade"].Value   =1;
    cmd["job_country"].Value ="USA";
    cmd["salary"].Value      =200001;

    cmd.ExecuteNonQuery();

    Console.WriteLine("NEW_ID: {0}. Direction: {1}.",cmd["NEW_ID"].Value,cmd["NEW_ID"].Direction);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_insert_returning_into

Следующий пример практически полностью идентичен предыдущему. Отличие заключается в использовании IN/OUT параметра «EMP_NO». На входе указывается NULL-значение (DBNull.Value). А на выходе в этом параметре будет сгенерированное значение колонки «EMP_NO».

static void Test_in_out_param()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   const string c_sql
    ="insert into EMPLOYEE (EMP_NO,FIRST_NAME,LAST_NAME,HIRE_DATE,DEPT_NO,JOB_CODE,JOB_GRADE,JOB_COUNTRY,SALARY)\n"
    +"values (:EMP_NO,:FIRST_NAME,:LAST_NAME,:HIRE_DATE,:DEPT_NO,:JOB_CODE,:JOB_GRADE,:JOB_COUNTRY,:SALARY)\n"
    +"returning EMP_NO into :EMP_NO";

   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd["emp_no"].Value      =DBNull.Value;
    cmd["first_name"].Value  ="Agent";
    cmd["last_name"].Value   ="Smith";
    cmd["hire_date"].Value   =DateTime.Now;
    cmd["dept_no"].Value     ="000";
    cmd["job_code"].Value    ="CEO";
    cmd["job_grade"].Value   =1;
    cmd["job_country"].Value ="USA";
    cmd["salary"].Value      =200001;

    cmd.ExecuteNonQuery();

    Console.WriteLine("EMP_NO: {0}. Direction: {1}.",cmd["emp_no"].Value,cmd["emp_no"].Direction);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_in_out_param

По-умолчанию, тип возвращаемого значения определяется типом самого параметра. К примеру, текстовый блоб будет возвращаться в виде строки (System.String). Однако это можно изменить и попросить ADO.NET провайдер вместо строки возвращать объект для потокового чтения данных (System.IO.TextReader).

static void Test_change_out_param_value_type()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   const string c_sql
    ="update PROJECT set PROJ_DESC=UPPER(PROJ_DESC)\n"
    +"where PROJ_ID=:id\n"
    +"returning PROJ_DESC\n"
    +"into :desc";

   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd["id"].Value="DGPII";

    cmd["desc"].OutputBinding.Set(OleDbType.IUnknown,
                                  typeof(System.IO.TextReader));
    cmd.ExecuteNonQuery();

    var v=cmd["desc"].Value;

    Console.WriteLine("type: {0}",v.GetType());

    Console.WriteLine("data: {0}",((System.IO.TextReader)v).ReadToEnd());
   }//using cmd

   tr.Rollback();
  }
 }//using cn
}//Test_change_out_param_value_type

В представленном примере, выполняется конфигурирование OUT-значения параметра «desc»:

cmd["desc"].OutputBinding.Set(OleDbType.IUnknown,
                              typeof(System.IO.TextReader));

Первый аргумент метода Set указывает OLE DB тип значения, которое должен вернуть OLE DB провайдер. В данном случае запрашивается значение в виде COM-объекта.

Второй аргумент указывает OLE DB провайдеру, что возвращаемый COM-объект должен предоставить интерфейс для загрузки текста. ADO.NET провайдер, в свою очередь, использует этот аргумент для определения типа .NET объекта для обслуживания этого COM-объекта — lcpi.data.oledb.OleDbTextReader.

На самом деле, конечно, в IBProvider передается не «typeof(System.IO.TextReader)», а идентификатор COM-интерфейса. Конкретно в данном случае это будет IID_IIBP_SequentialStream_WideChar – идентификатор нестандартного интерфейса для потоковой загрузки текстовых данных в виде двухбайтных UNICODE-символов.

Аналогичным способом можно получать OUT-значений бинарных блобов в виде потока байт:

cmd["binary_blob_out_param"].OutputBinding.Set(OleDbType.IUnknown,
                                               typeof(System.IO.Stream));

В этом случае, у OLE DB провайдера будет запрошен COM-объект со стандартным интерфейсом ISequentialStream. А ADO.NET провайдер создаст объект класса lcpi.data.oledb.OleDbStream.

У OleDbParameter.OutputBinding (это свойство возвращает объект класса OleDbValueBinding) есть и другие варианты метода Set, некоторые из которых разрешают явно указывать идентификатор COM-интерфейса. Это позволяет достаточно гибко настраивать представления значений OUT-параметров под конкретные задачи.

Вызов хранимых процедур

У Firebird и InterBase есть два вида хранимых процедур (SP):

1. Возвращающие результат в виде множества рядов.
2. Возвращающие результат через OUT-параметры. Сюда же относятся SP, которые ничего не возвращают.

Хранимые процедуры первого типа вызываются через «select … from …»:

select * from stored_procedure_name(<input_param_list>)

Для получения результата работы такого запроса, как уже отмечалось ранее, следует использовать метод OleDbCommand.ExecuteReader:

static void Test_028()
{
 const string c_sql
  ="select * from mail_label(:cust_no)";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(c_sql,cn,tr))
   {
    cmd["cust_no"].Value=1001; //Signature Design

    using(var rd=cmd.ExecuteReader())
    {
     if(!rd.Read())
     {
      Console.WriteLine("No record!");
     }
     else
     {
      for(int i=0,_c=rd.FieldCount;i!=_c;++i)
      {
       Console.Write("{0}: ",rd.GetName(i));

       if(rd.IsDBNull(i))
        Console.Write("DBNull");
       else
        Console.Write("\"{0}\"",rd.GetValue(i));

       Console.WriteLine("");
      }//for[ever]
     }//else
    }//using rd
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_028

Хранимые процедуры второго типа (возвращающие результат через OUT-параметры), вызываются с помощью SQL-запроса вида:

execute procedure stored_procedure_name(<input_param_list>);

OUT-параметры в тексте запроса не указываются. Но подразумеваются. При этом их имена совпадают с именами, которые были указаны при создании хранимой процедуры.

Для получения значений OUT-параметров нужно добавить в OleDbCommand.Parameters их описания.

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

/*

create procedure sp_add(a integer,b integer)
 returns (result integer)
as
begin
 result=a+b;
end;

*/

static void Test_029()
{
 const string c_sql_create_sp
  ="create procedure sp_add(a integer,b integer)\n"
  +" returns (result integer)\n"
  +"as\n"
  +"begin\n"
  +" result=a+b;\n"
  +"end;";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    if(cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures,new object[]{null,null,"SP_ADD"}).Rows.Count==0)
    {
     cmd.CommandText=c_sql_create_sp;

     cmd.ExecuteNonQuery();

     tr.CommitRetaining();
    }//if

    cmd.CommandText="execute procedure SP_ADD(:a,:b)";

    cmd["a"].Value=1;
    cmd["b"].Value=2;

    cmd.ExecuteNonQuery();

    Console.WriteLine("result: {0}",cmd["result"].Value);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_029

Помимо поддержки стандартных синтаксисов вызова хранимых процедур, IBProvider поддерживает альтернативные. Например, такой:

 exec stored_procedure_name; 

В случае подобного запроса, IBProvider самостоятельно определяет способ вызова хранимой процедуры и сформирует список параметров. По умолчанию, формируются неименованные параметры. Однако, указав в строке подключения «exec_sp_named_param=true», OLE DB провайдер можно заставить получать и использовать оригинальные имена параметров хранимой процедуры.

Перепишем предыдущий пример с использованием этого синтаксиса. Для краткости, код создания хранимой процедуры SP_ADD будет опущен.

static void Test_030()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;\n"
  +"exec_sp_named_param=true";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="exec SP_ADD;";

    cmd["a"].Value=1;
    cmd["b"].Value=2;

    cmd.ExecuteNonQuery();

    Console.WriteLine("result: {0}",cmd["result"].Value);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_030

Результат работы будет идентичным – хранимая процедура вернет «result» равный трём:

Второй способ предполагает явное указание IN и OUT параметров в тексте запроса:

static void Test_030__ado_net_style()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="exec SP_ADD :a1, :a2, :sum;";

    cmd["a1"].Value=1;
    cmd["a2"].Value=2;

    cmd.ExecuteNonQuery();

    Console.WriteLine("result: {0}",cmd["sum"].Value);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_030__ado_net_style

Третий способ позволяет явно указать в запросе IN-параметры. OUT-параметры возвращаются неявно.

static void Test_030__adodb_style()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="exec SP_ADD(:a1, :a2);";

    cmd["a1"].Value=1;
    cmd["a2"].Value=2;

    cmd.ExecuteNonQuery();

    Console.WriteLine("result: {0}",cmd["result"].Value);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_030__adodb_style

Ну и последний способ, о котором стоит упомянуть, это вызов хранимой процедуры с использованием синтаксиса ODBC. Предполагается явное перечисление IN и OUT параметров в тексте запроса. В строке подключения нужно указать «support_odbc_query=true».

static void Test_030__odbc_style()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll;\n"
  +"support_odbc_query=true";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="{call SP_ADD(:a1, :a2, :sum)};";

    cmd["a1"].Value=1;
    cmd["a2"].Value=2;

    cmd.ExecuteNonQuery();

    Console.WriteLine("result: {0}",cmd["sum"].Value);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_030__odbc_style

Работа с пакетами FB3

В Firebird v3 (на текущий момент эта версия Firebird находится в разработке) появилась возможность группировки хранимых процедур (и функций) в пакеты (PACKAGE).

Подробное описание пакетов можно найти в документации сервера. Здесь будет приведено краткое описание.

PACKAGE состоит из двух частей — заголовок и тело. Сначала определяется заголовок, который можно рассматривать как интерфейс PACKAGE, а потом определяется его тело (BODY).

К заголовку PACKAGE применяются DDL запросы вида:

• CREATE PACKAGE <name> …
• CREATE OR ALTER PACKAGE <name> …
• ALTER PACKAGE <name> …
• RECREATE PACKAGE <name> …
• DROP PACKAGE <name> …

К телу PACKAGE применяются DDL-запросы:

• CREATE PACKAGE BODY <name> …
• RECREATE PACKAGE BODY <name> …
• DROP PACKAGE BODY <name> …

PACKAGE представляет собой неделимый набор процедур и функций. DDL запросов для определения/модификации/удаления отдельных элементов PACKAGE (процедур и функций) не предусмотрено.

Тело PACKAGE должно содержать определения для всех элементов, перечисленных в заголовке. Плюс внутренние (private) элементы, к которым можно обращаться только в рамках процедур и функций самого PACKAGE.

Способы работы с хранимыми процедурами из пакета те же, что и для обычных (независимых) процедур.

Процедуры, возвращающие множество, вызываются через «select * from» запрос:

select * from package_name.stored_procedure_name(<input_param_list>);

Процедуры, возвращающие результат через OUT-параметры или вообще ничего не возвращающие, вызываются через «execute procedure» запрос:

execute procedure package_name.stored_procedure_name(<input_param_list>);

Можно воспользоваться одним из универсальных способов вызова, который реализуется средствами IBProvider-a. Например:

exec package_name.stored_procedure_name;

Создадим тривиальный пакет MATH с одной единственной хранимой процедурой – SP_ADD.

Заголовок:

create package MATH
as
begin
 procedure SP_ADD(a1 integer, a2 integer)
 returns (r integer);
end;

Тело:

create package body MATH
as
begin
 procedure SP_ADD(a1 integer, a2 integer)
 returns (r integer)
 as
 begin
  r=a1+a2;
 end
end;

И напишем код вызова этой хранимой процедуры посредством запроса «exec math.sp_add». Как обычно, воспользуемся услугами автоматической генерации описаний параметров.

static void Test_031()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=firebird3:d:\\database\\ibp_test_fb30_d3.gdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient_30.dll;\n"
  +"exec_sp_named_param=true";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="exec math.sp_add;";

    cmd["a1"].Value=1;
    cmd["a2"].Value=2;

    cmd.ExecuteNonQuery();

    Console.WriteLine("result: {0}",cmd["r"].Value);
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_031

Multiple Active Result Sets (MARS)

InterBase и, естественно, Firebird с самых первых версий позволяли создавать несколько активных курсоров результирующих множеств в рамках одного подключения и выбирать из них данные в произвольном порядке. Но только с появлением MSSQL 2005, благодаря маркетологам, у этой «технологии» появилось название – «Multiple Active Result Sets» (MARS).

IBProvider и ADO.NET провайдер позволяют работать с несколькими активными курсорами. Более того, можно многократно выполнять команду, не закрывая связанный с ней курсор результирующего множества (DataReader).

static void Test__MARS()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="select * from employee order by EMP_NO asc";

    var rd1=cmd.ExecuteReader();
    var rd2=cmd.ExecuteReader();

    cmd.CommandText="select * from employee order by EMP_NO desc";

    var rd3=cmd.ExecuteReader();

    int n1=0;
    int n2=0;
    int n3=0;

    for(;;)
    {
     bool f1=rd1.Read();
     bool f2=rd2.Read();
     bool f3=rd3.Read();

     if(f1) ++n1;
     if(f2) ++n2;
     if(f3) ++n3;

     if(f1 || f2 || f3)
      continue;

     break;
    }//for

    Console.WriteLine("n1: {0}",n1);
    Console.WriteLine("n2: {0}",n2);
    Console.WriteLine("n3: {0}",n3);

    rd1.Dispose();
    rd2.Dispose();
    rd3.Dispose();
   }//using cmd

   tr.Commit();
  }//using tr
 }//using cn
}//Test__MARS

Выполнение скриптов

IBProvider и ADO.NET провайдер поддерживают выполнение скриптов — команд с несколькими SQL-запросами. Запросы могут содержать именованные параметры (автоматическая генерация описаний параметров не поддерживается). Скрипт может управлять транзакцией и выполнять DDL-запросы.

Для перебора результатов выполнения скрипта нужно использовать метод OleDbDataReader.NextResult.

Рассмотрим выполнение скрипта, в котором:

• Создается таблица
• Добавляются записи в новую таблицу
• Делается выборка этих записей двумя разными запросами
• Удаляется таблица

Скрипт содержит IN-параметры (data1, data2, data3) и OUT-параметры (id1, id2, id3).

/*
  set transaction;

  SET AUTODDL ON; -- неявно коммитим DDL запросы

  create table TTT (id integer not null primary key,
                    data varchar(10));

  create generator GEN_ID_TTT;

  create trigger BI_TTT for TTT before insert as begin NEW.ID=GEN_ID(GEN_ID_TTT,1); end;

  insert into TTT (data) (:data1) returing ID into :id1;
  insert into TTT (data) (:data2) returing ID into :id2;
  insert into TTT (data) (:data3) returing ID into :id3;

  select ID,DATA from TTT order by ID;

  drop table TTT;

  drop generator GEN_ID_TTT;

  commit;
*/
static void Test__script()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  var cmd=cn.CreateCommand();

  cmd.CommandText=
    "set transaction;\n"
   +"\n"
   +"SET AUTODDL ON; -- неявно коммитим DDL запросы\n"
   +"\n"
   +"create table TTT (id integer not null primary key,\n"
   +"                  data varchar(10));\n"
   +"\n"
   +"create generator GEN_ID_TTT;\n"
   +"\n"
   +"create trigger BI_TTT for TTT before insert as begin NEW.ID=GEN_ID(GEN_ID_TTT,1); end;\n"
   +"\n"
   +"insert into TTT (data) values(:data1) returning ID into :id1;\n"
   +"insert into TTT (data) values(:data2) returning ID into :id2;\n"
   +"insert into TTT (data) values(:data3) returning ID into :id3;\n"
   +"\n"
   +"select ID,DATA from TTT order by ID asc;\n"
   +"select ID,DATA from TTT order by ID desc;\n"
   +"\n"
   +"drop table TTT;\n"
   +"\n"
   +"drop generator GEN_ID_TTT;"
   +"\n"
   +"commit;";

   //------- IN-parameters
   cmd.Parameters.AddWithValue("data1","QWERTY");
   cmd.Parameters.AddWithValue("data2","ASDFGH");
   cmd.Parameters.AddWithValue("data3","ZXCVBN");

   //------- OUT-parameters
   cmd.Parameters.Add("id1",OleDbType.Variant,0,ParameterDirection.Output);
   cmd.Parameters.Add("id2",OleDbType.Variant,0,ParameterDirection.Output);
   cmd.Parameters.Add("id3",OleDbType.Variant,0,ParameterDirection.Output);

   //-------
   var reader=cmd.ExecuteReader();

   //-------
   Console.WriteLine("id1: {0}",cmd["id1"].Value);
   Console.WriteLine("id2: {0}",cmd["id2"].Value);
   Console.WriteLine("id3: {0}",cmd["id3"].Value);

   //-------
   for(int n=0;;)
   {
    ++n;

    Console.WriteLine("");
    Console.WriteLine("{0}. ----------",n);

    while(reader.Read())
    {
     Console.WriteLine("[{0}]=\"{1}\"",reader["ID"],reader["DATA"]);
    }

    if(!reader.NextResult())
     break;
   }//for n
 }//using cn
}//Test__script

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

Отмена выполнения запроса выполняется с помощью метода OleDbCommand.Cancel. Вызов OleDbCommand.Cancel должен выполняться в отдельном потоке, потому что методы выполнения команды (ExecuteScalar, ExecuteReader, ExecuteNonQuery) блокируют текущий поток до завершения операции.

В следующем примере в отдельном потоке выполняется запрос с вызовом хранимой процедуры, а основной поток отменяет выполнение этого запроса.

/*

create procedure SP_EXEC_DUMMY_COUNTER(n integer)
as
 declare variable i integer;
begin
 i=0;

 while(i<n)do
 begin
  i=i+1;
 end
end;

*/

//------------------------------------------------------------------------
class ThreadWorker
{
 private readonly OleDbCommand m_cmd;

 public Exception m_exc=null;

 //-----------------------------------------------------------------------
 public ThreadWorker(OleDbCommand cmd)
 {
  m_cmd=cmd;
 }//ThreadWorker

 //-----------------------------------------------------------------------
 public void ExecuteNonQuery()
 {
  Console.WriteLine("Enter to ThreadWorker.ExecuteNonQuery");

  try
  {
   m_cmd.ExecuteNonQuery();
  }
  catch(Exception e)
  {
   Console.WriteLine("Catch exception in ThreadWorker.ExecuteNonQuery");

   m_exc=e;
  }//catch

  Console.WriteLine("Exit from ThreadWorker.ExecuteNonQuery");
 }//ExecuteNonQuery
};//class ThreadWorker

//------------------------------------------------------------------------
static void Test__cmd_cancel()
{
 const string c_sql_create_sp
  ="create procedure SP_EXEC_DUMMY_COUNTER(n integer)\n"
  +"as\n"
  +" declare variable i integer;\n"
  +"begin\n"
  +" i=0;\n"
  +"\n"
  +" while(i<n)do\n"
  +" begin\n"
  +"  i=i+1;\n"
  +" end\n"
  +"end;";

 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   if(cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures,
                             new object[]{null,
                                          null,
                                          "SP_EXEC_DUMMY_COUNTER"}).Rows.Count==0)
   {
    using(var cmd=new OleDbCommand(c_sql_create_sp,cn,tr))
    {
     cmd.ExecuteNonQuery();
    }

    tr.CommitRetaining();
   }//if

   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText="execute procedure SP_EXEC_DUMMY_COUNTER(100000000)";

    var threadWorker=new ThreadWorker(cmd);

    var thread=new System.Threading.Thread(threadWorker.ExecuteNonQuery);

    try
    {
     thread.Start();

     while(thread.IsAlive)
     {
      System.Threading.Thread.Sleep(2000);

      Console.WriteLine("Cancel");

      cmd.Cancel();
     }//while

     Console.WriteLine("threadWorker was stopped");

     Console.WriteLine("");

     if(Object.ReferenceEquals(threadWorker.m_exc,null))
     {
      Console.WriteLine("No exception");
     }
     else
     {
      Console.WriteLine("Thread exception: {0}\n\n{1}",
                        threadWorker.m_exc.Source,
                        threadWorker.m_exc.Message);
     }//else
    }
    finally
    {
     thread.Join();
    }//finally
   }//using cmd
  }//using tr
 }//using cn
}//Test__cmd_cancel

Поддержка типов данных

На текущий момент времени (2015 год), IBProvider предоставляет поддержку для всех типов данных Firebird/InterBase. А ADO.NET провайдер, используя возможности .NET Framework, максимально упрощает работу с ними на уровне прикладного кода.

Типы данных можно разделить на следующие группы:

• Целочисленные типы данных;
• Вещественные типы данных;
• Типы данных NUMERIC и DECIMAL;
• Строковые типы данных;
• Бинарные типы данных;
• Булевский тип;
• Типы данных даты и времени;
• Блобы;
• Массивы;

Целочисленные типы данных

К этой группе типов данных относятся типы SMALLINT, INTEGER, BIGINT.

Тип данных FB/IB	Размер в байтах	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
SMALLINT	2	DBTYPE_I2	DbType.Int16	OleDbType.Smallint	System.Int16
INTEGER	4	DBTYPE_I4	DbType.Int32	OleDbType.Integer	System.Int32
BIGINT	8	DBTYPE_I8	DbType.Int64	OleDbType.BigInt	System.Int64

Вещественные типы данных

К этой группе типов данных относятся типы FLOAT, DOUBLE PRECISION.

Тип данных FB/IB	Размер в байтах	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
FLOAT	4	DBTYPE_R4	DbType.Single	OleDbType.Single	System.Single
DOUBLE PRECISION	8	DBTYPE_R8	DbType.Double	OleDbType.Double	System.Double

Типы данных NUMERIC и DECIMAL

Типы данных NUMERIC и DECIMAL, в общем случае, не различаются и обрабатываются единообразно.

Тип данных FB/IB	Макс. Точность	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
DECIMAL	18	DBTYPE_NUMERIC	DbType.Decimal	OleDbType.Numeric	System.Decimal
NUMERIC	18	DBTYPE_NUMERIC	DbType.Decimal	OleDbType.Numeric	System.Decimal

Строковые типы данных

К этой группе относятся типы данных CHAR, VARCHAR с кодовой страницей отличной от OCTETS.

С этими типами данных есть определенные сложности, потому что их представление и обработка зависит от настроек подключения и кодовой страницы самих данных.

По-умолчанию IBProvider работает в UNICODE-режиме (unicode_mode=true). Это означает что IBProvider предлагает обмениваться текстовыми данными с использованием двухбайтных UNICODE-символов.

В этом случае, для строковых данных с кодовой страницей отличной от NONE будут действовать следующие правила:

Тип данных FB/IB	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
CHAR	DBTYPE_WSTR	DbType.String	OleDbType.WChar	System.String
VARCHAR	DBTYPE_WSTR	DbType.String	OleDbType.VarWChar	System.String

Если строковые данные имеют кодовую страницу NONE, то представление будет другим:

Тип данных FB/IB	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
CHAR	DBTYPE_STR	DbType.AnsiString	OleDbType.Char	System.String
VARCHAR	DBTYPE_STR	DbType.AnsiString	OleDbType.VarChar	System.String

По-умолчанию, преобразование таких «NONE-данных» в UNICODE и обратно осуществляется с использованием кодовой страницы ASCII.

Это можно поменять, указав в строке подключения параметр «ctype_none» с именем символьного набора отличным от NONE.

Максимальное количество символов в CHAR/VARCHAR колонках зависит от кодовой страницы. Для однобайтных кодировок эта величина составляет 32767 и 32765 для CHAR и VARCHAR соответственно.

Бинарные типы данных

К этой группе относятся типы данных CHAR, VARCHAR с кодовой страницей OCTETS.

Тип данных FB/IB	Макс. Размер	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
CHAR (OCTETS)	32767	DBTYPE_BYTES	DbType.Binary	OleDbType.Binary	System.Byte[]
VARCHAR (OCTETS)	32765	DBTYPE_BYTES	DbType.Binary	OleDbType.VarBinary	System.Byte[]

Булевский тип данных

К этой группе относится тип данных BOOLEAN, поддерживаемый InterBase v7 и Firebird v3.

Тип данных FB/IB	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
BOOLEAN	DBTYPE_BOOL	DbType.Boolean	OleDbType.Boolean	System.Boolean

Кроме того, IBProvider может эмулировать поддержку этого типа данных. Подробности можно посмотреть в этой статье.

Типы данных даты и времени

Тип данных FB/IB	Настройки	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
TIMESTAMP	dbtimestamp_rules=0	DBTYPE_DBTIMESTAMP	DbType.DateTime2	OleDbType.DBTimeStamp	System.DateTime
DATE	dbdate_rules=0	DBTYPE_DBDATE	DbType.Date	OleDbType.DBDate	System.DateTime
TIME	dbtime_rules=0	DBTYPE_DBTIME	DbType.Time	OleDbType.DBTime	System.TimeSpan
TIME	dbtime_rules=1	DBTYPE_DBTIME2	DbType.Time	OleDbType.DBTime2	System.TimeSpan

В первоначальной версии OLE DB, тип для представления времени (DBTYPE_DBTIME ) не предполагал хранение долей секунды. Это было исправлено в MSSQL 2008 – у него появился тип DBTYPE_DBTIME2. На текущий момент, по умолчанию для типа TIME используется старый формат представления времени (DBTYPE_TIME). При работе с IBProvider-ом через рассматриваемый ADO.NET провайдер рекомендуется указать в строке подключения «dbtime_rules=1».

Блобы

Блобы делятся на две группы – текстовые и бинарные.

К текстовым относятся блобы с подтипом (SUB_TYPE) TEXT (id: 1) и кодовой страницей отличной от OCTETS.

К бинарным относятся блобы с подтипом отличным от TEXT и блобы с подтипом TEXT и кодовой страницей OCTETS.

На текстовые блобы распространяется проблема с NONE кодировкой, описанной в разделе «Строковые типы данных».

В приведенной ниже таблице предполагается что у текстового блоба определена нормальная (не NONE) кодовая страница и подключение работает в режиме UNICODE (unicode_mode=true).

Тип данных FB/IB	Тип данных OLE DB	System.Data.DbType	lcpi.data.oledb.OleDbType	Тип данных .NET Framework
BLOB (binary)	DBTYPE_BYTES	DbType.Binary	OleDbType.LongVarBinary	System.Byte[]
BLOB (text)	DBTYPE_WSTR	DbType.String	OleDbType.LongVarWChar	System.String

ADO.NET провайдер поддерживает все способы обработки блобов, включая потоковую обработку с использованием System.IO.Stream и System.IO.TextReader.

Методы OleDbDataReader для получения значений текстовых блобов:

• string GetString(int ordinal)
• TextReader GetTextReader(int ordinal)
• object GetValue(int ordinal)
• long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length)

Методы OleDbDataReader для получения значений бинарных блобов:

• byte[] GetBytes(int ordinal)
• Stream GetStream(int ordinal)
• object GetValue(int ordinal)
• long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length)

Методы для блочной загрузки содержимого блобов «long GetChars(…)» и «long GetBytes(…)», в свете наличия методов GetTextReader и GetStream, являются анахронизмом ранних версий ADO.NET.

Массивы

Было время, когда первым «знанием» об InterBase было «он использовался в танке Абрамс», а вторым «знанием» было «он поддерживает хранение многомерных массивы в колонках таблиц».

IBProvider предоставляет поддержку для этого типа данных. Соответственно, ADO.NET провайдер так же поддерживает работу с массивами.

В массивах можно хранить любые типы данных кроме блобов.

В следующем примере будет продемонстрирован код чтения колонки QUART_HEAD_CNT с массивом «INTEGER[1:4]» из таблицы PROJ_DEPT_BUDGET:

static void Test_032__read_arrays()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText
     ="select FISCAL_YEAR, DEPT_NO, QUART_HEAD_CNT\n"
     +"from  PROJ_DEPT_BUDGET\n"
     +"where PROJ_ID='VBASE'\n"
     +"order by FISCAL_YEAR, DEPT_NO";

    using(var rd=cmd.ExecuteReader())
    {
     while(rd.Read())
     {
      Console.Write("{0} [{1}]: ",
                    rd["FISCAL_YEAR"],
                    rd["DEPT_NO"]);

      uint n=0;

      foreach(var x in rd.GetArray(2/*QUART_HEAD_CNT*/))
      {
       ++n;

       if(n>1)
        Console.Write(", ");

       Console.Write("{0}",x);
      }//foreach x

      Console.WriteLine("");
     }//while
    }//using rd
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_032__read_arrays

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

Рассмотрим пример обновления колонки QUART_HEAD_CNT таблицы PROJ_DEPT_BUDGET, осуществляющий переворачивание (реверс) содержимого массивов.

Первичный ключ обновляемой таблицы состоит из колонок (FISCAL_YEAR, PROJ_ID, DEPT_NO).

static void Test_033__update_arrays()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=SYSDBA;\n"
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 using(var cn=new OleDbConnection(c_cn_str))
 {
  cn.Open();

  using(var tr=cn.BeginTransaction())
  {
   using(var cmd=new OleDbCommand(null,cn,tr))
   {
    cmd.CommandText
     ="select FISCAL_YEAR, DEPT_NO, QUART_HEAD_CNT\n"
     +"from  PROJ_DEPT_BUDGET\n"
     +"where PROJ_ID='VBASE'\n"
     +"order by FISCAL_YEAR, DEPT_NO";

    using(var rd=cmd.ExecuteReader())
    {
     cmd.CommandText
      ="update PROJ_DEPT_BUDGET set QUART_HEAD_CNT=:arr\n"
      +"where FISCAL_YEAR=:year and DEPT_NO=:dept and PROJ_ID='VBASE'\n"
      +"returning QUART_HEAD_CNT\n"
      +"into :new_arr";

     while(rd.Read())
     {
      var arr=rd.GetArray(2/*QUART_HEAD_CNT*/);

      int baseIndex=arr.GetLowerBound(0);

      //reverse
      for(int i1=baseIndex,i2=baseIndex+arr.Length,_c=baseIndex+arr.Length/2;i1!=_c;++i1)
      {
       --i2;

       int v=(int)arr.GetValue(i1);

       arr.SetValue(arr.GetValue(i2),i1);

       arr.SetValue(v,i2);
      }//for i

      cmd["arr"].Value  =arr;
      cmd["year"].Value =rd["FISCAL_YEAR"];
      cmd["dept"].Value =rd["DEPT_NO"];

      cmd.ExecuteNonQuery();

      var new_arr=(System.Array)cmd["new_arr"].Value;

      Console.Write("{0} [{1}]: ",
                    rd["FISCAL_YEAR"],
                    rd["DEPT_NO"]);

      uint n=0;

      foreach(var x in new_arr)
      {
       ++n;

       if(n>1)
        Console.Write(", ");

       Console.Write("{0}",x);
      }//foreach x

      Console.WriteLine("");
     }//while
    }//using rd
   }//using cmd

   tr.Rollback();
  }//using tr
 }//using cn
}//Test_033__update_arrays

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

ADO.NET провайдер, наряду со стандартными классами исключений, определяет и использует свой класс исключения – OleDbException.

OleDbException содержит коллекцию описаний ошибок, сформированных OLE DB провайдером и непосредственно самим ADO.NET провайдером.

• Доступ к описаниям ошибок OLE DB провайдера осуществляется через коллекцию OleDbException.Errors. Эта коллекция, реализуемая классом OleDbErrorCollection, перечисляет объекты с интерфейсом OleDbError.
• Доступ ко всем ошибкам осуществляется через методы интерфейса «lcpi.lib.structure.t_err_records_r».

Свойства Source и Message, класса OleDbException, агрегируют описания всех ошибок.

Описания ошибок, полученных от OLE DB провайдера, могут предоставлять дополнительную информацию в виде строки с кодом SQLSTATE и номера оригинальной ошибки сервера базы данных – NativeError.

static void Test__OleDbException()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=dummyPSWD;\n" //incorrect password
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 try
 {
  using(var cn=new OleDbConnection(c_cn_str))
  {
   cn.Open();
  }
 }
 catch(OleDbException e)
 {
  Console.WriteLine("- - - - - - - - - - - - - - - - - - - - - - - OleDbException");
  Console.WriteLine("Error  : {0}",e.ErrorCode);
  Console.WriteLine("Source : {0}",e.Source);
  Console.WriteLine("");
  Console.WriteLine("{0}",e.Message);

  Console.WriteLine("");
  Console.WriteLine("- - - - - - - - - - - - - - - - - - - - - - - OleDbException.Errors");

  for(int i=0,_c=e.Errors.Count;i!=_c;++i)
  {
   if(i>0)
    Console.WriteLine("\n--------------");

   Console.WriteLine("Source      : {0}",e.Errors[i].Source);;
   Console.WriteLine("SQLState    : {0}",e.Errors[i].SQLState);
   Console.WriteLine("NativeError : {0}",e.Errors[i].NativeError);
   Console.WriteLine("");
   Console.WriteLine("{0}",e.Errors[i].Message);
  }//for i
 }//catch
}//Test__OleDbException

OleDbException (как, впрочем, и основная масса других исключений, генерируемых ADO.NET провайдером) поддерживает локализацию сообщений об ошибках. При этом язык сообщения определяется не в момент генерации исключения, а непосредственно в момент запроса текста сообщения.

В следующем примеры осуществляется подключение с некорректным паролем и дважды выводится текст перехваченного исключения – сначала на английском, потом на русском.

static void Test__Exception_And_CurrentCulture()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=dummyPSWD;\n" //incorrect password
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 try
 {
  using(var cn=new OleDbConnection(c_cn_str))
  {
   cn.Open();
  }
 }
 catch(Exception e)
 {
  Helper__PrintException(e,"EN");
  Helper__PrintException(e,"RU");
 }//catch
}//Test__Exception_And_CurrentCulture

//------------------------------------------------------------------------
static void Helper__PrintException(Exception exc,string cultureName)
{
 var prevCulture=System.Threading.Thread.CurrentThread.CurrentUICulture;

 try
 {
  System.Threading.Thread.CurrentThread.CurrentUICulture
   =new System.Globalization.CultureInfo(cultureName);

  Console.WriteLine("--------------------------------- [{0}]",cultureName);

  Console.WriteLine("{0}\n\n{1}",exc.Source,exc.Message);
 }
 finally
 {
  //restore culture
  System.Threading.Thread.CurrentThread.CurrentUICulture=prevCulture;
 }
}//Helper__PrintException

Событие InfoMessage

На уровне класса OleDbConnection определено событие InfoMessage, предназначенное для получения предупреждений и информационных сообщений от OLE DB провайдера. Класс объектов сообщений OleDbInfoMessageEventArgs идентичен классу OleDbException как по набору свойств/методов, так и по поведению. Собственно говоря, объект класса OleDbInfoMessageEventArgs возвращают данные из внутреннего объекта OleDbException.

Через InfoMessage можно получать и сообщения об ошибках (непосредственно перед тем как ADO.NET провайдер сгенерирует исключение). Для это нужно установить свойство OleDbConnection.FireInfoMessageEventOnUserErrors равным true.

static void Test__InfoMessage()
{
 const string c_cn_str
  ="provider=LCPI.IBProvider.3;\n"
  +"location=localhost:d:\\database\\employee.fdb;\n"
  +"user id=dummyPSWD;\n" //incorrect password
  +"password=masterkey;\n"
  +"dbclient_library=fbclient.dll";

 try
 {
  using(var cn=new OleDbConnection(c_cn_str))
  {
   cn.InfoMessage+=Helper__PrintInfoMessage;

   cn.FireInfoMessageEventOnUserErrors=true;

   cn.Open();
  }
 }
 catch(Exception)
 {
  Console.WriteLine("");
  Console.WriteLine("CATCH EXCEPTION!");
 }//catch
}//Test__InfoMessage

//------------------------------------------------------------------------
static void Helper__PrintInfoMessage(object Sender,OleDbInfoMessageEventArgs InfoMsg)
{
 Console.WriteLine("- - - - - - - - - - - - - - - - - - - - - - - InfoMsg");
 Console.WriteLine("Error  : {0}",InfoMsg.ErrorCode);
 Console.WriteLine("Source : {0}",InfoMsg.Source);
 Console.WriteLine("");
 Console.WriteLine("{0}",InfoMsg.Message);

 Console.WriteLine("");
 Console.WriteLine("- - - - - - - - - - - - - - - - - - - - - - - InfoMsg.Errors");

 for(int i=0,_c=InfoMsg.Errors.Count;i!=_c;++i)
 {
  if(i>0)
   Console.WriteLine("\n--------------");

  Console.WriteLine("Source      : {0}",InfoMsg.Errors[i].Source);;
  Console.WriteLine("SQLState    : {0}",InfoMsg.Errors[i].SQLState);
  Console.WriteLine("NativeError : {0}",InfoMsg.Errors[i].NativeError);
  Console.WriteLine("");
  Console.WriteLine("{0}",InfoMsg.Errors[i].Message);
 }//for i
}//Helper__PrintInfoMessage

---