How to get the return value and rows in c# thru stored procedure
<p>In the below Stored procedure, i am returning a row if all the conditions satisfies, orelse i am returning a message like which condition is nor satisfied.
The Stored proc is working perfectly...</p>

<p>ALTER PROCEDURE dbo.BookingCheck
    (
    @int_duration_of_stay int ,
    @int_number_of_guests int,
    @date_of_application date,
    @date_of_checkin  date,
    @date_of_checkout  date,
    @str_room_type   varchar(50),
    @ret_value    varchar(100) = '' output
    )
AS
    DECLARE @MaxPer int
    DECLARE @BasicCharge int
    DECLARE @SurCharge int
    DECLARE @TotalAmount int
    DECLARE @NoOfDays int
    DECLARE @Free VARCHAR(10)</p>

<pre><code>IF @int_duration_of_stay &gt; 6
BEGIN
 SET @NoOfDays = @int_duration_of_stay
 SET @Free = 'Yes'
END 
ELSE
BEGIN
 SET @NoOfDays = @int_duration_of_stay - 1
 SET @Free = 'No'
END


SELECT @MaxPer = int_max_pax, @BasicCharge = flt_basic_charge, @SurCharge = flt_surcharge_per_pax 
FROM RoomTypes WHERE UPPER(str_room_type) = UPPER(@str_room_type)

IF DATEDIFF(DAY, GETDATE(), @date_of_checkin) &lt; 40
BEGIN
 IF @int_number_of_guests &lt;= @MaxPer
 BEGIN
  SET @TotalAmount = (@NoOfDays * @int_number_of_guests * @SurCharge) + @BasicCharge
  SET @ret_value = 'Success'

  SELECT @str_room_type as 'Room Type', @MaxPer as 'Max Persons Allowed', @int_number_of_guests as 'No. of persons requested',  
  @int_duration_of_stay as 'No. of days stay', @BasicCharge as 'Basic Charge',  @SurCharge as 'Sur Charge', @Free as 'Complimentary',
   @TotalAmount as 'Total Amount'
 END
 ELSE
 BEGIN
  SET @ret_value = 'Max persons allowed is ' + CONVERT(VARCHAR(20), @MaxPer)
 END
END
ELSE
BEGIN
 SET @ret_value = 'The check in date should be less than 40 days from current date.'
END
RETURN
</code></pre>

<p><hr /></p>

<p>The problem is dont know how to get the return message or return row from the SP using c#.</p>

<p>The below code returns me the rows if the condition is satisfied in SP. if not, i am not getting the return message. How to get that ?</p>

<pre><code> public DataSet BookingCheck(int duration_of_stay, int number_of_guests,
    string date_of_application, string date_of_checkin, string date_of_checkout,
    string room_type)
{
    DataSet dsGetBookingCheck = new DataSet();
    SqlConnection conn = new SqlConnection(Con);
    SqlCommand command = new SqlCommand("BookingCheck", conn);
    command.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter();
    SqlParameter param = new SqlParameter();
    param = command.Parameters.Add("@int_duration_of_stay", SqlDbType.Int);
    param.Value = duration_of_stay;
    param = command.Parameters.Add("@int_number_of_guests", SqlDbType.Int);
    param.Value = number_of_guests;
    param = command.Parameters.Add("@date_of_application", SqlDbType.Date);
    param.Value = date_of_application;
    param = command.Parameters.Add("@date_of_checkin", SqlDbType.Date);
    param.Value = date_of_checkin;
    param = command.Parameters.Add("@date_of_checkout", SqlDbType.Date);
    param.Value = date_of_checkout;
    param = command.Parameters.Add("@str_room_type", SqlDbType.VarChar, 50);
    param.Value = room_type;
    conn.Open();
    command.ExecuteNonQuery();
    da.SelectCommand = command;
    da.Fill(dsGetBookingCheck);
    conn.Close();
    return dsGetBookingCheck;
}
</code></pre>
