locked
Stored Procedure vs 3-tier layering RRS feed

  • 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.

    Saturday, November 19, 2016 2:21 AM

All replies

  • 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

    Monday, November 21, 2016 6:12 AM
  • 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;
                }
            }

    Thursday, November 24, 2016 8:26 AM