C# What type should I cast here?

Question

I have a portion of code which calculates a new balance by reading from a database all prior charges and payments and then adding the new amount that's going to be charged to the balance. It was working fine with values that I'd hand planted into the database, but then I started getting errors when I wiped all the test values and started from scratch.

This is the code:

        //sum all prior charges
        try
        {
            connection.Open();
            command.Connection = connection;

            command.CommandText = "SELECT SUM(Amount) FROM Charges WHERE TransactionDate<='" + DateTime.Now + "';";
            chargesSoFar = (double)command.ExecuteScalar();
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }

        // sum all prior payments
        try
        {
            connection.Open();
            command.Connection = connection;

            command.CommandText = "SELECT SUM(Amount) FROM Payments WHERE TransactionDate<='" + DateTime.Now + "';";
            paymentsSoFar = (double)command.ExecuteScalar();
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }

        // calculate balance
        tempCharge.Balance = (decimal)(chargesSoFar + int.Parse(textBoxAmount.Text) - paymentsSoFar);

        DataWriter.WriteCharge(tempCharge);

The errors I'm getting are type conversion errors where I cast the ExecuteScalar result. The error is happening because there are no prior charges or payments, so null is being returned, which fails when casting to double.

Is there another type I can cast to, which I can then check if it's null? If not, how do I get around this?

Any help is appreciated. Please ask any questions if I haven't been clear in what I'm trying to do here.

Why don't you first check for null, then cast to the appropriate type? Or use nullable types? — L-Four, Feb 15 '18 at 09:19
L-Four. There is no way of checking if it's null before executing the query. — LennyLen, Feb 15 '18 at 09:21
"when casting to double" - You are not using `double` for monetary amounts, are you? I see you use the double value in calculation and then cast the result to `decimal`. So you seem to be partly aware of the drawbacks. Why not make that consistent? — Fildor, Feb 15 '18 at 09:24
it returns an object which can be null. And I did not mean before executing, i meant after executing of course, then casting. using a nullable is the same. — L-Four, Feb 15 '18 at 09:53
`if (!double.TryParse(command.ExecuteScalar(), out chargesSoFar) changesSoFar = 0;` — Rufus L, Feb 15 '18 at 09:56

John Woo · Accepted Answer · 2018-02-15T09:29:59.877

4

try nullable datatype:

double? chargesSoFar = null;
// other codes here
chargesSoFar = (double?)command.ExecuteScalar();

As a sidenote, a parameterized query is most likely the best practice to prevent sql injection. To do this,

try
{
    connection.Open();
    command.Connection = connection;

    command.CommandText = "SELECT SUM(Amount) FROM Payments WHERE TransactionDate <= @TransData;";
    command.Parameter.AddWithValue("TransData", DateTime.Now);
    chargesSoFar = (double?)command.ExecuteScalar();
    connection.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Error: " + ex.Message);
}

According to this question: What is the best data type to use for money in c#?

The best data type to use is decimal.

decimal? chargesSoFar = null;
// other codes here
chargesSoFar = (decimal?)command.ExecuteScalar();

edited Feb 15 '18 at 09:29

answered Feb 15 '18 at 09:19

John Woo

258,903
69
498
492

3

While it's not really contributing to a solution to the problem, I'd recommend to _not_ use `double` for monetary amounts. – Fildor Feb 15 '18 at 09:27
Thanks. That was something I have on my to do list for when I have the basic functionality working. – LennyLen Feb 15 '18 at 09:30
1

@Fildor definitely agree. – John Woo Feb 15 '18 at 09:30
I did originally have it as a decimal. I changed it to double when I first though that was the problem. I will change it back to decimal. – LennyLen Feb 15 '18 at 09:31
I still get a "Specified cast is not valid" error even when using `decimal?` as the type. I don't know what else could be causing the error. – LennyLen Feb 15 '18 at 09:41
how about changing to `chargesSoFar = command.ExecuteScalar() as decimal?;` or `chargesSoFar = Convert.ToDecimal(command.ExecuteScalar());` – John Woo Feb 15 '18 at 09:45
@LennyLen it should have corresponding type in your database too (like `decimal` or `money`). If it's still `float` in your database - `ExecuteScalar` will return double, which cannot be casted to .NET decimal. – Evk Feb 15 '18 at 09:55
John Woo: using `chargesSoFar = command.ExecuteScalar() as decimal?;` worked. Thank-you. – LennyLen Feb 15 '18 at 11:28

score 1 · Answer 2 · answered Feb 15 '18 at 09:18

You can cast to a nullable type by using a question mark suffix, i.e. (double?), then you can check for null.

     paymentsSoFar = (double?)command.ExecuteScalar()
     if (paymentSoFar.HasValue){

Please also read up on SQL Injection: https://www.acunetix.com/websitesecurity/sql-injection/ as your code is currently vulnerable to this type of attack

TheGeneral · Answer 3 · 2018-02-15T10:00:57.083

Maybe you can cast to Nullable<double> i.e double?

double? paymentsSoFar = (double?)command.ExecuteScalar();

if(paymentsSoFar.HasValue)
{
    // Then its safe to calculate 

    double myActualValue = paymentsSoFar.Value;
}

Nullable Types (C# Programming Guide)

Nullable types have the following characteristics:

Nullable types represent value-type variables that can be assigned the value of null. You cannot create a nullable type based on a reference type.

The syntax T? is shorthand for Nullable<T>, where T is a value type. The two forms are interchangeable.

Assign a value to a nullable type just as you would for an ordinary value type, for example int? x = 10; or double? d = 4.108. A nullable type can also be assigned the value null: int? x = null.

Use the Nullable<T>.GetValueOrDefault method to return either the assigned value, or the default value for the underlying type if the value is null, for example int j = x.GetValueOrDefault();

Use the HasValue and Value read-only properties to test for null and retrieve the value, as shown in the following example: if(x.HasValue) j = x.Value;

The HasValue property returns true if the variable contains a value, or false if it is null.

The Value property returns a value if one is assigned. Otherwise, a System.InvalidOperationException is thrown.

The default value for HasValue is false. The Value property has no default value.

You can also use the == and != operators with a nullable type, as shown in the following example: if (x != null) y = x;

Use the ?? operator to assign a default value that will be applied when a nullable type whose current value is null is assigned to a non-nullable type, for example int? x = null; int y = x ?? -1;

Nested nullable types are not allowed. The following line will not compile: Nullable<Nullable<int>> n;

@RufusL this was a fire and forget question, Thanks for the heads up — TheGeneral, Feb 15 '18 at 10:01

C# What type should I cast here?

3 Answers3