Beginner’s Tutorial on Using the Firebird ADO.NET Client 2.5

• Download source - 21.42 KB

Introduction

Firebird is an open source relational database management system that runs on Linux, Windows, and a variety of Unix platforms.  Among other things, this small light-weight RDBMS, apart from being absolutely free fully supports stored procedures and  is ACID compliant (and of course supports standard SQL) with Referential integrity.

You are probably reading this because you are a Windows developer with a lot of SQL in your head, but not enough money in your pockets to purchase a full SQL server license and the physical limitations on the SQL server Express are hopeless. Well, let me give you a glimmer of hope.

What you need:

1. FirebirdSQL Server 2.5
2. Firebird ADO.NET Client 2.5
3. Visual Studio 2008 (the Express is just fine) or any other C# IDE like SharpDevelop
4. FlameRobin [this is just a GUI tool for database administration, and is optional]

When I discovered and started to use the Firebird ADO.NET Client towards the end of last year, I stopped using SQL Server Express in all my Windows applications. I just had a problem with its size…it’s about 90-500MB depending on what you choose to install, and yet limited to only 4GB of physical storage (per database). I must say that using MySQL with its connector on .NET is somewhat OK, but MySQL is still a BIG RDBMS compared to Firebird. Then came this Firebird, the Windows installer just being about 6MB and with 64TB storage capacity (this is a hypothetical estimate…but, it means the actual value is around there somewhere!). It goes quite well with Delphi I would say, but not very many people love using Object Pascal (Sorry Codegear, but Delphi code just looks so funny), let alone get the opportunity. To be frank, kudos to the guys at Microsoft because they still have the easiest way of interfacing a Windows application with their SQL Server; that is probably why many developers use SQL Server when working on the .NET platform.

And Along Came the Firebird ADO.NET Client

This is to introduce to you the Firebird ADO.NET client, currently in its 2.5^th release! I love the way they keep every class with its name more or less the same. An example of the common classes in the System.Data.Sqlclient namespace and their equivalent in the FirebirdSql.Data.FirebirdClient namespace is shown below:

• SqlConnection() becomes FbConnection()
• SqlCommand() becomes FbCommand()
• SqlDataReader() becomes FbDataReader(), etc.

Let Us Start

After installing Firebird 2.5, the default username will be sysdba and the password will be masterkey. There is a very good quick-start guide in the docs directory of the Firebird installation folder [mine is C:\Program Files\Firebird\Firebird_2_5\doc\ Firebird-2.1-QuickStart.pdf]. You can add users, modify passwords, and all. You do this using the special gsec tool that comes along with the installation. But, you don’t want to start creating tables at the command line, do you? That is why I recommend a third party GUI tool. I always like using the mouse and seeing what you are actually doing. The GUI tool of my choice is called FlameRobin. It is a simple, very small, application written in C++, that enables you to create databases, register them, add tables, manage users, and so much more. Now, you are probably used to not having to write SQL to create your tables if you were using Management Studio for SQL Server, or Visual Studio 2008, which enables you to open the *.mdf file in your project and add the tables with a few mouse clicks. With FlameRobin, you are going to have to create the tables with SQL. That should not be difficult. You can also migrate your tables in SQL server as I will tell you at the end.

For the table we are going to use, I have included the query in the zip archive. If you are going to use my project exactly as it is, you will have to change the sysdba password to “12345”, without the quotations, of course.

Next, you will install the Firebird ADO.NET Client, it is just 320KB… not surprising for a package that effectively consists of only two DLLs.

If you are making your own project as you follow the tutorial, add FirebirdSql.Data.FirebirdClient.dll in C:\Program Files\FirebirdClient 2.0 (or wherever it is that you installed it) as a reference to your project, and then add:

using FirebirdSql.Data.FirebirdClient

the above statement at the beginning of your code.

I am assuming that you have worked with SQL Server before, and VC# or VC++ .NET or VB.NET, and know the procedure to follow to add data from your form to a database. In our project, we have created (you should create) a table called Details with Name, Age, and Sex columns (just use the query I included in the source code) in a database called UsingFirebird.fdb in C:\. We will just be adding details to these columns. The procedure with Firebird, after following the installation steps above, is almost exactly the same. You just have to type “Fb…” to look for the class you want, and the IntelliSense feature will shower you with all the available classes. Any class you are looking for is most probably there; I have not exhausted all of them, but I have translated some of my largest projects already.

