The return value of a SQL Server stored procedure and its use in C #

Question

My application allows the user to add a new player to the database. Before adding a new player, I would like the stored procedure to check if the player with the first and last name exists. I want the stored procedure to return a value bit, 0or 1. My C # method will return this value, and the program can decide whether to continue creating.

Please note that I have disabled the part of the general check outside the code below, i.e. if the fields are empty or the TextBox balance is invalid, etc.

I also know that when processing the return value, I can use the wrong data type. those. intinstead of bool.

When I run this, I get a message stating that my SP requires an input parameter @ReturnedValue.

Greetings

 Player newPlayer = new Player();
 newPlayer.PlayerID = Guid.NewGuid();
 newPlayer.FirstName = TextBoxFirstName.Text;
 newPlayer.LastName = TextBoxLastName.Text;
 newPlayer.Balance = Convert.ToDouble(TextBoxInitialCredit.Text);

var exists = newPlayer.CheckExists();

if (exists == 1)
{
     newPlayer.AddPlayer();
}

and here is the method:

 public int CheckExists()
 {
     SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Badminton"].ConnectionString);

     myConnection.Open();

     SqlCommand SqlCmd = new SqlCommand("CheckPlayerExists", myConnection);
     SqlCmd.CommandType = CommandType.StoredProcedure;

     SqlCmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = FirstName;
     SqlCmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = LastName;
     SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction = ParameterDirection.Output;

     SqlDataAdapter myDataAdapter = new SqlDataAdapter(SqlCmd);
     DataSet myDataSet = new DataSet();
     myDataAdapter.Fill(myDataSet);

     int exists = Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value);

     myConnection.Close();

     return exists;
 }

and now my stored procedure:

CREATE PROCEDURE dbo.CheckPlayerExists
   @firstName nvarchar(50),
   @lastName nvarchar(50),
   @ReturnValue bit output
AS 
BEGIN
IF EXISTS (SELECT * FROM Players WHERE FirstName = @firstName AND LastName = @lastName)
        SET @ReturnValue = 1
ELSE
        SET @ReturnValue = 0

RETURN @ReturnValue
END

Answer 1

Firstly, there is some inconsistency in your posted information. Is the parameter @ReturnValueor @ReturnedValue? This in itself can be a problem.

Secondly, either change the declaration of the parameter stored procedures @ReturnedValueto

@ReturnValue bit = 0 output

or change the C # code that adds the output parameter:

SqlParameter p = new SqlParameter();
p.ParameterName = "@ReturnedValue";
p.SqlDbType = SqlDbType.Int;
p.Value = 0;
p.Direction = ParameterDirection.InputOutput;
SqlCmd.Parameters.Add(p);

Answer 2

Problem: You assign the value of ParameterDirectionEnum to Output.

: ParameterDirection Enum Output ReturnValue

ReturnValue: ParameterDirection.ReturnValue

, , .

:

SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction
   = ParameterDirection.Output;

:

SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction
   = ParameterDirection.ReturnValue;

Answer 3

I think I see your problems. In line:

SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 2).Direction = ParameterDirection.Output;

change the name and type of direction to:

SqlCmd.Parameters.Add("@ReturnedValue", SqlDbType.Int, 2).Direction = ParameterDirection.ReturnValue;