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

Firebird 2 and Images

, 9 Jun 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Firebird 2 using stored procedure, manage Image ‘BLOB’ and easy to save and get back your images

Sample Image - Firebird_2____Images.jpg

Introduction

I am writing in Firebird 2 and how it can be easy to use our C#.NET application. I think Firebird is very powerful and reliable.

Note

  1. Collect your Firebird database from here
  2. FirebirdSql.Data.FirebirdClient.dll file from here
  3. GUI Firebird database management tool here

    This tool can easily manage your database.

Create a database

  1. By command prompt

    Sample screenshot

    Default User ='SYSDBA' Password = 'masterkey'

  2. By Program

    public static void CreateData()
    {
        FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
        csb.ServerType = FbServerType.Embedded;
        csb.Database = "temp.fdb";
        csb.UserID = "SYSDBA";
        csb.Password = "218026";
    
        if (File.Exists(csb.Database))
        {
            File.Delete(csb.Database);
        }
        FbConnection.CreateDatabase(csb.ToString());
    }
    

Connect Database

  1. By command prompt

    Sample screenshot

    My demo project connection string in the app.config file.

  2. By program

    value="User=SYSDBA;Password=218026;
    Database=D:\temp.FDB;Port=3050;Dialect=3;Charset=NONE;Role=;
    Connection lifetime=0;Connection timeout=15;Pooling=True;
    Packet Size=8192;Server Type=0" key="DBPathDef"
    connstring1="ConfigurationManager.AppSettings[" cnn="new" />
    

Database

In the demo project database, I have one table 'TAB'. This table has one auto generated field, 7 varchar fields and one 'BLOB' for saving images. If you want an auto generated field, you can get one easily in the SQL Manager tools. First you create a trigger:

  1. Trigger

    CREATE TRIGGER TAB_BI FOR TAB
    ACTIVE BEFORE INSERT
    POSITION 0
    AS
    BEGIN
      IF (NEW.AUTONO IS NULL) THEN
      NEW.AUTONO = GEN_ID(GEN_TAB_ID,1);
    END;
    

  2. Generator

    CREATE GENERATOR GEN_TAB_ID;
    
    SET GENERATOR GEN_TAB_ID TO 57;
     ( fist time this value is 0 ,here 57 is last generated value)
    

  3. Table ('TAB') Script

    CREATE TABLE TAB (
      AUTONO INTEGER NOT NULL,
      FIELD01 VARCHAR(78) CHARACTER SET ASCII COLLATE ASCII,
      USERNAME VARCHAR(25) CHARACTER SET ASCII COLLATE ASCII,
      F_NAME VARCHAR(20) CHARACTER SET ASCII COLLATE ASCII,
      L_NAME VARCHAR(20) CHARACTER SET ASCII COLLATE ASCII,
      S_NAME VARCHAR(20) CHARACTER SET ASCII COLLATE ASCII,
      PHONE VARCHAR(20) CHARACTER SET ASCII COLLATE ASCII,
      MOB VARCHAR(20) CHARACTER SET ASCII COLLATE ASCII,
      EMAIL VARCHAR(75) CHARACTER SET ASCII COLLATE ASCII,
      IMG BLOB SEGMENT SIZE 20);
    
    ALTER TABLE TAB ADD CONSTRAINT PK_TAB PRIMARY KEY (AUTONO);
    
    SET TERM ^ ;
    
    CREATE TRIGGER TAB_BI FOR TAB
    ACTIVE BEFORE INSERT
    POSITION 0
    AS
    BEGIN
      IF (NEW.AUTONO IS NULL) THEN
      NEW.AUTONO = GEN_ID(GEN_TAB_ID,1);
    END^
    
    SET TERM ; ^
    

  4. Exception

    CREATE EXCEPTION ERR_530 'INVALID EMPLOYEE NUMBER';
    CREATE EXCEPTION ERR_803 'THIS EMPLOYEE ALREADY EXIST !';
    

Stored Procedure

Insert User

CREATE PROCEDURE INSERTUSER(
USERNAME VARCHAR(25) CHARACTER SET ASCII,
         F_NAME VARCHAR(20) CHARACTER SET ASCII,
L_NAME VARCHAR(20) CHARACTER SET ASCII,
S_NAME VARCHAR(20) CHARACTER SET ASCII,
PHONE VARCHAR(20) CHARACTER SET ASCII,
MOB VARCHAR(20) CHARACTER SET ASCII,
EMAIL VARCHAR(75) CHARACTER SET ASCII,
IMG BLOB SEGMENT SIZE 20)
AS
BEGIN
INSERT INTO TAB(USERNAME,F_NAME,L_NAME,S_NAME,PHONE,MOB,EMAIL,IMG)
         VALUES(:USERNAME,
         :F_NAME, :L_NAME, :S_NAME, :PHONE, :MOB, :EMAIL, :IMG) ;
  WHEN SQLCODE -530 DO
            EXCEPTION ERR_530;
  WHEN SQLCODE -803 DO
       EXCEPTION ERR_803;

