Publish date: 2015-03-31
Updated: 2018-05-19

Using «LCPI ADO.NET Data Provider for OLE DB» with Firebird SQL Server

Introduction
Preparation of system
Preparation of project
Work with database connection
Work with transactions
Work with queries
Data type support
Error handling

Introduction

In this article is going to be examined the basic issues of using «LCPI ADO.NET Data Provider for OLE DB», that was created to address the limitations of the «standard ADO.NET provider for OLE DB» from the .NET Framework and full disclosure of Firebird SQL Server technological capabilities and IBProvider.

Most part of this article may be applied to InterBase SQL Server.

Preparation of system

Visual Studio

To create the code in this article was used Visual Studio 2013 Community Edition.

Firebird

For a core set of examples was used Firebird 2.5. In some cases — FB 3.0.

Make sure that in the System32 and SysWOW64 are contained modules of server’s client — fbclient.dll.

Test databases

For a core set of examples was used a standard database «employee.fdb» from the distributive of Firebird 2.5.

In some cases, there was used separate test databases. In these cases, DDL was given to produce desired constructions.

ADO.NET provider installation

  1. Download the EXE-installer of ADO.NET provider from site www.ibprovider.com.
  2. Run.
  3. Select «Install all».
  4. Select the primary assemblies for .NET Runtimes — 3.5 and 4.5.1.
  1. Click «Next» on each of the remaining pages until you get to «The Ready to Install» page. Start the installation.

IBProvider installation

  1. Download the MSI-installers of IBProvider for Windows 32bit and Windows 64bit.
  2. Install both packages using default configuration.

Preparation of project

For all examples was used console application C# for FW 4.5.1.

Adding ADO.NET provider assembly references

In the project will need to add references to ADO.NET provider assemblies. There are several ways to do it.

The first way. Through the interface Visual Studio.

  1. Open menu with the properties of the project and select «Add->Reference»:
  1. Find and select the assemblies:
    • «LCPI ADO.NET Data Provider for OLE DB [NET 4.5.1]»
    • «LCPI Instrumental Library for .NET 4.5.1»

The second way. Through direct editing csproj file. You can append release and debug assemblies in accordance to the configuration of the project:

<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' " />
Version numbers in your ADO.NET provider installation are likely to be others.

The third way. The last way — add NUGET-package «lcpi.data.oledb» to the project. This package can be found on www.nuget.org.

Namespaces

ADO.NET provider components are contained in the namespace «lcpi.data.oledb». Add to the top of CS-file the following line:

using lcpi.data.oledb;

Additionally, for convenience, you have to open access to the namespace «System.Data»:

using System.Data;

This namespace contains types shared between all ADO.NET data providers.

Work with database connection

Manage connection is realized through object of OleDbConnection class.

Database connection

To connect to the database:

  1. Create an object OleDbConnection.
  2. Specify the connection string.
  3. Call the method 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

The connection string can be passed directly to the constructor of OleDbConnection:

var cn=new OleDbConnection(cn_str);

Parameters of given connection string divided into two groups:

  1. ADO.NET provider parameters: provider.
  2. OLE DB provider parameters: location, user id, password, dbclient_library.

ADO.NET provider handles the following parameters:

Name Description
Provider Identifier of OLE DB provider.
File Name File with parameters of database connection.

OLE DB provider (IBProvider) has much more connection settings. You can view the full list in the documentation. Here are the main:

Name Description
Location Location database.
User ID. User name.
Password User password.
dbclient_library Name or file path to the module of server’s client (gds32.dll, fbclient.dll, ibclient64.dll).
dbclient_library_64 Name or file path to the 64-bit module of server’s client. Used only in 64-bit processes.
ctype Code page of the connection.
ctype_none Code page of text data with NONE charset.
auto_commit Permission for usage of automatic transactions.
nested_trans Permission for creation of «nested» transactions.
named_param_prefix Prefix of named parameters. By default — «:».

In addition, there are properties that are processed by ADO.NET provider and by OLE DB provider. For example:

Name Description
OLE DB Services Configuration interaction with the OLE DB provider. In particular — the using of connection pool.
Persist Security Info Configuring access to the values of the authentication parameters after connection to the database.

To simplify the process of forming the connection string from program code, in ADO.NET provider implemented a special component — OleDbConnectionStringBuilder.

