Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1383
  • Last Modified:

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

        //=================================================================//
    }
}
0
scott_46077
Asked:
scott_46077
  • 2
1 Solution
 
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
 
apeterCommented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now