How to get the return value and rows in c# thru stored procedure

Question

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...

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)

    IF @int_duration_of_stay > 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) < 40
BEGIN
    IF @int_number_of_guests <= @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

---

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

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 ?

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

Answer 1

You need to add an out parameter:

command.Parameters.Add("@ret_value", SqlDbType.String);
command.Parameters["@ret_value"].Direction = ParameterDirection.Output;

then after executing the SP

message = command.Parameters["@ret_value"].Value.ToString();

Here is function with out param:

 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, out string message)
{
    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;
    command.Parameters.Add("@ret_value", SqlDbType.String);
    command.Parameters["@ret_value"].Direction = ParameterDirection.Output;
    conn.Open();
    command.ExecuteNonQuery();
    da.SelectCommand = command;
    da.Fill(dsGetBookingCheck);
    message = command.Parameters["@ret_value"].Value.ToString();
    conn.Close();
    return dsGetBookingCheck;
}

NOTE: I have never done with with using ExecuteNonQuery and then using Fill on a data adapter. That might mess this up.

Answer 2

What is ExecuteNonQuery doing in your code when you are not inserting anything?

There are ways to do this. One, use a DataReader. It is a bit more helpful in these kind of scenarios. Or you can add a output parameter to the stored procedure and check that after you execute the proc through C#.

Answer 3

That's not doable in C#. You can only either return a DataTable with a single row (using the Fill method in your example) or you can return a single value (using an SqlCommand with a return parameter or ExecuteScalar).

Instead you should do a SELECT in both cases but with different fields depending on the IF statement. I.e.,

SET @ret_value = 'Max persons allowed is ' + CONVERT(VARCHAR(20), @MaxPer)

is converted to be

SELECT 'Max persons allowed is ' + CONVERT(VARCHAR(20), @MaxPer) AS Return_Value

And then you check for the field name in your return DataTable. E.g.

if (BookingCheck(...).Tables[0].Columns.Contains("Return_Value")) {
   // Handle my special condition here
}