static midiFile loadedFile; //The object that will store the loaded MIDI file
#region Form Event Triggered Subs
/// <summary>
/// Run when form loads.
/// Checks all the files are there
/// Gets a list of instruments and adds it to the comboBox
/// Populates dgvExercises with data from the database
/// </summary>
private void frmMainMenu_Load(object sender, EventArgs e)
{
#region Varibles
databaseInterface databaseInterface = new databaseInterface(dbConnectionString); //The database class that will allow me to interact with the database easily
SqlCommand currentCommand;
#endregion
#region @@ TEMP LOADING OF IMAGIES INTO DATABSE TESTING
//This bit of code just allows me to throw the image data for the fingering into the database nice and easy
#if false
/*fileOpener = File.Open(@"C:\Users\Thomas\source\repos\A-level Coursework\Desktop Programs\Coursework Project\Coursework Project\Resources\E5.png", FileMode.Open);
* fileOpener.CopyTo(imageStream);
*/
MemoryStream imageStream = new MemoryStream();
string[] fileNames = { "D5", "E5", "FS5", "G5", "A5", "B5", "CS5", "D6", "E6", "FS6", "G6", "A6", "B6", "CS6" };
byte[] noteNumbers = { 74, 76, 78, 79, 81, 83, 85, 86, 88, 90, 91, 93, 95, 97 };
FileStream fileOpener;
for (int i = 0; i < noteNumbers.Length; i++)
{
imageStream = new MemoryStream();
fileOpener = File.Open($@"C:\Users\Thomas\source\repos\A-level Coursework\Desktop Programs\Coursework Project\Coursework Project\Resources\{fileNames[i]}.png", FileMode.Open);
fileOpener.CopyTo(imageStream);
//currentCommand = new SqlCommand("INSERT INTO Notes(FingeringDrawing) VALUES @inputImage ");
currentCommand = new SqlCommand("UPDATE Notes SET FingeringDrawing = @inputImage WHERE Note = @noteNumber");
currentCommand.Parameters.AddWithValue("@inputImage", imageStream.ToArray());
currentCommand.Parameters.AddWithValue("@noteNumber", noteNumbers[i]);
databaseInterface.executeNonQuery(currentCommand);
}
#endif
#if false
MemoryStream memStream = new MemoryStream();
DataTable outputDataTable = new DataTable();
currentCommand = new SqlCommand("SELECT FingeringDrawing FROM Notes");
outputDataTable = databaseInterface.executeQuery(currentCommand);
for (int i = 0; i < 14; i++)
{
memStream.Write(outputDataTable.Rows[i].Field <byte[]>("FingeringDrawing"), 0, outputDataTable.Rows[i].Field <byte[]>("FingeringDrawing").Length);
picTesting.Image = new Bitmap(memStream);
}
#endif
#endregion
//Check all the files in the database to see if they are there
CheckDatabaseFiles(databaseInterface);
//Get a list of all of the instruments of the availble exercises and adds them to the Combo box view.
currentCommand = new SqlCommand("SELECT DISTINCT Instrument FROM Exercises WHERE FileExists = 1 INTERSECT SELECT DISTINCT Instrument FROM Notes"); //This command gets a list of all of the instruments that can be found in the Exercises table AND in the notes table. This ensures that there will be no exercises selected for which there aren't any defininitions for that instrument
cmbInstuments.Items.AddRange(databaseInterface.executeQuery(currentCommand).AsEnumerable().Select(row => row[0].ToString()).ToArray()); //Cycle through the datatable line by line and add each result to an array that can then be assigned to the combo box
//Query database to select all available songs and display them in the datagrid view
currentCommand = new SqlCommand("SELECT * FROM Exercises WHERE FileExists = 1 AND Exercises.Instrument IN(SELECT Instrument FROM Notes)"); //This command gets all of the exercises where the file can be found and the instrument used in the exercises also has note definitions for it
dgvExercises.DataSource = databaseInterface.executeQuery(currentCommand); //Assign the result from the sql query to dgvExercises
}
/// <summary>
/// Checks the database to see if the files can be found. If a file cannot be found, it's "FileExists" row will be set to 0, else it will be set to 1 to show it does exist.
/// </summary>
/// <param name="databaseToCheck">The database interface that will be searched</param>
private void CheckDatabaseFiles(databaseInterface databaseToCheck)
{
SqlCommand currentCommand;
List <string> filesNotFound = new List <string>(); //Stores the list of file paths that do not have files at the other end
string currentFilePath; //Stores the file path that is currently being checked to see if it exsists
string generatedSqlString; //Stores the generated SQL statement that will be used to update the database with which files are there or not
//Checks each file in the Database to see if it can be found. If it can't be, then it's filepath is output to a list
using (currentCommand = new SqlCommand("SELECT FilePath FROM Exercises")) {
//Loop through all of the paths returned from the database, converts them string, checks they exist. If they do, the ids are saved to one list, else, the ids are saved to another list
foreach (DataRow currentRow in databaseToCheck.executeQuery(currentCommand).Rows)
{
currentFilePath = Convert.ToString(currentRow.ItemArray[0]); //Get the file path from the row
//If the file doesn't exsist, then save the filePath to the fileNotFound list
if (!File.Exists(currentFilePath))
{
filesNotFound.Add(currentFilePath);
}
}
}
//Update the datbase so the files are shown not to exist. Else, update athe database so all files are shown to exist
using (currentCommand = new SqlCommand()) {
/* In order to update the database to store whether the file can be found or not, an UPDATE statement is used with parametisation.
*
* The parametised statement should look like this for example:
* UPDATE Exercises SET FileExists = CASE
* WHEN FilePath IN( @0, @1, @2, @3, @4) THEN 0
* ELSE 1
* END;
*
* In this statement, the parametised values will be the file paths of files that could not be found
*/
generatedSqlString = "UPDATE Exercises SET FileExists = CASE WHEN FilePath IN( "; //The first part of the SQL Statement that will update the database depedning if the files are availible (A default null value is added, so if all files are found, the database is still updated)
if (filesNotFound.Count != 0)
{
//Loop through the list of files that have not been found
for (int i = 0; i < filesNotFound.Count; i++)
{
generatedSqlString += $" @{i},"; //Add a paramenter to the end of the statement string. eg: " @5,"
currentCommand.Parameters.AddWithValue($"@{i}", filesNotFound[i]); //Add the parameter and the value that the parameter will assume to the command object which will be passed to the database interface
}
}
else
{
generatedSqlString += "NULL"; //If there are not files to update, then set it to null
}
generatedSqlString = generatedSqlString.TrimEnd(new char[] { ',' }); //Remove the last comma from the string, since it will be the unecerssary comma from the for loop
generatedSqlString += ") THEN 0 ELSE 1 END;"; //Finish off the SQL Statement
currentCommand.CommandText = generatedSqlString; //Assign the generated string to the command object to get executed
databaseToCheck.executeNonQuery(currentCommand); //Execute the command
}
}