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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 4
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);
        }
Esempio n. 6
0
 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);
     }
 }
Esempio n. 8
0
 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);
        }
Esempio n. 10
0
        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);
        }
Esempio n. 11
0
        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);
        }
Esempio n. 12
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);
        }
Esempio n. 13
0
        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);
        }
Esempio n. 14
0
 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);
     }
 }
Esempio n. 15
0
        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);
        }
Esempio n. 16
0
 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);
     }
 }
Esempio n. 17
0
 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);
     }
 }
Esempio n. 18
0
 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);
     }
 }
Esempio n. 19
0
 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);
     }
 }
Esempio n. 20
0
 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);
     }
 }
Esempio n. 21
0
        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);
        }
Esempio n. 23
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);
            }
        }
Esempio n. 25
0
        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);
        }
Esempio n. 27
0
        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);
        }
Esempio n. 28
0
        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);
        }
Esempio n. 29
0
 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);
     }
 }
Esempio n. 30
0
 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);
     }
 }