c# - 一段时间内 foreach 循环的奇怪行为 READ()

Question

我是一名 VFP 开发人员，正在尝试学习 C#。认为最快的学习方法是实践，我给自己设计了一个小项目来练习。

目标

将一些事务从“实时”数据库移动到存档数据库中。

总体思路是:

• 获取“合适的”工作列表(父)，然后逐一读取()它们一个。
• 使用 foreach 循环处理子表列表，插入历史数据库中的新记录，然后删除旧记录来自“实时”表。
• 将实际作业(父作业)移至存档，然后将其从“实时”中删除

一开始还好，后来突然碰壁了....

问题

我有一个名为 RefCombo 的变量，用于存储父项的主键，它存储在 while (READ()) 语句之后和 foreach 之前> 循环。

在有四个记录的测试数据库中，RefCombo 字段应该是:

while read() 结果:Job1

foreach childtable 在我的预定义列表中

• 处理 ChildTable1

• 处理 ChildTable2

• 处理 ChildTable3

• 处理 ChildTable4

• 处理 ChildTable5

然后重复 Job2、Job3 和 Job4。

让我发疯的是我得到的是这个:

while read() 结果:Job1

foreach childtable 在我的预定义列表中

• 处理 ChildTable1

• 跳过其他ChildTables，RefCombo(主键)变成Job2，循环回到while read()

事情是，一旦它更改为 Job2，它就会像我预期的那样继续工作，为每个剩余的父记录循环遍历每个子表。

我还收到“当前的 TransactionScope 已经完成。”每次我运行网页时都会出错。这发生在程序开始时的 connection.open() 部分，但很难确定，因为它不会一直发生。

我已将代码贴在底部，随便看一眼有点长，但如有任何帮助，我们将不胜感激。我已经为此工作了 2 天，真的不能再掉头发了，哈哈。

欢迎对我的代码发表任何评论，我是 C# 的新手，每一点都有帮助 :P

提前致谢。

#region Declaration
DateTime FromDate = Convert.ToDateTime("01/01/2011");
DateTime ToDate = Convert.ToDateTime("01/03/2011");
string conStrSource = @"Data Source=HOME\SQLEXPRESS;Initial Catalog=MCC_Live;Integrated Security=True";

// Declare a list of child tables to check and move together with the Job
List<String> TablesToMove = new List<String>();
    {
    TablesToMove.Add("JobItems");
    TablesToMove.Add("JobTerms");
    TablesToMove.Add("JobMessages");
    TablesToMove.Add("JobCalcs");
    TablesToMove.Add("JobContainers");
    TablesToMove.Add("JobMetrics");
    }
#endregion

#region Set SQL String
string QueryString =
    "SELECT " +
    "JobMaster.BranchID, JobMaster.JobNo, " +
    "JobMaster.ImportDate, PermitStatus.CurrentStatus " +
    "FROM JobMaster. " +
    "INNER JOIN PermitStatus ON " +
        "JobMaster.BranchID = PermitStatus.BranchID AND " +
        "JobMaster.JobNo = PermitStatus.JobNo " +
    "WHERE " +
        "(JobMaster.ImportDate BETWEEN @FromDate AND @ToDate) AND " +
        "PermitStatus.currentStatus NOT IN ('HT0', 'HT1', 'HT2')";
#endregion

// Display on web page for reference
ASPxFromDate.Value = FromDate;
ASPxToDate.Value = ToDate;
ASPxMemo1.Value = QueryString;

