Monday, 28 February 2011

Firebird with .NET - a quick and dirty guide

So, you have received from a client a .FDB database and you have to use it with .NET to, hopefully,  handle adn convert the data. Here’s a possible path to a quick and successful “opening” of the database: these are just my reworked notes on the subject, I have no pretence of completeness, but in my case, this worked.

FDB is the file extension for some versions Firebird. Firebird is an open source sql database: its origins lie in Interbase, a commercial SQL database from Borland. For a while, Borland changed its name to Inprise, one of the many blatant blunders that have brought a once great company to be a shadow of its former self. During this time (about 10 years ago) it looked like Linux was about to take over the world, at leat for a brief time. So Inprise put out an open source version of Interbase. There was only one or two open source releases from Inprise, but the code was taken and indepently developed in an independent open source project – Firebird.

There are, even here in Italy, several business applications using Firebird databases, most often written in Delphi. The advantages against MSDE/SQL Express are questionable, especially nowadays, but they do exist.

 Anyways: the .FDB database is not a backup file like the ones MS SQL dbs are usually shipped in. It’s the actual database file, much like MS SQL Files. It just needs to sit somewhere in a directory and to have a corresponding Firebird server servincing it. You might try, here, Firebird version 2.5. Install it into ‘classic mode’ (‘modern’ version use other filetypes). You don’t need to install it as service. Just run the black and yellow icon, and make sure the same icon appears in the tray area.

Firebird is just a databases server and does not come with a graphical interface like Management Studio Express ‘in the box’. There is a text mode utility, but of course a graphical manager is much better, especially for casual use. There are many 3rd party utilities available: the most advanced freeware option is FlameRobin, available here from sourceforge. Install it, run it after Firebird. To use the FDB database you must ‘attach’. Right click on localhost, select ‘Register Existing Database’. Choose the FDB file and use SYSDBA masterkey as the login account.

This should allow you to run queries over the database, inspect tables etc. Settings are retained after quitting Flamerobin but of course you must always run Firebird first.

To ‘use’ the FDB file programmatically from Visual Studio with ADO.NET you need a Firebird ADO.NET provider. Probably using OleDB or even ODBC will work just as well, but since a freeware provider is available, why not use it?

The right provider can be downloaded here. Pay attention, you must download the right version for your version of VS/.NET Framework. (get the 2.0 version for 2005, the 3.5 for 2008 etc. 2.0 in 2008 targeting .NET 2.0 doesn’t seem to work. The files live in %PROGRAMFILES%\FirebirdClient

Create a reference to  FirebirdSql.Data.FirebirdClient.dll, you may put it or not put it in your project folder.

Now, is it possible to add these using directives to your module:

using FirebirdSql.Data.FirebirdClient;
using FirebirdSql.Data.Isql;                

to open a database:

string ConnectionString = "User ID=sysdba;Password=masterkey;" +
               "Database=localhost:C:\\foo\\data\\barbaz.fdb; " +

FbConnection PizzaConnection = new FbConnection(ConnectionString);

This assuming the fdb file is in c:\foo\data\. To read a forward cursor:

private FbDataReader firebirdadoreader;
FbCommand firebirdcommand =
new FbCommand("SELECT bubba FROM tblBill", PizzaConnection);

firebirdadoreader = firebirdcommand.ExecuteReader();

while (firebirdadoreader.Read()  ) {


If you have a dataGridView1 Data Grid component on a form and want to fill it with the contents of a Firebird table (tblOthertable):

private System.Data.DataSet dataSet1;

FbDataAdapter firebirddataadapter = new FbDataAdapter("select * from tblOthertable", PizzaConnection);

dataGridView1.DataSource = dataSet1.Tables[0];

You should get the hang of it now…

No comments:

Post a Comment