END;

Update User

 CREATE PROCEDURE UPDATEUSER(
 USERNAME VARCHAR(25) CHARACTER SET ASCII,
  F_NAME VARCHAR(20) CHARACTER SET ASCII,
  L_NAME VARCHAR(20) CHARACTER SET ASCII,
  S_NAME VARCHAR(20) CHARACTER SET ASCII,
  PHONE VARCHAR(20) CHARACTER SET ASCII,
  MOB VARCHAR(20) CHARACTER SET ASCII,
  EMAIL VARCHAR(75) CHARACTER SET ASCII,
  ID INTEGER,
  EMP_IMG BLOB SEGMENT SIZE 20)
AS
BEGIN
UPDATE TAB SET USERNAME =:USERNAME, F_NAME =:F_NAME, L_NAME =:L_NAME,
 S_NAME =:S_NAME, PHONE =:PHONE, MOB =:MOB, EMAIL =:EMAIL,IMG =:EMP_IMG
   WHERE AUTONO = :ID;
END;

Delete User

CREATE PROCEDURE DELETEUSER( ID INTEGER)
AS
BEGIN
 DELETE FROM TAB WHERE AUTONO = :ID;
END;
4)    Select all User
CREATE PROCEDURE SELECTUSER
RETURNS(
  AUTONO INTEGER,
  USERNAME VARCHAR(25) CHARACTER SET ASCII,
  SURNAME VARCHAR(20) CHARACTER SET ASCII,
  LAST_NAME VARCHAR(20) CHARACTER SET ASCII,
  PHONE VARCHAR(20) CHARACTER SET ASCII,
  MOBILE VARCHAR(20) CHARACTER SET ASCII,
  EMAIL VARCHAR(75) CHARACTER SET ASCII)
AS
BEGIN
  FOR
SELECT AUTONO,USERNAME,S_NAME,L_NAME, PHONE,MOB,EMAIL FROM TAB
INTO :AUTONO, :USERNAME,
             :SURNAME, :LAST_NAME, :PHONE,
             :MOBILE, :EMAIL
  DO
  SUSPEND;
END;

Select One user Detail

CREATE PROCEDURE SELECTUSERNO(
  ID INTEGER)
RETURNS(
  USERNAME VARCHAR(25) CHARACTER SET ASCII,
  FNAME VARCHAR(20) CHARACTER SET ASCII,
  LNAME VARCHAR(20) CHARACTER SET ASCII,
  SNAME VARCHAR(20) CHARACTER SET ASCII,
  PHONE VARCHAR(20) CHARACTER SET ASCII,
  MOB VARCHAR(20) CHARACTER SET ASCII,
  EMAIL VARCHAR(75) CHARACTER SET ASCII,
  AUTONO INTEGER,
  IMG BLOB SEGMENT SIZE 20)
AS
BEGIN
  FOR SELECT USERNAME,F_NAME,L_NAME,S_NAME,PHONE,MOB,EMAIL,AUTONO,IMG FROM TAB
      WHERE AUTONO = :"ID"
      INTO :USERNAME, :FNAME, :LNAME, :SNAME,
      :PHONE, :MOB, :EMAIL, :AUTONO, :IMG
  DO
  SUSPEND;
END;

How can you use this data in the application ?

Insert

