Firebird 2 和图像

anishmm

4.06/5 (10投票s)

2007 年 1 月 27 日

CPOL

1分钟阅读

87379

1646

使用 Firebird 2 和存储过程，管理图像 ‘BLOB’，并轻松保存和检索图像。

下载源代码 - 163.2 Kb

Sample Image - Firebird_2____Images.jpg

引言

我正在使用 Firebird 2，以及如何将其轻松用于我们的 C#.NET 应用程序。我认为 Firebird 非常强大和可靠。

注意

从这里获取你的 Firebird 数据库。
从这里获取 FirebirdSql.Data.FirebirdClient.dll 文件。
GUI Firebird 数据库管理工具这里。
这个工具可以轻松管理你的数据库。

创建数据库

通过命令行

默认用户 ='SYSDBA' 密码 = 'masterkey'

通过程序

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());
}

连接数据库

通过命令行

我的演示项目连接字符串在 app.config 文件中。

通过程序

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" />

数据库

在演示项目的数据库中，我有一个名为 'TAB' 的表。该表有一个自动生成的字段，7 个 varchar 字段和一个用于保存图像的 'BLOB'。如果你想要一个自动生成的字段，可以在 SQL Manager 工具中轻松获得一个。首先你创建一个触发器

触发器

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;

生成器

CREATE GENERATOR GEN_TAB_ID;

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

表 ('TAB') 脚本

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 ; ^

异常

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

存储过程

插入用户

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;

更新用户

 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;

删除用户

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;

选择一个用户详情

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;

如何在应用程序中使用这些数据？

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;
    }
}

更新

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;
    }
}

删除

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;
    }
}

全选

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;
    }
}

选择一个用户详情

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;
    }
}

备份你的数据库

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;
    }
}

恢复你的数据库

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;
    }
}

最后的话

我希望你喜欢这篇文章，并将在你的项目中用到一些想法...祝你好运，非常感谢！

Firebird 2 和图像

引言

注意

创建数据库

通过命令行

通过程序

连接数据库

通过命令行

通过程序

数据库

触发器

生成器

表 ('TAB') 脚本

异常

存储过程

插入用户

更新用户

删除用户

选择一个用户详情

如何在应用程序中使用这些数据？

Insert

更新

删除

全选

选择一个用户详情

备份你的数据库

恢复你的数据库

最后的话