How to assign SQL @Action flag in ASP.NET MVC DAL class using single stored procedure?

97 Views Asked by At

Here is my SQL query:

CREATE PROCEDURE User_CRUD
    @Action varchar(20),
    @eno int,
    @ename varchar(50),
    @salary money
AS
    IF @Action = 'Insert'
    BEGIN
        INSERT INTO employee (eno, ename, salary) 
        VALUES (@eno, @ename, @salary)
    END
    ELSE IF @Action = 'Update'
    BEGIN
        UPDATE employee 
        SET ename = @ename, salary = @salary 
        WHERE eno = @eno
    END
    ELSE IF @Action = 'Delete'
    BEGIN
        DELETE FROM employee 
        WHERE eno =@eno
    END
    ELSE IF @Action = 'Getemp'
    BEGIN
        SELECT * 
        FROM employee
    END
    ELSE IF @Action = 'Search'
    BEGIN
        SELECT ename, salary 
        FROM employee 
        WHERE eno = @eno
    END

And here is my DAL class file:

public int AddEmployee(Models.Employee e1) 
{
        con.Open();
        
        SqlCommand cmd = new SqlCommand("User_CRUD", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Action", "Insert");
        cmd.Parameters.AddWithValue("@eno", e1.Eno);
        cmd.Parameters.AddWithValue("@ename", e1.Ename);
        cmd.Parameters.AddWithValue("@salary", e1.Salary);
       
        e1.RollNo = Convert.ToInt32(cmd.ExecuteScalar());
        con.Close();
        return e1.RollNo;
}

public int DeleteEmployee(Models.Employee e1)
{
        con.Open();
        
        SqlCommand cmd = new SqlCommand("User_CRUD", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Action", "Delete");
        cmd.Parameters.AddWithValue("@eno",e1.Eno);
        
        int i = cmd.ExecuteNonQuery();
        con.Close();
        return i;
}

public int UpdateEmployee(Models.Employee e1)
{
        con.Open();
        
        SqlCommand cmd = new SqlCommand("User_CRUD", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Action", "Update");
        cmd.Parameters.AddWithValue("@eno", e1.Eno);
        cmd.Parameters.AddWithValue("@ename", e1.Ename);
        cmd.Parameters.AddWithValue("@salary", e1.Salary);
        
        int i = cmd.ExecuteNonQuery();
        con.Close();
        return i;
}

public List<Models.Employee> GetEmployee()
{
        List<Models.Employee> li = new List<Models.Employee>();
        con.Open();
        SqlCommand cmd = new SqlCommand("User_CRUD", con);
        
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            while(dr.Read())
            {
                Models.Employee e1= new Models.Employee();
                e1.RollNo = int.Parse(dr[0].ToString());
                e1.Eno = int.Parse(dr[1].ToString());
                e1.Ename = dr[2].ToString();
                e1.Salary = double.Parse(dr[3].ToString());
                cmd.Parameters.AddWithValue("@Action", "Getemp");
                li.Add(e1);
            }
        }
        return li;
}

public Models.Employee SearchEmp(Models.Employee e1)
{
        con.Open();
        SqlCommand cmd = new SqlCommand("User_CRUD", con);
        cmd.CommandType= CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Action", "Search");
        cmd.Parameters.AddWithValue("@eno", e1.Eno);
        
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            if (dr.Read())
            {
                e1.Ename= dr[0].ToString();
                e1.Salary= double.Parse(dr[1].ToString());
            }
        }

        con.Close();  
        return e1;
}

When I try to perform some crud operation using single procedure it is showing error in GetEmployee() method like @Action method which was not supplied. SearchEmp() method is also showing the same error.

Can anyone help me with this?

Thanks in advance

0

There are 0 best solutions below