internal static DataTable GetSession(string sessionName)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("SESSIONS"))
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM SESSIONS WHERE [SessionName] = @sessionName";
cmd.Parameters.Add(new SQLiteParameter("@sessionName", DbType.String)
{
Value = sessionName
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static DataTable GetTriggers()
{
DataTable dataTable = null;
DataTable tempDataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("TRIGGERS"))
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM TRIGGERS";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static bool CreateAudioClipsDBTable()
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DROP TABLE IF EXISTS[AUDIOCLIPS];" +
"CREATE TABLE [AUDIOCLIPS] (" +
"[ClipId] INTEGER PRIMARY KEY AUTOINCREMENT, " +
"[Category] TEXT NOT NULL, " +
"[Label] TEXT NOT NULL, " +
"[DeviceName] TEXT, " +
"[ButtonColour] TEXT NOT NULL, " +
"[IsVisible] INT NOT NULL, " +
"[StatementText] TEXT, " +
"[ConfirmationText] TEXT, " +
"[CheckText] TEXT, " +
"[StatementAudioFile] TEXT, " +
"[ConfirmationAudioFile] TEXT, " +
"[CheckAudioFile] TEXT, " +
"[Tooltip] TEXT);";
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 0);
}
internal static bool DeleteAudioClip(int sessionId, int audioClipId)
{
lock (DBAdmin.padlock)
{
SQLiteTransaction trans = null;
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM SESSION_CLIPS WHERE [SessionId] = @sessionId AND [AudioClipId] = @audioClipId;";
cmd.Parameters.Add(new SQLiteParameter("@sessionId", DbType.Int32)
{
Value = sessionId
});
cmd.Parameters.Add(new SQLiteParameter("@audioClipId", DbType.Int32)
{
Value = audioClipId
});
updatedRows = cmd.ExecuteNonQuery();
}
trans.Commit();
}
return(updatedRows == 1);
}
}
internal static bool SaveLogEntryToDB(LogEntry logEntry)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "INSERT INTO [EVENT_LOG] (" +
"[EventId], [TimeStamp], [Category], [Message]) " +
"VALUES( @eventId, @timeStamp, @category, @message);";
cmd.Parameters.Add(new SQLiteParameter("@eventId", SqlDbType.Int)
{
Value = logEntry.EventId
});
cmd.Parameters.Add(new SQLiteParameter("@timeStamp", DbType.String)
{
Value = logEntry.EntryDateTime
});
cmd.Parameters.Add(new SQLiteParameter("@category", DbType.String)
{
Value = Enum.GetName(logEntry.Category.GetType(), logEntry.Category)
});
cmd.Parameters.Add(new SQLiteParameter("@message", DbType.String)
{
Value = logEntry.Message
});
updatedRows += cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
internal static bool DeleteRule(int rulesetId, int ruleNumber)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM [RULESET_RULES] WHERE [RulesetId] = @rulesetId AND [RuleNumber] = @ruleNumber";
cmd.Parameters.Add(new SQLiteParameter("@rulesetId", DbType.Int32)
{
Value = rulesetId
});
cmd.Parameters.Add(new SQLiteParameter("@ruleNumber", DbType.Int32)
{
Value = ruleNumber
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
internal static bool SaveOnlineVoicesToDB(List <string> voices)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM [ONLINE_VOICES];";
updatedRows += cmd.ExecuteNonQuery();
foreach (var voice in voices)
{
cmd.CommandText = "INSERT INTO [ONLINE_VOICES] ([Voice]) VALUES(@voice);";
cmd.Parameters.Add(new SQLiteParameter("@voice", DbType.String)
{
Value = voice
});
updatedRows += cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
return(updatedRows == voices.Count);
}
}
internal static bool DeleteTimeTriggerClip(string label)
{
lock (DBAdmin.padlock)
{
SQLiteTransaction trans = null;
int updatedRows = 0;
// Note that for DEBUG, Resources directory is bin\Debug\Resources
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM TIMECLIPS WHERE [Label] = @label;";
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.String)
{
Value = label
});
updatedRows = cmd.ExecuteNonQuery();
}
trans.Commit();
}
return(updatedRows == 1);
}
}
internal static DataTable GetEventLog()
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
string query;
if (DBAdmin.TableExists("EVENT_LOG"))
{
query = "SELECT * FROM EVENT_LOG";
}
else
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(query, dbConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static bool CreateRulesDBTable()
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DROP TABLE IF EXISTS[ACTIONS];" +
"CREATE TABLE [ACTIONS] (" +
"[ActionId] INTEGER PRIMARY KEY, " +
"[DeviceName] TEXT NOT NULL, " +
"[Category] TEXT NOT NULL, " +
"[Label] TEXT NOT NULL, " +
"[ActionText] TEXT NOT NULL, " +
"[Tooltip] TEXT);";
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
if (updatedRows == 0)
{
return(true);
}
return(false);
}
internal static bool CreateTriggersDBTable()
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DROP TABLE IF EXISTS[TRIGGERS];" +
"CREATE TABLE [TRIGGERS] (" +
"[TriggerId] INTEGER PRIMARY KEY, " +
"[DeviceName] TEXT NOT NULL, " +
"[Category] TEXT NOT NULL, " +
"[Label] TEXT NOT NULL, " +
"[TimeTrigger] INTEGER NOT NULL, " +
"[TriggerText] TEXT, " +
"[Recurrence] TEXT, " +
"[TriggerAudioFile] TEXT, " +
"[TriggerAudioFileExists] INTEGER NOT NULL, " +
"[Tooltip] TEXT);";
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 0);
}
internal static bool AddAudioClip(int sessionId, int audioClipId)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "INSERT INTO [SESSION_CLIPS] ([SessionId], [AudioClipId]) VALUES(@sessionId, @audioClipId);";
cmd.Parameters.Add(new SQLiteParameter("@sessionId", DbType.Int32)
{
Value = sessionId
});
cmd.Parameters.Add(new SQLiteParameter("@audioClipId", DbType.Int32)
{
Value = audioClipId
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
internal static DataTable GetRulesetRules(int rulesetId)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("RULESET_RULES"))
{
return(null);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM [RULESET_RULES] WHERE [RulesetId] = @rulesetId";
cmd.Parameters.Add(new SQLiteParameter("@rulesetId", DbType.Int32)
{
Value = rulesetId
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
return(dataTable);
}
internal static bool AddTrigger(DeviceTrigger trigger)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "INSERT INTO [TRIGGERS] ([DeviceName], [Category], " +
"[Label], [TimeTrigger], [TriggerText], [Recurrence], [TriggerAudioFile], " +
"[TriggerAudioFileExists], [Tooltip]) " +
"VALUES(@deviceName, @category, @label, @timeTrigger, " +
"@triggerText, @recurrence, @triggerAudioFile, @triggerAudioFileExists, @tooltip);";
cmd.Parameters.Add(new SQLiteParameter("@deviceName", DbType.String)
{
Value = trigger.DeviceName
});
cmd.Parameters.Add(new SQLiteParameter("@category", DbType.String)
{
Value = trigger.Category
});
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.String)
{
Value = trigger.Label
});
cmd.Parameters.Add(new SQLiteParameter("@timeTrigger", DbType.Int32)
{
Value = trigger.TimeTrigger ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@triggerText", DbType.String)
{
Value = trigger.TriggerText
});
cmd.Parameters.Add(new SQLiteParameter("@recurrence", DbType.String)
{
Value = trigger.Recurrence
});
cmd.Parameters.Add(new SQLiteParameter("@triggerAudioFile", DbType.String)
{
Value = trigger.TriggerAudioFile
});
cmd.Parameters.Add(new SQLiteParameter("@triggerAudioFileExists", DbType.Int32)
{
Value = trigger.TriggerAudioFileExists ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@tooltip", DbType.String)
{
Value = trigger.Tooltip
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
public static bool LoadRulesetFromDB(Session session)
{
DataTable ruleSetTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (!DBAdmin.TableExists("RULESETS"))
{
return(false);
}
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM RULESETS";
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
ruleSetTable = tempDataTable;
}
}
}
}
}
if (ruleSetTable != null)
{
foreach (DataRow dr in ruleSetTable.Rows)
{
string rulesetName = dr["RulesetName"].ToString();
if (rulesetName.Equals(session.SessionName))
{
string rulesetIdstr = dr["RulesetId"].ToString();
string duplicatesCheckstr = dr["CheckDuplicates"].ToString();
string conflictsCheckstr = dr["CheckConflicts"].ToString();
if (Int32.TryParse(rulesetIdstr, out int rulesetId) &&
Int32.TryParse(duplicatesCheckstr, out int duplicatesCheck) &&
Int32.TryParse(conflictsCheckstr, out int conflictsCheck))
{
string deviceName = dr["DeviceName"].ToString();
var ruleset = new Ruleset(session, deviceName, rulesetId)
{
CheckDuplicates = duplicatesCheck == 1,
CheckConflicts = conflictsCheck == 1
};
ruleset.LoadRulesFromDB();
session.Ruleset = ruleset;
}
break;
}
}
return(true);
}
return(false);
}
internal static bool UpdateActions(List <DeviceAction> actions)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM [ACTIONS];";
updatedRows += cmd.ExecuteNonQuery();
foreach (var action in actions)
{
cmd.CommandText = "INSERT INTO [ACTIONS] " +
"([DeviceName], [Category], [Label], [ActionText], [Tooltip]) " +
"VALUES(@deviceName, @category, @label, @actionText, @tooltip);";
cmd.Parameters.Add(new SQLiteParameter("@deviceName", DbType.String)
{
Value = action.DeviceName
});
cmd.Parameters.Add(new SQLiteParameter("@category", DbType.String)
{
Value = action.Category
});
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.String)
{
Value = action.Label
});
cmd.Parameters.Add(new SQLiteParameter("@actionText", DbType.String)
{
Value = action.ActionText
});
cmd.Parameters.Add(new SQLiteParameter("@tooltip", DbType.String)
{
Value = action.Tooltip
});
updatedRows += cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
return(updatedRows == actions.Count);
}
}
internal static bool UpdateRuleset(Ruleset ruleset)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM [RULESETS] WHERE [RulesetId] = @rulesetId";
cmd.Parameters.Add(new SQLiteParameter("@rulesetId", DbType.Int32)
{
Value = ruleset.RulesetId
});
updatedRows += cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [RULESETS] ([RulesetId], [RulesetName], [DeviceName], [CheckDuplicates], [CheckConflicts]) " +
"VALUES(@rulesetId, @rulesetName, @deviceName, @checkDuplicates, @checkConflicts); ";
cmd.Parameters.Add(new SQLiteParameter("@rulesetId", DbType.Int32)
{
Value = ruleset.RulesetId
});
cmd.Parameters.Add(new SQLiteParameter("@rulesetName", DbType.String)
{
Value = ruleset.RulesetName
});
cmd.Parameters.Add(new SQLiteParameter("@deviceName", DbType.String)
{
Value = ruleset.DeviceName
});
cmd.Parameters.Add(new SQLiteParameter("@checkDuplicates", DbType.Int32)
{
Value = ruleset.CheckDuplicates ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@checkConflicts", DbType.Int32)
{
Value = ruleset.CheckConflicts ? 1 : 0
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
internal static bool UpdateTimeTriggerClips(List <AudioClip> timeTriggerClips)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM [TIMECLIPS];";
updatedRows += cmd.ExecuteNonQuery();
foreach (var audioClip in timeTriggerClips)
{
cmd.CommandText = "INSERT INTO [TIMECLIPS] ([Category], [Label], [ButtonColour], [StatementText], " +
"[StatementAudioFile]) " +
"VALUES(@category, @label, @buttonColour, @statementText, @statementAudioFile);";
cmd.Parameters.Add(new SQLiteParameter("@category", DbType.String)
{
Value = audioClip.Category
});
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.String)
{
Value = audioClip.Label
});
cmd.Parameters.Add(new SQLiteParameter("@buttonColour", DbType.String)
{
Value = audioClip.ButtonColour
});
cmd.Parameters.Add(new SQLiteParameter("@statementText", DbType.String)
{
Value = audioClip.StateText
});
cmd.Parameters.Add(new SQLiteParameter("@statementAudioFile", DbType.String)
{
Value = audioClip.StateAudioFile
});
updatedRows += cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
return(updatedRows == timeTriggerClips.Count);
}
}
internal static bool UpdateSession(Session session)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "UPDATE [SESSIONS] SET SessionName=@sessionName, IsRuleset=@isRuleset, " +
"EnableCastDisplay=@enableCastDisplay, KeepAlive=@keepAlive, SpeedRatio=@speedRatio " +
"WHERE SessionId=@sessionId;";
cmd.Parameters.Add(new SQLiteParameter("@sessionId", DbType.Int64)
{
Value = session.SessionId
});
cmd.Parameters.Add(new SQLiteParameter("@sessionName", DbType.String)
{
Value = session.SessionName
});
cmd.Parameters.Add(new SQLiteParameter("@isRuleset", DbType.Int32)
{
Value = session.IsRuleset ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@enableCastDisplay", DbType.Int32)
{
Value = session.CastDisplayEnabled ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@keepAlive", DbType.Int32)
{
Value = session.KeepAlive ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@speedRatio", DbType.Double)
{
Value = session.SpeedRatio
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
internal static bool SaveSettingsToDB()
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM SETTINGS;";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO [SETTINGS] (" +
"[CredentialsFile], [LanguageCode], [OnlineVoice], [CheckAudioFiles], MaxLogEntries) " +
"VALUES(@credentialsFile, @languageCode, @onlineVoice, @checkAudioFiles, @maxLogEntries);";
cmd.Parameters.Add(new SQLiteParameter("@credentialsFile", DbType.String)
{
Value = Settings.CredentialsFile
});
cmd.Parameters.Add(new SQLiteParameter("@languageCode", DbType.String)
{
Value = Settings.LanguageCode
});
cmd.Parameters.Add(new SQLiteParameter("@onlineVoice", DbType.String)
{
Value = Settings.OnlineVoice
});
cmd.Parameters.Add(new SQLiteParameter("@checkAudioFiles", SqlDbType.Int)
{
Value = Settings.CheckAudioFiles ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@maxLogEntries", SqlDbType.Int)
{
Value = Settings.MaxLogEntries
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
internal static void LoadAudioClipsListFromDB(int sessionId, List <int> actionClipsList)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
if (DBAdmin.TableExists("SESSION_CLIPS"))
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT * FROM SESSION_CLIPS WHERE [SessionId] = @sessionId;";
cmd.Parameters.Add(new SQLiteParameter("@sessionId", DbType.Int32)
{
Value = sessionId
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
}
if (dataTable != null)
{
foreach (DataRow row in dataTable.Rows)
{
if (Int32.TryParse(row["AudioClipId"].ToString(), out int audioClipId))
{
actionClipsList.Add(audioClipId);
}
}
Logger.AddLogEntry(LogCategory.INFO, "ActionClips Inventory Loaded");
}
}
internal static bool CreateOnlineVoicesDBTable()
{
int updatedRows = 0;
SQLiteConnection dbConnection;
using (dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DROP TABLE IF EXISTS[ONLINE_VOICES];" +
"CREATE TABLE [ONLINE_VOICES] (" +
"[Voice] TEXT NOT NULL, " +
"CONSTRAINT[PK_ONLINE_VOICES] PRIMARY KEY([Voice]));";
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 0);
}
internal static bool CreateSessionsDBTable()
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DROP TABLE IF EXISTS[SESSION_CLIPS];" +
"CREATE TABLE [SESSION_CLIPS] (" +
"[SessionClipId] INTEGER PRIMARY KEY AUTOINCREMENT, " +
"[AudioClipId] INT NOT NULL, " +
"[SessionId] INT NOT NULL);";
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 0);
}
public static bool LoadOnlineVoicesFromDB(List <string> voices)
{
DataTable dataTable = null;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
string query;
if (DBAdmin.TableExists("ONLINE_VOICES"))
{
query = "SELECT * FROM ONLINE_VOICES";
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(query, dbConnection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (var tempDataTable = new DataTable())
{
tempDataTable.Load(reader);
dataTable = tempDataTable;
}
}
}
}
}
}
if (dataTable != null)
{
foreach (DataRow dr in dataTable.Rows)
{
voices.Add(dr["Voice"].ToString());
}
return(true);
}
else
{
return(false);
}
}
internal static bool SaveTrimmedLogToDB(DataTable trimmedLog) // save trimmed log to db
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM EVENT_LOG;";
cmd.ExecuteNonQuery();
foreach (DataRow dr in trimmedLog.Rows)
{
cmd.CommandText = "INSERT INTO [EVENT_LOG] (" +
"[EventId], [TimeStamp], [Category], [Message]) " +
"VALUES( @eventId, @timeStamp, @category, @message);";
cmd.Parameters.Add(new SQLiteParameter("@eventId", SqlDbType.Int)
{
Value = Int32.Parse(dr["EventId"].ToString())
});
cmd.Parameters.Add(new SQLiteParameter("@timeStamp", DbType.String)
{
Value = dr["TimeStamp"].ToString()
});
cmd.Parameters.Add(new SQLiteParameter("@category", DbType.String)
{
Value = dr["Category"].ToString()
});
cmd.Parameters.Add(new SQLiteParameter("@message", DbType.String)
{
Value = dr["Message"].ToString()
});
updatedRows += cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
return(updatedRows == trimmedLog.Rows.Count);
}
internal static int GetClipId(string label)
{
DataTable audioClipsTable = null;
lock (DBAdmin.padlock)
{
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT ClipId FROM [AUDIOCLIPS] WHERE [Label] = @label";
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.String)
{
Value = label
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (audioClipsTable = new DataTable())
{
audioClipsTable.Load(reader);
}
}
}
}
}
}
if (audioClipsTable != null)
{
string clipIdstr = audioClipsTable.Rows[0]["ClipId"].ToString();
if (Int32.TryParse(clipIdstr, out int clipId))
{
return(clipId);
}
}
return(-1);
}
internal static int GetSessionId(string sessionName)
{
DataTable sessionTable = null;
lock (DBAdmin.padlock)
{
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
cmd.CommandText = "SELECT SessionId FROM [SESSIONS] WHERE [SessionName] = @sessionName";
cmd.Parameters.Add(new SQLiteParameter("@sessionName", DbType.String)
{
Value = sessionName
});
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
using (sessionTable = new DataTable())
{
sessionTable.Load(reader);
}
}
}
}
}
}
if (sessionTable != null)
{
string sessionIdstr = sessionTable.Rows[0]["SessionId"].ToString();
if (Int32.TryParse(sessionIdstr, out int sessionId))
{
return(sessionId);
}
}
return(-1);
}
internal static bool AddSession(Session session)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "INSERT INTO [SESSIONS] ([SessionName], [IsRuleset], " +
"[EnableCastDisplay], [KeepAlive], [SpeedRatio]) " +
"VALUES(@sessionName, @isRuleset, @enableCastDisplay, @keepAlive, @speedRatio ); ";
cmd.Parameters.Add(new SQLiteParameter("@sessionName", DbType.String)
{
Value = session.SessionName
});
cmd.Parameters.Add(new SQLiteParameter("@isRuleset", DbType.Int32)
{
Value = session.IsRuleset ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@enableCastDisplay", DbType.Int32)
{
Value = session.CastDisplayEnabled ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@keepAlive", DbType.Int32)
{
Value = session.KeepAlive ? 1 : 0
});
cmd.Parameters.Add(new SQLiteParameter("@speedRatio", DbType.Double)
{
Value = session.SpeedRatio
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
internal static bool DeleteSession(int sessionId)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM SESSIONS WHERE [SessionId] = @sessionId";
cmd.Parameters.Add(new SQLiteParameter("@sessionId", DbType.String)
{
Value = sessionId
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
internal static bool DeleteTrigger(string label)
{
lock (DBAdmin.padlock)
{
int updatedRows = 0;
using (SQLiteConnection dbConnection = DBAdmin.GetSQLConnection())
{
dbConnection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(dbConnection))
{
SQLiteTransaction trans = dbConnection.BeginTransaction();
cmd.CommandText = "DELETE FROM TRIGGERS WHERE [Label] = @label";
cmd.Parameters.Add(new SQLiteParameter("@label", DbType.Int32)
{
Value = label
});
updatedRows = cmd.ExecuteNonQuery();
trans.Commit();
}
}
return(updatedRows == 1);
}
}