public void Save()
{
    byte[] content = null;
    try
    {
        FbCommand CMD = new FbCommand("INSERTUSER", cnn);
        CMD.CommandType = CommandType.StoredProcedure;
        CMD.Parameters.Add("@USERNAME", FbDbType.VarChar).Value = _Fname;
        CMD.Parameters.Add("@F_NAME", FbDbType.VarChar).Value = _FNAME;
        CMD.Parameters.Add("@L_NAME", FbDbType.VarChar).Value = _LANEM;
        CMD.Parameters.Add("@S_NAME", FbDbType.VarChar).Value = _SNAME;
        CMD.Parameters.Add("@PHONE", FbDbType.VarChar).Value = _PHONE;
        CMD.Parameters.Add("@MOB", FbDbType.VarChar).Value = _MOB;
        CMD.Parameters.Add("@EMAIL", FbDbType.VarChar).Value = _EMAIL;
        if (_img != null)
            content = _img.ToArray();
        CMD.Parameters.Add("@IMG", FbDbType.Binary).Value = content;

        CMD.Connection.Open();
        CMD.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}    

Update

public void Update()
{
    byte[] content =null;
    try
    {
        FbCommand cmd = new FbCommand("UPDATEUSER", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@USERNAME", FbDbType.VarChar).Value = _Fname;
        cmd.Parameters.Add("@F_NAME", FbDbType.VarChar).Value = _FNAME;
        cmd.Parameters.Add("@L_NAME", FbDbType.VarChar).Value = _LANEM;
        cmd.Parameters.Add("@S_NAME", FbDbType.VarChar).Value = _SNAME;
        cmd.Parameters.Add("@PHONE", FbDbType.VarChar).Value = _PHONE;
        cmd.Parameters.Add("@MOB", FbDbType.VarChar).Value = _MOB;
        cmd.Parameters.Add("@EMAIL", FbDbType.VarChar).Value = _EMAIL;
        cmd.Parameters.Add("@ID", FbDbType.Integer).Value = _Autono;
        if (_img != null)
            content = _img.ToArray();
        cmd.Parameters.Add("@EMP_IMG", FbDbType.Binary).Value = content;

        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Delete

public static void Delete(string _id)
{
    iU1_Table m = new iU1_Table();
    try
    {
        FbCommand cmd = new FbCommand("DELETEUSER",m.cnn);
        cmd.Parameters.Add("@ID",FbDbType.Integer).Value =_id;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Select All

public static DataTable iU1_TableD()
{
    try
    {
        iU1_Table m = new iU1_Table();
        m.OpenCon();
        DataTable dt = new DataTable();
        FbDataAdapter adb = new FbDataAdapter();
        FbCommand cmd = new FbCommand("SELECTUSER", m.cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        adb.SelectCommand = cmd;
        adb.Fill(dt);
        m.CloseCon();
        return dt;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Select one user details

public iU1_Table(string _uids)
{
    try
    {
        DataSet dst =new DataSet();
        FbDataAdapter adb = new FbDataAdapter();
        FbCommand cmd = new FbCommand("SELECTUSERNO", cnn);
        cmd.Parameters.Add("@ID",FbDbType.Integer).Value
                =Convert.ToInt32(_uids);
        cmd.CommandType = CommandType.StoredProcedure;
        adb.SelectCommand = cmd;

        adb.Fill(dst,"tab");
        cnn.Close();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Backup your Database

public static void BackUp(string _Pstring)
{
    try
    {
        FirebirdSql.Data.Services.FbBackup n
            = new FirebirdSql.Data.Services.FbBackup();
        FirebirdSql.Data.Services.FbBackupFile fl
            = new FirebirdSql.Data.Services.FbBackupFile
        (_Pstring.ToUpper(), 12485);
        n.BackupFiles.Add(fl);
        n.ConnectionString = connstring;
        n.Execute();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Restore your Database

public static void ReStore(string _Pstring)
{
    try
    {
        FbConnection.ClearAllPools();
        FirebirdSql.Data.Services.FbRestore n
            = new FirebirdSql.Data.Services.FbRestore();
        FirebirdSql.Data.Services.FbBackupFile fl
            = new FirebirdSql.Data.Services.FbBackupFile
        (_Pstring.ToUpper(),12485);
        n.BackupFiles.Add(fl);
        n.Options = FirebirdSql.Data.Services.FbRestoreFlags.Replace;
        n.Verbose = false;
        n.ConnectionString = connstring;
        n.Execute();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Last Word

I hope you enjoyed this article and will be using some ideas from here in your projects... Best of luck and thanks a lot!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

anishmm
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
Questionhow to install the firebird database in client system with windows application? Pinmembersubrahmanyam198713-Jul-12 7:26 
Hi Anishm m,
 
Its very nice article about datagridview and firebird database. I am developing a small windows application in c#.net and Firebird Database. i had created the setup file for my application and installed in my system its working fine. But When I install it in other systems its not working. I had installed firebird database in the new system and tried but its not working. Can you please help me get it right direction...
QuestionTanx Pinmemberzxcvnmm29-Apr-12 5:23 
GeneralRestoring database to primary and secondary files PinmemberMember 325225110-Mar-10 3:31 
GeneralModify the datagridview Pinmembertriplebit17-Jul-08 5:16 
GeneralRe: Modify the datagridview PinmemberDr.Serdar14-Sep-08 12:06 
QuestionError while retriving image from databse Pinmemberavvaru.murali21-Feb-08 21:42 
GeneralAlternative Firebird management GUI PinmemberIkkuh29-Jan-07 21:12 
GeneralRe: Alternative Firebird management GUI Pinmemberanishmm31-Aug-07 21:11 
GeneralSource code missing PinmemberTony Bermudez27-Jan-07 8:28 
GeneralFirebird Link Pinmembercykophysh3927-Jan-07 1:46 

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
Web03 | 2.8.141216.1 | Last Updated 9 Jun 2007
Article Copyright 2007 by anishmm
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid