0

I want to assign the parameter @ResponseMessage to the C# variable responseMessage. I want to use Postman to set the values of the other parameters.

I managed to get the JSON to return the response message to return NULL but never return the what I need to be.

I have the following SQL Server stored procedure:

ALTER PROCEDURE [dbo].[Register] 
    (@FirstName NVARCHAR(30), 
     @LastName NVARCHAR(30), 
     @Email NVARCHAR(30), 
     @Password NVARCHAR(30),
     @ResponseMessage NVARCHAR(30) OUTPUT)
AS
BEGIN TRANSACTION
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Users WHERE Email = @Email)
    BEGIN
        BEGIN TRY
            DECLARE @i INT;

            INSERT INTO Users (FirstName, LastName, Email, Password)
            VALUES (@FirstName, @LastName, @Email, @Password)

            SELECT @i = SCOPE_IDENTITY();
            SET @ResponseMessage = CONCAT(200, @i)

            COMMIT;
        END TRY
        BEGIN CATCH
            SET @ResponseMessage = 404
            ROLLBACK;
        END CATCH
    END
    ELSE
    BEGIN
        SET @ResponseMessage = 208
        COMMIT;
    END
END

This is my C# code that I want to save the parameter to

public void Register(SqlParameter[] parameters, out string responseMessage)
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 30);
    cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 30);
    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 30);
    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 30);
    cmd.Parameters.Add("@ResponseMessage", SqlDbType.NVarChar, 30).Direction = ParameterDirection.Output;

    responseMessage = (string)cmd.Parameters["@ResponseMessage"].Value;

    Database.ExecuteSqlRaw("EXEC Register @FirstName, @LastName, @Email, 
            @Password, @ResponseMessage", parameters);
}

[HttpPost]
public IActionResult registerUser (User newUser)
{
    Register newReg = new Register();
    SqlParameter[] parameters = 
        {
            new SqlParameter("@FirstName", newUser.FirstName.ToString()),
            new SqlParameter("@LastName", newUser.LastName.ToString()),
            new SqlParameter("@Email", newUser.Email.ToString()),
            new SqlParameter("@Password", newUser.Password.ToString())
        };

    _context.Register(parameters, out string responseMessage);

    Dictionary<string, string> keys = new Dictionary<string, string>();
    keys.Add("User ID ", responseMessage);

    JsonResult result = new JsonResult(keys);

    return Ok(result);
}

This shows my Postman screen and the values that I want to be entered. It does add to the database but the return message does not display what i want it to.

https://i.stack.imgur.com/rWqNI.png

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Josh Bent
  • 23
  • 1
  • 4
  • 1
    Does this answer your question? [Using stored procedure output parameters in C#](https://stackoverflow.com/q/10905782/2029983) – Thom A Jan 24 '21 at 16:17
  • Surely you want `CONCAT(200, @i)` rather than `+` – Charlieface Jan 24 '21 at 16:24
  • 1
    Also, the code you have strongly implies you are storing passwords as plaintext. **Never** do that. Has the password with a salt, and store said salt and has in the database. – Thom A Jan 24 '21 at 16:28
  • @Larnu yes i am storing them as plain text, only because the need to hash the password is out of the spec for my assignment. – Josh Bent Jan 24 '21 at 16:51
  • @Charlieface good catch, updated. – Josh Bent Jan 24 '21 at 16:52
  • 1
    That doesn't make it correct, @JoshBent . My statement is unchanged. ***NEVER*** store plain text passwords. – Thom A Jan 24 '21 at 16:52
  • @Larnu Yeh, I understand that you are right, it's just not priority for me in this moment. As I said, it is not expected from me for this assignment brief. – Josh Bent Jan 24 '21 at 17:06

0 Answers0