运行两个SQL命令会导致一个未执行 [英] Running two SQL commands results in one not being executed

问题描述

我正在比较两个表并检查文件是否仍存在于存储在DB中的路径中。如果该文件不存在，将删除具有前导ID的'Pruefdatei_Import'条目以及具有完全相同ID的第二个表中的所有相关条目。将删除前导条目（始终只有一个）但第二个表格未被触及。

I'm comparing two tables and check if the file still exists in the path which is stored in the DB. If the file doesn't exist, the entry from 'Pruefdatei_Import' which has the leading ID will be deleted as well as all related entries from the second table with the exact same ID. The leading entry (which is always only one) will be deleted but the second table is being untouched.

List<string> PDToDelete = new List<string>();
SqlConnection sqlConn = new SqlConnection("Server=" + config.DBHOST + ";Database=" + config.DBASE + ";User Id=" + config.DBUSER + ";Password=" + Decrypt(config.DBPASSWORD) + ";MultipleActiveResultSets=True");
sqlConn.Open();

SqlCommand checkIfFileExist = new SqlCommand("SELECT Pruefdatei FROM Pruefdatei_Import", sqlConn);
SqlDataReader reader = checkIfFileExist.ExecuteReader();
if (reader.HasRows)
while (reader.Read())
{
    string path = reader.GetString(0);
    FileInfo pruefDatei = new FileInfo(path);
    if (!pruefDatei.Exists)
    {
            PDToDelete.Add(path);
    }
}
reader.Close();

foreach (string entry in PDToDelete)
{
    if (MessageBox.Show("Die Prüfdatei " + entry + " existiert nicht mehr. Sollen dazugehörige Datenbanksätze gelöscht werden?", "Löschen bestätigen", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
    {
        using (SqlCommand deleteEntriesFromDB_1 = new SqlCommand("DELETE FROM Pruefdatei WHERE Referenznummer = @PDN", sqlConn))
        {
            SqlParameter pathParam = new SqlParameter("@PDN", entry);
            deleteEntriesFromDB_1.Parameters.Add(pathParam);
            //This one doesn't work
            deleteEntriesFromDB_1.ExecuteNonQuery();
        }
        using (SqlCommand deleteEntriesFromDB_2 = new SqlCommand("DELETE FROM Pruefdatei_Import WHERE Pruefdatei = @PDN", sqlConn))
        {
            SqlParameter pathParam2 = new SqlParameter("@PDN", entry);
            deleteEntriesFromDB_2.Parameters.Add(pathParam2);
            deleteEntriesFromDB_2.ExecuteNonQuery();
        }
    }
}
sqlConn.Close();

我的尝试：



我启用了MARS并将SQL语句分成两个单独使用。

What I have tried:

I have enabled MARS and put the SQL statements in two seperate usings.

推荐答案

我们无法直接帮助 - 我们有无法访问您的数据库，您需要加上条目变量中的信息来计算正在发生的事情。



所以从调试器开始，在第一个上使用块放置一个断点。当它命中它时，查看条目的内容并将其复制到剪贴板。

现在打开SSMS，并从两个表中选择所有记录匹配条目（一个WHERE Referenznummer匹配，一个Pruefdatei匹配）。

如果两个表中都有行，请使用逐步执行第一个阻止，然后再次执行SELECT。您应该在第二个表中有数据，但不是第一个表中的数据。逐步执行第二个块，然后再次选择SELECT。您应该丢失两个表的数据。



如果一切正常，请为的每次旅行重复此过程循环。



如果有任何不符合您的预期，请考虑所涉及的数据并尝试找出原因。
对不起，但我们不能为你做任何事情！

There isn't anything we can do to help directly - we have no access to your DB, and you need that plus the information in your entry variable to work out what is happening.

So start with the debugger, and put a breakpoint on the first using block. when it hits it, look at the content of entry and copy it to the clipboard.
Now open SSMS, and SELECT all records from both tables with matching entries (one WHERE Referenznummer matches, and one where Pruefdatei matches).
If you have rows in both tables, step through the first using block, and do the SELECTs again. You should have data in the second table, but not the first. Step through the second block, and again SELECT. You should have lost both table's data.

If it all worked, repeat the process for each trip round the for loop.

If anything doesn't match what you expected, think about the data involved and try to spot why not.
Sorry, but we can't do any of that for you!

这篇关于运行两个SQL命令会导致一个未执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！