Getting single response from SQL Server stored procedure with C#

Question

I've been wrestling with this for several days and have extensively dug through StackOverflow and various other sites. I'm literally drawing a blank. I'm trying to get a single result back from my stored procedure.

Here's my stored procedure:

ALTER PROC [dbo].[myHelper_Simulate]
    @CCOID nvarchar(100), @RVal nvarchar OUTPUT
AS
    DECLARE @UserID int 
    DECLARE @GUID uniqueidentifier

    SELECT @UserID = UserID 
    FROM [User] 
    WHERE CCOID = @CCOID AND deleted = 0

    SELECT @GUID = newid()

    IF @UserID > 0
        BEGIN
            INSERT [Audit] ([GUID], Created, UserID, ActionType, Action, Level)
            VALUES (@GUID, getdate(), @UserID, 'Authentication', 'Test Authentication', 'Success')

            SELECT @RVal = 'http://www.ttfakedomain.com/Logon.aspx?id=' + CAST(@GUID AS nvarchar(50))
            RETURN  
        END
    ELSE
        SELECT @RVal = 'Couldn''t find a user record for the CCOID ' + @CCOID
        RETURN 
GO

Originally the procedure was written to print out the result. I've added the @RVal and the RETURN in an attempt to get the value to pass back to my C# code.

Here's my C# routine (svrConn has already been connected to the database):

 private void btnSimulate_MouseClick(object sender, MouseEventArgs e)
 {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = svrConn;
        object result = new object();

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[" + tDatabase + "].[dbo].myHelper_Simulate";
        cmd.Parameters.Add(new SqlParameter("@CCOID", txtDUserCCOID.Text.Trim()));
        cmd.Parameters.Add(new SqlParameter("@RVal", ""));
        cmd.Parameters.Add(new SqlParameter("RETURN_VALUE", SqlDbType.NVarChar)).Direction = ParameterDirection.ReturnValue;

        try
        {
            result = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

        if (result != null)
        {
            tString = result.ToString();
            MessageBox.Show(tString);
        }
    }

The problem is that result is coming back null. I'm not sure if it's due to the stored procedure or how I'm setting my parameters and calling ExecuteScalar.

Answer 1

Don't need the return parameters or output parameters. ExecuteScalar returns the first column of the first row of your result set. So just select the text you want to return, like so...

IF @UserID > 0
        BEGIN
            INSERT [Audit] ([GUID], Created, UserID, ActionType, Action, Level)
            VALUES (@GUID, getdate(), @UserID, 'Authentication', 'Test Authentication', 'Success')

            SELECT  'http://www.ttfakedomain.com/Logon.aspx?id=' + CAST(@GUID AS nvarchar(50))
        END
    ELSE
        SELECT 'Couldn''t find a user record for the CCOID ' + @CCOID


RETURN 

Answer 2

try

private void btnSimulate_MouseClick(object sender, EventArgs e) {
  using (SqlConnection con = svrConn) {
    using (SqlCommand cmd = new SqlCommand("myHelper_Simulate", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@CCOID", SqlDbType.VarChar).Value = txtDUserCCOID.Text.Trim();
      var output = new SqlParameter("@RVal", SqlDbType.VarChar);
      output.Direction = ParameterDirection.Output;
      cmd.Parameters.Add(output);

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

Answer 3

SP return values are integers and are meant for error code. The correct way is to use OUTPUT parameter. You are assigning it correctly in the SP, you don't need the return statements.

In your C# code check the value after execution. Use ExecuteNonQuery as there is no result set from the SP.

var rval = new SqlParameter("@RVal", SqlDbType.NVarChar);
rval.Direction = ParameterDirection.Output;
cmd.Parameters.Add(rval);
cmd.ExecuteNonQuery();
result = rval.Value;