如何防御针对随机引用“表0"的代码进行防御和空值? [英] How can I defensively code against randomly referencing "Table 0" and null values?

问题描述

我正在检索适量的数据并对其进行处理-那里没有什么独特的.起初奇怪的是，使用某些数据集可以正常工作，而对于其他数据集，我得到了以下错误信息:

I am retrieving a moderate amount of data and processing it - nothing unique there. What was odd at first was that with some sets of data, it worked fine, and with others, I got the following err msg:

此错误消息似乎是完全错误的消息(无论如何还是有误导性的)，因为失败集的数据不会多于成功集的数据，因此运行不好"的数据.

This err msg seems to be total hogwash, though (misleading, at any rate), because there is no more data with the failing set than with the successful set, and so it shouldn't take any longer to run the "bad" data than the good.

也许启发人的是之后出现的其他错误信息:

Mor enlightening, perhaps, are the other err msgs that appear after that:

注意:由于调试代码的出现，您可以在下面的代码的catch块中看到这些详细的err对话框，而不是更简洁一些.

Note: I get these verbose err dialogs, rather than something more concise, because of the debugging code you can see in the catch block of the code below.

因此，正如最初的err msg所指示的，这似乎并不是一个数据过多"的问题.这也不是丢失数据"的问题，因为如果我查询2014年12月至2015年12月的数据(对于接下来的一个月没有数据)，它运行良好-它只返回2015年12月的全0；因此它一定是一个坏数据(不仅仅是丢失)"问题.如何确定不良数据是什么，并在防御上防止其破坏应用程序的运行?

So it seems apparent that it's not really a "too much data" issue, as the initial err msg indicates. It's also not a "missing data" issue, because if I query for data from December 2014 through December 2015 (for which upcoming month there is no data), it runs fine -- it just returns all 0s for December 2015; so it must be a "bad (not just missing) data" issue. How can I determine what the bad data is and defensively prevent it from ruining the run of the app?

第601行(包含在上面的err消息中)包含以下代码:

Line 601, implicated in the err msg above, contains this code:

private void ReadData(string _unit, string monthBegin, string monthEnd, string beginYear, string endYear)
{
    try
    {
        String dateBegin = UsageRptConstsAndUtils.GetYYYYMMDD(monthBegin, beginYear, true);
        String dateEnd = UsageRptConstsAndUtils.GetYYYYMMDD(monthEnd, endYear, false);
        DateTime dtBegin = UsageRptConstsAndUtils.DatifyYYYYMMDD(dateBegin);
        DateTime dtEnd = UsageRptConstsAndUtils.DatifyYYYYMMDD(dateEnd);
        DataTable dtUsage = SqlDBHelper.ExecuteDataSet("sp_ViewProductUsage_MappingRS", CommandType.StoredProcedure,
            new SqlParameter() { ParameterName = "@Unit", SqlDbType = SqlDbType.VarChar, Value = _unit },
            new SqlParameter() { ParameterName = "@BegDate", SqlDbType = SqlDbType.DateTime, Value = dtBegin },
            new SqlParameter() { ParameterName = "@EndDate", SqlDbType = SqlDbType.DateTime, Value = dtEnd }
        );

SqlDBHelper.ExecuteDataSet()是:

SqlDBHelper.ExecuteDataSet() is:

public static DataTable ExecuteDataSet(string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(UsageRptConstsAndUtils.CPSConnStr))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
        }
        catch (SqlException sqlex)
        {
            for (int i = 0; i < sqlex.Errors.Count; i++)
            {
                var sqlexDetail = String.Format("From ExecuteDataSet(), SQL Exception #{0}{1}Source: {2}{1}Number: {3}{1}State: {4}{1}Class: {5}{1}Server: {6}{1}Message: {7}{1}Procedure: {8}{1}LineNumber: {9}",
                    i + 1, // Users would get the fantods if they saw #0
                    Environment.NewLine,
                    sqlex.Errors[i].Source,
                    sqlex.Errors[i].Number,
                    sqlex.Errors[i].State,
                    sqlex.Errors[i].Class,
                    sqlex.Errors[i].Server,
                    sqlex.Errors[i].Message,
                    sqlex.Errors[i].Procedure,
                    sqlex.Errors[i].LineNumber);
                MessageBox.Show(sqlexDetail);
            }
        }
        catch (Exception ex)
        {
            String exDetail = String.Format(UsageRptConstsAndUtils.ExceptionFormatString, ex.Message, Environment.NewLine, ex.Source, ex.StackTrace);
            MessageBox.Show(exDetail);
        }
        return ds.Tables[0];
    }
}