#region Open Connection, Get List of filtered Master Jobs
using (SqlConnection connection = new SqlConnection(conStrSource))
    {
        int JobCount = 0;
        ASPxListBox1.Items.Clear();
        ASPxListBox2.Items.Clear();
        ASPxListBox3.Items.Clear();
        ASPxListBox1.Columns.Clear(); 

        SqlCommand command = new SqlCommand(QueryString, connection);
            {
            command.Parameters.Add(new SqlParameter("@FromDate", FromDate));
            command.Parameters.Add(new SqlParameter("@ToDate", ToDate));
            }
        connection.Open();
        SqlDataReader FilteredJobList = command.ExecuteReader();

#endregion

        try // Process MasterJob File
        {
        // Process individual jobs one by one so I won't tie up memory and have better logging
        while (FilteredJobList.Read())
            {
            #region Declare variables
            string RefCombo = (string)FilteredJobList[0] + (string)FilteredJobList[1]; //Get primary key
            JobCount = JobCount + 1;
            ASPxTextBox2.Value = JobCount;
            ASPxListBox2.Items.Add(RefCombo);
            #endregion

            // Start transaction scope
            TransactionScope TranScope = new TransactionScope();
                {
                try
                    {
                    // Loop through child tables
                    foreach (string CurrentTable in TablesToMove)
                        {
                        #region Transfer child tables
                        // update list so I know which part its working on
                        ASPxListBox1.Items.Add(CurrentTable);  
                        RefCombo = (string)FilteredJobList[0] + (string)FilteredJobList[1];
                        string RefTableNow = (string)CurrentTable;
                        bool CancelTrans = false;
                        MoveChild(ref RefCombo, ref RefTableNow, ref conStrSource, ref CancelTrans);
                            if (CancelTrans == false)
                                { //LogFailure();
                                break;
                                }
                        DelChild(ref RefCombo, ref RefTableNow, ref conStrSource, ref CancelTrans);
                            if (CancelTrans == false)
                                { //LogFailure();
                                break;
                                }
                        #endregion
                            // Remove remaing entries
                            //MoveLatestStatus();
                            //DeleteLatestStatus();
                            //MoveMasterJob();
                            //DeleteMasterJob();
                            //LogSuccess();
                            TranScope.Complete();
                        }
                catch
                    {
                    //LogFailure();
                    }
                }
            }
        finally
            {
                FilteredJobList.Close();
            }
        }
    }


//------------------------------------------------------------------------------------------------

private void MoveChild(ref string RefCombo, ref string CurrentTable, ref string conStrSource, ref bool CancelTrans)
    {
    #region Define Insert String
    string InsertSqlString =
        "INSERT INTO [MCC_History].[dbo].[" + @CurrentTable + "]" +
        " SELECT * FROM [MCC_Live].[dbo].[" + @CurrentTable + "] s" +
        " WHERE NOT EXISTS" +
        " (SELECT 1 FROM [MCC_History].[dbo].[" + @CurrentTable + "] t2" +
        " WHERE t2.BranchID + t2.JobNo = s.BranchID + s.JobNo)" +
        " AND s.BranchID + s.JobNo = @RefCombo";

    #endregion

    #region Open connection and execute query
    using (SqlConnection MoveConnect = new SqlConnection(conStrSource))
        {
        try
            {
            SqlCommand InsertCommand = new SqlCommand(InsertSqlString, MoveConnect);
                {
                InsertCommand.Parameters.Add(new SqlParameter("@RefCombo", RefCombo));
                }
            MoveConnect.Open();
            InsertCommand.ExecuteNonQuery();
            }
        catch
            {
            CancelTrans = true;
            }
        }
    #endregion
    }

//------------------------------------------------------------------------------------------------

private void DeleteChild(ref string RefCombo, ref string CurrentTable, ref string conStrSource, ref bool CancelTrans)
    {
    #region Define Delete query
    string DeleteSqlString =
        " DELETE FROM [MCC_Live].[dbo].[" + @CurrentTable + "]" +
        " WHERE [MCC_Live].[dbo].[" + @CurrentTable + 
        "].BranchID + [MCC_DB].[dbo].[" + @CurrentTable + "].JobNo = @RefCombo";
    #endregion

    #region Execute Delete query
    using (SqlConnection MoveConnect = new SqlConnection(conStrSource))
        {
        try
            {
            SqlCommand InsertCommand = new SqlCommand(DeleteSqlString, MoveConnect);
                {
                InsertCommand.Parameters.Add(new SqlParameter("@RefCombo", RefCombo));
                }
            MoveConnect.Open();
            InsertCommand.ExecuteNonQuery();
            }
        catch
            {
            CancelTrans = true;
            }
        }
    #endregion
    }

Answer 1

问题在于您的 TranScope.Complete(); 语句在哪里。它在您的 foreach 循环内，并且在您的 Foreach block 退出后属于它。您在列表的第一次迭代结束时完成交易。这会阻止您通过此事务发出更多命令。