ASP.NET 3 Tire Architecture



ASP.NET 3 Tire Architecture using stored procedure.
Please Follow me step by step, so you get less error and understanding flow of Program.
Design Part



Second page(List.aspx)


 Adding database in your application as following figure



CREATE Stored Procedure
For Insert Data
ALTER PROCEDURE InsertData
                        (
                                                @firstName varchar(50),
                                                @lastName varchar(50),
                                                @age int
                        )
AS
                        /* SET NOCOUNT ON */
                        DECLARE @Count int
                        select @Count = Count(PersonID) from Person WHERE FirstName = @firstName AND lastname=@lastname/* this is for checking  alredy exists or not as per firstname & lastname*/
                       
                        IF @Count = 0
                                                BEGIN
                                                                                                INSERT INTO Person (FirstName, LastName, Age)
                                                                                                VALUES
                                                                                                (@firstName, @lastName, @age)
                                                END
                                                RETURN @Count
                        RETURN

For Updateing Data


ALTER PROCEDURE UpdateData
(
                        @personID int,
                        @firstName varchar(50),
                        @lastName varchar(50),
                        @age int
)
AS
                        /* SET NOCOUNT ON */
                        UPDATE Person SET
                                                Firstname = @firstName,
                                                Lastname = @lastName,
                                                Age = @age
                                                WHERE PersonID = @personID
                        RETURN


For Updateing Data
ALTER PROCEDURE DeleteData
                        (
                                                @personID int
                        )
AS
                        /* SET NOCOUNT ON */
                        Delete Person WHERE PersonID = @personID
                        RETURN
For Seleting or loading Data

ALTER PROCEDURE LoadAll
                       
AS
                        /* SET NOCOUNT ON */
                        SELECT * FROM Person ORDER BY FirstName
                        RETURN
For Searching as per string Data


ALTER PROCEDURE SearchFirstName
                        (
                                                @firstName varchar(50)
                        )
                       
AS
                        /* SET NOCOUNT ON */
                        SELECT * FROM Person WHERE FirstName
                                                LIKE @firstName ORDER BY FirstName
                        RETURN



Web.Config file


<?xml version="1.0"?>
<!--
    Note: As an alternative to hand editing this file you can use the
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in
    machine.config.comments usually located in
    \Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration>
                        <appSettings/>
                        <connectionStrings>
                                                <add name="TutTestConn" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Tamagna\Documents\Visual Studio 2010\WebSites\4way\App_Data\Database.mdf;Integrated Security=True;User Instance=True"/>
                                                <add name="TutTestConn" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Tamagna\Documents\Visual Studio 2010\WebSites\4way\App_Data\Database.mdf;Integrated Security=True;User Instance=True"/>
                                                <!--<add name="TutTestConn" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\Nothing\Dream\Learning\TutorialsForUsers\N-TierArchitecture\App_Data\Database.mdf;Integrated Security=True;User Instance=True;"/>-->
                        </connectionStrings>
                       
</configuration>

right click on the project name in Solution Explorer and create new folder-à App_Code ,
After that click on App_Code Folder-àadd new folder-àgive name of folder BO,DAL,BAL
Again click on DAL Folder--àadd -àAdd New Item àand add Class1.cs(give name person.cs) Finally Ok.
Similar way for renaming 2 folder DAL,BAL
as shown following figure.


1)Person.cs class add code

/// <summary>
/// Summary description for Person
/// </summary>
public class Person
{
                public Person()
                {
       
                }


    int m_PersonID = 0;
    string m_FirstName = string.Empty;
    string m_LastName = string.Empty;
    int m_Age = 0;


    #region Propertiers
    public int PersonID
    {
        get { return m_PersonID; }
        set { m_PersonID = value; }
    }
   
    public string FirstName
    {
        get { return m_FirstName; }
        set { m_FirstName = value; }
    }
   
    public string LastName
    {
        get { return m_LastName; }
        set { m_LastName = value; }
    }
   
    public int Age
    {
        get { return m_Age; }
        set { m_Age = value; }
    }
    #endregion Properties

}

2)DAL.cs class add code

 /// <summary>
/// Summary description for PersonDAL
/// </summary>
public class PersonDAL
{
    string connStr = ConfigurationManager.ConnectionStrings["TutTestConn"].ToString();

                public PersonDAL()
                {

                }
   

