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