Introduction
In this article i will explain you how we can create a stored procedure for inserting, deleting and updating records in the table. and how we can execute that stored procedure using C# language.
User Level
User should have some basic knowledge about SQL stored procedure and C#.
Work
The following code assumes a sql table tbl_employee in the ‘employeeDatabase‘.
tbl_employee
Employee Id | EmpName | Age | Department | Salary |
1 | Ankur | 28 | Sales | 2500.00 |
2 | David | 32 | Sales | 3000.00 |
3 | John | 30 | Sales | 2200.00 |
5 | Mohan | 35 | Production | 4300.00 |
Stored Procedure
USE [EmployeeDatabase] GO /****** Object: StoredProcedure [dbo].[employee_data] Script Date: 06/12/2012 12:46:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[employee_data] -- Add the parameters for the stored procedure here @EmployeeId INT, @EmpName NVARCHAR(50) =NULL, @Age INT= NULL, @Department NVARCHAR(50)= NULL, @Salary MONEY= NULL, @FLAG NVARCHAR(1) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @FLAG='I' BEGIN INSERT INTO tbl_Employee ( EmployeeId,EmpName,Age ,Department,Salary )VALUES ( @EmployeeId, @EmpName, @Age , @Department, @Salary ); END ELSE IF @FLAG='U' BEGIN UPDATE tbl_Employee SET EmployeeId=@EmployeeId,EmpName=@EmpName,Age=@Age ,Department=@Department,Salary=@Salary WHERE EmployeeId=@EmployeeId; END ELSE IF @FLAG='D' BEGIN DELETE FROM tbl_Employee WHERE EmployeeId=@EmployeeId; END END
I add an additional parameter ‘Flag’ that indicate the operation on the table. We can pass flag parameter as ‘I’ for inserting data, ‘D’ for delete the records on the basis of employeeid and U for updating records on the basis of employeeid.
C# code
private void RunStoredProc() { int Employee_id = 12; string Emp_name = "New EmployeeName"; int Age = 56; string Department = "Sales"; double Salary = 3200; string FLAG = "I"; SqlParameter[] sqlparam = new SqlParameter[6]; sqlparam[0] = new SqlParameter("@EmployeeId ", Employee_id); sqlparam[1] = new SqlParameter("@EmpName", Emp_name); sqlparam[2] = new SqlParameter("@Age", Age); sqlparam[3] = new SqlParameter("@Department", Department); sqlparam[4] = new SqlParameter("@Salary", Salary); sqlparam[5] = new SqlParameter("@FLAG", FLAG); SqlConnection oConn = new SqlConnection(); oConn.ConnectionString = "Your Connection string here"; oConn.Open(); SqlCommand cmd = new SqlCommand(); try { //cmd = new SqlCommand(); cmd.Connection = oConn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.employee_data"; cmd.CommandTimeout = 6000; if ((cmd == null)) { throw new ArgumentNullException("command"); } if ((!(sqlparam == null))) { foreach (SqlParameter pp1 in sqlparam) { if ((!(pp1 == null))) { if ((pp1.Direction == ParameterDirection.InputOutput || pp1.Direction == ParameterDirection.Input) && pp1.Value == null) { pp1.Value = DBNull.Value; } cmd.Parameters.Add(pp1); } } } cmd.ExecuteNonQuery(); } catch { } }