    #region Default.aspx
    /// <summary>
    /// Used to insert records into database
    /// </summary>
    /// <param name="p"></param>
    /// <returns></returns>
    public int Insert(Person person)
    {
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        SqlCommand dCmd = new SqlCommand("InsertData", conn);
        dCmd.CommandType = CommandType.StoredProcedure;
        try
        {
            dCmd.Parameters.AddWithValue("@firstName", person.FirstName);
            dCmd.Parameters.AddWithValue("@lastName", person.LastName);
            dCmd.Parameters.AddWithValue("@age", person.Age);
            return dCmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            dCmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }


    /// <summary>
    /// Update record into database
    /// </summary>
    /// <param name="p"></param>
    /// <returns></returns>
    public int Update(Person person)
    {
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        SqlCommand dCmd = new SqlCommand("UpdateData", conn);
        dCmd.CommandType = CommandType.StoredProcedure;
        try
        {
            dCmd.Parameters.AddWithValue("@firstName", person.FirstName);
            dCmd.Parameters.AddWithValue("@lastName", person.LastName);
            dCmd.Parameters.AddWithValue("@age", person.Age);
            dCmd.Parameters.AddWithValue("@personID", person.PersonID);
            return dCmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            dCmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }
    /// <summary>
    /// Delete record from database
    /// </summary>
    /// <param name="person"></param>
    /// <returns></returns>
    public int Delete(Person person)
    {
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        SqlCommand dCmd = new SqlCommand("DeleteData", conn);
        dCmd.CommandType = CommandType.StoredProcedure;
        try
        {
            dCmd.Parameters.AddWithValue("@personID", person.PersonID);
            return dCmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            dCmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }
    /// <summary>
    /// Load all records from database
    /// </summary>
    /// <returns></returns>
    public DataTable Load()
    {
        SqlConnection conn = new SqlConnection(connStr);
        SqlDataAdapter dAd = new SqlDataAdapter("LoadAll", conn);
        dAd.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet dSet = new DataSet();
        try
        {
            dAd.Fill(dSet, "PersonTable");
            return dSet.Tables["PersonTable"];
        }
        catch
        {
            throw;
        }
        finally
        {
            dSet.Dispose();
            dAd.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }

    #endregion Default.aspx

}

3)BAL.cs class add code

/// <summary>
/// Summary description for PersonBAL
/// </summary>
public class PersonBAL
{
                public PersonBAL()
                {
                               
                }  

    #region for default.aspx
    /// <summary>
    /// insert records into database
    /// </summary>
    /// <param name="person"></param>
    /// <returns></returns>
    public int Insert(Person person)
    {
        PersonDAL pDAL = new PersonDAL();
        try
        {
            return pDAL.Insert(person);
        }
        catch
        {
            throw;
        }
        finally
        {
            pDAL = null;
        }
    }

    /// <summary>
    /// Update records into database
    /// </summary>
    /// <param name="person"></param>
    /// <returns></returns>
    public int Update(Person person)
    {
        PersonDAL pDAL = new PersonDAL();
        try
        {
            return pDAL.Update(person);
        }
        catch
        {
            throw;
        }
        finally
        {
            pDAL = null;
        }
    }
    /// <summary>
    /// Delete record from database
    /// </summary>
    /// <param name="person"></param>
    /// <returns></returns>
    public int Delete(Person person)
    {
        PersonDAL pDAL = new PersonDAL();
        try
        {
            return pDAL.Delete(person);
        }
        catch
        {
            throw;
        }
        finally
        {
            pDAL = null;
        }
    }
    /// <summary>
    /// Load records from database
    /// </summary>
    /// <returns></returns>
    public DataTable Load()
    {
        PersonDAL pDAL = new PersonDAL();
        try
        {
            return pDAL.Load();
        }
        catch
        {
            throw;
        }
        finally
        {
            pDAL = null;
        }
    }

    #endregion for default.aspx
}

Now go to the code behind page and write the following code
Default.aspx.cs


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


//PLZ add per ur app name
public partial class _4_Tier_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            BindGrid();
        txtFirstName.Focus();
    }

    /// <summary>
    /// Add records into database
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void AddRecords(object sender, EventArgs e)
    {
        //Lets validate the page first
        if (!Page.IsValid)
            return;

        int intResult = 0;
        // Page is valid, lets go ahead and insert records
        // Instantiate BAL object
        PersonBAL pBAL = new PersonBAL();
        // Instantiate the object we have to deal with
        Person person = new Person();
        // set the properties of the object
        person.FirstName = txtFirstName.Text;
        person.LastName = txtLastName.Text;
        person.Age = Int32.Parse(txtAge.Text);

        try
        {
            intResult = pBAL.Insert(person);
            if (intResult > 0)
                lblMessage.Text = "New record inserted successfully.";
            else
                lblMessage.Text = "FirstName & LastName [<b>" + txtFirstName.Text + "</b> && <b>" +txtLastName.Text + "</b> ] alredy exists, try another name";
            //After Click Submit btn clear all the textbox.
            ClearAll();
            //After Click Submit btn cursor goes on txtfirstname.
            txtFirstName.Focus();
        }           
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            person = null;
            pBAL = null;
        }       
    }
    private void ClearAll()
    {
        txtFirstName.Text = "";
        txtLastName.Text = string.Empty;
        txtAge.Text = string.Empty;
    }