第396行(在最后一个err msg中引用)是此处的第一行代码:

Line 396 (referenced in the last err msg) is the first line of code here:

private String GetContractedItemsTotal()
{
    var allContractRecords = _itemsForMonthYearList.Where(x => x.ContractItem);
    var totalContractItemPurchases = allContractRecords.Sum(x => x.TotalPurchases);
    return totalContractItemPurchases.ToString("C");
}

是什么导致该代码有时因找不到表0"和值不能为空"异常而崩溃?或更重要的是，当值为null时，如何防止它造成如此严重的破坏?

What could be causing this code to sometimes crash with the "Cannot find Table 0" and "Value cannot be null" exceptions? Or more to the point, how can I prevent it from wreaking such havoc when a value is null?

更多背景信息:

_itemsForMonthYearList的定义如下:

_itemsForMonthYearList is defined like this:

private List<ItemsForMonthYear> _itemsForMonthYearList;

..并按以下方式填充:

..and populated like so:

var ifmy = new ItemsForMonthYear();

int qty = Convert.ToInt32(productUsageByMonthDataRow["TotalQty"]);
// TotalPrice as Decimal for calculation
Decimal totPrice = Convert.ToDecimal(productUsageByMonthDataRow["TotalPrice"]);
Decimal avgPrice = Convert.ToDecimal(productUsageByMonthDataRow["AvgPrice"]);
String monthYear = productUsageByMonthDataRow["MonthYr"].ToString();

ifmy.ItemDescription = desc;
ifmy.TotalPackages = qty;
ifmy.TotalPurchases = totPrice;
ifmy.AveragePrice = avgPrice;
ifmy.monthYr = monthYear;
ifmy.ContractItem = contractItem; // added 11/16/2016
if (null == _itemsForMonthYearList)
{
    _itemsForMonthYearList = new List<ItemsForMonthYear>();
}
_itemsForMonthYearList.Add(ifmy);

推荐答案

正如jmcilhinney所建议的那样，调整CommandTimeout值似乎是成功的窍门.

As jmcilhinney suggests, tweaking the CommandTimeout value seems to have been the ticket/done the trick.

我最初将SqlCommand的CommandTimeout值设置为300(5分钟)，但是这样我得到了发生上下文切换死锁".因此，我将其减少到120(2分钟)，这似乎对我来说或多或少是最佳点".在几次测试中，我确实一次获得了"Timeout expired"，但是当我重试相同的精确范围时，它第二次成功完成了，所以我想那只是其中之一"-120有时还不够超时，但是300显然太多了. IOW，这种在太少和太多之间的平衡行为似乎并不是一门精确的科学".

I originally made the SqlCommand's CommandTimeout value 300 (5 minutes), but with that I got "Context Switch Deadlock occurred." So I then reduced it to 120 (2 minutes), and that seems to be more or less the "sweet spot" for me. I did get "Timeout expired" one time out of several tests, but when I retried the same exact range, it completed successfully the second time, so I guess it's just "one of those things" - 120 will sometimes not be enough of a timeout, but 300 is apparently too much. IOW, this balancing act between too little and too much doesn't appear to be "an exact science."

这篇关于如何防御针对随机引用“表0"的代码进行防御和空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！