However, let us look at the most important feature now.

The Firebird ADO.NET Client Connection String

You must have a correct connection string before you can do anything else with a database. It took me quite a long while to get the exact way in which the connection string must be written. And, there are many confusing leads on the internet. My main reason to writing this article is to provide a one stop centre for many solutions to simple issues. Look at the code in the Submit button event handler:

void SubmitButtonClick(object sender, EventArgs e)
{
    try 
    {
        string ConnectionString = "User ID=sysdba;Password=12345;" + 
               "Database=localhost:C:\\USINGFIREBIRD.FDB; " + 
               "DataSource=localhost;Charset=NONE;";

Please note the space in “User ID” and the “localhost:C:\\” instruction. As with SQL Server, there are many other advanced options that you can include in this string… just put a semicolon after every setting that you will have added. Contrary to SQL Server, a Firebird database must always have server authentication. Other ways exist, but this is the technique I would recommend to writing the connection string:

    FbConnection addDetailsConnection = new FbConnection(ConnectionString);
    addDetailsConnection.Open(); 

    FbTransaction addDetailsTransaction = 
                  addDetailsConnection.BeginTransaction();
    string SQLCommandText = " INSERT into Details Values"+ 
                            "('"+ NameBox.Text+ 
                            "',"+Int32.Parse(AgeBox.Text)+ 
                            ","+"'"+SexBox.Text+"')"; 

    FbCommand addDetailsCommand = new FbCommand(SQLCommandText, 
              addDetailsConnection,addDetailsTransaction);
    addDetailsCommand.ExecuteNonQuery();
    addDetailsTransaction.Commit();
    MessageBox.Show(" Details Added");
    //as you can see, the procedure is exactly the same.
}
catch(Exception x)
{
    MessageBox.Show(x.Message);
}

Just to prove to you further that you do not have anything new to learn, have a look at a possible implementation of the FbDataReader() class when we want to delete an item we have added. We will be populating the combo box when FormDelete loads. Please note that in FormDelete.cs, the ConnectionString variable has been declared outside the methods to widen its scope. This way, you don’t have to write it every time.

void FormDeleteLoad(object sender, EventArgs e)
{
    FbConnection deleteConnection = new FbConnection(ConnectionString);
    try
    {
        deleteConnection.Open();
            
        // declare command
        FbCommand readCommand = 
          new FbCommand("Select * From Details", deleteConnection);
        FbDataReader myreader=  readCommand.ExecuteReader();
        while(myreader.Read())
        {
            // load the combobox with the names of the people inside.
            // myreader[0] reads from the 1st Column
            DeleteComboBox.Items.Add(myreader[0]);
        }
        myreader.Close(); // we are done with the reader
    }
    catch(Exception x)
    {
        MessageBox.Show(x.Message);
    }
    finally
    {
        deleteConnection.Close(); 
    }
}

Points of Interest

The Firebird exception messages are not fully developed. I am angered by messages like "No message for error code 335544755"! Well, there is some not so bad documentation on the Firebird error codes available here.

I have met a few problems with the FbDataReader when it is being called more than once in the same source file… it's like it does not completely close the first time you use it.

Finally

So, what next? Look at the first project you designed when you were first working with interfacing forms with SQL Server. Try to translate it. If it is a multi-document form, you could consider interfacing only a few forms first, then go on carefully. You will actually come to realise that the forms seem to add details faster and DataGridViews seem to load data faster with FirebirdSQL than with SQL Server.

You can follow this link to see how to migrate your SQL data to Firebird.

Please note that the Firebird ADO.NET Client 2.5, even in all its glory, is still a prelease version. So, perhaps it may not be suitable for mission critical projects.

Nonetheless, it is a very helpful project. You no longer need to spend the 14,000 dollars to buy a full-fledged RDBMS to use with Visual Studio. For all the work that you have been doing, give Uncle Bill a leave. Enjoy that small server with (almost) unlimited physical storage…it is definitely worth your try. Thumbs up (.db?!!!!) to the Free Software Foundation!

History

• 25^th February, 2009: Initial post
• 20^th March, 2009: Article updated