未处理FormatException：SQL数值字段转double格式错误求助

Hey there! Let's work through that FormatException you're hitting when trying to convert SQL field values to double in your code. The issue happens because double.Parse() is strict—it'll throw an error if the input string isn't a perfectly valid number, or if it encounters unexpected values like database NULLs or format mismatches.

Here are practical fixes tailored to your code:

1. First, Debug the Actual Field Value #

Before jumping into fixes, let's confirm what's actually coming from the database. Sometimes the issue is obvious once you see the raw value. Add a quick debug line to check:

// Add this before your parsing code to see what dr["OT"] really holds
string otRawValue = dr["OT"].ToString();
Console.WriteLine($"Raw OT value from DB: '{otRawValue}'");

Look for things like empty strings, spaces, letters, or even "" (which happens if the field is NULL in the database).

2. Use double.TryParse() Instead of double.Parse() #

Parse() crashes on invalid inputs, but TryParse() safely tells you if conversion succeeded without throwing exceptions. Here's how to update your assignment code:

double otAct = 0; // Default value if conversion fails
double otPlan = 0;

// Handle OT field
if (!double.TryParse(dr["OT"].ToString(), out otAct))
{
    // Optional: Log the invalid value or handle it per your business logic
    Console.WriteLine($"Failed to parse OT value: '{dr["OT"].ToString()}'");
}

// Repeat the same logic for SH, SHtwo, and OTPlan fields
if (!double.TryParse(dr["OTPlanFieldName"].ToString(), out otPlan))
{
    Console.WriteLine($"Failed to parse OTPlan value: '{dr["OTPlanFieldName"].ToString()}'");
}

mylist.Add(new OTTotal { OTAct = otAct, OTPlan = otPlan });

3. Handle Database NULL Values (DBNull) #

If your SQL fields allow NULLs, dr["OT"].ToString() will return an empty string, which breaks Parse(). Add a check for DBNull first:

// Safe way to get the value, defaulting to "0" if it's NULL
string otValue = dr["OT"] != DBNull.Value ? dr["OT"].ToString() : "0";
double otAct = double.Parse(otValue); // Or combine with TryParse for extra safety

Or a more concise approach using Convert:

double otAct = dr["OT"] is DBNull ? 0 : Convert.ToDouble(dr["OT"]);

4. Avoid ToString() Altogether—Read Directly from DataReader #

Instead of converting the database value to a string first, use the DataReader's built-in methods to get the numeric value directly. This is faster and avoids string parsing issues:

int otColumnIndex = dr.GetOrdinal("OT");
double otAct = dr.IsDBNull(otColumnIndex) ? 0 : dr.GetDouble(otColumnIndex);

5. Account for Culture/Format Differences #

If your database uses a different decimal separator (like commas instead of periods, common in European locales), specify a culture to ensure consistent parsing:

using System.Globalization;

// Use InvariantCulture to parse numbers in standard format, regardless of system settings
double.TryParse(dr["OT"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out otAct);

Quick Recap of Your Updated Code #

Putting it all together, here's how your assignment code might look with the safest practices:

using System.Globalization;

// ...

double otAct = 0;
double shValue = 0;
double shTwoValue = 0;
double otPlan = 0;

// Parse OT
if (dr["OT"] != DBNull.Value)
{
    double.TryParse(dr["OT"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out otAct);
}

// Parse SH (uncommented from your original code)
if (dr["SH"] != DBNull.Value)
{
    double.TryParse(dr["SH"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out shValue);
}

// Parse SHtwo
if (dr["SHtwo"] != DBNull.Value)
{
    double.TryParse(dr["SHtwo"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out shTwoValue);
}

// Parse OTPlan
if (dr["OTPlanField"] != DBNull.Value) // Replace with your actual OTPlan field name
{
    double.TryParse(dr["OTPlanField"].ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out otPlan);
}

mylist.Add(new OTTotal 
{ 
    OTAct = otAct + shValue + shTwoValue, 
    OTPlan = otPlan 
});

内容的提问来源于stack exchange，提问作者Alexis Villar