Stored Procedure vs 3-tier layering

Question

User-2071549094 posted

Dear experts,

I am so new in asp.net and yet now I have to do a project which requires me to use Stored Procedure.

My question is then do we still need the Value Objects or entitles we stored in the Model layer and what about the business logic which we put in our function ?

How is Stored Procedure works with the 3 layering ?

No entity framework is allowed.

Any step by step tutorial which covers above scenario that I can do a hands-on ?

Tks.

Answer 1

User-2057865890 posted

Hi Tangara,

Brief descriptions to each tier:

• The presentation tier, or user services layer - gives a user access to the application.
• The middle tier, or business services layer - consists of business and data rules.
• The data tier, or data services layer - interacts with persistent data usually stored in a database or in permanent storage.

reference: https://msdn.microsoft.com/en-us/library/windows/desktop/ms685068%28v=vs.85%29.aspx 

Stored Procedure in 3-tier code snippets

Data Access Layer

public static DataSet GetData(string _sql, SqlParameter[] Param)
    {
        try
        {
            sqlcmd = new SqlCommand(_sql, sqlcn);
            // cdm.CommandText = _sql;
            foreach (SqlParameter p in Param)
            {
                sqlcmd.Parameters.Add(p);

            }
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcn.Open();
            DataSet ds = new DataSet();
            sqlda = new SqlDataAdapter(sqlcmd);
            sqlda.Fill(ds);
            return ds;

        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            sqlcn.Close();
        }

    }

Business Logic Layer

public DataSet GetData("Parameter list if necessary")
    {
        string _sql = "SpName";
 //List of parameter required
        SqlParameter[] Param = new SqlParameter[0];
        DataSet ds = DataHelper.GetData(_sql,param);
        return ds;
    }

User Interface

Recieve the dataset return by our Business Logic Class.

Dataset ds=ClassObj.GetData("values")

reference: https://msdn.microsoft.com/en-us/library/aa581778.aspx 

Best Regards,

Chris

Answer 2

User1847352307 posted

Data access layer Code
namespace DAL_BookApp
{
  public class Books_DAL
    {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
       public Int32 SaveBooks(Books_BEL objBEL)
       {
           int result;
           try
           {
               SqlCommand cmd = new SqlCommand("InsertBooks_SP", con);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.AddWithValue("@BookName", objBEL.BookName);
               cmd.Parameters.AddWithValue("@Author", objBEL.Author);
               cmd.Parameters.AddWithValue("@Publisher", objBEL.Publisher);
               cmd.Parameters.AddWithValue("@Price", objBEL.Price);
               if (con.State == ConnectionState.Closed)
               {
                   con.Open();
               }               
               result = cmd.ExecuteNonQuery();
               cmd.Dispose();
               if (result > 0)
               {
                   return result;
               }
               else
               {
                   return 0;
               }           
           }
           catch (Exception ex)
           {
               throw;              
           }
           finally
           {             
               if (con.State != ConnectionState.Closed)
               {
                   con.Close();
               }              
           }       
       }
    }
}

Business access layer Code
 namespace BLL_BookApp
{
   public class Books_BLL
    {
        public Int32 SaveBooks(Books_BEL objBel)
        {
            Books_DAL objDal = new Books_DAL();
            try
            {
                return objDal.SaveBooks(objBel);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                objDal = null;
            }
        }         
    }
}

Code behind Code
  Books_BEL objBooksBEL = new Books_BEL();
        Books_BLL objBooksBLL = new Books_BLL();
protected void btnSubmit_Click(object sender, EventArgs e)
        {           
            objBooksBEL.BookName = txtBookName.Text.Trim();
            objBooksBEL.Author = txtAuthor.Text.Trim();
            objBooksBEL.Publisher = txtPublisher.Text.Trim();
            objBooksBEL.Price = Convert.ToDecimal(txtPrice.Text);
            try
            {
                int retVal = objBooksBLL.SaveBooks(objBooksBEL);          
            }
            catch (Exception ex)
            {
                Response.Write("Oops! error occured :" + ex.Message.
            }
            finally
            {
                objBooksBEL = null;
                objBooksBLL = null;
            }
        }