Get return code from a stored procedure using c#

Question

I have a return code from a stored procedure that I need to verify. I can't change the proc because it is part of a vendor package. Changing it to an output parameter is not possible. I don't care so much about the parameters as capturing the return code in c#. Here is an example of what I'm trying to do: Procedure:

create proc tester
   @parm nvarchar(3)
as

if @parm = 'Yes'
       return 1
else
       return -1
go

C# sample code:

SqlConnection sqlMon = new SqlConnection("Whatever");

sqlMon.Open();
SqlCommand sqlcomm = new SqlCommand("tester", sqlMon);
SqlDataReader sqlR; 
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Parameters.Add(new SqlParameter("@parm", "Yes"));

sqlR = sqlcomm.ExecuteReader();

sqlMon.Close();

What I can't figure out is how do I now get the return code. Any help you can provide is appreciated. Thanks.

score 2 · Answer 1 · answered Jun 15 '15 at 19:36

2

You can add a new parameter as below.

    SqlParameter ret = sqlcomm.Parameters.Add("@R", SqlDbType.Int);
    ret.Direction = ParameterDirection.ReturnValue;
    string returnvalue = (string)sqlcomm.Parameters["@R"].Value;

answered Jun 15 '15 at 19:36

coder231

453
2
14

This worked. I had to add the return code as the first parameter. Than you! – scot davenport Jun 18 '15 at 14:29

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

ExecuteReader() returns a SqlDataReader:

SqlDataReader dr = sqlComm.ExecuteReader();

You can then work with the SqlDataReader to bind the data to a control, or whatever else you'd like to do.

https://msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx

Here is an example of how to bind a DataReader to a GridView:

Binding sqldatareader to gridview c#

edited May 23 '17 at 12:05

Community

1
1

answered Jun 15 '15 at 19:32

maniak1982

707
2
7
23

Get return code from a stored procedure using c#

2 Answers2