Click here to Skip to main content
Click here to Skip to main content

Embedded Firebird: Full-Featured Embedded Database with 2 MB Runtime

, 28 Jan 2005
Rate this:
Please Sign up or sign in to vote.
An example that shows how Firebird database excels in embedding.

Sample Image - screenshot.gif

The advantages of Embedded Firebird

Firebird is a database with 20 years of history, full set of features (including transactions, stored procedures, hot-backup, excellent scalability, etc.) and a friendly open source license. It is an overlooked but compelling alternative to Microsoft Jet and Microsoft MSDE 2000/SQL Express 2005. Let's take a look at how it can be used embedded in your desktop application. What makes Embedded Firebird ideal for embedding:

  • The embedded runtime is < 2 MB (starting at just one DLL + one .NET assembly). The runtime is deployed by simple copying, no installation or component registration is required.
  • The database file (it's just a single file) can have any name and extension. You can associate the extension with your application.
  • The migration to a standalone server couldn't be easier. Just copy the database file to the server and change a connection string on your client.

Working with Embedded Firebird

To start using Embedded Firebird in .NET, you need to download:

  • Embedded Firebird Engine (current stable version is 1.5.2)
  • Firebird ADO.NET Provider (current stable version is 1.6.3)

After creating a new project in Visual Studio .NET, add a reference to FirebirdSql.Data.Firebird.dll (from Firebird ADO.NET Provider installation), and copy fbembed.dll (from Embedded Firebird ZIP package) to the project output directory (e.g., bin/Debug).

The Firebird ADO.NET Provider can connect to a standalone Firebird Server using a connection string like this:

FbConnection c = new FbConnection("ServerType=0;User=SYSDBA;" + 
         "Password=masterkey;Dialect=3;Database=c:\\data\\mydb.fdb");

It can connect to an embedded Firebird using this connection string:

FbConnection c = new FbConnection("ServerType=1;User=SYSDBA;" + 
                 "Password=masterkey;Dialect=3;Database=mydb.fdb");

The database path can be relative to fbembed.dll when using the Embedded Firebird.

You see that switching from embedded and standalone Firebird and vice versa is a piece of cake.

Creating a new database

There are two ways to create a new database:

  • Creating a database programmatically.
  • Copying an existing database template.

It's up to you which way you choose. Copying an existing database is easy (just make sure the template is not open before copying), so let's try to create it programmatically.

Hashtable parameters = new Hashtable();
parameters.Add("User", "SYSDBA");
parameters.Add("Password", "masterkey");
parameters.Add("Database", @"mydb.fdb");
parameters.Add("ServerType", 1);
FbConnection.CreateDatabase(parameters);

Continue as usual

Working with Firebird ADO.NET Provider is easy. You will reuse your experience with other ADO.NET providers. It even has some nice features, like calling the stored procedures using the MSSQL style:

FbCommand cmd = new FbCommand("MYSTOREDPROCEDURE", 
            new FbConnection(connectionString));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@first", "value1");
cmd.Parameters.Add("@second", "value2");
cmd.Connection.Open();
try
{
    cmd.ExecuteNonQuery();
}
finally
{
    cmd.Connection.Close();
}

Useful Resources

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Dan Letecky

Czech Republic Czech Republic
My open-source event calendar/scheduling AJAX controls:
 
DayPilot for JavaScript/HTML5/jQuery
DayPilot for ASP.NET
DayPilot for MVC
DayPilot for Java

Comments and Discussions

 
Questionconnection to an asp.net erp Pinmemberezekielokeyo18-Jul-12 20:08 
QuestionFor those of you who're interested to get it setup on 64 bit platform (plus create db/table/insert rows) [modified] Pinmemberdevvvy14-May-12 3:05 
Tutorial - Get it working with 64 bit Winform
 
REF:
Brief tutorial: http://www.codeproject.com/Articles/9445/Embedded-Firebird-Full-Featured-Embedded-Database
Embedded Firebird: http://www.firebirdsql.org/en/firebird-2-5-1/#Win64
Firebird provider src: http://www.firebirdsql.org/en/net-provider/
Firebird doc: http://www.firebirdsql.org/en/documentation/
Firebird Language reference: http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25.html
Firebird DataType (Compare to M$SQL): http://www.firebirdsql.org/manual/migration-mssql-data-types.html
Firebird Language Reference - CREATE TABLE: http://www.firebirdsql.org/refdocs/langrefupd15-create-table.html
 
STEP 1. Download Firebird embedded dll's - in this example, "Firebird-2.5.1.26351-0_x64_embed" from \Download\Firebird 2.5\Win64\64-bit Embedded
http://www.firebirdsql.org/en/firebird-2-5-1/#Win64
Extract the package. You'd later need to copy "fbembed.dll" and other dll's into your application root directory.

STEP 2. Download .NET Provider
In this example, NETProvider-2.7.5-src. Extract the package and compile with [target=x64] from Visual Studio
http://www.firebirdsql.org/en/net-provider/

Your "Provider" can be found here:
...\ClientProviderSrc\source\FirebirdSql\Data\bin\Debug\FirebirdSql.Data.FirebirdClient.dll

STEP 3. Create a dummy .NET Winform project,
 
STEP 3.1 add reference to your Firebird provider:
...\ClientProviderSrc\source\FirebirdSql\Data\bin\Debug\FirebirdSql.Data.FirebirdClient.dll

Also, put all dll's from Firebird-2.5.1.26351-0_x64_embed package (STEP 1) under application root.

STEP 3.2 From Form1.cs,
using FirebirdSql.Data.FirebirdClient;

STEP 3.3 Add a button, try out basic moves ...

string connectionString = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=";
 
// REF: http://www.codeproject.com/Articles/9445/Embedded-Firebird-Full-Featured-Embedded-Database?msg=4252054#xx4252054xx

private void button1_Click(object sender, System.EventArgs e)
{
string DbFilePath = @"C:\...\EmbeddedDB\Firebird\Sample\EmbeddedFirebirdExample\bin\x64\Debug\TestingDatabase.FDB";
string FullConnString = this.connectionString + DbFilePath;
 
// STEP 1. Create (or Re-create) database and database file
if (!System.IO.File.Exists(DbFilePath))
{
FbConnection.CreateDatabase(FullConnString);
}

 
FbConnection conn = new FbConnection(FullConnString);
conn.Open();
 
// STEP 2. Change default SYSDBA pwd? No!
// This will not work on embedded Firebird!
/*
string AlterPwdCmdSQL = "alter user SYSDBA password 'xyzabc123'";
FbCommand AlterPwdCmd = conn.CreateCommand();
AlterPwdCmd.CommandText = AlterPwdCmdSQL;
AlterPwdCmd.ExecuteNonQuery();
*/
 
// STEP 3. Create a table
// NOTE: Can only do this once!
// REF 1: http://web.firebirdsql.org/dotnetfirebird/blog/2005/01/creating-database-programmatically.html
// REF 2: http://www.firebirdfaq.org/faq69/
// STEP 3.1 Check if table exists - CAUTION: Table name upper case!!!
string SimpleCheckIfTableExists = "select count(1) from rdb$relations where rdb$relation_name = 'EIK'";
FbCommand CheckTableExistCmd = conn.CreateCommand();
CheckTableExistCmd.CommandText = SimpleCheckIfTableExists;
int CountTable = (int)CheckTableExistCmd.ExecuteScalar();
if (CountTable > 0)
{
// Drop table
string SimpleDropTableIfExists = "drop table EIK;";
FbCommand DropTableCmd = conn.CreateCommand();
DropTableCmd.CommandText = SimpleDropTableIfExists;
DropTableCmd.ExecuteNonQuery();
}
 
// STEP 3.2 Re-create the table
// CAUTION:
// (a) "Timestamp" is a reserved keyword for Firebird.
// (b) Table name "eik" -- Actual created table name = "EIK"
string SimpleCreateTableSQL = "create table eik (a int not null primary key, b int not null unique, c timestamp default current_timestamp);";
FbCommand CreateTableCmd = conn.CreateCommand();
CreateTableCmd.CommandText = SimpleCreateTableSQL;
CreateTableCmd.ExecuteNonQuery();

// STEP 4. Insert row
// NOTE: Can only do this once! (PK constraint!)
string SimpleInsertSQL = "insert into eik (a,b) values (123,456);";
FbCommand InsertRowCmd = conn.CreateCommand();
InsertRowCmd.CommandText = SimpleInsertSQL;
InsertRowCmd.ExecuteNonQuery();

// STEP 5. A simple select
FbDataAdapter da = new FbDataAdapter("SELECT * FROM eik", FullConnString);
// FbDataAdapter da = new FbDataAdapter("SELECT * FROM table1", this.connectionString + this.textBox1.Text);
DataTable dt = new DataTable();
da.Fill(dt);
this.dataGrid1.DataSource = dt;
}

 
STEP 4. Bulk Insert?
With SQL Server,

DataRow[] RowsImported = LoadFromSomeDataTable(MyData);
bc = new System.Data.SqlClient.SqlBulkCopy(Conn);
bc.BulkCopyTimeout = 60 * 60;
bc.DestinationTableName = SomeName;
bc.WriteToServer(RowsImported);

 
With Firebird, you have to make do with external table, quoting - http://www.firebirdfaq.org/faq209/

CREATE TABLE ext1 EXTERNAL 'c:\myfile.txt'
(
field1 char(20),
field2 smallint
);

 
To do quick import into regular table, do something like this:

INSERT INTO realtable1 (field1, field2)
SELECT field1, field2 FROM ext1;

 
This insert would still check constraints, foreign keys, fire triggers and build indexes. If you can, it is wise to deactivate indexes and triggers while loading and activate them when done.

STEP 5. You can't change SYSDBA pwd for embedded Firebird - in fact there's no need to do so. The following won't work!

string DbFilePath = @"D:\...\SampleData.FDB";
string FullConnString = this.connectionString + DbFilePath;
FbConnection conn = new FbConnection(FullConnString);
conn.Open();

string AlterPwdCmdSQL = "alter user SYSDBA password 'xyzabc123'";
FbCommand AlterPwdCmd = conn.CreateCommand();
AlterPwdCmd.CommandText = AlterPwdCmdSQL;
AlterPwdCmd.ExecuteNonQuery();


Upon ExecuteNonQuery -

+ ex {"unsuccessful metadata update\r\nI/O error during \"CreateFile (open)\" operation for file \"C:\\...\\EMBEDDEDFIREBIRDEXAMPLE\\BIN\\X64\\DEBUG\\SECURITY2.FDB\"\r\nError while trying to open file"} FirebirdSql.Data.Common.IscException

Seems like I'm not the first person running into this problem:
http://tech.groups.yahoo.com/group/firebird-support/message/83729

Now, quoting here http://www.firebirdsql.org/manual/ufb-cs-embedded.html

The Windows Embedded Server is a Superserver engine plus client rolled into one library, called fbembed.dll. It is available as a separate download package. The embedded server (introduced with Firebird 1.5) was specifically designed to facilitate deployment of applications that ship with a Firebird server included. Installation is just a matter of unpacking the DLL and a few other files to the desired location. The security database is not used at all: anyone can connect to any database, as long as the user running the application has filesystem-level access rights to the database(s) in question... The embedded server has no facility to accept any network connections. Only true local access is possible, with a connect string that doesn't contain a host name (not even localhost).

So, there's nothing you can do to secure an embedded Firebird - you can only secure the Windows machine/OS on which the Firebird db resides.

STEP 6. DbProviderFactories.GetFactory will fail unless you reference the dll from app.config!

DbProviderFactories.GetFactory("FirebirdSql.Data.FirebirdClient"); <-- This will blow up!


To do this,
STEP 6.1 - determine public key token


D:\...\Firebird\ClientProviderSrc\source\FirebirdSql\Data\bin\Debug>gacutil /i FirebirdSql.Data.FirebirdClient.dll
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.1
Copyright (c) Microsoft Corporation. All rights reserved.
Assembly successfully added to the cache
 
Now, /u will tell you public key of the dll!

D:\...Firebird\ClientProviderSrc\source\FirebirdSql\Data\bin\Debug>gacutil /u FirebirdSql.Data.FirebirdClient
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.1
Copyright (c) Microsoft Corporation. All rights reserved.

D:\...\Firebird\ClientProviderSrc\source\FirebirdSql\Data\bin\x64\Debug>gacutil /u FirebirdSql.Data.FirebirdClient
Assembly: FirebirdSql.Data.FirebirdClient, Version=2.7.5.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c, processorArchitecture=AMD64
Uninstalled: FirebirdSql.Data.FirebirdClient, Version=2.7.5.0, Culture=neutral,
PublicKeyToken=3750abcc3150b00c, processorArchitecture=AMD64
Number of assemblies uninstalled = 1
Number of failures = 0

 
STEP 6.2




... other stuff ...

 


<!-- rao: Firebird driver; prefer locally placed driver (any version)-->




... other stuff ...


 
REF: http://programming.2be-it.com/?p=160
dev


modified 16-May-12 4:16am.

Generalembedded firebird with c#.net(3.5) and vs2008 Pinmembersaurabh.patil@hotmail.com19-May-10 23:12 
QuestionHow Maintain Firebird Embedded Connection Open!?!?! PinmemberRafael Fernandes Brasil25-Aug-09 5:07 
QuestionStill Supported? PinmemberJawz-X8-Sep-08 11:24 
AnswerRe: Still Supported? PinmemberDr.Serdar12-Sep-08 13:35 
GeneralRe: Still Supported? PinmemberJawz-X16-Sep-08 9:22 
Generaldoesn't work for firebird 2 Pinmemberujal17-Aug-08 22:10 
QuestionProblems with Stored Procedures with Embedded Firebird 2.0 Pinmemberkiweed17-Jan-08 8:13 
QuestionHelp PinmemberK_Farhod17-Sep-07 23:46 
AnswerRe: Help Pinmember_CloudyOne_5-Jun-08 15:01 
QuestionDLL Not Found Pinmemberjet858-May-07 8:31 
AnswerRe: DLL Not Found Pinmembertcdavis9-May-07 7:06 
GeneralRe: DLL Not Found Pinmemberjet8510-May-07 10:04 
GeneralRe: DLL Not Found Pinmembertcdavis10-May-07 10:14 
GeneralRe: DLL Not Found Pinmemberbalazs_hideghety16-Jul-07 4:41 
GeneralRe: DLL Not Found PinmemberDaberElay31-Jan-09 11:55 
Generalnot managed PinmemberAndrew Shapira4-Feb-07 4:11 
QuestionChange the password? PinmemberIsmok15-Jan-07 16:39 
AnswerRe: Change the password? PinmemberBalazs Zoltan16-Jan-07 22:27 
GeneralRe: Change the password? Pinmembertransoft8-Sep-09 10:02 
GeneralRe: Change the password? PinmemberTrinh Tuan4-Sep-10 17:30 
AnswerRe: Change the password? [modified] Pinmemberdevvvy15-May-12 15:55 
QuestionHelp Help Help PinmemberLinXG28-Nov-06 15:06 
AnswerRe: Help Help Help PinmemberArgoDragon4-Dec-06 16:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 28 Jan 2005
Article Copyright 2005 by Dan Letecky
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid