public FolderMsgPrompt(string question, string title, string defaultValue = "", InputType inputType = InputType.Text)
{
InitializeComponent();
conn = new DBConnection();
fillCombo();
this.Loaded += new RoutedEventHandler(PromptDialog_Loaded);
txtCreateFolder.Text = question;
Title = title;
txtFolderName.Text = defaultValue;
txtFolderDesc.Text = defaultValue;
txtFolderName.Visibility = Visibility.Visible;
}
public ObservableCollection<AnalysisViewModel> loadAnalysis(string fileName)
{
analysis.Clear();
using (DBConnection db = new DBConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = @"SELECT a.AID, a.Description, a.FileType FROM Analysis a
INNER JOIN File2Analysis f2a ON a.AID = f2a.Analysis_AID
INNER JOIN File f ON f.FID = f2a.File_FID
WHERE f.Name = @ID";
cmd.Parameters.AddWithValue("@ID", fileName);
var table = db.getFromDB(cmd);
foreach (DataRow dr in table.Rows)
{
analysis.Add(new AnalysisViewModel { Analysis = new Analysis { AID = dr["AID"].ToString(), Description = dr["Description"].ToString(), FileType = dr["FileType"].ToString() } });
}
}
return this.analysis;
}
public ObservableCollection<SpeakerViewModel> loadSpeakers(string PID)
{
//if (PID == null)
//{
// PID = "DefaultProject";
//}
speakers.Clear();
using (DBConnection db = new DBConnection())
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT * FROM File WHERE PID = @pName"; // @name" ; // WHERE ProjectName = '" + PID + "'";
//cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@pName", PID);
var table = db.getFromDB(cmd);
foreach (DataRow dr in table.Rows)
{
var speaker = dr["Speaker"].ToString();
speakers.Add(new SpeakerViewModel { Speaker = new Speaker { ID = dr["FID"].ToString(), Name = dr["Name"].ToString(), PID = dr["PID"].ToString(), SpeakerName = speaker, FileType = dr["FileType"].ToString(), Age = speaker[0].ToString() } });
}
}
return this.speakers;
}
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 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);
}
}
// fill in the values of each project name in the combobox
private void fillCombo()
{
using (DBConnection db = new DBConnection())
{
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Project");
var table = db.getFromDB(cmd);
foreach (DataRow dr in table.Rows)
{
string projectName = dr["PID"].ToString();
cbChooseFolder.Items.Add(projectName);
folderNameCB = (string)cbChooseFolder.SelectedValue;
}
}
}
//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");
}
/*When analysis datagrid item clicked, opens the selected item. */
private void analysisDataGrid_MouseDoubleClick(object sender, MouseButtonEventArgs e)
{
if (sender != null)
{
DataGridRow dgr = sender as DataGridRow;
var item = dgr.DataContext as Analysis;
if (item != null)
{
string fileType;
if (item.FileType == null)
{
fileType = "txt"; //default in case of shenanigans
}
else
{
fileType = item.FileType.ToString();
}
string fileName = item.AID.ToString();
//Check if file exists locally, if not open from db
if (File.Exists(testDBRoot + "\\ANALYSIS\\" + fileName + fileType))
{
openOrPlayLocalFile(testDBRoot + "\\ANALYSIS\\" + fileName + fileType);
}
else
{
using (DBConnection db = new DBConnection())
{
var cmd = new MySqlCommand();
cmd.CommandText = "SELECT FileData FROM Analysis where AID = @fileName";
cmd.Parameters.AddWithValue("@fileName", fileName);
//call the download and save method
openOrPlayFile(cmd, fileName, fileType, "ANALYSIS", item);
}
}
}
}
}
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);
}
}
}
/*When a row in the projects grid is selected, load the relevent speech data into the
* speech datagrid, and do so efficiently.*/
private void dataGridProjects_GotCellFocus(object sender, RoutedEventArgs e)
{
this.emptyGrid.Visibility = System.Windows.Visibility.Hidden;
if (e.OriginalSource.GetType() == typeof(DataGridCell) && sender != null)
{
DataGridRow dgr = sender as DataGridRow;
var item = dgr.DataContext as Project;
if (item != null)
{
string projectName = item.PID.ToString();
dgl.loadSpeakers(projectName);
//rowS = dgl.getCollection("S");
buildDatagridGroups(new ListCollectionView(rowS));
}
}
using (DBConnection db = new DBConnection())
{
var cmd = new MySqlCommand();
if (!projectSelectedName.Equals(""))
{
cmd.CommandText = "SELECT Description FROM Project WHERE PID = '" + projectSelectedName + "'";
}
var table = db.getFromDB(cmd);
foreach (DataRow dr in table.Rows)
{
descTextBlock.Text = dr["Description"].ToString();
}
}
}
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")));
}
/* Downloads a selected project on a background thread. */
private void backgroundWorker_DoWork(
object sender,
DoWorkEventArgs e)
{
string[] parameters = e.Argument as string[];
string PID = parameters[1];
string path = parameters[0];
this.Dispatcher.Invoke((Action)(() =>
{
prog.Show();
}));
byte[] rawData;
FileStream fs;
string filePath = "";
/* Get file data for every file in project and save it in the correct folder structure. */
using (DBConnection db = new DBConnection())
{
var cmd = new MySqlCommand();
cmd.CommandText = "SELECT f.Name, f.Speaker, f.FileType, fd.FileData FROM FileData fd INNER JOIN File f ON f.FID = fd.FID WHERE f.PID = @PID";
cmd.Parameters.AddWithValue("@PID", PID);
var table = db.getFromDB(cmd);
foreach (DataRow dr in table.Rows)
{
rawData = (byte[])dr["FileData"];
Directory.CreateDirectory(path + PID + @"\" + dr["Speaker"].ToString());
fs = new FileStream(path + PID + @"\" + dr["Speaker"].ToString() + @"\" + dr["Name"].ToString() + dr["FileType"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);
filePath = fs.Name;
//Fixed access denied error
File.SetAttributes(filePath, FileAttributes.Normal);
// Writes a block of bytes to this stream using data from
// a byte array.
fs.Write(rawData, 0, rawData.Length);
// close file stream
fs.Close();
}
}
}
/* Downloads a selected project on a background thread. */
private void backgroundWorker_DoUploadWork(
object sender,
DoWorkEventArgs e)
{
//string[] parameters = e.Argument as string[];
//string PID = parameters[1];
//string path = parameters[0];
List<String> projectDetails = null;
this.Dispatcher.Invoke((Action)(() =>
{
projectDetails = getFolderName();
prog.Show();
}));
using (DBConnection db = new DBConnection())
{
//testDBRoot = "C:\\Users\\Rodel\\Documents\\p4p\\P4Ptestfiles";
DirectoryInfo dirN = null;
DirectoryInfo[] dirs = new DirectoryInfo(tempPath).GetDirectories();
string projDescription = "None";
// If there are no directories inside the chosen path.
if (dirs == null || dirs.Length == 0)
{
dirN = new DirectoryInfo(tempPath);
}
if (projectDetails != null && dirN == null)
{
if (projectDetails.Count > 1)
{
projDescription = projectDetails.Last();
}
foreach (DirectoryInfo dir in dirs)
{
addIndividualFile(dir, projectDetails, projDescription, db);
}
}
else if (dirN != null)
{
if (projectDetails.Count > 1)
{
projDescription = projectDetails.Last();
}
addIndividualFile(dirN, projectDetails, projDescription, db);
}
}
}
/*Downlaods and opens the file selected, or plays it if its a .wav(audio) */
private void openOrPlayFile(MySqlCommand cmd, string fileName, string fileType, string projectName, Object row)
{
using (DBConnection db = new DBConnection())
{
byte[] rawData;
FileStream fs;
string filePath = "";
//Checks for the file type (speaker or analysis) and then puts it in the correct folder location
if (row is Analysis)
{
Directory.CreateDirectory(testDBRoot + "\\ANALYSIS");
fs = new FileStream(testDBRoot + "\\ANALYSIS\\" + fileName + fileType, FileMode.OpenOrCreate, FileAccess.Write);
}
else
{
Directory.CreateDirectory(@"..\..\..\..\testOutput\" + projectName + "\\" + ((Speaker)row).SpeakerName);
fs = new FileStream(@"..\..\..\..\testOutput\" + projectName + "\\" + ((Speaker)row).SpeakerName + "\\" + fileName + fileType, FileMode.OpenOrCreate, FileAccess.Write);
}
var table = db.getFromDB(cmd);
foreach (DataRow dr in table.Rows)
{
rawData = (byte[])dr["FileData"]; // convert successfully to byte[]
filePath = fs.Name;
//Fixed access denied error
File.SetAttributes(filePath, FileAttributes.Normal);
// Writes a block of bytes to this stream using data from
// a byte array.
fs.Write(rawData, 0, rawData.Length);
// close file stream
fs.Close();
}
// Filter audio, images etc. to open appropriate program
if (fileType.Equals(".wav") || fileType.Equals(".WAV"))
{
var audio = new AudioWindow(filePath);
audio.Show();
}
else
{
try
{
Process.Start("notepad++.exe", filePath);
//Process.Start(filePath);
}
catch
{
MessageBox.Show("Access denied, please run application with administrator privileges.");
}
}
}
}
public ObservableCollection<ProjectViewModel> loadProjects()
{
projects.Clear();
using (DBConnection db = new DBConnection())
{
MySqlCommand query = new MySqlCommand("SELECT PID, DateCreated, Description FROM Project");
var table = db.getFromDB(query);
foreach (DataRow dr in table.Rows)
{
string[] dateOnly = dr["dateCreated"].ToString().Split(new char[0], StringSplitOptions.RemoveEmptyEntries);
projects.Add(new ProjectViewModel { Project = new Project { PID = dr["PID"].ToString(), DateCreated = dateOnly[0], Description = dr["Description"].ToString() } });
}
}
return this.projects;
}
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);
}
}