//Add Groupbox Add button clicked
private void addMasterListAddButton_Click(object sender, EventArgs e)
{
//Add Entry button pressed, gather info and add entry
if(addMasterListNameTextbox.Text.Length > 0)
{
AddToDatabase database = new AddToDatabase();
sendValues = new MasterListValues(addMasterListNameTextbox.Text, commonMethod.GetCameraNumber(cameraAddMasterCombo.Text));
if (database.AddMasterList(sendValues))
{
//Add entry success
updateStatus.UpdateStatusBar("New Master Tape added to database", mainform);
addMasterListNameTextbox.Clear();
cameraAddMasterCombo.SelectedIndex = 0;
MakeGroupboxesInvisible();
PopulateMasterList();
}
else
{
//Add Entry failure
updateStatus.UpdateStatusBar("Entry was not added to database", mainform);
}
}
}
/// <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);
}
}