sql - 填充 CartesianChart 时,对象不能从 DBNULL 转换为其他类型异常
问题描述
尝试加载我的时出现以下异常CartesianChart
对象不能从 DBNULL 转换为其他类型
我有以下查询返回几个NULL
s
这是我的 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
这是我加载我的 WPF 代码CartesianChart
:
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());
}
}
解决方案
您需要在分配变量之前检查 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"]));
推荐阅读
- css - 在具有 ng-deep 的全局主题混合中使用主题颜色
- python - 当你点击它被复制的文本时如何做到这一点 pytelegrambotapi
- jmeter - Jmeter - 来自集中位置的标头
- c# - 类型对象作为泛型方法的类型参数
- here-api - HERE API 计算路由 - 避免任何改变路由器行为
- mysql - 如何在 MySQL 中禁用 varchar 到文本的自动转换?
- apache-flink - Flink - 无法从检查点恢复
- javascript - 我想使用纯 javaScript 通过 webRTC 进行点对点视频聊天
- php - 如何创建一个检查作为参数传递的多个值的函数
- angular - 在 ionic v3 中在真实设备中运行应用程序时出错