Работа с InterBase и Firebird в ADO.NET. Часть 3 — Дополнительные возможности (DDL, метаданные, последовательности ODBC)

Схемы метаданных БД.

Неотъемлемой частью всех Ole Db провайдеров являются схемы метаданных. Они используются клиентами для получения описания базы данных: списка хранимых процедур, структур таблиц, зарегистрированных доменов, ограничений, первичных и внешних ключей и т.д. Для того чтобы Ole Db провайдер смог работать с библиотекой ADO .Net он должен поддерживать Ole Db схемы, так как компоненты библиотеки активно используют эту информацию.

В документации к IBPRovider вы найдете список схем, которые он поддерживает.

Запросить определенную схему можно по её названию. Для этого у объекта OleDbConnection есть метод GetSchema(). В Net 2 появился метод GetOleDbSchema(), который в качестве аргумента принимает одно из значений OleDbSchemaGuid. Оба этих метода ведут себя одинаково и возвращают абсолютно идентичные экземпляры DataTable с набором информации по схеме.

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

Restriction columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. Если мы хотим получить описание всех колонок для таблицы EMPLOYEE мы должны использовать схему COLUMNS следующим образом:

DataTable schema_table = connection.GetSchema(«COLUMNS», new string[] { null, null, «EMPLOYEE» });

Аналогично для метода GetOleDbSchema():

DataTable schema_table =
connnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, «EMPLOYEE» });

DDL запросы. CREATE/ALTER/DROP

Данный вид запросов позволяет управлять метаданными БД. Вы можете создавать, удалять и модифицировать колонки, таблицы и целые базы данных через SQL выражения, в тексте которых содержатся DDL инструкции. Если применять их совместно со схемами метаданных, то можно без особых усилий копировать структуры существующих баз данных и создавать новые.

Все DDL запросы, за исключением CREATE DATABASE и DROP DATABASE, могут выполняться как в режиме автоматического подтверждения, так и в контексте транзакции. По умолчанию фиксирование изменений произведенных DDL запросами отключено. Это сделано из соображений безопасности. Для того чтобы включить подтверждение DDL запросов необходимо установить свойство auto_commit_ddl. Его описание есть в разделе «Методы подключения к базе данных» этой статьи.

Приведу пример использования DROP DATABASE для удаления базы данных:

private void DropDatabase()
{
    if (File.Exists(databasePath))
    {
        OleDbConnectionStringBuilder builder =
           CreateConnectionStringBuilderForSample();

        //отключаем использование пула для этого подключения
        builder.OleDbServices = OleDbServicesValues.EnableAll &
                                ~OleDbServicesValues.ResourcePooling;

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

        new OleDbCommand(«drop database»,con).ExecuteNonQuery();
        con.Close();
    }
}

Обратите внимание на то, что для подключения, которое будет использоваться для удаления базы данных, мы отключили использование пула ресурсов. IBProvider умеет информировать сервисы Ole Db о, ставших недоступными, подключениях и в данном случае это действие является избыточным. Но данный код оставлен здесь для решения возможных проблем при использовании Ole Db провайдеров других производителей.

Теперь DDL для создания новой базы данных:

private OleDbConnection CreateDatabase()
{
    //подключение к существующей бд employee.gdb
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();

    //создание новой базы данных
    new OleDbCommand(
               «create database ‘» + server_name + «:» + databasePath + «‘\n» +
                       «USER     ‘» + user_name + «‘    \n» +
                       «PASSWORD ‘» + password + «‘ \n», con).ExecuteNonQuery();

    con.Close();

    return new OleDbConnection(
          CreateConnectionStringBuilderForSample().ToString());
}

И, наконец, законченный пример, который сначала удаляет базу данных, потом создает на её месте новую и определяет в ней две таблицы, связанные внешним ключом:

