Steps to create and run SQL stored procedure in C#.

 

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 IdEmpNameAgeDepartmentSalary
1Ankur28Sales2500.00
2David32Sales3000.00
3John30Sales2200.00
5Mohan35Production4300.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
            { }
        }