Firebird 2 and Images

• Download source files - 163.2 Kb

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

   

   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

   

   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

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!