C#.NET Data Access Layer - How To

I have a class that I set up that I want to use as a DAL.  I am not sure if this is how you do this so I wanted to get feedback or advice from experts out here.  I also have a class containing some constants (not shown).  Here is my code.  Keep in mind this is my first attempt.

Question:
1.  How do I handle errors from the connection or from the database?
2.  Am I way off base here?

==========================================================================
Here is how I use my DAL class:
==========================================================================
        private void button1_Click(object sender, EventArgs e)
        {
            // Establish Data Access Layer Object.
            oraDataAccessLayer myDataAccessLayer = new oraDataAccessLayer();

            try
            // TRY - Attempt to complete the following code.
            {
                // Obtain the ConnectionString.
                myDataAccessLayer.strConnStr = Constants.CONNECTION_STRING;

                // Obtain the SQL Statement.
                myDataAccessLayer.strSQL = Constants.SELECT_COMMAND_SubCassette;

                // Execute SQL statement and return a DataSet using the supplied SQL.
                myDataAccessLayer.Return_DataTable();

                // Bind the DataSet to the dataGridView.
                this.dataGridView1.DataSource = myDataAccessLayer.dtDAL_DataTable;
            }

            catch (Exception Ex)
            {
                // CATCH - Block of code that does error handling.
                MessageBox.Show(Ex.ToString());
            }
            finally
            {
                // FINALLY - coding that will run no matter what happens before this point.
                // Dispose the Data Access Layer object.
                myDataAccessLayer = null;
            }
        }

==========================================================================
Here is my DAL Class
==========================================================================
using System;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Web;

namespace MSFT_DP.Data_Access_Layer
{
    /// <summary>
    /// Oracle Database helper Class
    /// </summary>
    public class oraDataAccessLayer
    {
        //=================================================================//

        /// <summary>
        /// Establish a new Oracle Data Access Layer Object
        /// </summary>
        public oraDataAccessLayer()
        {
            PopulateDefault();
        }

        //=================================================================//

        /// <summary>
        /// Generic Populate Default method.  Set any default values here.
        /// </summary>
        public void PopulateDefault()
        {
            _dbConn = null;
            _dtDAL_DataTable = null;
            _strConnStr = "";
            _strSQL = "";
        }

        //=================================================================//

        /// <summary>
        /// An OracleConnection Object.
        /// </summary>
        private OracleConnection _dbConn;
        public OracleConnection dbConn
        {
            get { return _dbConn; }
            set { _dbConn = value; }
        }

        //=============================//
        /// <summary>
        /// Holds the ConnectionString.
        /// </summary>
        private string _strConnStr;
        public string strConnStr
        {
            get { return _strConnStr; }
            set { _strConnStr = value; }
        }

        //=============================//
        /// <summary>
        /// Holds the SQL statement.
        /// </summary>
        private string _strSQL;
        public string strSQL
        {
            get { return _strSQL; }
            set { _strSQL = value; }
        }

        //=============================//

        /// <summary>
        /// Establish a DataTable Object to hold the results of the queries.
        /// </summary>
        private DataTable _dtDAL_DataTable;
        public DataTable dtDAL_DataTable
        {
            get { return _dtDAL_DataTable; }
            set { _dtDAL_DataTable = value; }
        }

        //=============================//

        /// <summary>
        /// Establish a integer to hold the results of the queries.
        /// </summary>
        private int _intDAL_NonQuery;
        public int intDAL_NonQuery
        {
            get { return _intDAL_NonQuery; }
            set { _intDAL_NonQuery = value; }
        }

        //=============================//

        /// <summary>
        /// Establish a DataSet Object to hold the results of the queries.
        /// </summary>
        private Int32 _int32DAL_Scalar;
        public Int32 int32DAL_Scalar
        {
            get { return _int32DAL_Scalar; }
            set { _int32DAL_Scalar = value; }
        }

        //=============================//

        /// <summary>
        /// Establish an OracleConnection using the ConnectionString passed in.
        /// </summary>
        /// <param name="strConnString"></param>
        /// <returns></returns>
        public OracleConnection Open_Connection(string strConnString)
        {
            try
            // TRY - Attempt to complete the following code.
            {
                //OracleConnection dbConn = new OracleConnection(strConnString);
                _dbConn = new OracleConnection(_strConnStr);

                if (IsOpen(_dbConn) != true)
                {
                    _dbConn.Open();
                    return _dbConn;
                }

                return _dbConn;
            }
            catch (Exception Ex)
            {
                // CATCH - Block of code that does error handling.
                return null;
            }
            finally
            {
                // FINALLY - coding that will run no matter what happens before this point.
            }
        }

        //=================================================================//

        /// <summary>
        /// Check to see if the Connection is already open.
        /// </summary>
        /// <param name="oraConn"></param>
        /// <returns></returns>
        public bool IsOpen(OracleConnection oraConn)
        {
            if ((oraConn != null) && (oraConn.State == ConnectionState.Open))
                return true;
            else
                return false;
        }

        //=================================================================//

        /// <summary>
        /// Close the OracleConnection.
        /// </summary>
        /// <param name="dbConn"></param>
        public void Close_Connection(OracleConnection oraConn)
        {
            try
            // TRY - Attempt to complete the following code.
            {
                // Determine if a connection was actually made with the database
                if ((oraConn.State == ConnectionState.Open))
                {
                    oraConn.Close();
                }
            }
            catch (Exception Ex)
            {
                // CATCH - Block of code that does error handling.
            }
            finally
            {
                // FINALLY - coding that will run no matter what happens before this point.
            }
        }

