store procedure

Question

my question is

in store procedure

if i have multiple action in one single store procdure thn how can i implement in c# application..

i mean in single procedure i hav 3 action insert,update,delete how can i use particular action in c# application.like insert ,update etc

Satyapriya Nayak · Answer

http://www.c-sharpcorner.com/UploadFile/satyapriyanayak/insert-edit-update-in-gridview-using-single-stored-procedure/

Jignesh Trivedi · Answer

no dear... "Insert", "update" and "Delete" word are use to identify operation. above SP code is not support select but you can do with some modification in SP. if you want to delete record than you required empId which is PK of employeeMaster table. you may pass other parameter to null and mode parameter to "Delete". hope you understand what i trying to say.

vikas kanani · Answer

u mean u can save "insert" word too in database...if it is do like tht then it wronmg.. if i hav to delete record or search record like this method thn how can i do...

Jignesh Trivedi · Answer

hi, try following code here with the help of mode we can identify whether the operation is insert, update or delete. SQL side... -- just replace your table name column. Create PROCEDURE [dbo].[Usp_EmpEntry] ( @EmpID INT, @EmpName VARCHAR(50), @Status CHAR(1), @Mode VARCHAR(10), @Result INT OUT ) AS IF @Mode='INSERT' BEGIN INSERT INTO EmployeeMaster(EmpID,EmpName,Status) VALUES(@EmpID,@EmpName,@Status) SET @Result=1 END ELSE IF @Mode='UPDATE' BEGIN UPDATE EmployeeMaster SET EmpName=@EmpName, Status=@Status WHERE EmpID=@EmpID SET @Result=1 END ELSE IF @Mode='DELETE' BEGIN DELETE EmployeeMaster WHERE EmpID=@EmpID SET @Result=1 END RETURN @Result IF @@ERROR 0 SET @Result=@@ERROR C# code string conString = "your connection string"; using (SqlConnection conn = new SqlConnection(conString)) { using (SqlCommand cmd = new SqlCommand("Usp_EmpEntry")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@EmpID", "6")); cmd.Parameters.Add(new SqlParameter("@EmpName", "Fahran")); cmd.Parameters.Add(new SqlParameter("@Status", "N")); cmd.Parameters.Add(new SqlParameter("@Mode", "INSERT")); SqlParameter param = new SqlParameter("@Result", 0); param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); conn.Open(); cmd.Connection = conn; cmd.ExecuteNonQuery(); Result = Int32.Parse(cmd.Parameters["@Result"].Value.ToString()); conn.Close(); } hope this will help you.

store procedure

Answers (4)