SQL function in ADO.Net not value not being returned/displayed

Question

I'm newish at c# and i have this assignment. Part of it is to get a function to work. I'm not getting any errors but I'm also not getting a response whilst running it. Can you look at my code and tell me how to get the return "money" to show up in the text box and message box?

private void button2_Click(object sender, EventArgs e)
    {
       SqlConnection conn = Database.GetConnection();

       SqlDataReader rdr = null;

       using (SqlConnection a = Database.GetConnection())
       using (SqlCommand cmd = new SqlCommand("SELECT CalcRentalCharge", a))
            cmd.CommandType = CommandType.StoredProcedure;
            string CarRentalNo = "1";
       try
       {
            conn.Open();

            SqlCommand cmd = new SqlCommand(
            "CalcRentalCharge", conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(
            new SqlParameter("@RentalStartDateTime", RentalStartDateTimeBox.Text));
            cmd.Parameters.Add(
            new SqlParameter("@RentalEndDateTime", RentalEndDateTimeBox.Text));
            cmd.Parameters.Add(
                new SqlParameter("@CarTypeID", CarTypeID.Text));
            rdr = cmd.ExecuteReader();


            while (rdr.Read())
            {
                RentalChargeBox.Text = rdr["@Money"].ToString();                      

                MessageBox.Show("@Money");

            }}

        catch
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }   
    }

the stored procedure looks like this :

USE [CarRental_P117365]
GO
/****** Object:  UserDefinedFunction [dbo].[CalcRentalCharge]    Script Date:         8/15/2013 09:06:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/* Create Function CalcFinanceMonthlyPayment to calculate finance monthly repayment */
ALTER FUNCTION [dbo].[CalcRentalCharge] (
@CarTypeID              INT,
@RentalStartDateTime    DATETIME,
@RentalEndDateTime      DATETIME) RETURNS MONEY   

AS
   BEGIN
    DECLARE @NumDays        INT
    DECLARE @DailyRate      MONEY

IF (IsNull(@CarTypeID, 0) <= 0) OR (@RentalStartDateTime IS NULL) OR     (@RentalEndDateTime IS NULL) OR (@RentalEndDateTime <= @RentalStartDateTime)
    RETURN 0

SELECT @DailyRate = DailyRate FROM CarType WHERE CarTypeID = @CarTypeID
IF (IsNull(@DailyRate, 0) <= 0)
    RETURN 0

SELECT @NumDays = CEILING(DATEDIFF(mi, @RentalStartDateTime, @RentalEndDateTime)/ 1440.00)
RETURN CONVERT(MONEY, @NumDays * @DailyRate)
END

Answer 1

It may be worth pointing out a fatal but obvious flaw in your code. Your stored procedure is NOT a stored procedure, you're actually showing us an user defined FUNCTION, which makes perfect sense for it to fail when invoked as if it where a SP. Reason you're not getting an error is that you have a catch block in the C# side, and it does absolute nothing about the error, just swallowing and hiding it, and that's a no-no in C#. I can think of various approaches to solve this, as the problem seems more a design problem rather than a technical problem.

First of, before doing anything else, make sure you put proper error handling in C# side. Your catch block should at least give you some clue on why it failed, log the error somewhere, show a message or whatever, and if you're not able to do anything better, even no try/catch at all is better, as you'll get an error and not a silent failure.

Now for the actual problem. In SQL side, your FUNCTION does a few calculation on input parameters and then gets data from the DB to perform further computations. Generally speaking, data access inside a function is not a great idea, but since you'll be calling from client side it doesn't hurt that much. BTW, are you calling this function as part of some other query/stored procedure/view/whatever, outside of the range of this question? If not, maybe it merits to be a real SP.

Since FUNCTIONs can only be invoked as part of a query, to call this directly from client-side, you should submit a query, that probably just calls this on a dummy SELECT statement. Probably this is the easiest way to use this FUNCTION as it's now. This can be done by changing part of the C# side:

private void button2_Click(object sender, EventArgs e)
{
   try
   {
       using (SqlConnection connection = Database.GetConnection())
       {
           using (SqlCommand cmd = new SqlCommand("SELECT dbo.CalcRentalCharge(@RentalStartDateTime,@RentalEndDateTime,@CarTypeID)", connection))
           {
               cmd.CommandType = CommandType.Text;
               cmd.Parameters.Add("@RentalStartDateTime", SqlDbType.DateTime).Value = RentalStartDateTimeBox.Text;
               cmd.Parameters.Add("@RentalEndDateTime", SqlDbType.DateTime).Value =  RentalEndDateTimeBox.Text;
               cmd.Parameters.Add("@CarTypeID", SqlDbType.Int).Value = CarTypeID.Text;

               connection.Open();
               decimal rentalChange = (decimal)cmd.ExecuteScalar();
               connection.Close();

               MessageBox.Show("The rental change is: " + rentalChange.ToString());
           }
       }
   }
   catch(Exception ex)
   {
       MessageBox.Show(ex.ToString());
   }
}

Answer 2

Several points:

You have a potential parameters type mismatch. Your stored proc gets 2 DATETIME parameters, while you actually pass strings. So you need to fix the two DATETIME parameters to:

cmd.Parameters.Add(new SqlParameter("@RentalStartDateTime", SqlDbType.DateTime)
{
   Value = DateTime.Parse(RentalStartDateTimeBox.Text)
});

You have to indicate your stored procedure has an output parameter (add to the list of parameters):

@retVal MONEY output

You have to actually return the right value, so your proc (it's final part) should be changed to:

SET @retVal = CONVERT(MONEY, @NumDays * @DailyRate)
RETURN @retVal

You need to indicate there is a return value you want to receive, so the code starting from the line "rdr = cmd.ExecuteReader();" should be changed to:

SqlParameter retval = cmd.Parameters.Add("@retVal", SqlDbType.Money);
retval.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
double retunvalue = (double) cmd.Parameters["@retval"].Value;

Also, please note that (a) you do not have to use a reader, (b) invoking a stored procedure is a non-query operation (and not a reader operation).