Three layer application architecture design

Question

0.00/5 (No votes)

See more:

I have three layer application as follows:

Presentation > Entities (VO) > Business Layer (BAL) > Data Layer (DAL)

I do not like following things in this solution:

in BAL classes there is still System.Data reference
each BAL class requires DAL class

My questions:

Each VO entity has their own BAL class and each BAL class contains it's own DAL class. Is it possible to have one generic DAL class for all BALs classes instead of creating every time DAL for BAL?
Is it fine to have System.Data in BAL?

My code:

Presentation Layer:

public partial class FrmLogin : Form
{
        private readonly UserBal _userBal;

        public FrmLogin()
        {
            InitializeComponent();
            _userBal = new UserBal();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            var userVo = _userBal.SearchByName(txtUsername.Text);
        }
}

VO example class:

public class UserVo
{
    public int IdUser { get; set; }

    public string Firstname { get; set; }

    public string Lastname { get; set; }

    public string Email { get; set; }
}

BAL example class:

public class UserBal
{
    private readonly UserDal _userDal;

    public UserBal()
    {
        _userDal  = new UserDal();
    }

    public UserVo SearchByName(string name)
    {
        var userVo = new UserVo();
        var dataTable = _userDal.SearchByName(name);

        foreach (DataRow dr in dataTable.Rows)
        {
            userVo.IdUser = int.Parse(dr["t01_id"].ToString());
            userVo.Firstname = dr["t01_firstname"].ToString();
            userVo.Lastname = dr["t01_lastname"].ToString();
            userVo.Email = dr["t01_email"].ToString();
        }
        return userVo;
    }

    public UserVo SearchById(string _id)
    {
        var userVo = new UserVo();
        var dataTable = _userDal.SearchById(_id);

        foreach (DataRow dr in dataTable.Rows)
        {
            userVo.IdUser = int.Parse(dr["t01_id"].ToString());
            userVo.Firstname = dr["t01_firstname"].ToString();
            userVo.Lastname = dr["t01_lastname"].ToString();
            userVo.Email = dr["t01_email"].ToString();
        }
        return userVo;
    }
}

DAL example class:

public class UserDal
        {
            private readonly DbManager _dbManager;

            public UserDal()
            {
                _dbManager = new DbManager("DBConnection");
            }
            public DataTable SearchByName(string username)
            {              
                    var parameters = new List<IDbDataParameter>
                    {
                        _dbManager.CreateParameter("@FirstName", 50, username, DbType.String),
                    };

//_dbManager.SearchByName returns DataTable object
                   return _dbManager.SearchByName("SELECT * FROM tbUsers WHERE FirstName=@FirstName", CommandType.Text, parameters.ToArray());
            }
            public DataTable SearchById(string id)
            {
                var parameters = new List<IDbDataParameter>
                {
                    _dbManager.CreateParameter("@Id", 50, id, DbType.Int32),
                };

//_dbManager.SearchById returns DataTable object
                return _dbManager.SearchById("SELECT * FROM tbUsers WHERE Id=@Id", CommandType.Text, parameters.ToArray());
            }
        }

What I have tried:

as above shown current design of code

Posted 3-May-18 23:51pm

nighttrain_

Updated 4-May-18 15:03pm

Wendelius

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

j snooze · Answer 1 · 2018-05-04T06:30:00