Property values can be specified via the «indexer»:

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

Or via OleDbConnectionStringBuilder properties:

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

By default, after you connect to the database from the connection string (OleDbConnection.ConnectionString) will be excluded property with password:

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

This action depends on the value of the property «Persist Security Info»:

  • False indicates to exclude parameters from the connection string, related to security. This is the default value.
  • True saves property values in the connection string, related to security.

Using file with initialization parameters

You can stored connection initialization parameters in UDL-file and specify path to this file in the connection string:

  1. Create an empty file with the extension UDL — «test.udl».
  2. Select this file in Windows Explorer and press Enter.
  3. The dialog box «Data Links» will be open.
  4. Switch to the tab «Provider» and select «LCPI OLE DB Provider for InterBase [v3]»:
  1. Click «Next >>» or click on the tab «Connection».
  2. Fill in the fields with the main connection parameters:
  1. On the tab «Advanced», you can specify advanced connection settings. For example, the name of the server’s client module (fbclient.dll):
Don’t worry about the «type» of server’s client.
IBProvider defines the actual type and version of the client by analyzing the VERSIONINFO resource of this DLL.
  1. Now you can return to the tab «Connection» and check your connection settings by clicking the «Test connection». If everything was specified properly, a dialog appears with information about the type and version of the database server:
  1. Close the dialogs («OK», «OK»). Allow to save the password in open form:
  1. Write the following code:
static void Test_UDL()
{
 var cn=new OleDbConnection("file name=test.udl");

 cn.Open();
}//Test_UDL

Take care that the test process found «test.udl». The best way — to keep this file in the same directory as the EXE program.

Database disconnection

There are several ways to close connection to the database.

The first way — using the method OleDbConnection.Close. After calling method «Close» connection object remains operational and available to working. For example, call the method «Open» again:

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

The second way — calling method OleDbConnection.Dispose. It releases the connection and fully deinitializes connection object. If you try to continue to work with it, you’ll get an ObjectDisposedException exception:

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

And the last, third way — don’t call any methods. The garbage collector earlier or later will find this connection object and forces it to release all the resources which are the pointers to OLE DB objects:

In all cases, ADO.NET provider does not initiate the actual disconnect of the database. He just frees pointers to objects of OLE DB provider.

In fact disconnect will occur only after releasing of the last reference to OLE DB connection.

If the connection to the database had been created through a connection pool (this is the default mode) or remained external links to the COM-object of OLE DB data source, the connection remains active.

It is recommended to call Close or Dispose after finishing of work with connection.

Connection pool

Connection pool (in simplified view) is the map of connection strings to the connection objects. All the mechanics is hidden in the standard service components of OLE DB. ADO.NET provider may only allow the use of a connection pool, or prohibit its use. Enable/disable connection pooling is made through initialization property «OLE DB Services».

By default, as noted earlier, the connection pool is allowed:

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

If you disable the pool, the connection from the previous example in each iteration will be has a new ID:

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

Creation and deletion of database

To create and connect the new database, you should:

  1. Create connection string with parameters of a new database and its connection parameters.
  2. Set this string in OleDbConnection.
  3. Call the method OleDbConnection.CreateDatabase.

Available parameters of the new database are described in documentation of the IBProvider.

Consider the example of creating a new database, specifying:

  1. SQL dialect of the database.
  2. Page size.
  3. Code page by default.
  4. Collate of the code page by default.

After connected let’s read some of the parameters that were specified when the database is created.

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

After calling CreateDatabase, the connection object moves into the open state and allows to work with database as it works after execution of the Open method.

Database is removed using OleDbConnection.DropDatabase method. Example of deleting a database created in the previous example:

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

Work with transactions

Manage connection is realized through object of OleDbTransaction class.

Start of transaction

The transaction is created through a call of OleDbConnection.BeginTransaction. Exists a couple overloaded variants of this method.

The first variant, without parameters, creates a transaction with ReadCommitted isolation level:

OleDbTransaction BeginTransaction();

The second variant of this method allows to specify the isolation level of the new transaction:

OleDbTransaction Begin(IsolationLevel isolationLevel);

IBProvider supports three transaction isolation levels:

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

Completion of transaction

Completion of the transaction is carried out by one of the methods OleDbTransaction: Commit or Rollback. OleDbTransaction.Commit fixes the changes, OleDbTransaction.Rollback aborts the changes.

