private void ProcessDataBaseCorrection () { WCFStandardsNS.WCFStandards DataBase = new WCFStandardsNS.WCFStandards (); DataSet WrongVideoFiles = DataBase.GetCommonDataSet ("SELECT DISTINCT FoldedArchivePath, COUNT(*) AS Number " + "FROM VideoFiles " + "GROUP BY FoldedArchivePath " + "ORDER BY FoldedArchivePath"); int NumberOfNonAssignedVideoFiles = 0; int NumberOfSingleAssignedVideoFiles = 0; int NumberOfMultipleAssignedVideoFiles = 0; int OverallNumberOfWrongEntries = 0; foreach (DataRow WrongEntry in WrongVideoFiles.Tables ["VideoFiles"].Rows) { int NumberOfWrongEntries = Convert.ToInt32 (WrongEntry ["Number"]); if (NumberOfWrongEntries < 2) continue; OverallNumberOfWrongEntries++; String WrongFoldedPath = WrongEntry ["FoldedArchivePath"].ToString (); DataSet VideoFilesPlusMaterial = DataBase.GetCommonMultiDataSet (new String[] { String.Format ( "Select * from VideoFiles where FoldedArchivePath = '{0}' order by ModifyTimeStamp", WrongFoldedPath), String.Format ( "Select * from StructuredMaterialDataBase where FoldedArchivePath = '{0}' order by ArchiveID", WrongFoldedPath) }); if (WCFStandardsNS.WCFStandards.GetErrorMessages(VideoFilesPlusMaterial) != null) { String[] ErrorStrings = WCFStandardsNS.WCFStandards.GetErrorMessages (VideoFilesPlusMaterial); } List<String> FoundIDs = new List<string> (); int NumberOfAssignedVideoFiles = 0; String ArchiveIDs = String.Empty; foreach (DataRow VideoRow in VideoFilesPlusMaterial.Tables ["VideoFiles"].Rows) { String FileID = VideoRow ["FileID"].ToString (); DataSet Zuordnung = DataBase.GetCommonDataSet (String.Format ("Select * from VideoFilesZuBeitraege where FileID = '{0}'", FileID)); String ModifyTimeStamp = VideoRow ["ModifyTimeStamp"].ToString (); if (Zuordnung.Tables ["VideoFilesZuBeitraege"].Rows.Count == 0) { FoundIDs.Add (FileID); } else { NumberOfAssignedVideoFiles++; FoundIDs.Add (FileID); foreach (DataRow ZuordnungRow in Zuordnung.Tables ["VideoFilesZuBeitraege"].Rows) { } } } foreach (DataRow MaterialRow in VideoFilesPlusMaterial.Tables ["StructuredMaterialDataBase"].Rows) { ArchiveIDs = ArchiveIDs + MaterialRow ["ArchiveID"].ToString () + " "; } if (NumberOfAssignedVideoFiles > 1) { NumberOfMultipleAssignedVideoFiles++; MessageBox.Show ("Bei \"" + WrongFoldedPath + "\" ist mehr als ein VideoFile Entry zugeordnet\r\n" + String.Join ("\r\n", FoundIDs.ToArray ())); } if (NumberOfAssignedVideoFiles== 1) { NumberOfSingleAssignedVideoFiles++; MessageBox.Show ("Bei \"" + WrongFoldedPath + "\" ist ein VideoFile Entry zugeordnet\r\n" + String.Join ("\r\n", FoundIDs.ToArray ())); } if (NumberOfAssignedVideoFiles == 0) { NumberOfNonAssignedVideoFiles++; DataSet MaterialToChange = DataBase.GetCommonDataSet (String.Format ("Select * from StructuredMaterialDataBase where FoldedArchivePath = '{0}'", WrongFoldedPath)); String IDToRetain = FoundIDs [0]; FoundIDs.RemoveAt (0); foreach (DataRow MaterialRowToChange in MaterialToChange.Tables ["StructuredMaterialDataBase"].Rows) { String MaterialID = MaterialRowToChange ["ID"].ToString (); String OldFileID = MaterialRowToChange ["FileID"].ToString (); if (OldFileID != IDToRetain) DataBase.RunSQLBatch (String.Format ("Update StructuredMaterialDataBase set FileID = '{0}' where ID = '{1}'", IDToRetain, MaterialID)); } foreach (String IDToDelete in FoundIDs) { DataBase.RunSQLBatch (String.Format ("Delete VideoFiles where FileID = '{0}'", IDToDelete)); } //MessageBox.Show ("Bei \"" + WrongFoldedPath + "\" ist kein VideoFile Entry zugeordnet\r\n" // + String.Join ("\r\n", FoundIDs.ToArray ())); } } MessageBox.Show ("Insgesamt falsch = " + Convert.ToString (OverallNumberOfWrongEntries) + "\r\n" + "Non Assigned = " + Convert.ToString (NumberOfNonAssignedVideoFiles) + "\r\n" + "Single Assigned = " + Convert.ToString (NumberOfSingleAssignedVideoFiles) + "\r\n" + "Multi Assigned = " + Convert.ToString (NumberOfMultipleAssignedVideoFiles) + "\r\n"); }