//Edit button pressed private void editTapeEditButton_Click(object sender, EventArgs e) { //check to see if project ID is a number if (commonMethod.StringIsANumber(editProjectIDTextbox.Text)) { AddToDatabase editDB = new AddToDatabase(); string projectNameEdit = ""; if (editProjectNameLabel.Text.Length > 0) { projectNameEdit = editProjectNameLabel.Text; } else { projectNameEdit = editTapeNameTextbox.Text; } //if there is text in tags textbox then add it on the end of the tag string if (editTagsTextbox.TextLength > 0 && !editTagList.Contains(editTagsTextbox.Text.ToLower().Replace(",", ""))) { editTagList.Add(editTagsTextbox.Text); } //Create new TapeDatabaseValues for edited entry TapeDatabaseValues newTapeValues = new TapeDatabaseValues( editTapeNameTextbox.Text, editTapeNumberUpDown.Value.ToString(), editProjectIDTextbox.Text, projectNameEdit, commonMethod.GetCameraNumber(editCameraDropdown.Text), String.Join(",", editTagList), commonMethod.ConvertDateFromDropdownForDB(editDateShotDate.Value), editTapeMasterListLabel.Text, editPersonDropdown.Text); //Check if user made a change if (CompareOldEditValues(newTapeValues)) { //Update entry and check to make sure it is updated if (editDB.UpdateTapeDatabase(newTapeValues, tapeValues)) { //Enrty Update Successful ClearEditControls(); tapeValues.Clear(); PopulateTapeList(); MakeBoxesVisible(); tapeListListView.Focus(); updateStatus.UpdateStatusBar("Values Updated in Database", mainform); } else { //Entry Update Unsuccessful updateStatus.UpdateStatusBar("There Was A Problem Updated Entry", mainform); } } else { //User Needs to change a value updateStatus.UpdateStatusBar("Must Change At Least One Value", mainform); } }else { //project ID was NOT a number updateStatus.UpdateStatusBar("Project ID must be a number", mainform); } }
/// <summary> /// Compares the old edit values. /// </summary> /// <param name="newValues">The new tape values.</param> /// <returns>True if they do not match</returns> private bool CompareOldEditValues(TapeDatabaseValues newValues) { //check to see if old and new values match if(tapeValues.ProjectId.Equals(newValues.ProjectId) && tapeValues.ProjectName.Equals(newValues.ProjectName) && tapeValues.TapeName.Equals(newValues.TapeName) && tapeValues.TapeNumber.Equals(newValues.TapeNumber) && tapeValues.Camera.Equals(newValues.Camera) && tapeValues.DateShot.Equals(newValues.DateShot) && tapeValues.TapeTags.Equals(newValues.TapeTags) && tapeValues.MasterArchive.Equals(newValues.MasterArchive) && tapeValues.PersonEntered.Equals(newValues.PersonEntered)) { return false; } else { return true; } }
/// <summary> /// Adds the tapes from file. /// </summary> /// <param name="worker">The worker.</param> /// <param name="importStream">The import stream.</param> /// <param name="ofd">The ofd.</param> /// <returns>true if succeeded, false if failed</returns> public static bool AddTapesFromFile(BackgroundWorker worker, Stream importStream, OpenFileDialog ofd) { List<TapeDatabaseValues> tapeList = new List<TapeDatabaseValues>(); TapeDatabaseValues tapeValues = new TapeDatabaseValues(); if ((importStream = ofd.OpenFile()) != null) { try { //items for string line; string newLine; char[] seperators = ",".ToCharArray(); //streamReader to read csv file StreamReader textReader = new StreamReader(importStream); while ((line = textReader.ReadLine()) != null) { newLine = line.Replace("-", ","); string[] lineArray = newLine.Split(seperators, 7); //check to make sure there are 2 parts to each line if (lineArray.Length > 1) { lineArray[0] = lineArray[0].Trim(); lineArray[1] = lineArray[1].Replace(',', '-').Trim(); tapeValues = new TapeDatabaseValues(lineArray[0], lineArray[1], lineArray[2], lineArray[3], commonMethod.GetCameraNumber(lineArray[4]), lineArray[6], commonMethod.ConvertDateFromDropdownForDB(Convert.ToDateTime(lineArray[5])), "", "Imported"); tapeList.Add(tapeValues); } else { //only one part, it will not add this value to database } } } catch (Exception error) { MainForm.LogFile(error.Message); } } int counter = 0; int progressCounter = 0; float queryCounter = 100.0f / tapeList.Count; float progress = 0.0f; string projectName = ""; try { SQLiteConnection projectConnection = new SQLiteConnection(database); projectConnection.Open(); SQLiteCommand command = new SQLiteCommand(projectConnection); foreach (TapeDatabaseValues value in tapeList) { //list for the master list query List<string> masterList = new List<string>(); //query db for names of masterlist for a project command.CommandText = "select master_tape from MasterArchiveVideos where project_id = @pid"; command.Parameters.Clear(); command.Parameters.AddWithValue("@pid", value.ProjectId); //read results and add values if any to a comma seperated string using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { masterList.Add(reader["master_tape"].ToString()); } value.MasterArchive = String.Join(",", masterList); } } //query db for the project name command.CommandText = "select project_name from Projects where project_id = @pid limit 1"; command.Parameters.Clear(); command.Parameters.AddWithValue("@pid", value.ProjectId); using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { projectName = reader["project_name"].ToString(); } //add value for addition based on if empty or not if (!projectName.Equals("")) { value.ProjectName = projectName; } } } //add values into tape database table command.Parameters.Clear(); command.CommandText = "insert into TapeDatabase (tape_name,tape_number,project_id, project_name,camera,tape_tags,date_shot,master_archive,person_entered) values (@tapeName,@tapeNumber,@projectID, @projectName,@camera,@tapeTags,@dateShot,@masterArchive,@person)"; command.Parameters.AddWithValue("@tapeName", value.TapeName); command.Parameters.AddWithValue("@tapeNumber", value.TapeNumber); command.Parameters.AddWithValue("@projectID", value.ProjectId); command.Parameters.AddWithValue("@projectName", value.ProjectName); command.Parameters.AddWithValue("@camera", value.Camera); command.Parameters.AddWithValue("@tapeTags", value.TapeTags); command.Parameters.AddWithValue("@dateShot", value.DateShot); command.Parameters.AddWithValue("@masterArchive", value.MasterArchive); command.Parameters.AddWithValue("@person", ComputerInfo.ComputerUser); if (command.ExecuteNonQuery() == 1) { //Success counter++; progressCounter++; if ((progressCounter * queryCounter) >= 1) { progress += (queryCounter * progressCounter); progressCounter = 0; worker.ReportProgress(Convert.ToInt32(progress)); Debug.WriteLine("Added: " + value.ProjectId + ", Progress = " + progress); } } else { //Failure } } if (counter > 0) { Debug.WriteLine(counter + " items added to database"); MainForm.LogFile(counter + " tapes added to database"); projectConnection.Close(); return true; } else { Debug.WriteLine("No Tapes added to database"); projectConnection.Close(); return false; } } catch (SQLiteException e) { MainForm.LogFile("Import Tapes Error: " + e.Message); return false; } }
//Delete entry button pressed private void tapeListDeleteEntryButton_Click(object sender, EventArgs e) { //Place delete tape groupbox and make visible if (tapeListListView.SelectedItems.Count == 1) { LoadTapeValuesFromList(); deleteProjectIDLabel.Text = tapeValues.ProjectId; deleteProjectNameLabel.Text = tapeValues.ProjectName; deleteTapeNameLabel.Text = tapeValues.TapeName; deleteTapeNumberLabel.Text = tapeValues.TapeNumber; deleteCameraLabel.Text = commonMethod.GetCameraName(tapeValues.Camera); //split csv int a list and display deleteTagList = tapeValues.TapeTags.Split(',').ToList(); DisplayTags("delete", deleteTagFlowLayoutPanel, deleteTagList); deleteDateShotLabel.Text = tapeValues.DateShot; deleteMasterArchiveLabel.Text = tapeValues.MasterArchive; deletePersonLabel.Text = tapeValues.PersonEntered; MakeBoxesVisible("delete"); }else if(tapeListListView.SelectedItems.Count > 1) { //multiple items selected in listview //Show message box to make sure user is to be deleted DialogResult deleteMessage = MessageBox.Show("Do you want to delete these " + tapeListListView.SelectedItems.Count + " entries?", "Deletion Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); //Check to see if user pressed yes or no if (deleteMessage == DialogResult.Yes) { //clear delete list if (tapesToDelete == null) { tapesToDelete = new List<TapeDatabaseValues>(); } else { tapesToDelete.Clear(); } //iterate over each item selected and save data in a value, then a list foreach (ListViewItem item in tapeListListView.SelectedItems) { TapeDatabaseValues value = new TapeDatabaseValues( item.SubItems[2].Text, item.SubItems[3].Text, item.SubItems[0].Text, item.SubItems[1].Text, commonMethod.GetCameraNumber(item.SubItems[4].Text), item.SubItems[5].Text, item.SubItems[6].Text, item.SubItems[7].Text, item.SubItems[8].Text, Convert.ToInt32(item.Tag) ); tapesToDelete.Add(value); } if (tapeListListView.SelectedItems.Count > 1 && tapesToDelete.Count > 0) { Debug.WriteLine("sending " + tapesToDelete.Count + " items to delete"); updateStatus.UpdateStatusBar(AddToDatabase.DeleteMultipleTapeSelected(tapesToDelete) + " items deleted", mainform); } PopulateTapeList(); MakeBoxesVisible(); } else if (deleteMessage == DialogResult.No) { //No Pressed, nothing will be done } } }
/// <summary> /// Gets all tables from imported database and inserts the information into the default database /// </summary> /// <param name="file">The file.</param> /// <param name="mainForm">The main form.</param> public static void GetTableName(string file, TNG_Database.MainForm mainForm) { List<string> allTables = new List<string>(); Debug.WriteLine("Starting to import"); //new database filename string fileDatabase = "Data Source=" + file + ";Version=3;"; Debug.WriteLine(fileDatabase); //progressbar status for each table int everyTable = 0; int progress = 0; try { //command to get all tables in database const string GET_TABLES_QUERY = "select name from sqlite_master where type='table'"; SQLiteConnection connection = new SQLiteConnection(fileDatabase); connection.Open(); SQLiteCommand command = new SQLiteCommand(GET_TABLES_QUERY, connection); using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { Debug.WriteLine("import has tables"); while (reader.Read()) { if (TNGTables.Contains(reader.GetString(0))) { allTables.Add(reader.GetString(0)); } } } } //close connection connection.Close(); }catch(SQLiteException e) { MainForm.LogFile("SQLite Error: " + e.Message); Debug.WriteLine("SQLite Error: " + e.Message); } try { //check to see if there are any tables returned if (!allTables.Count.Equals(0)) { //set progressbar status to division on count of tables everyTable = 100 / allTables.Count; Debug.WriteLine("Table Number #: " + everyTable); AddToDatabase addDB = new AddToDatabase(); foreach (string table in allTables) { //connection for new database SQLiteConnection connect = new SQLiteConnection(fileDatabase); connect.Open(); SQLiteCommand command = new SQLiteCommand(connect); command.CommandText = "Select * from " + table; switch (table) { case "TapeDatabase": case "DeleteTapeDatabase": //create values object List<TapeDatabaseValues> tapeList = new List<TapeDatabaseValues>(); TapeDatabaseValues tapeValues = new TapeDatabaseValues(); //execute query using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { tapeValues.Clear(); tapeValues.ProjectId = reader["project_id"].ToString(); tapeValues.ProjectName = reader["project_name"].ToString(); tapeValues.TapeName = reader["tape_name"].ToString(); tapeValues.TapeNumber = reader["tape_number"].ToString(); tapeValues.Camera = Convert.ToInt32(reader["camera"]); tapeValues.TapeTags = reader["tape_tags"].ToString(); tapeValues.DateShot = reader["date_shot"].ToString(); tapeValues.MasterArchive = reader["master_archive"].ToString(); tapeValues.PersonEntered = reader["person_entered"].ToString(); //add values to list tapeList.Add(tapeValues); } } } //Close import connection connect.Close(); //iterate over values to add to database foreach (TapeDatabaseValues values in tapeList) { if (table == "TapeDatabase") { //add value to database addDB.AddTapeDatabase(values); } else if(table == "DeleteTapeDatabase") { //Open up new connection SQLiteConnection deleteConnect = new SQLiteConnection(database); deleteConnect.Open(); command.Connection = deleteConnect; command.CommandText = "select count(*) from DeleteTapeDatabase where lower(project_name) = @projectName and project_id = @projectID"; command.Parameters.Clear(); command.Parameters.AddWithValue("@projectID", values.ProjectId); command.Parameters.AddWithValue("@projectName", values.ProjectName.ToLower()); Int32 check = Convert.ToInt32(command.ExecuteScalar()); //Make sure that entry doesn't exist already if (check < 1) { //add value to deleted database //There is not an entry go ahead and insert new row command.CommandText = "insert into DeleteTapeDatabase (tape_name, tape_number, project_id, project_name, camera, tape_tags, date_shot, master_archive, person_entered) values (@tapeName, @tapeNumber, @projectID, @projectName, @camera, @tapeTags, @dateShot, @masterArchive, @personEntered)"; command.Parameters.Clear(); command.Parameters.AddWithValue("@tapeName", values.TapeName); command.Parameters.AddWithValue("@tapeNumber", values.TapeNumber); command.Parameters.AddWithValue("@projectID", values.ProjectId); command.Parameters.AddWithValue("@projectName", values.ProjectName); command.Parameters.AddWithValue("@camera", values.Camera); command.Parameters.AddWithValue("@tapeTags", values.TapeTags); command.Parameters.AddWithValue("@dateShot", values.DateShot); command.Parameters.AddWithValue("@masterArchive", values.MasterArchive); command.Parameters.AddWithValue("@personEntered", values.PersonEntered); command.ExecuteNonQuery(); } deleteConnect.Close(); } } UpdateProgess(everyTable, mainForm); progress += everyTable; break; case "Projects": case "DeleteProjects": //create values object List<ProjectValues> projectList = new List<ProjectValues>(); ProjectValues projectValues = new ProjectValues(); //execute query using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { projectValues.Clear(); projectValues.ProjectID = reader["project_id"].ToString(); projectValues.Projectname = reader["project_name"].ToString(); //add values to list projectList.Add(projectValues); } } } //Close import connection connect.Close(); //iterate over values to add to database foreach (ProjectValues values in projectList) { if (table == "Projects") { //add value to database addDB.AddProjects(values); } else if (table == "DeleteProjects") { //Open up new connection SQLiteConnection deleteConnect = new SQLiteConnection(database); deleteConnect.Open(); command.Connection = deleteConnect; command.CommandText = "select count(*) from DeleteProjects where lower(project_name) = @projectName and project_id = @projectID"; command.Parameters.Clear(); command.Parameters.AddWithValue("@projectID", values.ProjectID); command.Parameters.AddWithValue("@projectName", values.Projectname.ToLower()); Int32 check = Convert.ToInt32(command.ExecuteScalar()); //Make sure that entry doesn't exist already if (check < 1) { //add value to deleted database //There is not an entry go ahead and insert new row command.CommandText = "insert into DeleteProjects (project_id, project_name) values (@projectID, @projectName)"; command.Parameters.Clear(); command.Parameters.AddWithValue("@projectID", values.ProjectID); command.Parameters.AddWithValue("@projectName", values.Projectname); command.ExecuteNonQuery(); } deleteConnect.Close(); } } UpdateProgess(everyTable, mainForm); progress += everyTable; break; case "People": case "DeletePeople": //create values object List<PeopleValues> peopleList = new List<PeopleValues>(); PeopleValues peopleValues = new PeopleValues(); //execute query using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { peopleValues.Clear(); peopleValues.PersonName = reader["person_name"].ToString(); //add values to list peopleList.Add(peopleValues); } } } //Close import connection connect.Close(); //iterate over values to add to database foreach (PeopleValues values in peopleList) { if (table == "People") { //add value to database addDB.AddPerson(values); } else if (table == "DeletePeople") { //Open up new connection SQLiteConnection deleteConnect = new SQLiteConnection(database); deleteConnect.Open(); command.Connection = deleteConnect; command.CommandText = "select count(*) from DeletePeople where lower(person_name) = @personName"; command.Parameters.Clear(); command.Parameters.AddWithValue("@personName", values.PersonName.ToLower()); Int32 check = Convert.ToInt32(command.ExecuteScalar()); //Make sure that entry doesn't exist already if (check < 1) { //add value to deleted database //There is not an entry go ahead and insert new row command.CommandText = "insert into DeletePeole (person_name) values (@personName)"; command.Parameters.Clear(); command.Parameters.AddWithValue("@personName", values.PersonName); command.ExecuteNonQuery(); } deleteConnect.Close(); } } UpdateProgess(everyTable, mainForm); progress += everyTable; break; case "MasterList": case "DeleteMasterList": //create values object List<MasterListValues> masterTapeList = new List<MasterListValues>(); MasterListValues masterTapeValues = new MasterListValues(); //execute query using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { masterTapeValues.Clear(); masterTapeValues.MasterArchive = reader["master_archive"].ToString(); masterTapeValues.MasterMedia = Convert.ToInt32(reader["master_media"]); //add values to list masterTapeList.Add(masterTapeValues); } } } //Close import connection connect.Close(); //iterate over values to add to database foreach (MasterListValues values in masterTapeList) { if (table == "MasterList") { //add value to database addDB.AddMasterList(values); } else if (table == "DeleteMasterList") { //Open up new connection SQLiteConnection deleteConnect = new SQLiteConnection(database); deleteConnect.Open(); command.Connection = deleteConnect; command.CommandText = "select count(*) from DeleteMasterList where lower(master_archive) = @masterArchive and master_media = @masterMedia"; command.Parameters.Clear(); command.Parameters.AddWithValue("@masterArchive", values.MasterArchive.ToLower()); command.Parameters.AddWithValue("@masterMedia", values.MasterMedia); Int32 check = Convert.ToInt32(command.ExecuteScalar()); //Make sure that entry doesn't exist already if (check < 1) { //add value to deleted database //There is not an entry go ahead and insert new row command.CommandText = "insert into DeleteMasterList (master_archive, master_media) values (@masterArchive, @masterMedia)"; command.Parameters.Clear(); command.Parameters.AddWithValue("@masterArchive", values.MasterArchive); command.Parameters.AddWithValue("@masterMedia", values.MasterMedia); command.ExecuteNonQuery(); } deleteConnect.Close(); } } UpdateProgess(everyTable, mainForm); progress += everyTable; break; case "MasterArchiveVideos": case "DeleteMasterArchiveVideos": //create values object List<MasterArchiveVideoValues> masterArchiveVideoList = new List<MasterArchiveVideoValues>(); MasterArchiveVideoValues MasterArchiveVideoValues = new MasterArchiveVideoValues(); //execute query using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { MasterArchiveVideoValues.Clear(); MasterArchiveVideoValues.ProjectId = reader["project_id"].ToString(); MasterArchiveVideoValues.VideoName = reader["video_name"].ToString(); MasterArchiveVideoValues.MasterTape = reader["master_tape"].ToString(); MasterArchiveVideoValues.ClipNumber = reader["clip_number"].ToString(); //add values to list masterArchiveVideoList.Add(MasterArchiveVideoValues); } } } //Close import connection connect.Close(); //iterate over values to add to database foreach (MasterArchiveVideoValues values in masterArchiveVideoList) { if (table == "MasterArchiveVideos") { //add value to database addDB.AddMasterArchiveVideo(values); } else if (table == "DeleteMasterArchiveVideos") { //Open up new connection SQLiteConnection deleteConnect = new SQLiteConnection(database); deleteConnect.Open(); command.Connection = deleteConnect; command.CommandText = "select count(*) from DeleteMasterArchiveVideos where project_id = @projectID and video_name = @videoName and clip_number = @clipNumber and master_tape = @masterTape"; command.Parameters.Clear(); command.Parameters.AddWithValue("@projectID", values.ProjectId); command.Parameters.AddWithValue("@videoName", values.VideoName); command.Parameters.AddWithValue("@masterTape", values.MasterTape); command.Parameters.AddWithValue("@clipNumber", values.ClipNumber); Int32 check = Convert.ToInt32(command.ExecuteScalar()); //Make sure that entry doesn't exist already if (check < 1) { //add value to deleted database //There is not an entry go ahead and insert new row command.CommandText = "insert into DeleteMasterArchiveVideos (project_id, video_name, master_tape, clip_number) values (@projectID, @videoName, @masterTape, @clipNumber)"; command.Parameters.Clear(); command.Parameters.AddWithValue("@projectID", values.ProjectId); command.Parameters.AddWithValue("@videoName", values.VideoName); command.Parameters.AddWithValue("@masterTape", values.MasterTape); command.Parameters.AddWithValue("@clipNumber", values.ClipNumber); command.ExecuteNonQuery(); } deleteConnect.Close(); } } UpdateProgess(everyTable, mainForm); progress += everyTable; break; case "ComputerInfo": //create values object List<ComputerInfoValues> computerInfoList = new List<ComputerInfoValues>(); ComputerInfoValues computerInfoValues = new ComputerInfoValues(); //execute query using (SQLiteDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { computerInfoValues.Clear(); computerInfoValues.UniqueHash = reader["computer_hash"].ToString(); computerInfoValues.ComputerName = reader["computer_name"].ToString(); computerInfoValues.ComputerUser = reader["computer_user"].ToString(); //add values to list computerInfoList.Add(computerInfoValues); } } } //Close import connection connect.Close(); //iterate over values to add to database foreach (ComputerInfoValues values in computerInfoList) { //add value to database addDB.AddComputerInfo(values); } UpdateProgess(everyTable, mainForm); progress += everyTable; break; } } } }catch(SQLiteException e) { MainForm.LogFile("SQLite Error: " + e.Message); Debug.WriteLine("SQLite Table Error: " + e.Message); } if((100 - progress) > 0) { UpdateProgess((100-progress), mainForm); } }