    //for BindGrid Edit, Delete, Pageing ,etc
    /// <summary>
    /// Fired when Cancel button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void CancelRecord(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        BindGrid();
    }


    /// <summary>
    /// Fires when Edit button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void EditRecord(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindGrid();
    }

    /// <summary>
    /// Fires when Update button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void UpdateRecord(object sender, GridViewUpdateEventArgs e)
    {
        int personID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
        int intResult = 0;
        GridViewRow row = GridView1.Rows[e.RowIndex];

        TextBox tFN = (TextBox)row.FindControl("txtFName");
        TextBox tLN = (TextBox)row.FindControl("txtLName");
        TextBox tAge = (TextBox)row.FindControl("txtAge");

        // instantiate BAL
        PersonBAL pBAL = new PersonBAL();
        Person person = new Person();
        try
        {
            person.PersonID = personID;
            person.FirstName = tFN.Text;
            person.LastName = tLN.Text;
            person.Age = Int32.Parse(tAge.Text);
            intResult = pBAL.Update(person);
            if (intResult > 0)
                lblMessage.Text = "Record Updated Successfully.";
            else
                lblMessage.Text = "Record couldn't updated";
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            person = null;
            pBAL = null;
        }

        GridView1.EditIndex = -1;
        // Refresh the list
        BindGrid();
    }

    /// <summary>
    /// fires when Delete button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
    {
        int personID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());


        // instantiate BAL
        PersonBAL pBAL = new PersonBAL();
        Person person = new Person();
        try
        {
            person.PersonID = personID;
            pBAL.Delete(person);

            lblMessage.Text = "Record Deleted Successfully.";
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            person = null;
            pBAL = null;
        }

        GridView1.EditIndex = -1;
        // Refresh the list
        BindGrid();
    }

    /// <summary>
    /// Fires when page links are clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void ChangePage(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        // Refresh the list
        BindGrid();
    }

    /// <summary>
    /// Fires when Columns heading are clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void SortRecords(object sender, GridViewSortEventArgs e)
    {
        DataTable dataTable = GridDataSource();

        if (dataTable != null)
        {
            DataView dataView = new DataView(dataTable);

            dataView.Sort = GetSortExpression(e);

            GridView1.DataSource = dataView;
            GridView1.DataBind();
        }
    }

    #region Private Methods

    /// <summary>
    /// Bind the gridview
    /// </summary>
    private void BindGrid()
    {
        GridView1.DataSource = GridDataSource();
        GridView1.DataBind();
    }

    /// <summary>
    /// Get GridView DataSource
    /// </summary>
    private DataTable GridDataSource()
    {
        PersonBAL p = new PersonBAL();
        DataTable dTable = new DataTable();
        try
        {
            dTable = p.Load();
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            p = null;
        }

        return dTable;
    }

    /// <summary>
    /// Get sort expression for the gridview
    /// </summary>
    /// <param name="e"></param>
    /// <returns></returns>
    private string GetSortExpression(GridViewSortEventArgs e)
    {
        string sortDirection = string.Empty;
        // if clicked on the same column twice then let it toggle the sort order, else reset to ascending
        if (ViewState["SortExpression"] != null)
        {
            if (!ViewState["SortExpression"].ToString().Equals(e.SortExpression.ToLower()))
            {
                ViewState["SortDirection"] = null;
            }
        }

        if (ViewState["SortDirection"] != null)
        {
            if (ViewState["SortDirection"].ToString().Equals("ASC"))
            {
                sortDirection = "DESC";
                ViewState["SortDirection"] = "DESC";
            }
            else
            {
                sortDirection = "ASC";
                ViewState["SortDirection"] = "ASC";
            }
        }
        else
        {
            ViewState["SortDirection"] = "ASC";
        }
        ViewState["SortExpression"] = e.SortExpression.ToLower();

        return e.SortExpression + " " + sortDirection;
    }
    #endregion Private Methods

}

Now go to the code behind page and write the following code

List.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

