Solved

C#.NET Data Access Layer - How To

Posted on 2007-03-22
4
1,318 Views
Last Modified: 2013-12-17
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
Comment
Question by:scott_46077
  • 2
4 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 18777772
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
 
LVL 25

Expert Comment

by:apeter
ID: 18778528
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
 

Author Comment

by:scott_46077
ID: 18779500
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
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 18784747
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now