Click here to Skip to main content
15,867,939 members
Articles / Programming Languages / C#
Article

Firebird 2 and Images

Rate me:
Please Sign up or sign in to vote.
4.06/5 (10 votes)
9 Jun 2007CPOL1 min read 85.2K   1.6K   38   10
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

    C#
    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

    SQL
    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

    SQL
    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

    SQL
    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

    SQL
    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

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

Stored Procedure

Insert User

SQL
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

SQL
 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

SQL
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

SQL
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

C#
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

C#
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

C#
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

C#
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

C#
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

C#
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

C#
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)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionhow to install the firebird database in client system with windows application? Pin
SubrahmanyamGurram13-Jul-12 6:26
SubrahmanyamGurram13-Jul-12 6: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...

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

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