Vfrdtyky

I have a stored procedure which looks like this:

CREATE OR REPLACE PROCEDURE

Get_Username

(

    p_Username IN user.USERNAME%TYPE,

    p_ReturningUsername OUT user.Username%TYPE

)

IS

BEGIN

    SELECT username INTO p_ReturningUsername FROM user WHERE p_Username = username;

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        p_ReturningUsername := null;

END;

I've executed it from the database and it worked perfectly.
When I execute it from c# app I don't get any value back.

public bool getUsername(string username)

    {

        OracleConnection connection = getConnection();

        OracleCommand oracleCommand = new OracleCommand("Get_Username", connection);

        oracleCommand.CommandType = CommandType.StoredProcedure;

        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

        oracleCommand.Parameters.Add("p_Username", OracleDbType.Varchar2).Value = username;



        try

        {

            oracleCommand.ExecuteNonQuery();

        }

        catch (OracleException ex)

        {

            MessageBox.Show("Exception Message: " + ex.Message);

            MessageBox.Show("Exception Source: " + ex.Source);

        }





        string tmp = oracleCommand.Parameters["p_ReturningUsername"].Value.ToString();

        connection.Close();

        return tmp == username;

    }

For a WPF registration window I need to check if the username is already taken.
When I execute this code I receive an empty string when it has to be the username in the user table.

Please help me, thanks in advance.

As @Codo says, add the line command.BindByName = true;, but also replace the line

        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

with

        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2, 100, null, ParameterDirection.Output);

Replace the number 100 with the number of characters in your username column.

We use this overload of the Add method so that we can specify the size of the output parameter. This allows the Oracle driver to make enough space to receive the value from the database. The null argument is for the initial value of the parameter, which we don't care about because this is an output parameter, but we have to specify it as there is no overload of the Add method that takes only the name, type, size and direction.

Try this code (I've just swapped two lines):

public bool getUsername(string username)

    {

        OracleConnection connection = getConnection();

        OracleCommand oracleCommand = new OracleCommand("Get_Username", connection);

        oracleCommand.CommandType = CommandType.StoredProcedure;

        oracleCommand.Parameters.Add("p_Username", OracleDbType.Varchar2).Value = username;

        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2).Direction = ParameterDirection.Output;



        try

        {

            oracleCommand.ExecuteNonQuery();

        }

        catch (OracleException ex)

        {

            MessageBox.Show("Exception Message: " + ex.Message);

            MessageBox.Show("Exception Source: " + ex.Source);

        }





        string tmp = oracleCommand.Parameters["p_ReturningUsername"].Value.ToString();

        connection.Close();

        return tmp == username;

    }

Or add:

oracleCommand.BindByName = true;

By default, the OracleCommand class binds parameters by order, not by name.