Tuesday, March 13, 2018

3 tier architecture in asp.net with stored procedure example

1)Table
         Create Table
        create table MuraliInsert
                   (
                        id int identity(1,1) primary key,
                        from_date varchar(30),
                        to_date varchar(30),
                        employeename varchar(30),
                       branch varchar(30),
                      company varchar(30),
                     salary varchar(30)
                   )
2)Create Procedure

 1) CREATE PROCEDURE [dbo].[SP_MuraliInsert]

@from_date varchar(20),
@last_date varchar(20),
@employeename varchar(30),
 @branch varchar(30),
 @company varchar(30),
@salary varchar(30)
)
AS
BEGIN 
insert into MuraliInsert (from_date,last_date,employeename,branch,company,salary) values( @from_date,@last_date,@employeename,@branch,@company,@salary)
END

2)CREATE procedure [dbo].[SP_Muraliname]
as
begin
select id,employeename from MuraliInsert
end
3)CREATE  PROCEDURE [dbo].[SP_Muralibranch]
(
@employeename varchar(30)
)
AS
BEGIN
SELECT ID,branch FROM MuraliInsert WHERE employeename=@employeename
END
4)CREATE PROCEDURE SP_Company
(
@branch varchar(30)
)
AS
BEGIN
SELECT id,company FROM MuraliInsert where branch=@branch
END




3)Code

DATA ACCESS LAYER


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using DAOCredit;
using BLCredit;

/// <summary>
/// Summary description for MuraliDataAccess
/// </summary>
public class MuraliDataAccess
{
 
public int MuraliDataInsert(MuraliInsert_Objects objobject )
{
     
            ConnectionStringSettings bbl= ConfigurationManager.ConnectionStrings["BBL"];
            SqlConnection con = new SqlConnection(bbl.ConnectionString);
            SqlCommand cmd = new SqlCommand("SP_MuraliInsert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@from_date", objobject.textbox);
            cmd.Parameters.AddWithValue("@last_date", objobject.to_date);
            cmd.Parameters.AddWithValue("@employeename", objobject.employeename);
            cmd.Parameters.AddWithValue("@branch", objobject.branch);
            cmd.Parameters.AddWithValue("@company", objobject.company);
            cmd.Parameters.AddWithValue("@salary", objobject.salary);
            con.Open();
            int Result = cmd.ExecuteNonQuery();
            con.Close();
            return Result;
    }

