Link to home
Start Free TrialLog in
Avatar of scott_46077
scott_46077

asked on

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

        //=================================================================//
    }
}
Avatar of Gautham Janardhan
Gautham Janardhan

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
                  }
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.
Avatar of scott_46077

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Gautham Janardhan
Gautham Janardhan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial