C# WPF实现仅当[Stock].Amount更新为0时发送邮件的需求及代码优化求助

没问题，我来帮你调整代码实现这个需求！核心思路是先获取更新前的库存数量，确认这次操作确实是把非0的库存改成了0，再触发邮件发送，避免重复发送或者误发。

修改后的完整代码 #

private void BtnSend_Click(object sender, RoutedEventArgs e)
{
    DataRowView o = (DataRowView)g2.SelectedItem;
    int id = Convert.ToInt32(o.Row.ItemArray[0]);
    int newTotal = Convert.ToInt32(o.Row.ItemArray[5]); // 重命名变量更清晰：newTotal是要更新后的数值

    try
    {
        int oldAmount = 0;
        using (SqlConnection con = new SqlConnection(connectionstring))
        {
            // 第一步：先获取更新前的Amount值
            string getOldAmountQuery = "SELECT [Amount] FROM [Stock] WHERE [Id] = @Id;";
            using (SqlCommand getOldCmd = new SqlCommand(getOldAmountQuery, con))
            {
                getOldCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
                con.Open();
                oldAmount = Convert.ToInt32(getOldCmd.ExecuteScalar());
            }

            // 第二步：执行更新操作
            const string updateQuery = @"UPDATE [Stock] SET [Amount] = @NewTotal WHERE [Id] = @Id;";
            using (SqlCommand updateCmd = new SqlCommand(updateQuery, con))
            {
                updateCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
                updateCmd.Parameters.Add("@NewTotal", SqlDbType.Int).Value = newTotal;
                updateCmd.ExecuteNonQuery();
            }
        }

        MessageBox.Show("Update completed successfully");
        binddatagrid();

        // 关键判断：只有当更新前不是0，且更新后是0的时候才发邮件
        if (oldAmount != 0 && newTotal == 0)
        {
            var fromAddress = new MailAddress("FROM-Email", "From Name");
            var toAddress = new MailAddress("To-Email", "To Name");
            const string fromPassword = "*****";
            const string subject = "Stock Alert: Item is now out of stock!";
            const string body = "An item in your stock has been updated to 0 amount.";

            var smtp = new SmtpClient
            {
                Host = "smtp.gmail.com",
                Port = 587,
                EnableSsl = true,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                UseDefaultCredentials = false,
                Credentials = new NetworkCredential(fromAddress.Address, fromPassword),
                Timeout = 20000
            };

            using (var message = new MailMessage(fromAddress, toAddress)
            {
                Subject = subject,
                Body = body
            })
            {
                smtp.Send(message);
                MessageBox.Show("E-mail has been sent.", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }
    }
    catch (Exception ex)
    {
        // 这里建议捕获具体异常，方便调试
        MessageBox.Show($"Error: {ex.Message}\nPlease correct the input and try again.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
}

关键修改点说明 #

• 获取更新前的数值：先查询数据库拿到该条记录原来的Amount值，避免“原来已经是0，再次更新为0”的情况下误发邮件。
• 添加判断逻辑：用oldAmount != 0 && newTotal == 0来确认是“从非0变为0”的更新操作，只有满足这个条件才执行邮件发送代码。
• 优化代码结构：把数据库操作合并到同一个连接里，减少连接开销；重命名变量让代码更易读；完善异常捕获，显示具体错误信息方便调试。
• 移除无用代码：删掉了原来那个没有实际作用的SELECT COUNT(*)命令（它没有连接数据库，执行会报错）。

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