In addition, the current implementation of ADO.NET provider automatically rolls back the transaction when calling OleDbTransaction.Dispose.
This was implemented for compatibility with «System.Data.OleDb». However, in future it can be changed.

After completion of the transaction, the object OleDbTransaction becomes useless — there is no ways to activate it again.

If transaction was completed OleDbTransaction.Connection contains a null reference.

Completion of transaction with retaining of context

Beside of «hard» finish of the transaction, OleDbTransaction provides additional pair of methods with preserving of activity after commit or rollback: CommitRetaining, RollbackRetaining.

Nested transactions

One of the unique features IBProvider is simulation of «nested» transactions through savepoints. By default, this mechanism is turned off. To activate it needed to specify in the connection string «nested_trans=true». This allows to call the method OleDbTransaction.Begin, which returns the control object of «nested» transaction. For «nested» transaction are also available methods Commit, Rollback, CommitRetaining, RollbackRetaining.

It should be noted that ADO.NET provider rejects by default commit of the parent transaction if it has an active nested transaction.

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

To change this behavior in the connection string must specify «NetProv: NestedTransRules=AllowImplicitCommit». Or set OleDbConnectionStringBuilder.NetProvider.NestedTransRules property (valid values are defined in OleDbPropertiesValues.NetProvider.NestedTransRules enumeration). In this case if the parent transaction is being commited, ADO.NET does an implicit commit all nested transactions:

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

Automatic transactions

In addition to explicity control the start / end of the transaction, IBProvider provides the automatic generation of a transactional context for database operations. Suffice it to point in the connection string «auto_commit = true» and IBProvider will self start and end the transaction for each database operation.

Isolation level of automatic transaction is defined by the property of the connection string «auto_commit_level». The isolation level by default — «Repeatable Read».

Multiple transactions in the one connection

One of the distinguishing features of the Firebird and InterBase is the ability to simultaneously create multiple, independent transactions within a single connection. IBProvider provides support for this feature. However, in ADO.NET technology this feature is not supported — recall of the method OleDbConnection.BeginTransaction, if previous transaction still active, will be finished with error:

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

To solve this problem at the level of OleDbConnection was added the CloneSession method. He creates a new OleDbConnection, which is tied to an existing object OLE DB connection and to the new OLE DB session object (which, in fact, presents the transaction). Schematically, it looks like this:

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

In this example, were created two objects «cn1» and «cn2», attached to the same database connection (id: 1346). And were created two independent transactions (3698 and 3699), belonging to this connection.

«Parallel» is only available for first-level transaction. Unable to create two active «parallel» transactions at the second (and beyond) level with general parent transaction.

Distributed transactions

ADO.NET provider supports two of ways of joining to the distributed transaction, created by the .Net Framework: explicit and implicit.

For explicitly joining need to use the OleDbConnection.EnlistTransaction method:

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

Implicit connection to a distributed transaction is carried out by creating a connection to the database within the «context» of the active object 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

Automatic connection to a distributed transaction can be disabled by modifying the initialization property «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

Work with queries

For work with queries to database need to create and use the objects of OleDbCommand class. Queries are exists in the part of an open connection and active transaction. There are a couple of ways to create an object of command.

The first way — an explicit creation of the object:

var cmd=new OleDbCommand();

OleDbCommand has additional constructors for specifying the query text, connection and transaction.

The second way — through a call of OleDbConnection.CreateCommand method:

var cmd=cn.CreateCommand();

The resulting command object will be attached to the connection object «cn».

The text of the query is indicated by the CommandText property or passed to the OleDbCommand constructor.

For execution of query need to call one of the methods: ExecuteScalar, ExecuteReader or ExecuteNonQuery. It depends on the type of request and the way of receiving result. Formally, all queries can be performed using the method ExecuteReader.

At the level of OLE DB provider all types of queries are executed by unified method ICommand::Execute.

ExecuteScalar

OleDbCommand.ExecuteScalar method returns the one value from the first column of the first row. The other results are ignored. For example, this method is useful for queries that count the number of records in the table — respectively return only one value:

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

In the case of an empty resulting set, that is, with zero rows amount, ExecuteScalar returns 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

If the query does not return a set (e.g., this is an update-query), then ExecuteScalar also returns 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 executes the command and returns an OleDbDataReader, designed for one-way enumeration of result records.