        //=================================================================//

        /// <summary>
        /// Establishes a Connection to the database, executes SQL statement
        /// and return results as a DataTable using the ConnectionString and
        /// SQL passed in, then closes the Connection.
        /// </summary>
        public void Return_DataTable()
        {
            // Establish an Oracle Connection.
            _dbConn = Open_Connection(_strConnStr);

            // If the Connection IS NOT open OR null, return an empty DataTable.
            if ((_dbConn.State != ConnectionState.Open) || (_dbConn==null))
            {
                return;
            }

            // If the Connection IS open, fill the DataTable.
            OracleCommand cmd = new OracleCommand(_strSQL, _dbConn);
            cmd.CommandType = CommandType.Text;

            OracleDataAdapter da = new OracleDataAdapter(cmd);
            OracleCommandBuilder cb = new OracleCommandBuilder(da);
            DataTable dt = new DataTable();

            try
            // TRY - Attempt to complete the following code.
            // Fill the DataTable.
            {
                da.Fill(dt);
                _dtDAL_DataTable = dt;
            }

            catch (Exception Ex)
            {
                // CATCH - Block of code that does error handling.
            }
            finally
            {
                // FINALLY - coding that will run no matter what happens before this point.
                // Close the connection to the database and dispose of all objects.
                Close_Connection(_dbConn);
                cmd.Dispose();
                da.Dispose();
                dt.Dispose();
                _dbConn.Dispose();
            }
        }

        //=================================================================//

        /// <summary>
        /// Executes a Non-Query using the provided Connection and SQL statement.
        /// For UPDATE, INSERT, and DELETE statements, the return value is the number
        /// of rows affected by the command. For CREATE TABLE and DROP TABLE statements,
        /// the return value is 0. For all other types of statements, the return value is -1.
        /// If a rollback occurs, the return value is also -1.
        /// </summary>
        public void Execute_NonQuery()
        {
            // Establish an Oracle Connection.
            _dbConn = Open_Connection(_strConnStr);

            // If the Connection IS NOT open OR null, return nothing.
            if ((_dbConn.State != ConnectionState.Open) || (_dbConn == null))
            {
                return;
            }

            // If the Connection IS open, perform the ExecuteNonQuery.
            OracleCommand cmd = new OracleCommand(_strSQL, _dbConn);
            cmd.CommandType = CommandType.Text;
           
            try
            // TRY - Attempt to complete the following code.
            // Obtain the Return value.
            {
                _intDAL_NonQuery = cmd.ExecuteNonQuery();
            }

            catch (Exception Ex)
            {
                // CATCH - Block of code that does error handling.
            }
            finally
            {
                // FINALLY - coding that will run no matter what happens before this point.
                // Close the connection to the database and dispose of all objects.
                Close_Connection(_dbConn);
                cmd.Dispose();
                _dbConn.Dispose();
            }
        }

        //=================================================================//

        /// <summary>
        /// Executes a Scalar Query using the provided Connection and SQL statement.
        /// Executes the query, and returns the first column of the first row in the
        /// result set returned by the query as a .NET Framework data type. Extra columns
        /// or rows are ignored.
        /// Example - "SELECT COUNT(*) AS NumberOfRegions FROM Region";
        /// </summary>
        public void Execute_Scalar()
        {
            // Establish an Oracle Connection.
            _dbConn = Open_Connection(_strConnStr);

            // If the Connection IS NOT open OR null, return 0.
            if ((_dbConn.State != ConnectionState.Open) || (_dbConn==null))
            {
                _int32DAL_Scalar = 0;
                return;
            }

            // If the Connection IS open, perform the ExecuteScalar.
            OracleCommand cmd = new OracleCommand(_strSQL, _dbConn);
            cmd.CommandType = CommandType.Text;
           
            try
            // TRY - Attempt to complete the following code.
            // Obtain the Return value.
            {
                _int32DAL_Scalar = (Int32)cmd.ExecuteScalar();
            }

            catch (Exception Ex)
            {
                // CATCH - Block of code that does error handling.
            }
            finally
            {
                // FINALLY - coding that will run no matter what happens before this point.
                // Close the connection to the database and dispose of all objects.
                Close_Connection(_dbConn);
                cmd.Dispose();
                _dbConn.Dispose();
            }
        }

        //=================================================================//
    }
}
scott_46077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gautham JanardhanCommented:
since u are using oracle i would suggest u have two catch block like one to cath sql exceptions and other to catch all other exception

like

try
                  {
                  }
                  catch(System.Data.OleDb.OleDbException ex)
                  {
                        //handle sql error
                  }
                  catch(Exception ex)
                  {
                        //handle other errors
                  }
0
Aaron JabamaniTechnical ArchitectCommented:
InDAL , you will get exception first if there is any, maybe for the catch block u can thrown(throw ex;) the exception back to that callee (button_click) and u can display or log the message u want.
0
scott_46077Author Commented:
thank you for the responses, could you provide an example of how to handle the errors?

or

how to throw the exception back to the callee button?

Regards
Scott
0
Gautham JanardhanCommented:
try
                  {
                  }
                  catch(System.Data.OleDb.OleDbException ex)
                  {
                        throw new Exception("SqlError");
                        //or
                        throw ex;
                  }
                  catch(Exception ex)
                  {
                        throw new Exception("Other Exception");
                        //or
                        throw ex;                  
                  }
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.