===== Using Embedded Firebird Database In .Net Projects =====

This [[http://nazmialtun.blogspot.it/2012/01/using-embedded-firebird-database-in-net.html|Nazmi Altun post]] will show an example of using an embedded  firebird database in .NET/C# projects. 

==== What is FireBird? ====
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.


==== Creating  Database Tables ====
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 : 

<code sql>
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) 
); 
</code>

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.

<code sql>
--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 ; !!
</code>

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 .

==== Application ====
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.

==== Firebird  Data Access Application Block ====
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.

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

=== Connection  string for embeeded  database ===

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

=== Fetching  class list  from database ===
<code c#>
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"; 
}
</code>

=== Insert  student Into  Student Table ===
<code c#>
//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!");
</code>

=== Fetching student list  of  selected  class ===
<code c#>
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]; 
} 
</code>

==== Links ====
[[https://docs.google.com/document/d/1s8Ton07yX3PCvVwCmAMYGSZZelKARKXBJZJMgiMVt-k/edit|Firbird Sample Sql Script]] \\
[[http://nazmialtun.com/Uploads/DLLs.rar|Download Firebird DLLs]] \\
[[http://nazmialtun.com/Uploads/FireBirdTest.rar|Download VS 2010 Sample Project]] \\