Using SqlParameter Direction in C#.Net with with Stored Procedure

Introduction

When using the Microsoft Data Access in your Application and accessing your Microsoft SQL Server 2008 database and the CommandType is a stored procedure, there may be a need to pass input parameters to the stored procedure and get an output or a return value coming from the stored procedure. This article will show you the basic way on how to use the SqlParameter Direction (Input, InputOutput, Output and RetrunValue)

Code

C#

private int Execute(ref object outputParam,ref object inoutParam)
    {
            string oUsername = "Jelo";
            int InOutParamValue = 3;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = "MyStoredProcedure";
                cmd.CommandType = CommandType.StoredProcedure;

                // Set Input Parameter
                SqlParameter oParam = new SqlParameter("@Username", oUsername);
                oParam.SqlDbType = SqlDbType.VarChar; 
                cmd.Parameters.Add(oParam);
                
                // Set Output Paramater
                SqlParameter OutputParam = new SqlParameter("@OutputParam", SqlDbType.VarChar);
                OutputParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(OutputParam);

                // Set Input Output Parameter
                SqlParameter InOutParam = new SqlParameter("@InputOutputParam", InOutParamValue);
                InOutParam.SqlDbType = SqlDbType.Int;
                InOutParam.Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add(InOutParam);

                // Set ReturnValue Parameter
                SqlParameter RetParam = new SqlParameter("ReturnValue", DBNull.Value);
                RetParam.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(RetParam);

                cmd.ExecuteNonQuery();
                 
                // Start getting the RetrunValue and Output from Stored Procedure
                int IntReturn = Convert.ToInt32(cmd.Parameters["ReturnValue"].Value);
                if (IntReturn == 1)
                {
                    outputParam = cmd.Parameters["@OutputParam"].Value;
                    inoutParam = cmd.Parameters["@InputOutputParam"].Value;
                }
                return IntReturn;
           }
           conn.Close();
    }

Stored Procedure

ALTER PROCEDURE MyStoredProcedure
    @Username VARCHAR(100),
    @OutputParam VARCHAR(100) OUTPUT,
    @InputOutputParam INT OUTPUT
AS
BEGIN
    IF EXISTS(SELECT * FROM dbo.Table1 WHERE MemberCode = @Username AND id < @InputOutputParam)
    BEGIN                
        SELECT @OutputParam = MAX(ReferenceNo),@InputOutputParam = MAX(id) FROM dbo.Table1
        WHERE MemberCode = @Username    
        RETURN 1
    END
    ELSE
    BEGIN
        RETURN -1
    END        
END

Hope it helps someone!