    public DataSet empname(MuraliInsert_Objects objobject)
    {
        ConnectionStringSettings bbl= ConfigurationManager.ConnectionStrings["BBL"];
        SqlConnection con = new SqlConnection(bbl.ConnectionString);
        SqlCommand cmd = new SqlCommand("SP_Muraliname", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
    public DataSet empbranch(MuraliInsert_Objects objobject)
    {
        SqlConnection con;
        SqlDataAdapter adapter;
        SqlCommand cmd = new SqlCommand();
        SqlParameter param;
        DataSet ds = new DataSet();
        //int i = 0;
         ConnectionStringSettings bbl= ConfigurationManager.ConnectionStrings["BBL"];
         con = new SqlConnection(bbl.ConnectionString);


         con.Open();
         cmd.Connection = con;
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "SP_Muralibranch";

         param = new SqlParameter("@employeename", objobject.employeename);
         param.Direction = ParameterDirection.Input;
         param.DbType = DbType.String;
         cmd.Parameters.Add(param);

         adapter = new SqlDataAdapter(cmd);
         adapter.Fill(ds);

         con.Close();
        return ds;
    }

    public DataSet empcompany(MuraliInsert_Objects objobject)
    {
        SqlConnection con;
        SqlDataAdapter adapter;
        SqlCommand cmd = new SqlCommand();
        SqlParameter param;
        DataSet ds = new DataSet();
        ConnectionStringSettings bbl = ConfigurationManager.ConnectionStrings["BBL"];
        con = new SqlConnection(bbl.ConnectionString);


        con.Open();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_Company";

        param = new SqlParameter("@branch", objobject.branch);
        param.Direction = ParameterDirection.Input;
        param.DbType = DbType.String;
        cmd.Parameters.Add(param);

        adapter = new SqlDataAdapter(cmd);
        adapter.Fill(ds);

        con.Close();
        return ds;
    }
 
}

BUSINESS OBJECTS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
public class MuraliInsert_Objects
{
    public string textbox { get; set; }
    public string to_date { get; set; }
    public string employeename { get; set; }
    public string branch { get; set; }
    public string company { get; set; }
    public string salary { get; set; }
    public int id { get; set; }
}



BUSINESS LOGIC

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using DAOCredit;
using Ecargo.DAL;

/// <summary>
/// Summary description for Murali_BL
/// </summary>
public class Murali_BL
{

    public int MuraliDataInsert(MuraliInsert_Objects BuObject)
    {
        MuraliDataAccess objda = new MuraliDataAccess();
        return objda.MuraliDataInsert(BuObject);
}
    public DataSet empname(MuraliInsert_Objects BuObject)
    {
        MuraliDataAccess objda = new MuraliDataAccess();
        return objda.empname(BuObject);
    }
    public DataSet empbranch(MuraliInsert_Objects BuObject)
    {
        MuraliDataAccess objda = new MuraliDataAccess();
        return objda.empbranch(BuObject);
    }
    public DataSet empcompany(MuraliInsert_Objects BuObject)
    {
        MuraliDataAccess objda = new MuraliDataAccess();
        return objda.empcompany(BuObject);
    }
}

CODE BEHIND

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using DAOCredit;
using BLCredit;
using System.Data.SqlClient;

public partial class Credit_Murali : System.Web.UI.Page
{
    MuraliInsert_Objects objmuraliobject = new MuraliInsert_Objects();
    Murali_BL objmuralibl = new Murali_BL();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindcountry();
        }
    }


    public void bindcountry()
    {
        DataSet ds = objmuralibl.empname(objmuraliobject);
        DropDownList1.DataTextField = "employeename";
        DropDownList1.DataValueField = "id";
        DropDownList1.DataSource = ds;
        DropDownList1.DataBind();
        DropDownList1.Items.Insert(0, new ListItem("---Select State---"));
    }


    protected void Button1_Click(object sender, EventArgs e)
    {
           objmuraliobject.textbox= TextBox1.Text;
           objmuraliobject.to_date = TextBox2.Text;
           objmuraliobject.employeename = DropDownList1.SelectedItem.Text;
           objmuraliobject.branch = DropDownList2.SelectedItem.Text;
           objmuraliobject.company = DropDownList3.SelectedItem.Text;
           objmuraliobject.salary = TextBox3.Text;

        int i = objmuralibl.MuraliDataInsert(objmuraliobject);
        if (i == 1)
        {
            Label1.Text = "Success Fully Inserted";
        }
        else
        {
            Label1.Text = "fail to insert";
        }
    }

    protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
    {
        objmuraliobject.employeename = DropDownList1.SelectedItem.Text;
        //int emplyeename = Convert.ToString(DropDownList1.SelectedItem.Text);
        DataSet ds = objmuralibl.empbranch(objmuraliobject);
        DropDownList2.DataTextField = "branch";
        DropDownList2.DataValueField = "ID";
        DropDownList2.DataSource = ds;
        DropDownList2.DataBind();
        DropDownList1.Items.Insert(0, new ListItem("---Select State---"));
    }

    protected void TextBox1_TextChanged(object sender, EventArgs e)
    {

    }
    protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
        objmuraliobject.branch = DropDownList2.SelectedItem.Text;
        //int emplyeename = Convert.ToString(DropDownList1.SelectedItem.Text);
        DataSet ds = objmuralibl.empcompany(objmuraliobject);
        DropDownList3.DataTextField = "company";
        DropDownList3.DataValueField = "ID";
        DropDownList3.DataSource = ds;
        DropDownList3.DataBind();
        DropDownList3.Items.Insert(0, new ListItem("---Select State---"));

    }
    protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}



No comments: