Ipercube

This Nazmi Altun post will show an example of using an embedded firebird database in .NET/C# projects.

From its official web-site (http://www.firebirdsql.org), Firebird is a powerful, open-source relational database system, with high performance and extensive support for powerful SQL features with close adherence to the SQL standards. Whether you're developing a small program or a large enterprise information system, Firebird is the database for you. Take a look at the Case Studies to get an impression of other people using Firebird.
So, firebird is a database system that has stored procedure and trigger functionality. It also be used as server database or embedded database. This post will be about using embedded Firebird database in your .NET application.

Since this is just a sample that show basic things , we will create a small student-classroom system. Each class has students and user will add students under each class.

SQL command for creating tables :

CREATE TABLE CLASS 
(     CLASS_ID     INT ,     
      CLASS_NAME   VARCHAR(30) NOT NULL,
      CONSTRAINT UNIQUE_CLASS_NAME         
      UNIQUE      (CLASS_NAME),     
      CONSTRAINT PK_CLASS_CLASS_ID     
      PRIMARY KEY(CLASS_ID)        
); 
CREATE TABLE STUDENT 
(     STUDENT_ID     INT NOT NULL,     
      NAME_SURNAME   VARCHAR(60) NOT NULL,     
      CLASS_ID       INT,           
      CONSTRAINT PK_STUDENT_STUDENT_ID     
      PRIMARY KEY(STUDENT_ID),     
      CONSTRAINT FK_STUDENT_CLASS_ID         
          FOREIGN KEY (CLASS_ID) REFERENCES CLASS(CLASS_ID) 
); 

It's standard sql command for creating table. We want CLASS_ID column of the CLASS table to be an Auto increment type. For auto incrementing, firebird provides us Generators.
With help of Generators and Trigger we can make CLASS_ID column an auto-increment column. Here’s the sql command to be executed.

--Create generator and starts it from 1 
CREATE GENERATOR CLASS_ID_GENERATOR;
SET GENERATOR CLASS_ID_GENERATOR TO 1;
 
-- Create Trigger for setting auto-increment ID 
SET term !! ; 
CREATE TRIGGER CLASS_BI FOR CLASS 
ACTIVE BEFORE INSERT POSITION 0 
AS
BEGIN
IF (NEW.CLASS_ID IS NULL) THEN
    NEW.CLASS_ID = gen_id(CLASS_ID_GENERATOR,1); -- Generate ID 
END!!
SET term ; !!

So we created generator and a trigger that is triggered before insert to the CLASS table. Trigger simply generates ID and assigns it to CLASS_ID .

In this sample, we create a Windows Forms applications. Since .NET doesn’t have Firebird data provider, we need to download it from Firebirds web-site. Here’s link http://www.firebirdsql.org/en/net-provider/
After we download it, we add FirebirdSql.Data.FirebirdClient .dll as reference in our project. For embedded usage of firebird database we also need to download some dll files and add our project. Go, http://www.firebirdsql.org/en/server-packages/ and download embedded package. Add:

• fbembed.dll
• icudt30.dll
• icuin30.dll
• icuuc30.dll

in your project as file and set Copy To Output Directory property as Copy always so those dll files will always be copied to output directory.

For simplifying and optimizing database access codes we use Data Access Application Block. Data Access Application Block for FireBird can be downloaded from here http://www.codeproject.com/KB/cs/FireBirdSqlHelper.aspx
Download and add it in your project.

As it’s mentioned above, we have two tables Class and Student. Students will be inserted into class table.

"Database=Data\\DBSCHOOL.FDB;User=SYSDBA;Password=masterkey;Dialect=3;ServerType=1"

private void FillClassCB() 
{ 
string query = "SELECT *FROM CLASS"; 
 
//Fetch data from class table and bind it to combobox 
this.cbClasses.DataSource = FireBirdHelper.ExecuteDataset(conStr, CommandType.Text, query).Tables[0]; 
this.cbClasses.DisplayMember = "CLASS_NAME"; 
this.cbClasses.ValueMember = "CLASS_ID"; 
}

//Read data from Interface 
string name = txtName.Text; 
int id = int.Parse(txtID.Text); 
int classId = Convert.ToInt32(cbClasses.SelectedValue);   
 
string insertCmdStr = "INSERT INTO STUDENT(STUDENT_ID,NAME_SURNAME,CLASS_ID)"; 
insertCmdStr += " VALUES(@studentId,@name,@classId)";   
 
//Insert Database 
int total = FireBirdHelper.ExecuteNonQuery(conStr, CommandType.Text, insertCmdStr,    
   new FbParameter[]{      
     new FbParameter("@studentId",id),      
     new FbParameter("@name",name),      
     new FbParameter("@classId",classId), 
});   
 
if (total == 0)      
    throw new Exception("Unknow error! 0 student added!");

private void BindGridView() 
{     string query = "SELECT STUDENT_ID,NAME_SURNAME FROM STUDENT WHERE CLASS_ID=@classId";
 
      //Fetch student list of class and bind it to gridview    
      this.dgvStudents.DataSource = FireBirdHelper.ExecuteDataset(conStr, CommandType.Text,                     
            query, new FbParameter("@classId", cbClasses.SelectedValue)).Tables[0]; 
} 

Firbird Sample Sql Script
Download Firebird DLLs
Download VS 2010 Sample Project