public partial class _4_Tier_List : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            BindGrid();
    }


    /// <summary>
    /// Fired when Cancel button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void CancelRecord(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        BindGrid();
    }


    /// <summary>
    /// Fires when Edit button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void EditRecord(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindGrid();
    }

    /// <summary>
    /// Fires when Update button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void UpdateRecord(object sender, GridViewUpdateEventArgs e)
    {
        int personID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
        int intResult = 0;
        GridViewRow row = GridView1.Rows[e.RowIndex];

        TextBox tFN = (TextBox) row.FindControl("txtFName");
        TextBox tLN = (TextBox)row.FindControl("txtLName");
        TextBox tAge = (TextBox)row.FindControl("txtAge");

        // instantiate BAL
        PersonBAL pBAL = new PersonBAL();
        Person person = new Person();
        try
        {
            person.PersonID = personID;
            person.FirstName = tFN.Text;
            person.LastName = tLN.Text;
            person.Age = Int32.Parse(tAge.Text);
            intResult = pBAL.Update(person);
            if (intResult > 0)
                lblMessage.Text = "Record Updated Successfully.";
            else
                lblMessage.Text = "Record couldn't updated";
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            person = null;
            pBAL = null;
        }

        GridView1.EditIndex = -1;
        // Refresh the list
        BindGrid();
    }

    /// <summary>
    /// fires when Delete button is clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
    {
        int personID = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
       

        // instantiate BAL
        PersonBAL pBAL = new PersonBAL();
        Person person = new Person();
        try
        {
            person.PersonID = personID;
            pBAL.Delete(person);

            lblMessage.Text = "Record Deleted Successfully.";
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            person = null;
            pBAL = null;
        }

        GridView1.EditIndex = -1;
        // Refresh the list
        BindGrid();
    }

    /// <summary>
    /// Fires when page links are clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void ChangePage(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        // Refresh the list
        BindGrid();
    }

    /// <summary>
    /// Fires when Columns heading are clicked
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void SortRecords(object sender, GridViewSortEventArgs e)
    {
        DataTable dataTable = GridDataSource();

        if (dataTable != null)
        {
            DataView dataView = new DataView(dataTable);

            dataView.Sort = GetSortExpression(e);

            GridView1.DataSource = dataView;
            GridView1.DataBind();
        }
    }

    #region Private Methods

    /// <summary>
    /// Bind the gridview
    /// </summary>
    private void BindGrid()
    {
        GridView1.DataSource = GridDataSource();
        GridView1.DataBind();
    }

    /// <summary>
    /// Get GridView DataSource
    /// </summary>
    private DataTable GridDataSource()
    {
        PersonBAL p = new PersonBAL();
        DataTable dTable = new DataTable();
        try
        {
            dTable = p.Load();
        }
        catch (Exception ee)
        {
            lblMessage.Text = ee.Message.ToString();
        }
        finally
        {
            p = null;
        }

        return dTable;
    }

    /// <summary>
    /// Get sort expression for the gridview
    /// </summary>
    /// <param name="e"></param>
    /// <returns></returns>
    private string GetSortExpression(GridViewSortEventArgs e)
    {
        string sortDirection = string.Empty;
         // if clicked on the same column twice then let it toggle the sort order, else reset to ascending
        if (ViewState["SortExpression"] != null)
        {
            if (!ViewState["SortExpression"].ToString().Equals(e.SortExpression.ToLower()))
            {
                ViewState["SortDirection"] = null;
            }
        }

        if (ViewState["SortDirection"] != null)
        {
            if (ViewState["SortDirection"].ToString().Equals("ASC"))
            {
                sortDirection = "DESC";
                ViewState["SortDirection"] = "DESC";
            }
            else
            {
                sortDirection = "ASC";
                ViewState["SortDirection"] = "ASC";
            }
        }
        else
        {
            ViewState["SortDirection"] = "ASC";
        }
        ViewState["SortExpression"] = e.SortExpression.ToLower();

        return e.SortExpression + " " + sortDirection;
    }
    #endregion Private Methods
}

Build-àRun

OUTPUT
1ST SCREEN

After inserting message is display (New Record inserted successfully).
Click View Of Recordàpage goes to list.aspx & inserted data is display as following figure


We can Edit/Cancel/Delete as well as sorting & paging.

2 comments:

Unknown said...
This comment has been removed by the author.
March 2, 2013 at 2:32 AM
Unknown said...

hi

March 2, 2013 at 2:32 AM

Post a Comment

Subscribe to: Posts (Atom)

Tabs

Tab 1 Tab 2 Tab 3

Tab 1 content goes here

Tab 2 content goes here

Tab 3 content goes here
Multi-Tabbed Widget | DotNetIs