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.

Answer 1

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();