Data Application Block for Firebird SQL

using System;
using System.Data;

using FirebirdSql.Data.FirebirdClient;


namespace MyApplication.Data
{
    /// <summary>
    /// DbObject is the class from which all classes in the Data Services Tier
    /// inherit. The core functionality of establishing a connection with the
    /// database and executing simple stored procedures is also provided by
    /// this base class.
    /// DbObject can be used by referencing it directly by the abstract "abstract"
    /// modifier. Then the syntax will be:
    /// using MyApplication.Data;
    /// DBObject myDBObject = new DBObject(myConnectionString);
    /// myDBObject.RunProcedure("MyProcedureName", new IDataParameter[] { }, "tblMyDataTable");
    /// </summary>
    class DBObject
    {

        // Private Variables
        //- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        #region Private Variables
            protected FbConnection myConnection;
            private String myConnectionString;
        #endregion

        // Constructors
        //- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        #region Constructors
        /// <summary>
        /// A parameterized constructor, it allows us to take a connection string
        /// as a constructor argument, automatically instantiating a new connection
        /// </summary>
        public DBObject(string pNewConnectionString)
        {
            myConnectionString = pNewConnectionString;
            myConnection = new FbConnection(myConnectionString);
        }
        #endregion

        // Methods
        //- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        #region Methods

        /// <summary>
        /// Runs a stored procedure, and it returns an integer indicating the return value of the
        /// stored procedure, and also returns the value of the RowsAffected aspect
        /// of the stored procedure that is returned by the ExecuteNonQuery method.
        /// </summary>
        public int RunProcedure(
                String storedProcName,
                IDataParameter[] parameters,
                out int rowsAffected)
        {
            int result;

            myConnection.Open();
            FbCommand command = BuildIntCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            rowsAffected = command.ExecuteNonQuery();
            result = (int)(command.Parameters["ReturnValue"].Value);
            myConnection.Close();
            return result;
        }

        /// <summary>
        /// Will run a stored procedure, can only be called by those classes
        /// deriving from this base. It returns a FbDataReader containing the
        /// result of the stored procedure.
        /// </summary>
        public FbDataReader RunProcedure(
            String storedProcName,
            IDataParameter[] parameters)
        {
            FbDataReader returnReader;

            myConnection.Open();
            FbCommand command = BuildQueryCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;

            returnReader = command.ExecuteReader();
            //Connection.Close();
            return returnReader;
        }

        /// <summary>
        /// Creates a DataSet by running the stored procedure and placing
        /// the results of the query/proc into the given tablename.
        /// </summary>
        public DataSet RunProcedure(
            String storedProcName,
            IDataParameter[] parameters,
            String tableName)
        {
            DataSet myDataSet = new DataSet();

            myConnection.Open();
            FbDataAdapter FbDA = new FbDataAdapter();
            FbDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
            FbDA.Fill(myDataSet, tableName);
            myConnection.Close();
            return myDataSet;
        }

        /// <summary>
        /// Takes an -existing- dataset and fills the given table name
        /// with the results of the stored procedure.
        /// </summary>
        public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet pDataSet, string tableName)
        {
            myConnection.Open();
            FbDataAdapter FbDA = new FbDataAdapter();
            FbDA.SelectCommand = BuildIntCommand(storedProcName, parameters);
            FbDA.Fill(pDataSet, tableName);
            myConnection.Close();
        }
        #endregion

        // Properties
        //- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        #region Properties
        /// <summary>
        /// Protected property that exposes the connection string
        /// to inheriting classes. Read-Only.
        /// </summary>
        protected string ConnectionString
        {
            get
            {
                return myConnectionString;
            }
        }
        #endregion

        // Private Code
        //- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
        #region Private Code

        /// <summary>
        /// Private routine allowed only by this base class, it automates the task
        /// of building a SqlCommand object designed to obtain a return value from
        /// the stored procedure.
        /// </summary>
        /// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>
        /// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>
        /// <returns>Newly instantiated FbCommand instance</returns>
        public FbCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = BuildQueryCommand(storedProcName, parameters);

            command.Parameters.Add(new FbParameter(
                "ReturnValue",
                FbDbType.Integer,
                4, /* Size */
                ParameterDirection.ReturnValue,
                false, /* is nullable */
                0, /* byte precision */
                0, /* byte scale */
                string.Empty,
                DataRowVersion.Default,
                ""));


            return command;
        }


        /// <summary>
        /// Builds a SqlCommand designed to return a SqlDataReader, and not
        /// an actual integer value.
        /// </summary>
        /// <param name="storedProcName">Name of the stored procedure</param>
        /// <param name="parameters">Array of IDataParameter objects</param>
        /// <returns></returns>
        private FbCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = new FbCommand(storedProcName, myConnection);
            command.CommandType = CommandType.StoredProcedure;

            foreach (FbParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;

        }
        #endregion

    }
}