FireBird 简单使用 C# 包含FireBirdHelper

嵌入式数据库包含很多，比如常用的sqlite、Access，此类文件类数据库不支持并发，是限制了桌面端系统的瓶颈。
sqlite并发可使用内存模式，弊端是系统异常或者电脑断电是造成数据丢失。
可尝试使用BerkeleyDB 或者FireBird 内嵌是数据库来满足需求。
本篇讲述FireBird 数据库
FireBird 是一种嵌入式关系型的数据库，支持高并发等特性。
使用方法：

1. 数据库打开
2. 增删改查
3. 数据库创建、还原、备份

附件demo

可视化工具

主要代码 详见demo

 
        /// <summary>
        /// Open FbConnection
        /// </summary>
        /// <remarks>
        ///  FbConnection conn = OpenConnection();
        /// </remarks>
        /// <returns>FbConnection</returns>
        public FbConnection OpenConnection()
        {
            FbConnection connection = null;
            try
            {
                connection = new FbConnection(connStr);
                connection.Open();
            }
            catch
            {
                if (connection != null)
                    connection.Close();
            }
            return connection;
        }

        /// <summary>
        /// Execute a FbCommand (that returns no resultset) using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        ///  int result = ExecuteNonQuery( CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
        {
            FbCommand cmd = new FbCommand();
            using (FbConnection conn = OpenConnection())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a FbCommand (that returns no resultset) using an existing SQL Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public int ExecuteNonQuery(FbTransaction trans, CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
        {
            FbCommand cmd = new FbCommand();
            using (FbConnection conn = OpenConnection())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a FbCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        ///  SqlDataReader r = ExecuteReader( CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public FbDataReader ExecuteReader(CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
        {
            FbCommand cmd = new FbCommand();
            FbConnection conn = OpenConnection();
            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                FbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute a FbCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        ///  Object obj = ExecuteScalar(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of FbParameter used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params FbParameter[] commandParameters)
        {
            FbCommand cmd = new FbCommand();
            using (FbConnection conn = OpenConnection())
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Return a DataSet
        /// </summary>
        public void DoLoadDataSet(DataSet dataSet, string cmdText)
        {
            try
            {
                using (var da = new FbDataAdapter(cmdText, connStr))
                {
                    da.Fill(dataSet, "dataSet");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get the list of User Tables
        ///  TABLE_CATALOG,  TABLE_SCHEMA, TABLE_NAME,  TABLE_TYPE
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableNameList_User()
        {
            using (FbConnection conn = OpenConnection())
            {
                DataTable dta = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });

                if (dta != null)
                {
                    DataTable dt = new DataTable();
                    dt.Columns.Add("Name");
                    foreach (DataRow dra in dta.Rows)
                    {
                        DataRow dr = dt.NewRow();
                        dr[0] = dra["TABLE_NAME"].ToString().Trim();
                        dt.Rows.Add(dr);
                    }
                    return dt;
                }
                return null;
            }

        }

        /// <summary>
        /// Get the list of System Tables
        ///  TABLE_CATALOG,  TABLE_SCHEMA, TABLE_NAME,  TABLE_TYPE
        /// </summary>
        /// <returns></returns>
        public DataTable GetTableNameList_Sys()
        {
            using (FbConnection conn = OpenConnection())
            {
                DataTable dta = conn.GetSchema("Tables", new string[] { null, null, null, "SYSTEM TABLE" });
                return dta;
            }

        }

        /// <summary>
        /// Get Table Columns
        /// TABLE_NAME,COLUMN_NAME,COLUMN_DATA_TYPE,COLUMN_SIZE,ORDINAL_POSITION
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetColumnInfo(string tableName = "")
        {
            using (FbConnection conn = OpenConnection())
            {
                DataTable dta = string.IsNullOrWhiteSpace(tableName)
              ? conn.GetSchema("Columns", new string[] { null, null, null })
              : conn.GetSchema("Columns", new string[] { null, null, tableName.ToUpper() });
                return dta;
            }

        }
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="pathScript">1.sql以;分割 </param>
        public void CreateEmbeddedDb(string pathScript)
        {
            
            if (File.Exists(path))
            {
                return;
            }

            // create a new database
            FbConnection.CreateDatabase(connStr);

            // parse the SQL script
            FbScript script = new FbScript(pathScript);
            script.Parse();

            // execute the SQL script
            using (FbConnection c = new FbConnection(connStr))
            {
                FbBatchExecution fbe = new FbBatchExecution(c);
                foreach (FbStatement cmd in script.Results)
                {
                    fbe.Statements.Add(cmd);
                }
                fbe.Execute();
            }
        }

        /// <summary>
        /// 数据库还原 
        /// </summary>
        /// <param name="pathBak">文件路径</param>
        public void RestoreDb(string pathBak)
        {
            try
            {
                FbRestore res = new FbRestore();
                res.ConnectionString = connStr;
                res.BackupFiles.Add(new FbBackupFile(pathBak, 2048));
                res.Verbose = true;
                res.PageSize = 4096;
                res.Options = FbRestoreFlags.Create | FbRestoreFlags.Replace;
                res.Execute();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <param name="pathBak">文件路径</param>
        public void BackUpDb(string pathBak)
        {
            try
            {
                FbBackup bakup = new FbBackup();
                bakup.ConnectionString = connStr;
                bakup.BackupFiles.Add(new FbBackupFile(pathBak, 2048));
                bakup.Verbose = true;
                bakup.Options = FbBackupFlags.IgnoreLimbo;
                bakup.Execute();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">FbCommand object</param>
        /// <param name="conn">FbConnection object</param>
        /// <param name="trans">FbTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">FbParameter to use in the command</param>
        private void PrepareCommand(FbCommand cmd, FbConnection conn, FbTransaction trans, CommandType cmdType, string cmdText, FbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (FbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        /// <summary>
        /// 服务器式连接 代码生成
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private string GetConnString_Server()
        {
            FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
            cs.UserID = "SYSDBA";
            cs.Password = "masterkey";
            cs.Database = path;
            cs.DataSource = "localhost";
            cs.Charset = "UTF8";
            cs.Port = 3050;
            cs.Dialect = 3;
            cs.Role = "";
            cs.ConnectionLifeTime = 15;
            cs.Pooling = true;
            cs.MinPoolSize = 0;
            cs.MaxPoolSize = 50;
            cs.PacketSize = 8192;
            cs.ServerType = FbServerType.Default;
            return cs.ToString();
        }

        /// <summary>
        /// 嵌入式连接 代码生成
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private string GetConnString_Client()
        {
            FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
            // cs.ClientLibrary = $"{_pathSys}\\fbembed.dll";
            cs.UserID = "SYSDBA";
            cs.Password = "masterkey";
            cs.Database = path;
            cs.Charset = "UTF8";
            cs.ServerType = FbServerType.Embedded;
            return cs.ToString();
        }