填充CartesianChart时，对象不能从DBNULL强制转换为其他类型异常

Question

尝试加载CartesianChart时出现以下异常

对象不能从DBNULL转换为其他类型的

我有以下查询，它返回了一些NULL的

这是我的SQL语句：

SELECT DATENAME(MONTH, d.OPENED) AS MonthValue, 
AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) 
ELSE 0 END) AS SmallCommercialIndust_avg, 
AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) 
ELSE 0 END) AS Residential_avg
FROM hb_Disputes d
WHERE YEAR(d.OPENED) = YEAR(GETDATE())
GROUP BY DATENAME(MONTH, d.OPENED)
ORDER BY MIN(d.OPENED);

这是输出：

MonthValue  SmallCommercialIndust_avg     Residential_avg
----------------------------------------------------------
January             0                         0
February            0                         0
March               NULL                      0
April               0                         0
May                 0                         NULL
June                0                         NULL
July                73                        0
August              123                       0
September           0                         136
October             166                       0
November            169                       0
December            0                         NULL

这是我用来加载CartesianChart:的WPF代码

private void ChartValues()
{
// Defines the variable for differnt lines.
List<double> allValues = new List<double>();
List<double> someValues = new List<double>();

try
{
SqlConnection connection = new SqlConnection("Data Source=WINDOWS-B1AT5HC\SQLEXPRESS;Initial Catalog=CustomerRelations;Integrated Security=True;");

string selectQuery = ("SELECT DATENAME(MONTH, OPENED) AS MonthValue, SUM(CASE WHEN d .ASSGNTO = 'E099255' AND d .REV_CLS = 2 THEN 1 ELSE 0 END) AS SmallCommercialIndust, AVG(CASE WHEN d .ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) ELSE 0 END) AS SmallCommercialIndust_avg, SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN 1 ELSE 0 END) AS Residential, AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) ELSE 0 END) AS Residential_avg FROM hb_Disputes AS d WHERE(YEAR(OPENED) = YEAR(GETDATE())) GROUP BY DATENAME(MONTH, OPENED) ORDER BY MIN(OPENED)");
connection.Open();
using  SqlCommand command = new SqlCommand(selectQuery, connection);
SqlDataReader sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
{
// Select the values from the columns selected 
allValues.Add(Convert.ToDouble(sqlReader["SmallCommercialIndust_avg"]));
someValues.Add(Convert.ToDouble(sqlReader["Residential_avg"]));
}
// Starts new line series.
SeriesCollection = new SeriesCollection
{
new LineSeries
{
Title = "Residential",
Values = new ChartValues<double>(allValues),
LineSmoothness = 1, //0: straight lines, 1: really smooth lines
},
new LineSeries
{
Title = "Small Commercial Indust",
Values = new ChartValues<double>(someValues),
LineSmoothness = 1, //0: straight lines, 1: really smooth lines
}
};
Labels = new[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
YFormatter = value => value.ToString("N");
DataContext = this;
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}            
}

Answer 1

在分配变量之前，您需要检查DBNull

// Check for DBNull and then assign the variable
if (sqlReader["SmallCommercialIndust_avg"] != DBNull.Value)
SmallCommercialIndustValues.Add(Convert.ToInt32(sqlReader["SmallCommercialIndust_avg"]));
// Check for DBNull and then assign the variable
if (sqlReader["Residential_avg"] != DBNull.Value)
ResidentialValues.Add(Convert.ToInt32(sqlReader["Residential_avg"]));