Publish date: 2012-06-08

Dozens of new options for working with Firebird and InterBase in the .NET Framework

If you have ever worked with Firebird or InterBase using the standard .NET provider for OLE DB (System.Data.OleDb), you will have likely encountered issues like this:

  • The necessity to add ALL parameters manually.
    Even though the OLE DB provider supports parameter generation for commands, the .NET provider doesn’t use it, so you as the programmer must do that work on your own.
  • The lack of support for named parameters.
    Although they actually exist, using them in reality requires a whole set of complex manipulations (the steps needed to add a parameter to a collection, etc.).
  • The impossibility of using multiple DataReader objects at the same time (Multiple Active Result Sets).
  • No execution of SQL scripts which contain several SQL expressions per command.
  • This list of difficulties a .NET developer has to deal with is far from complete.

Now it’s all different: meet a fundamentally new ADO.NET Data Provider for OLE DB

During the entire year we had been intensely working on a new project, LCPI ADO.NET Data Provider for OLE DB, meant for all programmers who must otherwise use flawed components for working with Firebird and InterBase within the .NET Framework.

From now on, this option-rich provider is a part of our standard IBProvider distribution.

At the moment it is still a beta with limited functionality

The OLE DB schemes, OleDbDataAdapter, OleDbCommandBuilder as well as the interfaces for the Entity Framework are not yet implemented, so the functionality is limited – especially if you are a fan of code generators and WYSIWYGs.

We know though that many of our fellow programmers avoid using generators as a matter of principle, preferring to code by hand the entire logic of the interaction with database.

You can do all this already, gaining access to the full-throttle functionality of the following Firebird, InterBase and the .NET Framework features:

  1. Automatic generation of command parameters which you do not need to enter manually anymore;
  2. Named and unnamed parameters, also IN, OUT and IN-OUT parameters;
  3. Connection pooling and SQL statement pooling which let your code run even faster;
  4. Nested («nested_trans=true») and distributed (TransactionScope, EnlistTransaction) transactions;
  5. All data types, including GUID, TIME (Dialect 3), DATE (Dialect 3) and multidimensional arrays;
  6. The MARS technology which enables working simultaneously with several active DataReader objects;
  7. And hey, ExecuteScalar now really loads one value only, not the entire record 
  8. Multiple parameter-containing commands in one statement (SQL scripts);

All these options are implemented in the following Classes of the lcpi.data.oledb Namespace:

  • OleDbConnectionStringBuilder
  • OleDbConnection
  • OleDbTransaction
  • OleDbCommand
  • OleDbParameter
  • OleDbParameterCollection
  • OleDbDataReader
  • OleDbException

Although our new OLE DB Provider for .NET works via the standard OLE DB interfaces, currently it supports only LCPI.IBProvider.3 for Firebird and InterBase, since that is where these standard interfaces are implemented 100 per cent.

If you are a developer of an OLE DB provider for another database, please contact us, and we will send you the specifications necessary for your provider to work in the .NET Framework through our OLE DB Provider for .NET

What you can do with the new ADO.NET provider already:

//Use
// - Firebird SQL Server v2.5
// - LCPI.IBProvider v3.11.1
// - LCPI ADO.NET Data Provider for OLE DB
using System;
using System.Data;
using xdb=lcpi.data.oledb;

class Program
{
 static void Main(string[] args)
 {
  const string c_cn_str
   ="provider=LCPI.IBProvider.3;"
   +"location=home2:d:\\database\\employee.fdb;"
   +"user id=SYSDBA;"
   +"password=masterkey;";

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

    using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
    {
     using(var cmd=new xdb.OleDbCommand("",cn,tr))
     {
      cmd.CommandText="select EMP_NO,FULL_NAME from EMPLOYEE where FIRST_NAME=:x";

      cmd["x"].Value="Leslie"; //set params without manual addition.

      using(var reader=cmd.ExecuteReader())
      {
       cmd.CommandText="update EMPLOYEE set FIRST_NAME=UPPER(FIRST_NAME) "
                      +"where EMP_NO=:id RETURNING NEW.FIRST_NAME;";

       cmd.Parameters.Refresh(); // recreate params without creating new command

       while(reader.Read())
       {
        cmd.Parameters["id"].Value=reader["EMP_NO"];

        cmd.ExecuteNonQuery();

        Console.WriteLine("Update \"{0}\" emp_no={1}. New FIRST_NAME=\"{2}\"",
                          reader["FULL_NAME"],
                          reader["EMP_NO"],
                          cmd["FIRST_NAME"].Value);
       }//while
      }//using reader
     }//using cmd

     tr.Rollback();
    }//using tr
   }//using cn
  }
  catch(Exception exc)
  {
   Console.WriteLine("ERROR: [{0}] - {1}",exc.Source,exc.Message);
  }
 }//Main
};//class Program

Result of work:

Result of work

As of now, we have 3800 proofs of reliability of the new ADO.NET provider

In your opinion, can people speak of reliability if all testing and QA consists of gathering critical error reports from users and frantically correcting these errors?

Would you entrust your important data to such components, and what would be the price of each error for you?

Within our new project, we have already run more than 3,800 various automatic tests based on the NUnit Framework. We thoroughly check EVERY release. All tests are included with the distribution so you can judge for yourself whether they are accurate.

What else we have done to ensure even greater reliability of working with data:

  1. Localization of error messages. English and Russian message templates are implemented.
  2. Aggressive garbage collection and object lifespan management. Calling an object’s methods after Dispose has been called is not allowed. Delayed execution of Dispose until all currently running calls to the object’s methods are completed.
  3. Usage of CER throughout the program to prevent resource drain.
  4. Multithreading support.
  5. Custom COM object management solution (based on SafeHandle).
  6. Unified support for 32-bit and 64-bit platforms.

Now is a great moment to download and install the new IBProvider and LCPI ADO.NET Data Provider for OLE DB

All existing customers of IBProvider with active licenses will get new LCPI ADO.NET Data Provider for OLE DB absolutely free. Its already available in the customer area.

Since our project is a commercial one, we can afford to react promptly (!) to every user request and to enhance the necessary functionality with every release. By the way, our releases come out quite often because:

  • For us, testing a new version is not endless nightmare and error correction, but rather day-to-day, organized and conscious effort, a part of which is performed automatically.
  • Thanks to the scalable architecture, we save a lot of time and do not need to start coding each release from scratch – whether we are adding an important new feature or a minor detail.

So frequent releases with new functionality specially for you is our norm, not some sort of a cheap trick. We only ask that you tell us what you would like to see next by emailing ibprovider@ibprovider.com, and we will do the rest.


Publish date: 2012-06-08. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link https://www.ibprovider.com/eng required.