public void CreateNewDBSample()
{
    DropDatabase();

    OleDbConnection con = CreateDatabase();
    con.Open();
    OleDbTransaction trans = con.BeginTransaction();

    //создаем таблицу SAMPLE_TABLE с двумя колонками
    ExecuteDDL(
        «CREATE TABLE SAMPLE_TABLE(                     « + //int column                
        » ID             INTEGER      NOT NULL,         « + //varchar column
        » NAME           VARCHAR(64),                   « + //primary key
        «CONSTRAINT PK_SAMPLE_TABLE PRIMARY KEY(ID)    )», trans);

    //создаем SAMPLE_TABLE_2 связанную через FOREIGN KEY 
    ExecuteDDL(
        «CREATE TABLE SAMPLE_TABLE_2 (                  « +
        » ID             INTEGER      NOT NULL,         « + //int columns
        » PARENT         INTEGER      NOT NULL,         « + //int column
        «CONSTRAINT PK_SAMPLE_TABLE_2 PRIMARY KEY(ID),   « + //primary key
        «CONSTRAINT FK_SAMPLE_TABLE_PARENT              « +  //foreign key
        «FOREIGN KEY(PARENT) REFERENCES SAMPLE_TABLE(ID))», trans);

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

В последних версия IBProvider.3 появилась возможность создания базы даннных без промежуточного подключения к БД.

О том как это делать, читайте здесь: Как программно создать базу данных Firebird или InterBase используя ADOX и Visual C# .NET (или VBScript)

Управляющие последовательности ODBC

Управляющие последовательности позволяют преобразовывать текст запроса в процессе выполнения. Последовательность включается в текст запроса в фигурных скобках. Например {fn CURDATE} — выражение будет преобразовано в значение серверного времени.

В ODBC определены управляющие последовательности для следующих характеристик:

• Работа со временем и датами
• Функции преобразования типов
• Предикат LIKE
• OUTER JOIN
• Вызовы хранимых процедур

Данное расширение активно используется такими инструментами как MS SQL Server, Business Intelligence, Crystal Reports, и их поддержка со стороны Ole Db провайдера обеспечивает возможность их совместного использования.

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

Следующий пример демонстрирует применение управляющих последовательностей в тексте SQL запросов:

public void ODBCQueriesTest()
{
   OleDbConnectionStringBuilder builder =
ConnectionProvider.GetConnectionStringBuilderFromUDL();
   builder.Provider = «LCPI.IBProvider.2»;
   builder.Add(«support_odbc_query»,«true»);

   OleDbConnection con = new OleDbConnection(builder.ToString());
   con.Open();
   OleDbTransaction trans = con.BeginTransaction();

   //select current day name
   OleDbCommand cmd = new OleDbCommand(
       «select « +
       «{fn dayname({fn now()})} as DAY_NAME,» +
       «{fn dayofweek({fn now()})} as DAY_OF_WEEK,» +
       «{fn dayofmonth({fn now()})} as DAY_OF_MONTH,» +
       «{fn dayofyear({fn now()})} as DAY_OF_YEAR « +
       «from RDB$DATABASE», con, trans);

   using (OleDbDataReader rdr = cmd.ExecuteReader())
       if (rdr.Read())
           for (int i = 0; i < rdr.FieldCount; i++)
                  Console.WriteLine(rdr.GetName(i) + «: « + rdr[i].ToString());

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

Заключение

В своем обзоре я рассмотрел наиболее часто используемые возможности библиотеки ADO .Net на примере OLE DB провайдера.

Основное преимущество OLE DB провайдеров перед управляемыми (.Net Data Providers) — это возможность их использования не только в среде .Net Framework, а практически в любых средах поддерживающих COM.

Использование IBProvider в качестве поставщика данных Firebird и InterBase позволяет:

• Обмениваться данными с приложениями Microsoft Office (включая Access и Outlook) и использовать VBA (Visual Basic for Applications)
• Выгружать данные в 1С и обратно.
• Подключать Firebird и InterBase к MS SQL Linked Server.
• Писать расширенные сценарии автоматизации при помощи WSH (Windows Script Host), VBScript, Java Script
• Разрабатывать WEB-приложения, WEB-сервисы c базами данных не только на ASP.Net, но и на ASP
• Производить анализ данных при помощи OLAP средств, разрабатывать отчетные системы при помощи средств Business Intelligence (SSAS, SSRS).
• Использовать провайдер в составе распределенных транзакций.

IBProvider поддерживает все существующие версии InterBase/Yaffil/Firebird. Имеет встроенный менеджер управления памятью и SWAP для пользовательских данных. Поддерживает многопоточность, а так же серверные и клиентские курсоры. Осуществляет многоязыковую поддержку.

Надеюсь, данное руководство позволит вам расширить круг применяемых возможностей ADO .Net не только для работы с Firebird и InterBase, но и с другими базами данных.

Другие части статьи

Часть 1. InterBase и Firebird — Основы работы с Firebird и InterBase в ADO.Net.
Часть 2. InterBase и Firebird — Поддержка визуальных средств Visual Studio 2005-2008.

Скачать примеры к статье.
Скачать IBProvider Professional Edition

Полезные ссылки

• Новые возможности Firebird 2.0. Примеры для ADO .Net (c#).
• Новые возможности Firebird 2.1. Примеры для ADO .Net (c#).
• ODBC Firebird driver, ODBC InterBase или OLE DB?
• Управляющие последовательности ODBC драйверов.
• Описание Ole Db схем в MSDN.
• Firebird скачать
• Firebird 2 release notes.
• Официальный сайт Firebird SQL Server.

Список литературы

1. М.Фаулер. Архитектура корпоративных программных приложений. Изд. Москва — Спб — Киев. 2004 г.
2. Э. Гамма, Р. Хелм, Р. Джонсон, Дж. Влиссидес. Приемы объектно-ориентированного проектирования. Паттерны проектирования. Изд. Питер. 2006 г.
3. А. Ковязин, С.Востриков. Мир InterBase. Архитектура, администрирование и разработка приложений баз данных в InterBase/Firebird/Yaffil (+ CD-ROM). Изд. КУДИЦ-Образ, Питер. 2005 г.
4. Б.Бошемин. Основы ADO .Net. Изд. Вильямс. 2003 г.