C# SQL stored procedure (which inserts) - pass parameters and retrieve parameter

Question

I have a stored procedure on my server that inserts some parameters and returns the ID that was inserted. I am writing a form to do this easily but I cannot seem to get the parameter which is passed back.

To save you doing a whole bunch of possibly pointless reading, it's probably better to just pay attention to my C# code and let me know what I need to do in order to pass parameters and get one in return.

C# Default.aspx

connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionInfo"]);
sql = "aStoredProc";

command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;

command.Parameter.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = sFirstname;
command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname;

connection.Open(); 
int ID = command.ExecuteNonQuery();
connection.Close();

SQL aStoredProc

IF EXISTS(SELECT * FROM aTable WHERE ID = @ID)
  -- User exists, update details
  BEGIN
    BEGIN TRAN
      UPDATE aTable
        SET 
          FirstName = @FirstName,
          SurName = @SurName,
          LastUpdate = GetDate()
        WHERE ID = @ID

        IF (@@Error != 0)
          ROLLBACK TRAN
        ELSE
          COMMIT TRAN
        END
ELSE
  -- New user
  BEGIN
    BEGIN TRAN
      INSERT aTable (
        FirstName,
        SurName,
        GetDate()
      )
        VALUES (
          @FirstName, 
          @SurName,
          @LastUpdate
      ) 

      SELECT @ID = @@IDENTITY

      IF (@@Error != 0)
        ROLLBACK TRAN
      ELSE
        COMMIT TRAN
      END

The parameter @ID is listed in the stored proc as:

@ID (int, Input/Output, No default)

and proc has 'Return integer'. This used to work fine with a VBA solution prior to a SQL Server 2005 upgrade.

Thanks in advance.

Answer 1

connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionInfo"]); 
sql = "aStoredProc"; 
command = new SqlCommand(sql, connection); 
command.CommandType = CommandType.StoredProcedure; 
command.Parameter.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = sFirstname; 
command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname; 
command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname; 
SqlParameter ParamId = cmd.Parameters.Add( "@Id", SqlDbType.Int);
ParamId.Direction = ParameterDirection.InputOutput;
command.Parameter.Add(ParamId);
connection.Open();  
command.ExecuteNonQuery(); 
int ID = ParamId.Value;
connection.Close();

1. you have to add output paramter in Parameter collection.
2. Read Value like above.