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.

Tim don't forget to wrap the commend in a using statement so you don't leak resources or flood the connection pool. — Kane, Jan 25 '14 at 06:25

TomT · Answer 1 · 2014-01-25T12:13:46.247

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;

score 1 · Accepted Answer · answered Jan 25 '14 at 06:20

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

This was it! I knew it had to be something with the stored procedure and how I was passing the information back. I changed the proc per your suggestion and removed the last 2 parameter.add statements in the C# code and the code worked. Thanks. — Tim, Jan 27 '14 at 18:21

mnshahab · Answer 3 · 2014-01-25T00:52:13.503

0

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();
    }
  }
}

edited Jan 25 '14 at 00:52

answered Jan 25 '14 at 00:16

mnshahab

770
7
16

This isn't correct because @rval needs to be an output parameter. – Dmitriy Khaykin Jan 25 '14 at 00:47

Getting single response from SQL Server stored procedure with C#

3 Answers3