Click here to Skip to main content
15,888,527 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

C#
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();


What I have tried:

I have enabled MARS and put the SQL statements in two seperate usings.
Posted
Updated 24-Apr-19 0:17am
Comments
[no name] 24-Apr-19 6:09am    
a.) That is not related to MARS. You execute the SQLs sequential and they have also no result set.
b.) Any exception while debuging? Maybe a Foreign Key violation? Or does deleteEntriesFromDB_1 returns affected rows == 0?

1 solution

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!
 
Share this answer
 
Comments
Patrick-Et. B. 24-Apr-19 7:47am    
Sadly I can't debug at the client system but I will try to rebuild it locally. Thank you for your detailled input.
OriginalGriff 24-Apr-19 8:19am    
If you can't debug, then add the same thing as logging statements to a flat file or DB and check it after the failure. It may be "old school" but it's how we used to do it before all these new-fangled IDEs! :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900