/// <summary>
/// Using the .wav files in the Audio directory, populates the Word and Recording tables.
/// </summary>
private void populatetables()
{
DirectoryInfo dirInfo = new DirectoryInfo(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Audio"));
foreach (FileInfo fInfo in dirInfo.GetFiles("*.wav", SearchOption.AllDirectories)) // Also searches subdirectories.
{
if (fInfo.Extension.Contains("wav"))
{
// Dynamically create recordings and words.
String fileName = Path.GetFileName(fInfo.FullName);
String wordName = NameParser.WordNameFromFile(fileName);
Speaker speaker = NameParser.SpeakerFromFile(fileName);
// Create the word if it doesn't exist, get the name from the filename.
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "MPAiDb.sqlite") + "; Version=3;"))
{
connection.Open();
// Select the number of words that match the current word.
string sql = "select count(*) from Word " +
"where wordName = '" + wordName + "'";
SQLiteCommand command = new SQLiteCommand(sql, connection);
int count = Int32.Parse(command.ExecuteScalar().ToString());
// If the word is not currently in the database, add it.
if (count <= 0)
{
sql = "insert into Word (wordName)" +
"values('" + wordName +
"')";
command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
}
// Select the number of recordings that match the current recording.
sql = "select count(*) from Recording " +
"where filePath = '" + fInfo.FullName + "'";
command = new SQLiteCommand(sql, connection);
count = Int32.Parse(command.ExecuteScalar().ToString());
// If the recording is not currently in the database, add it, and associate it with the word we just made.
if (count <= 0)
{
sql = "select wordId from Word " +
"where wordName = '" + wordName + "'";
command = new SQLiteCommand(sql, connection);
int wordID = Int32.Parse(command.ExecuteScalar().ToString());
sql = "insert into Recording(speaker, wordId, filePath) " +
"values(" + Convert.ToInt32(speaker).ToString() +
", " + wordID.ToString() +
", '" + fInfo.FullName + "')";
command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
}
}
}
}
}