Positioning to the next record is performed by OleDbDataReader.Read. It returns:

  • true, if the next record was selected.
  • false, if there are no more records.

Note that the positioning on the first record of the set is not carried out — you need to call the OleDbDataReader.Read method.

After work with the OleDbDataReader object, it is desirable to called the Close or Dispose method.

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

You can check availability of records using the OleDbDataReader.HasRows property:

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 allows the execution of queries that do not return a result set. In this case, all the same OleDbDataReader object is returned. This object is just used for reading OleDbDataReader.RecordsAffected property. It is indicates the number of records affected by the query.

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 method designed for queries that do not return a result set — insert, update, delete, DDL queries. The method returns the number of rows affected by the command.

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

Commands with parameters

In the actual operation with the database are usually used queries with parameters. This allows you to prepare a query once and execute it multiple times for different sets of values. ADO.NET provider and IBProvider are given a full support for parameterized queries:

  • You can use named and unnamed (positional) parameters.
  • You can form description of the parameters by yourself and generate them automatically.
  • Supports IN, OUT and IN/OUT parameters.
  • Provides an opportunity of configuring type of parameter output values.
  • Supports usage of parameters in scripts (command with multiple SQL-queries).

So, you’ve got all for comfort work with parameterized SQL-queries.

For work with parameters need to use the OleDbCommand.Parameters property. This property returns an object of OleDbParameterCollection class, serving a collection of command parameters. In addition, by analogy with the classic ADODB, at the level of the same OleDbCommand class implemented two non-standard «indexer» to access the parameters by name and by index.

Unnamed parameters in the query text are indicated by a marker ‘?’:

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

The next couple of examples demonstrate «classic» way of running this query with the explicit definition of query parameter descriptions:

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

The size of this code can be reduced through usage of the AddWithValue method. This method independently determines the type of parameters and defines the «input» direction (by default):

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 provider may to provide parameter descriptions. Generation of command parameter descriptions is performed using the OleDbParameterCollection.Refresh method:

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

And the last way — it’s just assign parameter values through «indexers» of command parameters. OleDbCommand will automatically obtain parameter descriptions from OLE DB provider:

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

Unnamed (or positional — as you wish) parameters have the one serious problem — they need to be defined strictly in the order in which they appear in the text of the command. And since there is no name, it will not turn twice to use the same parameter in the query. There are, of course, a slight advantage — an appeal to the parameter by index is more effective than by name. But on the whole, much safer define and use parameters with names.

Standard .NET provider for OLE DB of the .NET Framework (System.Data.OleDb) implements limited support of named parameters.

Marker of named parameter consist of prefix and name. By default, as prefix used a colon — ‘:’. The prefix can be changed through the initialization property «named_param_prefix».

