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:

FirebirdSQL Server 2.5

Firebird ADO.NET Client 2.5

Visual Studio 2008 (the Express is just fine) or any other C# IDE like SharpDevelop

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.5th 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:

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:

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.

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!

Comments and Discussions

I viewed this code not once, not twice and I thought.. oohh this guy could actually be better than my teacher! On realising later, this is my best University lecturer so far and my Icon in Engineering! Way to go Maximus!

Am delighted to know! There is still a lot of work that needs to be done, both to Firebird and to its .NET data provider, especially in terms of documentation. But for the start, .NET AND Fb are not so un related afterall!
Enjoy.

First, good start to the article - I like the initiative and the time you took to write about the topic with so little out there.

With that said, the article needs some work. It is not clear what you are trying to get across, other than how to interface with Firebird. It cannot be assumed that people know what Firebird is or have time to read about it - esspecially with supposily little documentation about the class. For my vote to increase, I would like to see some additional background discussion, with the code examples better formatted - yes it is a pain, but it helps with the readability. You also might want to add links to the quick start tutorials of firebird.

Ok, with all of that, good job with the start, and I can't wait to learn more from future versions of the article!

Hi Adam,
thanks for the vote for the start! yes, all i am trying to get across is how to interface Forms with Firebird... and i must say i overlooked writing about Firebird in the first place. I have actually got quite a great deal of critisicm from my pals about lack of background to the article and am working on that now. Should be up by the end of the week!

There are many developers that target .NET, and the reason for the article is to enable them not to have to use SQL Server each time they work with any of the .NET languages. Firebird is a free, small and efficient alternative....and who doesnt love portability?

Then write an article extolling the benefits of Firebird. My point is that, in my experience, there is no difference between accessing the various databases available to ADO.net, including Firebird. An ADO.net provider is an ADO.net provider regardless of which database it is written for, that's the whole point of ADO.net.

Otherwise you might as well write an article about ADO.net with no focus on any particular database.