Click here to Skip to main content
Click here to Skip to main content
 
Add your own
alternative version

Data Application Block for Firebird SQL

, 29 Mar 2006
Data Application Block for Firebird SQL intended to speed the development of applications.
fbdataapplicationblock.zip
FbDataApplicationBlock
FbDataApplicationBlock.csproj.user
FirebirdSql.Data.FirebirdClient.dll
Properties
Settings.settings
bin
EMPLOYEE.FDB
FbDataApplicationBlock.exe
FbDataApplicationBlock.vshost.exe
fbembed.dll
FirebirdSql.Data.FirebirdClient.dll
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

    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Xela220
Web Developer
United States United States
Alex is the founder, President, Architect, of a one man development and consulting company (Avida, Corp of Miami, FL), and he is also a shameless self promoter. A deserter of the accounting profession, Alex is been developing/supporting windows and web applications for Fortune 500 and small companies as well since 2000.
 
His programming experience includes VB6, VB.NET, C#, ASP, ASP.NET, MSSQL 7/2000/2005, Firebird SQL 1.7. Alex has acquired expertise in Visual Studio, Reporting Services, SQL Server, and other stuff that I can’t remember right now.
 
In his spare time, he enjoys bicycling with his trusted Litespeed titanium racing bike which is his favorite material possession.
 
He goes anywhere and takes any challenge that the Technology field troughs at him, and sometimes he even wins. He can be more often found at code camps, user groups, the local Microsoft office’s cafeteria, or sleep in his couch.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 29 Mar 2006
Article Copyright 2006 by Xela220
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid