FireBird C# 帮助类

    class DbHelper
    {
        //Properties.Settings.Default.ELSConnectionStr;
        // User=账号;Password=密码;Database=数据库路径;Charset=NONE;ServerType=0
        private readonly string connStr = Properties.Settings.Default.ELSConnectionStr;

        protected char ParameterToken
        {
            get { return '@'; }
        }

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