c# - 如果出现 DBNULL，我如何使消息框出现

Question

我有一个问题，如果我通过 TextBox 搜索数据并且数据库中的数据不存在我收到错误

'Object cannot be cast from DBNull to other types.'

我正在尝试制作 MessageBox似乎在说数据不存在并且不知道如何做到这一点。

我试过使用 if语句 where if TextBox等于 DBNull然后是MessageBox出现。这不t work and im not too sure why. The error occurs with me trying to **equal** to数据库空 . How do i produce a TextBox` 说数据不存在？

{
        SqlConnection con = new SqlConnection("***COnString**");
        con.Open();
        SqlCommand comm = new SqlCommand("SELECT SUM (Total_Hours_Day) FROM Sign_In_Out_Table, User_Table WHERE User_Table.FirstName = '" + Search_Username_Alerts_Admin_txtbox.Text + "' AND Sign_In_Out_Table.eb_number = User_Table.eb_number AND Date between GETDATE()-14 and GETDATE()", con);
        decimal TotalHoursFortnight = Convert.ToDecimal(comm.ExecuteScalar());
        con.Close();

        decimal sum = 0;

        sum += Convert.ToDecimal(TotalHoursFortnight);

        if (Search_Username_Alerts_Admin_txtbox.Text == DBNull)
        {
            MessageBox.Show("No Data Exists");
        }
        else
        {
            MessageBox.Show(Search_Username_Alerts_Admin_txtbox.Text + ":" + Environment.NewLine + " Hours Worked = " + TotalHoursFortnight, ("Working Info Admin"), MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
}

我的预期结果 是出现一个消息框，指出如果搜索数据不存在。但如果是这样，数据将显示在消息框中。

Answer 1

您有 3 个案例要实现:

1. 根本没有数据:检查null
2. 无效 数据，例如1 + 2 + NULL + 3 == NULL:检查 DBNull.Value
3. 有效数据，例如1 + 2 + 3 == 6:借助 Convert.ToDecimal()
进行转换

代码:

// wrap IDisposable into using
using (SqlConnection con = new SqlConnection("***COnString**")) {
  con.Open();

  //DONE: Make sql readable
  //DONE: Make sql parametrized
  //TODO: you may want to change eb_number = eb_number into INNER JOIN
  string sql = 
    @"SELECT SUM (Total_Hours_Day) 
        FROM Sign_In_Out_Table, 
             User_Table 
       WHERE User_Table.FirstName = @prm_FirstName  
         AND Sign_In_Out_Table.eb_number = User_Table.eb_number 
         AND Date BETWEEN GETDATE() - 14 AND GETDATE()"; 

  using (SqlCommand comm = new SqlCommand(sql, con)) {
    //TODO: Better specify RDBMS type explictly with "comm.Parameters.Add(...)"
    comm.Parameters.AddWithValue(
      "@prm_FirstName", Search_Username_Alerts_Admin_txtbox.Text); 

    var result = comm.ExecuteScalar();

    if (null == result) {              // No Data
      MessageBox.Show("No Data Exist");
    }
    else if (DBNull.Value == result) { // We have the Data and it's RDBMS Null
      MessageBox.Show("Data Exist, but not valid.");
    } 
    else {                             // We have a valid Decimal
      Decimal sum = Convert.ToDecimal(result); 
      //TODO: put the relevant code here
    }
  }
}