/// <summary>
/// Adds all projects from a file.
/// </summary>
/// <param name="values">The values.</param>
/// <returns>true if succeeded, false if failed</returns>
public static bool AddProjectsFromFile(BackgroundWorker worker, Stream importStream, OpenFileDialog ofd)
{
//List of Projectvalues to send to database
List<ProjectValues> projectList = new List<ProjectValues>();
ProjectValues values = new ProjectValues();
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, 2);
//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();
values = new ProjectValues(lineArray[0], lineArray[1]);
projectList.Add(values);
}
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 / projectList.Count;
float progress = 0.0f;
try
{
SQLiteConnection projectConnection = new SQLiteConnection(database);
projectConnection.Open();
foreach (ProjectValues value in projectList)
{
string query = "insert into Projects (project_id, project_name) values (@projectID, @projectName)";
SQLiteCommand command = new SQLiteCommand(query, projectConnection);
command.Parameters.AddWithValue("@projectID", value.ProjectID);
command.Parameters.AddWithValue("@projectName", value.Projectname);
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 + " projects added to database");
projectConnection.Close();
return true;
}
else
{
Debug.WriteLine("No projects added to database");
projectConnection.Close();
return false;
}
}
catch (SQLiteException e)
{
MainForm.LogFile("Import Projects Error: " + e.Message);
return false;
}
}
/// <summary>
/// Gets all project items.
/// </summary>
/// <returns></returns>
public static List<ProjectValues> GetAllProjectItems()
{
//List item to return
List<ProjectValues> values = new List<ProjectValues>();
ProjectValues projectValue;
try
{
//start SQLite Connection
SQLiteConnection projectsConnection = new SQLiteConnection(database);
projectsConnection.Open();
SQLiteCommand command = new SQLiteCommand(projectsConnection);
command.CommandText = "select * from Projects order by project_id asc";
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
projectValue = new ProjectValues(reader["project_id"].ToString(), reader["project_name"].ToString(), Convert.ToInt32(reader["id"]));
values.Add(projectValue);
}
CloseConnections(command, projectsConnection);
}
else
{
CloseConnections(command, projectsConnection);
}
}
}
catch (SQLiteException e)
{
MainForm.LogFile("SQLite Error: " + e.Message);
}
return values;
}
/// <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);
}
}