You can define your named parameters by yourself (as you define the unnamed parameters) or request from the OLE DB provider. Rewrite the last example using named parameters with usage of ‘@’ prefix (in the style of 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

If you want to save the prefix of parameter in its name, the connection string must contain «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 the previous examples are viewed requests with IN-parameters, which transmit values to the database server. Also exist output parameters, with which you can get the result of the query.

OUT-parameters are present not only in queries for execution of stored procedures but in the queries with section «RETURNING» too. For example, in the query «INSERT … RETURNING …».

In «INSERT … RETURNING …» the names of OUT-parameters are assigned by server and, most likely, coincide with the names of re-readed columns. It is not always convenient, therefore IBProvider extends this query to the design «INSERT … RETURNING … INTO …», providing the ability to explicitly specify the names of OUT-parameters.

Consider the use of query «INSERT … RETURNING … INTO …» on the example of adding a new record to the table EMPLOYEE of standard database employee.fdb. This table has a trigger «BEFORE INSERT», which generates the primary key value. This value will be received through the OUT-parameter:

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

The following example is almost identical to the previous one. The difference lies in the use of IN/OUT parameter «EMP_NO». At the entrance indicated the NULL-value (DBNull.Value). As a result, this parameter contains the generated value of the column «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

By default, the return type of value is determined by the type of the parameter. For example, a text BLOB will be returned as a string (System.String). However, this can be changed. ADO.NET provider allows to return the object for the data flow reading (System.IO.TextReader) instead of a string.

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

In this example configures the OUT-values of the parameter «desc»:

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

The first argument of the method «Set» specifies OLE DB type value which is returning by the OLE DB provider. In this case it is COM-object.

The second argument indicates to the OLE DB provider that the COM-object must provide interface for text reading. ADO.NET provider, in turn, uses this argument to determine the type of .NET object to service this COM-object — lcpi.data.oledb.OleDbTextReader.

In reality, of course, «typeof (System.IO.TextReader)» is not transmitted to IBProvider, but the identifier COM-interface is. In this case it will be IID_IIBP_SequentialStream_WideChar – identifier of a specific interface for streaming loading text data in the form of double-byte UNICODE-characters.

Unfortunately, the OLE DB specification has not a standard interface for such a task. But it does not prohibit to define and use own interfaces for these goals.

You can return the OUT-values of binary blobs as a stream of bytes in a similar manner:

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

In this case, is required COM-object with standard interface ISequentialStream. And ADO.NET provider will create an object of lcpi.data.oledb.OleDbStream class.

OleDbParameter.OutputBinding (this property returns an object of OleDbValueBinding class) has another variants of the method Set, some of them allow to explicitly specify identifier of COM-interface. This allows enough flexibility to customize the presentation of the values of OUT-parameters for specific tasks.

Execution of stored procedures

Firebird и InterBase has two types of stored procedures (SP):

  1. SP which return the result as a set of rows.
  2. SP which return the result through OUT-parameters or return nothing.

For execution of first type stored procedure need to use «select … from …» statement:

select * from stored_procedure_name(<input_param_list>)

Obviously, to obtain a result of this request you have to use the OleDbCommand.ExecuteReader method:

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

Stored procedure of the second type (with result in output parameters) is executed by using SQL-query of the form:

execute procedure stored_procedure_name(<input_param_list>);

OUT-parameters in the query text is not specified. They are implicit with names that were specified when creating a stored procedure.

For obtain OUT-parameter values need to define their descriptions in OleDbCommand.Parameters.

In the following example, we will create a simple stored procedure SP_ADD (if it was not created earlier) and execute it. As usual, we take the opportunity of automatic formation of parameter descriptions.

It should be noted that the automatic generation of parameter descriptions is initiated by setting IN-parameter values through «indexer».
In the general case, for generation of parameter descriptions need to call the OleDbParameterCollection.Refresh method. 
/*

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

In addition to supporting of standard syntax of calling stored procedures, IBProvider supports alternative syntax. For example:

 exec stored_procedure_name;

In the case of a such request, IBProvider automatically determines a way of stored procedure execution and generates a list of parameters. By default, formed the list of unnamed parameters. However, if you specify in the connection string «exec_sp_named_param=true», OLE DB provider will receive and use the original names of the stored procedure parameters.

Let’s rewrite the previous example with usage of this syntax. For brevity, the code of creating the stored procedure SP_ADD is omitted.

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

The result will be identical — the stored procedure returns «result» equal to three:

The second way involves an explicit definition of the IN and OUT parameters in the query text:

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

The third way allows you to explicitly specify IN-parameters in the query. Output parameters will be implicit.

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

And the last way, is the call to the stored procedure using the syntax ODBC. The query text contains the explicity list of IN and OUT parameters. In the connection string must specify «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

Work with FB3 packages

In Firebird v3 (currently this Firebird version is in development) appeared opportunity of grouping stored procedures (and functions) into packets (PACKAGE).

Detailed description of the packages can be found in the server documentation. Here is a brief description.

PACKAGE consists of two parts — a header and a body. First is defines the header, which can be considered as interface of PACKAGE, and then determined its body (BODY).

These DDL queries are applied to the PACKAGE header:

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

To the PACKAGE body are used DDL-queries:

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

PACKAGE is an indivisible set of procedures and functions. DDL queries for define/modify/delete separate elements PACKAGE (procedures and functions) are not provided.

PACKAGE body must contain definitions for all the elements listed in the header. Plus internal (private) elements, which can be accessed only from procedures and functions of the PACKAGE.

The methods of work for the stored procedures of the package are the same as for ordinary (independent) procedures.

Procedures that return a set are executed by «select * from» statement:

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

Procedures that return result through OUT-parameters or return nothing, are executed by «execute procedure» statement:

execute procedure package_name.stored_procedure_name(<input_param_list>);

You can use one of the universal calls, which is implemented by means of the IBProvider. For example:

exec package_name.stored_procedure_name;

Let’s make a trivial MATH package with a single stored procedure – SP_ADD.

Title:

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

Body:

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

And let’s write code to call the stored procedure by request «exec math.sp_add». As usual, we use the services of automatic generation of parameter descriptions.

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 and, of course, Firebird with the very first version allows to create multiple active cursors of result sets in a single connection and select data from them in random order. But only with the advent of MSSQL 2005, thanks to marketers, this «technology» got the name – «Multiple Active Result Sets» (MARS).

IBProvider and ADO.NET provider allow you to work with multiple active cursors. Moreover, it is possible to repeatedly perform the command without closing of the associated cursor of the result set (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

Execution of scripts

IBProvider and ADO.NET provider provide the support for execution of scripts — commands with multiple SQL-queries. Queries can contain named parameters (automatic generation of parameter descriptions is not supported). The script can manage transactions and perform DDL-queries.

For enumeration of script results, use the method OleDbDataReader.NextResult.

Consider the script, that contains following operations:

  • Creation of table
  • Insertion records to the new table
  • Selection of these records by two different requests
  • Deletion of table

The script contains IN-parameters (data1, data2, data3) and OUT-parameters (id1, id2, id3).

/*
  set transaction;

  SET AUTODDL ON; -- implicitly commit the DDL queries

  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; -- implicitly commit the DDL queries\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

Cancellation of the query execution

Query cancellation is performed by OleDbCommand.Cancel method. The call of OleDbCommand.Cancel should be done in a separate thread, because the command execution methods (ExecuteScalar, ExecuteReader, ExecuteNonQuery) block the current thread up to operation completion.

In the following example, in the separate thread performed query of calling a stored procedure, and the main thread cancels this request.

/*

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

Data type support

At the current time (2015), IBProvider provides support for all data types Firebird/InterBase. ADO.NET provider, using the .NET Framework capabilities, makes it simple to work with them at the level of the application code.

Data types can be divided into the following groups:

  • Integer data types;
  • Real data types;
  • Data types NUMERIC and DECIMAL;
  • String data types;
  • Binary data types;
  • Boolean;
  • Data types date and time;
  • Blobs;
  • Arrays;

Integer data types

This group of data types includes types SMALLINT, INTEGER, BIGINT

Data type FB/IB The size in bytes Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .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

Floating-point data types

This group of data types includes types FLOAT, DOUBLE PRECISION.

Data type FB/IB The size in bytes Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type.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 data types

Data types NUMERIC and DECIMAL, in general, are not distinguished and processed the same.

Data type FB/IB Max. accuracy Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .NET Framework
DECIMAL 18 DBTYPE_NUMERIC DbType.Decimal OleDbType.Numeric System.Decimal
NUMERIC 18 DBTYPE_NUMERIC DbType.Decimal OleDbType.Numeric System.Decimal

String data types

This group includes the data types CHAR, VARCHAR with a code page other than OCTETS.

These data types have some problems because their representation and processing depends on the connection settings and code page of the data.

By default IBProvider works in UNICODE-mode (unicode_mode=true). This means that IBProvider offers to exchange text data using double-byte UNICODE-characters.

In this case, for the string data with a code page other than NONE will be applied the following rules:

Data type FB/IB Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .NET Framework
CHAR DBTYPE_WSTR DbType.String OleDbType.WChar System.String
VARCHAR DBTYPE_WSTR DbType.String OleDbType.VarWChar System.String

If string data have a code page «NONE», then representation will be different:

Data type FB/IB Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .NET Framework
CHAR DBTYPE_STR DbType.AnsiString OleDbType.Char System.String
VARCHAR DBTYPE_STR DbType.AnsiString OleDbType.VarChar System.String

By default, the conversion of such «NONE-data» in the UNICODE and back is carried out using the code page ASCII.

This can be changed if you specify in the connection string parameter «ctype_none» with name of character set other than NONE.

Maximum number of characters in CHAR / VARCHAR columns depends on the code page. For single-byte encodings, these values are 32767 and 32765 for CHAR and VARCHAR, respectively.

Binary data types

This group includes the data types CHAR, VARCHAR with a code page OCTETS.

Data type FB/IB Max. size Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .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 data type

This group includes data type BOOLEAN, supported by InterBase v7 and Firebird v3.

Data type FB/IB Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .NET Framework
BOOLEAN DBTYPE_BOOL DbType.Boolean OleDbType.Boolean System.Boolean

In addition, IBProvider can emulate supporting this type of data. Details can be found here article.

Date and time data types

Data type FB/IB Settings Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .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

The type to represent time (DBTYPE_DBTIME) in the original version of OLE DB does not support the fraction of second. This has been fixed in MSSQL 2008 — it got the type DBTYPE_DBTIME2. To date, type TIME is using old time format (DBTYPE_TIME) by default. When you work with IBProvider through the ADO.NET provider is recommended to specify in the connection string «dbtime_rules=1».

Blobs

Blobs are divided into two groups — text and binary

Text blobs include blobs with subtype (SUB_TYPE) TEXT (id: 1) and code page other than OCTETS.

Binary blobs include blobs with subtype other than TEXT and blobs with subtype TEXT and code page OCTETS.

Text blobs have the same problem with NONE encoding as in the part «String data types».

In the following table assumes that the text blob has normal (not NONE) code page and the connection works on UNICODE mode (unicode_mode = true).

Data type FB/IB Data type OLE DB System.Data.DbType lcpi.data.oledb.OleDbType Data type .NET Framework
BLOB (binary) DBTYPE_BYTES DbType.Binary OleDbType.LongVarBinary System.Byte[]
BLOB (text) DBTYPE_WSTR DbType.String OleDbType.LongVarWChar System.String

ADO.NET provider supports all ways of handling blobs, including stream processing through System.IO.Stream и System.IO.TextReader.

OleDbDataReader methods to obtain the values of text blobs:

  • 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 methods to obtain the values of binary blobs:

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

Methods «long GetChars(…)» и «long GetBytes(…)» (used for downloading the contents of blobs by blocks) are anachronism of earlier versions ADO.NET. Now recommended to use the methods GetTextReader и GetStream.

Arrays

There was a time when the first «knowledge» about InterBase was «it was used in the tank Abrams» and the second «knowledge» was «it supports the storage of multidimensional arrays in columns of tables.»

IBProvider provides support for this type of data. Accordingly, ADO.NET provider also supports work with arrays.

Arrays can store any type of data except blobs.

The following example will show the code for reading the column QUART_HEAD_CNT with an array «INTEGER [1:4]» from the table 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

Arrays are modified through parameterized queries. You can’t specify the value of the array directly in the command text.

Consider the example of updating the table column QUART_HEAD_CNT PROJ_DEPT_BUDGET, which performs a «reverse» of content of arrays.

The primary key of the table is composed of columns (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

Error handling

ADO.NET provider defines and uses its own exception class OleDbException along with the standard exception class.

To work with the OleDbException class you must add a reference to «lcpi.lib.netxxxx.dll» assembly.

OleDbException contains a collection of error descriptions generated by OLE DB provider and directly by the ADO.NET provider.

  • The descriptions of OLE DB provider errors are accessed through OleDbException.Errors collection. This collection that is being implemented by OleDbErrorCollection class, enumerates the objects with OleDbError interface.
  • All error records are accessed through the «lcpi.lib.structure.t_err_records_r» interface.

Descriptions of all errors are aggregated in properties «Source» and «Message» of the OleDbException class.

Description of error from OLE DB provider can provide additional information as a string with the SQLSTATE code and identifier of original error of database server — 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 (as the majority of other exceptions generated by ADO.NET provider) supports localization of error messages. It is means, in particular, that the message language is determined not at the moment of generation an exception, but directly at the moment of message text query.

The following examples show connection with an incorrect password. Here twice displayed text of catched exception object — first in English and then in Russian.

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 event

At the level of the class OleDbConnection defined the InfoMessage event, designed to receive warnings and informational messages from the OLE DB provider. The message class OleDbInfoMessageEventArgs has the same properties, methods and behavior as the OleDbException class. Strictly speaking, an object of OleDbInfoMessageEventArgs class returns data from the internal OleDbException object.

Through OleDbConnection.InfoMessage you can also receive error messages (immediately before throw of exception). For this you need to set OleDbConnection.FireInfoMessageEventOnUserErrors property to 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
Publish date: 2015-03-31. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link https://www.ibprovider.com/eng required.
All documentation