private void executeInsert(String filename, String ext, Microsoft.Win32.OpenFileDialog dlg, List <string> folderDetails, byte[] rawData) { var path = Path.GetExtension(filename); filename = Path.GetFileName(filename); string speaker = ""; if (dlg != null) { speaker = Path.GetFileNameWithoutExtension(dlg.SafeFileName).Substring(0, 4); } else { speaker = "Template"; } using (DBConnection db = new DBConnection()) { if (folderDetails != null) { MySqlCommand comm = new MySqlCommand(); comm = new MySqlCommand(); comm.CommandText = "INSERT IGNORE INTO Project (PID, DateCreated, Description) VALUES(@PID, @dateCreated, @description)"; comm.Parameters.AddWithValue("@PID", folderDetails.First()); comm.Parameters.AddWithValue("@dateCreated", DateTime.Now.ToString()); if (folderDetails.Count == 2) { comm.Parameters.AddWithValue("@description", folderDetails.Last()); } else { comm.Parameters.AddWithValue("@description", "No description given"); } db.insertIntoDB(comm); System.Console.WriteLine(folderDetails.First() + " . " + filename); comm.CommandText = "INSERT INTO File (PID, Name, FileType, Speaker) VALUES(@fPID, @Name, @Type, @Speaker)"; comm.Parameters.AddWithValue("@fPID", folderDetails.First()); comm.Parameters.AddWithValue("@Name", Path.GetFileNameWithoutExtension(filename)); comm.Parameters.AddWithValue("@Type", path); comm.Parameters.AddWithValue("@Speaker", speaker); db.insertIntoDB(comm); comm = new MySqlCommand(); comm.CommandText = "INSERT INTO FileData (FID, FileData) VALUES (LAST_INSERT_ID(), @FileData)"; comm.Parameters.AddWithValue("@FileData", rawData); db.insertIntoDB(comm); } } }
//Testing, randomly macthes analysis to files private void randomlyMatchAnalysis() { using (DBConnection db = new DBConnection()) { var cmd = new MySqlCommand(); cmd.CommandText = @"SELECT FID FROM File"; var tableF = db.getFromDB(cmd); cmd = new MySqlCommand(); cmd.CommandText = @"SELECT AID FROM Analysis"; var tableA = db.getFromDB(cmd); MessageBox.Show(tableA.Rows.Count.ToString()); foreach (DataRow dr in tableF.Rows) { foreach (DataRow drA in tableA.Rows) { Random random = new Random(); int randomNumber = random.Next(0, 10); System.Diagnostics.Debug.WriteLine(randomNumber); if (randomNumber < 2) { cmd = new MySqlCommand(); cmd.CommandText = "INSERT IGNORE INTO File2Analysis (File_FID, Analysis_AID) VALUES (@FID2, @AID)"; cmd.Parameters.AddWithValue("@FID2", dr["FID"].ToString()); cmd.Parameters.AddWithValue("@AID", drA["AID"].ToString()); db.insertIntoDB(cmd); } } } } MessageBox.Show("DONE"); }
private void addIndividualFile(DirectoryInfo dir, List <String> projectDetails, string projDescription, DBConnection db) { byte[] rawData; FileInfo[] files = new DirectoryInfo(dir.FullName).GetFiles("*.*", SearchOption.AllDirectories); //Create projects table var cmd = new MySqlCommand(); cmd.CommandText = "INSERT IGNORE INTO Project (PID, DateCreated, Description) VALUES (@PID, @date, @desc)"; //ignore = Dont insert dups cmd.Parameters.AddWithValue("@PID", projectDetails.First()); cmd.Parameters.AddWithValue("@date", DateTime.Today); cmd.Parameters.AddWithValue("@desc", projDescription); db.insertIntoDB(cmd); foreach (FileInfo file in files) { string fileName = Path.GetFileNameWithoutExtension(file.FullName); string ext = Path.GetExtension(file.Name).Replace(".", ""); rawData = File.ReadAllBytes(@file.FullName); //The raw file data as a byte array string speaker = fileName.Substring(0, 4); //Add file paths to the above table cmd = new MySqlCommand(); cmd.CommandText = "INSERT INTO File (PID, Name, FileType, Speaker) VALUES (@PID, @Name, @FileType, @Speaker)"; cmd.Parameters.AddWithValue("@PID", projectDetails.First()); cmd.Parameters.AddWithValue("@Name", fileName); cmd.Parameters.AddWithValue("@FileType", file.Extension); cmd.Parameters.AddWithValue("@Speaker", speaker); db.insertIntoDB(cmd); //Add file data cmd = new MySqlCommand(); cmd.CommandText = "INSERT INTO FileData (FID, FileData) VALUES (LAST_INSERT_ID(), @data)"; cmd.Parameters.AddWithValue("@data", rawData); db.insertIntoDB(cmd); } }
private void ButtonDelete_Click(object sender, RoutedEventArgs e) { MessageBoxResult messageBoxResult = System.Windows.MessageBox.Show("Are you sure?", "Delete Confirmation", System.Windows.MessageBoxButton.YesNo); if (messageBoxResult == MessageBoxResult.No) { return; } var listOfItems = dataGridFiles.SelectedItems; //Delete each selected file and its related records foreach (var item in listOfItems) { var speakerID = (item as Speaker).ID; using (DBConnection db = new DBConnection()) { MySqlCommand comm = new MySqlCommand(); comm.CommandText = "DELETE FROM FileData WHERE FID=@speakerID"; comm.Parameters.AddWithValue("@speakerID", speakerID); db.insertIntoDB(comm); comm = new MySqlCommand(); comm.CommandText = "DELETE FROM File WHERE FID=@speakerID"; comm.Parameters.AddWithValue("@speakerID", speakerID); db.insertIntoDB(comm); comm = new MySqlCommand(); comm.CommandText = "DELETE FROM File2Analysis WHERE File_FID=@speakerID"; comm.Parameters.AddWithValue("@speakerID", speakerID); db.insertIntoDB(comm); } } //Reload grid (DOES SAVE EXPANSION STATE) dgl.loadSpeakers((listOfItems[1] as Speaker).PID); dataGridFiles.ItemsSource = new ListCollectionView(dgl.getCollection("S")); buildDatagridGroups(new ListCollectionView(dgl.getCollection("S"))); }
private void Button_Click(object sender, RoutedEventArgs e) { // Open file system to select file(s) Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.Multiselect = true; Nullable <bool> result = dlg.ShowDialog(); // Display OpenFileDialog by calling ShowDialog method byte[] rawData; List <Tuple <string, byte[]> > dataList = new List <Tuple <string, byte[]> >(); string ext = ""; string filename = ""; // Add all files selected into the the db. If multiple files added, project destination is the same. foreach (String file in dlg.FileNames) { // Get the selected file name and display in a TextBox if (result.HasValue == true && result.Value == true) { rawData = File.ReadAllBytes(file); dataList.Add(Tuple.Create(file, rawData)); ext = Path.GetExtension(file); filename = file; } } AnalysisMsgPrompt a = new AnalysisMsgPrompt(new DataGridLoader(), null); if (a.ShowDialog() == true) { dgl.loadSpeakers(a.PID); rowS = dgl.getCollection("S"); foreach (var elem in rowS.ToList()) { ((dynamic)rowS).Add((Speaker)elem); } foreach (var dataItem in dataList) { var comm = new MySqlCommand(); filename = Path.GetFileName(dataItem.Item1); using (DBConnection db = new DBConnection()) { comm.CommandText = "INSERT INTO Analysis (AID, Description, FileData, FileType) VALUES(@AID, @Desc, @FileAsBlob, @FileType)"; comm.Parameters.AddWithValue("@AID", filename); if (a.Desc.Equals("")) { comm.Parameters.AddWithValue("@Desc", "No description"); } else { //Add to analysis table comm.CommandText = "create table if not exists analysis (AID varchar(150) primary key, File mediumblob, Description varchar(500))"; comm.ExecuteNonQuery(); comm.CommandText = "INSERT INTO analysis (AID, File, Description) VALUES(@AID, @FileAsBlob, @Desc)"; comm.Parameters.AddWithValue("@AID", dataItem.Item1); comm.Parameters.AddWithValue("@FileAsBlob", dataItem.Item2); if (a.Desc.Equals("")) { comm.Parameters.AddWithValue("@Desc", "No description"); } else { comm.Parameters.AddWithValue("@Desc", a.Desc); } comm.ExecuteNonQuery(); //Add to the mapping table(to link with speaker) List <Row> startsWithAge = rowS.Where(s => ((Speaker)s).SpeakerName.StartsWith(a.Age)).ToList(); MessageBox.Show(a.Age); foreach (var row in rowS) { //comm.CommandText = "create table if not exists files2analysis (AID varchar(150) primary key, ID varchar(150) primary key)"; //comm.ExecuteNonQuery(); if (((Speaker)row).SpeakerName.StartsWith(a.Age)) { db.insertIntoDB(comm); } comm.CommandText = "INSERT IGNORE INTO files2analysis (ID, AID) VALUES (@ID2, @AID2)"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@ID2", ((Speaker)row).ID); comm.Parameters.AddWithValue("@AID2", dataItem.Item1); comm.ExecuteNonQuery(); } HashSet <Tuple <String, String> > uniqueAnalysis = new HashSet <Tuple <String, String> >(); HashSet <Tuple <String, String> > uniqueRowName = new HashSet <Tuple <String, String> >(); string previous = ""; foreach (var row in rowS) { if (!((Speaker)row).Name.Equals(previous)) { previous = ((Speaker)row).Name; uniqueAnalysis.Add(Tuple.Create(((Speaker)row).Name, ((Speaker)row).ID)); } } foreach (var uRow in uniqueAnalysis) { if ((uRow.Item1.StartsWith(a.Age))) { comm.CommandText = "INSERT IGNORE INTO File2Analysis (File_FID, Analysis_AID) VALUES (@FileID, @AID)"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@FileID", uRow.Item2); comm.Parameters.AddWithValue("@AID", filename); db.insertIntoDB(comm); } } } } } } }
//Testing, randomly macthes analysis to files private void randomlyMatchAnalysis() { using (DBConnection db = new DBConnection()) { var cmd = new MySqlCommand(); cmd.CommandText = @"SELECT FID FROM File"; var tableF = db.getFromDB(cmd); cmd = new MySqlCommand(); cmd.CommandText = @"SELECT AID FROM Analysis"; var tableA = db.getFromDB(cmd); MessageBox.Show(tableA.Rows.Count.ToString()); foreach (DataRow dr in tableF.Rows) { foreach (DataRow drA in tableA.Rows) { Random random = new Random(); int randomNumber = random.Next(0, 10); System.Diagnostics.Debug.WriteLine(randomNumber); if (randomNumber < 2) { cmd = new MySqlCommand(); cmd.CommandText = "INSERT IGNORE INTO File2Analysis (File_FID, Analysis_AID) VALUES (@FID2, @AID)"; cmd.Parameters.AddWithValue("@FID2", dr["FID"].ToString()); cmd.Parameters.AddWithValue("@AID", drA["AID"].ToString()); db.insertIntoDB(cmd); } } } } MessageBox.Show("DONE"); }
private void executeInsert(String filename, String ext, Microsoft.Win32.OpenFileDialog dlg, List<string> folderDetails, byte[] rawData) { var path = Path.GetExtension(filename); filename = Path.GetFileName(filename); string speaker = ""; if (dlg != null) { speaker = Path.GetFileNameWithoutExtension(dlg.SafeFileName).Substring(0, 4); } else { speaker = "Template"; } using (DBConnection db = new DBConnection()) { if (folderDetails != null) { MySqlCommand comm = new MySqlCommand(); comm = new MySqlCommand(); comm.CommandText = "INSERT IGNORE INTO Project (PID, DateCreated, Description) VALUES(@PID, @dateCreated, @description)"; comm.Parameters.AddWithValue("@PID", folderDetails.First()); comm.Parameters.AddWithValue("@dateCreated", DateTime.Now.ToString()); if (folderDetails.Count == 2) { comm.Parameters.AddWithValue("@description", folderDetails.Last()); } else { comm.Parameters.AddWithValue("@description", "No description given"); } db.insertIntoDB(comm); System.Console.WriteLine(folderDetails.First() + " . " + filename); comm.CommandText = "INSERT INTO File (PID, Name, FileType, Speaker) VALUES(@fPID, @Name, @Type, @Speaker)"; comm.Parameters.AddWithValue("@fPID", folderDetails.First()); comm.Parameters.AddWithValue("@Name", Path.GetFileNameWithoutExtension(filename)); comm.Parameters.AddWithValue("@Type", path); comm.Parameters.AddWithValue("@Speaker", speaker); db.insertIntoDB(comm); comm = new MySqlCommand(); comm.CommandText = "INSERT INTO FileData (FID, FileData) VALUES (LAST_INSERT_ID(), @FileData)"; comm.Parameters.AddWithValue("@FileData", rawData); db.insertIntoDB(comm); } } }
private void Button_Click(object sender, RoutedEventArgs e) { // Open file system to select file(s) Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.Multiselect = true; Nullable<bool> result = dlg.ShowDialog(); // Display OpenFileDialog by calling ShowDialog method byte[] rawData; List<Tuple<string, byte[]>> dataList = new List<Tuple<string, byte[]>>(); string ext = ""; string filename = ""; // Add all files selected into the the db. If multiple files added, project destination is the same. foreach (String file in dlg.FileNames) { // Get the selected file name and display in a TextBox if (result.HasValue == true && result.Value == true) { rawData = File.ReadAllBytes(file); dataList.Add(Tuple.Create(file, rawData)); ext = Path.GetExtension(file); filename = file; } } AnalysisMsgPrompt a = new AnalysisMsgPrompt(new DataGridLoader(), null); if (a.ShowDialog() == true) { dgl.loadSpeakers(a.PID); rowS = dgl.getCollection("S"); foreach (var elem in rowS.ToList()) { ((dynamic)rowS).Add((Speaker)elem); } foreach (var dataItem in dataList) { var comm = new MySqlCommand(); filename = Path.GetFileName(dataItem.Item1); using (DBConnection db = new DBConnection()) { comm.CommandText = "INSERT INTO Analysis (AID, Description, FileData, FileType) VALUES(@AID, @Desc, @FileAsBlob, @FileType)"; comm.Parameters.AddWithValue("@AID", filename); if (a.Desc.Equals("")) { comm.Parameters.AddWithValue("@Desc", "No description"); } else { //Add to analysis table comm.CommandText = "create table if not exists analysis (AID varchar(150) primary key, File mediumblob, Description varchar(500))"; comm.ExecuteNonQuery(); comm.CommandText = "INSERT INTO analysis (AID, File, Description) VALUES(@AID, @FileAsBlob, @Desc)"; comm.Parameters.AddWithValue("@AID", dataItem.Item1); comm.Parameters.AddWithValue("@FileAsBlob", dataItem.Item2); if (a.Desc.Equals("")) { comm.Parameters.AddWithValue("@Desc", "No description"); } else { comm.Parameters.AddWithValue("@Desc", a.Desc); } comm.ExecuteNonQuery(); //Add to the mapping table(to link with speaker) List<Row> startsWithAge = rowS.Where(s => ((Speaker)s).SpeakerName.StartsWith(a.Age)).ToList(); MessageBox.Show(a.Age); foreach (var row in rowS) { //comm.CommandText = "create table if not exists files2analysis (AID varchar(150) primary key, ID varchar(150) primary key)"; //comm.ExecuteNonQuery(); if (((Speaker)row).SpeakerName.StartsWith(a.Age)) { db.insertIntoDB(comm); } comm.CommandText = "INSERT IGNORE INTO files2analysis (ID, AID) VALUES (@ID2, @AID2)"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@ID2", ((Speaker)row).ID); comm.Parameters.AddWithValue("@AID2", dataItem.Item1); comm.ExecuteNonQuery(); } HashSet<Tuple<String, String>> uniqueAnalysis = new HashSet<Tuple<String, String>>(); HashSet<Tuple<String, String>> uniqueRowName = new HashSet<Tuple<String, String>>(); string previous = ""; foreach (var row in rowS) { if (!((Speaker)row).Name.Equals(previous)) { previous = ((Speaker)row).Name; uniqueAnalysis.Add(Tuple.Create(((Speaker)row).Name, ((Speaker)row).ID)); } } foreach (var uRow in uniqueAnalysis) { if ((uRow.Item1.StartsWith(a.Age))) { comm.CommandText = "INSERT IGNORE INTO File2Analysis (File_FID, Analysis_AID) VALUES (@FileID, @AID)"; comm.Parameters.Clear(); comm.Parameters.AddWithValue("@FileID", uRow.Item2); comm.Parameters.AddWithValue("@AID", filename); db.insertIntoDB(comm); } } } } } } }
private void ButtonDelete_Click(object sender, RoutedEventArgs e) { MessageBoxResult messageBoxResult = System.Windows.MessageBox.Show("Are you sure?", "Delete Confirmation", System.Windows.MessageBoxButton.YesNo); if (messageBoxResult == MessageBoxResult.No) { return; } var listOfItems = dataGridFiles.SelectedItems; //Delete each selected file and its related records foreach (var item in listOfItems) { var speakerID = (item as Speaker).ID; using (DBConnection db = new DBConnection()) { MySqlCommand comm = new MySqlCommand(); comm.CommandText = "DELETE FROM FileData WHERE FID=@speakerID"; comm.Parameters.AddWithValue("@speakerID", speakerID); db.insertIntoDB(comm); comm = new MySqlCommand(); comm.CommandText = "DELETE FROM File WHERE FID=@speakerID"; comm.Parameters.AddWithValue("@speakerID", speakerID); db.insertIntoDB(comm); comm = new MySqlCommand(); comm.CommandText = "DELETE FROM File2Analysis WHERE File_FID=@speakerID"; comm.Parameters.AddWithValue("@speakerID", speakerID); db.insertIntoDB(comm); } } //Reload grid (DOES SAVE EXPANSION STATE) dgl.loadSpeakers((listOfItems[1] as Speaker).PID); dataGridFiles.ItemsSource = new ListCollectionView(dgl.getCollection("S")); buildDatagridGroups(new ListCollectionView(dgl.getCollection("S"))); }
private void addIndividualFile(DirectoryInfo dir, List<String> projectDetails, string projDescription, DBConnection db) { byte[] rawData; FileInfo[] files = new DirectoryInfo(dir.FullName).GetFiles("*.*", SearchOption.AllDirectories); //Create projects table var cmd = new MySqlCommand(); cmd.CommandText = "INSERT IGNORE INTO Project (PID, DateCreated, Description) VALUES (@PID, @date, @desc)"; //ignore = Dont insert dups cmd.Parameters.AddWithValue("@PID", projectDetails.First()); cmd.Parameters.AddWithValue("@date", DateTime.Today); cmd.Parameters.AddWithValue("@desc", projDescription); db.insertIntoDB(cmd); foreach (FileInfo file in files) { string fileName = Path.GetFileNameWithoutExtension(file.FullName); string ext = Path.GetExtension(file.Name).Replace(".", ""); rawData = File.ReadAllBytes(@file.FullName); //The raw file data as a byte array string speaker = fileName.Substring(0, 4); //Add file paths to the above table cmd = new MySqlCommand(); cmd.CommandText = "INSERT INTO File (PID, Name, FileType, Speaker) VALUES (@PID, @Name, @FileType, @Speaker)"; cmd.Parameters.AddWithValue("@PID", projectDetails.First()); cmd.Parameters.AddWithValue("@Name", fileName); cmd.Parameters.AddWithValue("@FileType", file.Extension); cmd.Parameters.AddWithValue("@Speaker", speaker); db.insertIntoDB(cmd); //Add file data cmd = new MySqlCommand(); cmd.CommandText = "INSERT INTO FileData (FID, FileData) VALUES (LAST_INSERT_ID(), @data)"; cmd.Parameters.AddWithValue("@data", rawData); db.insertIntoDB(cmd); } }