Use of BAL in 3 tier architecture?How to call methods from DAL to BAL

Question

I am a newbie to 3 tier architecture and below is my DAL code

public static int Insert(string firstname, string lastname, DateTime dob, string gender,string email, string password)
    {
        // bool flag = false;
        SqlParameter pid;
        SqlParameter result;

        SqlConnection con = Generic.DBConnection.OpenConnection();

        try
        {

            SqlCommand cmd1 = new SqlCommand("Insertreg", con);
            cmd1.CommandType = CommandType.StoredProcedure;
            cmd1.Parameters.AddWithValue("@FirstName", firstname);
            cmd1.Parameters.AddWithValue("@LastName", lastname);
            cmd1.Parameters.AddWithValue("@Dob", dob);
            cmd1.Parameters.AddWithValue("@Gender", gender);
           cmd1.Parameters.AddWithValue("@EmailId", email);
            cmd1.Parameters.AddWithValue("@Password", password);
            result = cmd1.Parameters.Add("@result", System.Data.SqlDbType.Int);
            result.Direction = System.Data.ParameterDirection.Output;
            pid = cmd1.Parameters.Add("@id", System.Data.SqlDbType.Int);
            pid.Direction = System.Data.ParameterDirection.Output;
            return cmd1.ExecuteNonQuery();


            con.Close();

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

this in BAL

 public int insert(string firstname,string lastname,DateTime dob,string gender,string email,string password)
    {
      ProfileMasterDAL dal=new ProfileMasterDAL();
      try
      {
          return ProfileMasterDAL.Insert(firstname, lastname, dob, gender,email, password);
      }
      catch (Exception ex)
      {

          throw ex;
      }
        finally
      {
          dal = null;
      }

    }

I my UI

  ProfileMasterBLL pmBLL = new ProfileMasterBLL();
 pmBLL.insert(firstname, lastname, dob, gender, mobile, country, state, email, password);

Is this the correct way to code in 3 tier??I mean how to call methods from DAL to BAL and into UI?If not suggest me some good way.Thanks.

Whipple

Have you tried Googling for this? There are quite a few good tutorials and demos out there. google.co.uk/… got me here: forums.asp.net/t/1681592.aspx/1 – Billiebilling

When i google i am getting different results and i am confused,so i have asked a question here. – Whipple

Instead of passing all the args into the BLL, consider passing an object with properties. If the BL doesn't have a direct reference on the DAL, you can have a types assembly. – Venitavenite

Can you tell me what is passing an object with properties? – Whipple

@Venitavenite - Could you please explain what do you benefit from passing an object to the BLL rather then passing strings and ints? – Fyn

If you're BLL has many methods (and even overloads on those) and if you take firstName, lastName etc... as args to those overloads, when you add another data point (like alternateEmail) do you add that to all the insert, add etc... overloads. It's better to just take an Employee object ... The properties on the employee can change but you won't have to churn all of your signatures. – Venitavenite

Answer 1

Normally I do the following:

1. Define a Business Layer (BL, you call it BAL). This contains the definitions of you business entities. It also defines interfaces to retrieve/save/delete data for whatever patterns you use (repository, context, etc).
2. Define a Data Access Layer (DAL). This contains the actual implementation for the retrieve/save/delete interfaces.
3. Define a UI layer. This contains UI elements (forms, controls, models, controllers, etc), which can use the BL to load data.

The references are the following:

1. The BL doesn't know the DAL or the UI.
2. The DAL knows the BL. The DAL does not know the UI.
3. THe UI knows the BL. The UI does not know the DAL.

The big question for you probably is, how does the BL retrieve/save/delete data when it doesn't know the DAL, and therefore cannot create an instance of a class in the DAL. Well, this is where a little Dependency Injection comes in handy. All you have to wire up is the injection of the DAL-class to the BL-interface.

Hope this makes sense. I use it as my standard 3-tier implementation, and it works absolutely without problems. Specifically, I use Entity Framework with POCO for entities, and the DI I use is a custom one, but any of the ones out there will do.

UPDATE

The BL does not know the DAL.

• The BL defines an interface (lets call it IRepository) which it can use to do what it needs to do.
• The DAL defines a class (Repository) which implements the interface IRepository. So the actual implementation of the repository is in the DAL.
• Obviously the BL cannot create an instance of the repository directly. This is where dependency injection comes in, this allows the developer to create an instance of a class where it normally cannot be done. A simple crude version of this, is to use reflection.

I hope this makes more sense.

Rhumb

Thanks for the reply but i have a doubt here The BL doesn't know the DAL or the UI... but we have to call the DAL methods here right? BAL acts as middle layer between DAL and UI so DAL and UI cant know each other directly. – Whipple

I've added an update to the answer. Yes you have to call a method, but you do not have to know the class type if you hide the class behind an interface. – Rhumb

Sorry i dont understand this.... but you do not have to know the class type if you hide the class behind an interface. – Whipple

What I mean it that for the BL to use a certain object, it does not have to know of what type the object is, as long as the object implements an interface, and the BL knows the interface. Google for c# and interfaces to know more about them, e.g. codeproject.com/Articles/18743/Interfaces-in-C-For-Beginners. – Rhumb

Answer 2

You can you the following sample code for 3 tier architecture :-

CLASS - BAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;

public class BAL
{
    DAL objDAL;
    public BAL()
    {

    }

    public string _Name;
    public string Name
    {
        get { return _Name; }
        set { _Name = value; }
    }

    public int insert()
    {
        objDAL = new DAL();
        int val = 0;
        try
        {
            Hashtable objHash = new Hashtable();
            objHash.Add("@Name", Convert.ToString(_Name));
            val = objDAL.Insert("Your SP Name", objHash);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            objDAL = null;
        }
        return val;
    }
}

CLASS - DAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

public class DAL : IDisposable
{
    SqlConnection con;

    public DAL()
    {
        con = new SqlConnection("Connection String");
    }

    public int Insert(string CMD, Hashtable objHash)
    {
        int val = 0;
        try
        {
            SqlCommand cmd1 = new SqlCommand(CMD, con);
            cmd1.CommandType = CommandType.StoredProcedure;
            foreach (DictionaryEntry de in objHash)
            {
                cmd1.Parameters.AddWithValue(Convert.ToString(de.Key), Convert.ToString(de.Value));
            }
            con.Open();
            val = cmd1.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
        return val;
    }

    #region IDisposable Members

    public void Dispose()
    {
        throw new NotImplementedException();
    }

    #endregion
}

UI:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{

    BAL objBAL;

    protected void Page_Load(object sender, EventArgs e)
    {
        Insert();
    }

    public void Insert()
    {
        int val = 0;
        objBAL = new BAL();
        objBAL.Name = "stackoverflow";
        try
        {
            val = objBAL.insert();
        }
        catch { }
        finally
        {
            objBAL = null;
        }
        if (val != 0)
        {
            //Insert sucessful
        }
        else
        {
            //Error in Insert.
        }
    }
}

Simons

Answer 3

It might help you to see some actual code. I suggest you download NetTiers, run it against your db schema and see the outputted code for the implementation details.

Billiebilling

Answer 4

using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;
using System.ComponentModel;

namespace dal
{

    /// <summary>
    /// Summary description for Data Access Layer
    /// </summary>
    public class DataAccess
    {
        public string strConnectionString;
        private DbConnection objConnection;
        private DbCommand objCommand;
        private DbProviderFactory objFactory = null;
        private bool boolHandleErrors=false;
        private string strLastError;
        private bool boolLogError=false;
        private string strLogFile;

        public DataAccess()
        {

            //strConnectionString = ;
            strConnectionString = objCommon.GetConnectionString;
            objFactory = OleDbFactory.Instance;
            objConnection = objFactory.CreateConnection();
            objCommand = objFactory.CreateCommand();
            objConnection.ConnectionString = strConnectionString;
            objCommand.Connection = objConnection;
        }

        public bool HandleErrors
        {
            get
            {
                return boolHandleErrors;
            }
            set
            {
                boolHandleErrors = value;
            }
        }

        public string LastError
        {
            get
            {
                return strLastError;
            }
        }

        public bool LogErrors
        {
            get
-            {
                return boolLogError;
            }
            set
            {
                boolLogError = value;
            }
        }

        public string LogFile
        {
            get
            {
                return strLogFile;
            }
            set
            {
                strLogFile = value;
            }
        }

        public int AddParameter(string name, object value)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            return objCommand.Parameters.Add(p);
        }

        public int AddParameter(string name, object value, ParameterDirection direction)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            p.Direction = direction;
            return objCommand.Parameters.Add(p);
        }

        public int AddParameter(string name, object value, DbType type)
        {
            DbParameter p = objFactory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            p.DbType = type;
            return objCommand.Parameters.Add(p);
        }

        public int AddParameter(DbParameter parameter)
        {
            return objCommand.Parameters.Add(parameter);
        }

        public DbCommand Command
        {
            get
            {
                return objCommand;
            }
        }

        public void BeginTransaction()
        {
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                objCommand.Transaction = objConnection.BeginTransaction();
            }
            catch (Exception Ex)
            {
                HandleExceptions(Ex);
            }
        }

        public void CommitTransaction()
        {
            objCommand.Transaction.Commit();
            objConnection.Close();
        }

        public void RollbackTransaction()
        {
            objCommand.Transaction.Rollback();
            objConnection.Close();
        }

        public int ExecuteNonQuery(string query)
        {
            return ExecuteNonQuery(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query, CommandType commandtype)
        {
            return ExecuteNonQuery(query, commandtype, ConnectionState.CloseOnExit);
        }

        public int ExecuteNonQuery(string query, ConnectionState connectionstate)
        {
            return ExecuteNonQuery(query, CommandType.Text, connectionstate);
        }

        public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            int i = -1;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                i = objCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return i;
        }

        public object ExecuteScalar(string query)
        {
            return ExecuteScalar(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query, CommandType commandtype)
        {
            return ExecuteScalar(query, commandtype, ConnectionState.CloseOnExit);
        }

        public object ExecuteScalar(string query, ConnectionState connectionstate)
        {
            return ExecuteScalar(query, CommandType.Text, connectionstate);
        }

        public object ExecuteScalar(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            object o = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                o = objCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    objConnection.Close();
                }
            }

            return o;
        }

        public DbDataReader ExecuteReader(string query)
        {
            return ExecuteReader(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DbDataReader ExecuteReader(string query, CommandType commandtype)
        {
            return ExecuteReader(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DbDataReader ExecuteReader(string query, ConnectionState connectionstate)
        {
            return ExecuteReader(query, CommandType.Text, connectionstate);
        }

        public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            DbDataReader reader = null;
            try
            {
                if (objConnection.State == System.Data.ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    reader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    reader = objCommand.ExecuteReader();
                }

            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
            }

            return reader;
        }

        public DataSet ExecuteDataSet(string query)
        {
            return ExecuteDataSet(query, CommandType.Text, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query, CommandType commandtype)
        {
            return ExecuteDataSet(query, commandtype, ConnectionState.CloseOnExit);
        }

        public DataSet ExecuteDataSet(string query, ConnectionState connectionstate)
        {
            return ExecuteDataSet(query, CommandType.Text, connectionstate);
        }

        public DataSet ExecuteDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
        {
            DbDataAdapter adapter = objFactory.CreateDataAdapter();
            objCommand.CommandText = query;
            objCommand.CommandType = commandtype;
            adapter.SelectCommand = objCommand;
            DataSet ds = new DataSet();

            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                HandleExceptions(ex);
            }
            finally
            {
                objCommand.Parameters.Clear();
                if (connectionstate == ConnectionState.CloseOnExit)
                {
                    if (objConnection.State == System.Data.ConnectionState.Open)
                    {
                        objConnection.Close();
                    }
                }
            }
            return ds;
        }

        private void HandleExceptions(Exception ex)
        {

            throw ex;

        }

        private void WriteToLog(string msg)
        {
            StreamWriter writer = File.AppendText(LogFile);
            writer.WriteLine(DateTime.Now.ToString() + " - " + msg);
            writer.Close();
        }

        public void Dispose()
        {
            objConnection.Close();
            objConnection.Dispose();
            objCommand.Dispose();
        }



        public enum Providers
        {
            SqlServer, OleDb, Oracle, ODBC, ConfigDefined
        }

        public enum ConnectionState
        {
            KeepOpen, CloseOnExit
        }

        public interface ILoadFromDataRow
        {
            bool LoadFromDataRow(DataRow row);
        }


    }

}

Tildatilde