I personally would not have searchbyname in the data access layer. I generally leave the stored procedure or sql statements in the business logic layer because thats a moving target and can change with business needs vs accessing data can be a nice reusable dll. Below is a sample SQL Server class, oracle is very similar. All it does is access the database in the way you tell it, and what database you tell it to and return or execute stuff. I use this across many projects without having to change a thing because with any new project the only thing that usually changes are the business requirements and the front end to work with the data. 1/3rd of my coding is done before I start any new project :)..sort of usually the business logic and saving users from themselves takes the longest.
Having System.data in the BAL is fine. DataTables are a fairly generic way of returning data without locking yourself into a certain database type.
Thats just my 2 cents.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataAccess
{
    public class SqlServer : IDisposable
    {
        // Flag: Has Dispose already been called? 
        bool disposed = false;

        protected Dictionary<int, SqlParameter> _parameters { get; set; }
        protected int _commandTimeout { get; set; }
        /// <summary>
        /// Name of connection in config file
        /// </summary>
        public string ConnectionName { get; set; }
        /// <summary>
        /// Full connectionstring
        /// </summary>
        public string ConnectionString { get; set; }

        public enum ReturnType
        {
            DataTable,
            DataReader,
            Scalar,
            DataSet,
            NoResult
        }

        /// <summary>
        /// Instantiate object
        /// </summary>
        public SqlServer()
            :this("DefaultConnection")
        {

        }

        /// <summary>
        /// Instantiate object
        /// </summary>
        /// <param name="connectionName">Connection Name attribute in app or web config file</param>
        public SqlServer(string connectionName)
        {
            ConnectionName = connectionName;
        }

        /// <summary>
        /// Get SQL Connection
        /// </summary>
        /// <param name="connectionName">Connection Name attribute in app or web config file</param>
        /// <returns>SQL Connection</returns>
        private SqlConnection getConnection(string connectionName)
        {
            SqlConnection sqlConn;
            string connString;

            try
            {
                connString = (ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString != null) ? ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString : ConnectionString;
                sqlConn = new SqlConnection(connString);
                sqlConn.Open();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sqlConn;
        } // private SqlConnection getConnection()


        /// <summary>
        /// Adds the parameters to a SQL command
        /// </summary>
        /// <param name="commandText">The SQL query to execute</param>
        /// <param name="parameters">Parameters to pass to the SQL query</param>
        private static void AddParameters(SqlCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                return;
            }

            foreach (var param in parameters)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = param.Key;
                parameter.Value = param.Value ?? DBNull.Value;
                command.Parameters.Add(parameter);
            }
        }

        /// <summary>
        /// Executes SQL Statement
        /// </summary>
        /// <param name="SQL">SQL String</param>
        /// <param name="parms">Dictionary of sql parameters, collection expects the dictionary key to start with 1 and go in sequential order.</param>
        /// <param name="returnDataType">Enum of datatype you want returned.</param>
        /// <returns>Specified returnDataType Object</returns>
        public object executeSQL(string SQL, ReturnType returnDataType)
        {
            SqlCommand sqlComm = new SqlCommand();
            SqlDataAdapter sqlDA = null;

            try
            {
                sqlComm = new SqlCommand(SQL, getConnection(ConnectionName));
                sqlComm.CommandType = CommandType.Text;

                switch (returnDataType)
                {
                    case ReturnType.DataReader:
                        return sqlComm.ExecuteReader();
                    case ReturnType.DataTable:
                        DataTable dtResult = new DataTable();
                        sqlDA = new SqlDataAdapter(sqlComm);
                        sqlDA.Fill(dtResult);
                        return dtResult;
                    case ReturnType.DataSet:
                        sqlDA = new SqlDataAdapter(sqlComm);
                        DataSet dsResult = new DataSet();
                        sqlDA.Fill(dsResult);
                        return dsResult;
                    case ReturnType.Scalar:
                        return sqlComm.ExecuteScalar();
                    case ReturnType.NoResult:
                        return sqlComm.ExecuteNonQuery();
                    default:
                        return sqlComm.ExecuteReader();
                }//end switch

            } //end try
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlComm != null)
                {
                    //can't close connection on a datareader
                    if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
                    sqlComm.Dispose();
                }
                if (sqlDA != null) { sqlDA.Dispose(); }
            }

        } //public object executeSQL

        /// <summary>
        /// Executes SQL Statement
        /// </summary>
        /// <param name="SQL">SQL String</param>
        /// <param name="parms">Dictionary of sql parameters, collection expects the dictionary key to start with 1 and go in sequential order.</param>
        /// <param name="returnDataType">Enum of datatype you want returned.</param>
        /// <returns>Specified returnDataType Object</returns>
        public object executeSQL(string SQL, Dictionary<string,object> parameters, ReturnType returnDataType)
        {
            SqlCommand sqlComm = new SqlCommand();
            SqlDataAdapter sqlDA = null;

            try
            {
                sqlComm = new SqlCommand(SQL, getConnection(ConnectionName));
                sqlComm.CommandType = CommandType.Text;
                AddParameters(sqlComm, parameters);

                switch (returnDataType)
                {
                    case ReturnType.DataReader:
                        return sqlComm.ExecuteReader();
                    case ReturnType.DataTable:
                        DataTable dtResult = new DataTable();
                        sqlDA = new SqlDataAdapter(sqlComm);
                        sqlDA.Fill(dtResult);
                        return dtResult;
                    case ReturnType.DataSet:
                        sqlDA = new SqlDataAdapter(sqlComm);
                        DataSet dsResult = new DataSet();
                        sqlDA.Fill(dsResult);
                        return dsResult;
                    case ReturnType.Scalar:
                        return sqlComm.ExecuteScalar();
                    case ReturnType.NoResult:
                        return sqlComm.ExecuteNonQuery();
                    default:
                        return sqlComm.ExecuteReader();
                }//end switch

            } //end try
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlComm != null)
                {
                    //can't close connection on a datareader
                    if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
                    sqlComm.Dispose();
                }
                if (sqlDA != null) { sqlDA.Dispose(); }
            }

        } //public object executeSQL

        /// <summary>
        /// Executes stored procedure 
        /// </summary>
        /// <param name="storedProcName">Name of stored procedure</param>
        /// <param name="parms">Dictionary of sql parameters, collection expects the dictionary key to start with 1 and go in sequential order.</param>
        /// <param name="returnDataType">Enum of datatype you want returned.</param>
        /// <returns>Specified returnDataType Object</returns>
        public object executeProcedure(string storedProcName, Dictionary<int, SqlParameter> parms, ReturnType returnDataType)
        {
            SqlCommand sqlComm = new SqlCommand();
            SqlDataAdapter sqlDA = null;

            try
            {
                sqlComm = new SqlCommand(storedProcName, getConnection(ConnectionName));
                sqlComm.CommandType = CommandType.StoredProcedure;

                for (int i = 1; i <= parms.Count; i++)
                {
                    sqlComm.Parameters.Add(parms[i]);
                }

                switch (returnDataType)
                {
                    case ReturnType.DataReader:
                        return sqlComm.ExecuteReader();
                    case ReturnType.DataTable:
                        DataTable dtResult = new DataTable();
                        sqlDA = new SqlDataAdapter(sqlComm);
                        sqlDA.Fill(dtResult);
                        return dtResult;
                    case ReturnType.DataSet:
                        sqlDA = new SqlDataAdapter(sqlComm);
                        DataSet dsResult = new DataSet();
                        sqlDA.Fill(dsResult);
                        return dsResult;
                    case ReturnType.Scalar:
                        return sqlComm.ExecuteScalar();
                    case ReturnType.NoResult:
                        return sqlComm.ExecuteNonQuery();
                    default:
                        return sqlComm.ExecuteReader();
                }//end switch

            } //end try
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlComm != null)
                {
                    //can't close connection on a datareader
                    if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
                    sqlComm.Dispose();
                }
                if (sqlDA != null) { sqlDA.Dispose(); }
            }

        } //public object executeProcedure

        // Dispose() calls Dispose(true)
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        // Protected implementation of Dispose pattern. 
        protected virtual void Dispose(bool disposing)
        {
            if (disposed)
                return;

            if (disposing)
            {
                // Free any other managed objects here. 
            }
            disposed = true;
        }
    }
}

Gerry Schmitz · Answer 2 · 2018-05-04T15:05:00

It's over-engineered, IMO.

Your "BAL" (as shown) could easily combined with your DAL to create a single "access layer".

Your existing BAL would be "public"; the DAL methods private.

("Data rows" in the BAL should be a give-away; they are only 1 step removed from the physical database; I would expect to see only "collections" of entities / DTOs).

Nothing wrong with loading an "observable collection" (for a view) directly from the "data access layer" via a query; instead of resorting to an artificial "business layer" just to satisfy the model.

Three layer application architecture design

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0