public ICard GetNewCard(int id) { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "INSERT INTO \"Cards\"(settings_id) VALUES (@sid); SELECT @@IDENTITY;"; cmd.Parameters.Add("@sid", MsSqlCeSettingsConnector.CreateNewSettings(Parent)); ICard card = new DbCard(Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)), false, Parent); List <ICard> cardsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardsList, id)] as List <ICard>; if (cardsCache != null) { cardsCache.Add(card); } return(card); } }
/// <summary> /// Adds the new chapter. /// </summary> /// <param name="lmid">The id of the learning module.</param> /// <returns></returns> /// <remarks>Documented by Dev02, 2008-08-05</remarks> /// <remarks>Documented by Dev08, 2009-01-12</remarks> public int AddNewChapter(int lmid) { int position = 0; using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT position FROM \"Chapters\" WHERE lm_id=@lmid ORDER BY position DESC"; cmd.Parameters.Add("@lmid", lmid); int?pos = MSSQLCEConn.ExecuteScalar <int>(cmd); position = pos.HasValue ? pos.Value + 10 : 10; } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "INSERT INTO \"Chapters\" (lm_id, position, settings_id) VALUES (@lmid, @pos, @sid); SELECT @@IDENTITY;"; cmd.Parameters.Add("@lmid", lmid); cmd.Parameters.Add("@pos", position); cmd.Parameters.Add("@sid", MsSqlCeSettingsConnector.CreateNewSettings(parent)); int chapterId = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)); parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid)); return(chapterId); } }
/// <summary> /// Creates the settings. /// </summary> /// <returns></returns> /// <remarks>Documented by Dev08, 2009-01-13</remarks> public ISettings CreateSettings() { int sid = MsSqlCeSettingsConnector.CreateNewSettings(parent); return(new DbSettings(sid, false, parent)); }
/// <summary> /// Gets the user settings. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev08, 2009-01-12</remarks> public ISettings GetUserSettings(int id) { DbSettings settingsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserLearningModuleSettings, id)] as DbSettings; if (settingsCache != null) { return(settingsCache); } int count; using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT count(*) FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;"; cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id); cmd.Parameters.Add("@param_lm_id", id); count = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)); } int result; if (count < 1) //Create new settings { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "INSERT INTO \"UserProfilesLearningModulesSettings\" (user_id, lm_id, settings_id) VALUES (@param_user_id, @param_lm_id, @param_new_settings);"; cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id); cmd.Parameters.Add("@param_lm_id", id); cmd.Parameters.Add("@param_new_settings", MsSqlCeSettingsConnector.CreateNewSettings(parent)); MSSQLCEConn.ExecuteNonQuery(cmd); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;"; cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id); cmd.Parameters.Add("@param_lm_id", id); result = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)); } //Selected Learn Chapters using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=@param_lm_id"; cmd.Parameters.Add("@param_lm_id", id); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); while (reader.Read()) { using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd2.CommandText = "INSERT INTO \"SelectedLearnChapters\" VALUES (@cid, @result);"; cmd2.Parameters.Add("@cid", reader["id"]); cmd2.Parameters.Add("@result", result); MSSQLCEConn.ExecuteNonQuery(cmd2); } } reader.Close(); } DbSettings settings = new DbSettings(result, false, parent); parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings; return(settings); } else { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" WHERE user_id=@param_user_id and lm_id=@param_lm_id;"; cmd.Parameters.Add("@param_user_id", parent.CurrentUser.Id); cmd.Parameters.Add("@param_lm_id", id); result = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)); } DbSettings settings = new DbSettings(result, false, parent); parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings; return(settings); } }
/// <summary> /// Gets the user learning module settings. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev05, 2009-01-16</remarks> public int GetUserLearningModuleSettings(int id) { int cnt; SqlCeCommand cmd_main = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd_main.CommandText = "SELECT count(*) FROM \"UserProfilesLearningModulesSettings\" " + "WHERE user_id=@uid and lm_id=@lm_id;"; cmd_main.Parameters.Add("@uid", Parent.CurrentUser.Id); cmd_main.Parameters.Add("@lm_id", id); cnt = MSSQLCEConn.ExecuteScalar <int>(cmd_main).Value; if (cnt > 0) { //Workaround for Issue: ML-2458 If System Date is older than Timestamps only one card is asked SqlCeCommand timeCmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); timeCmd.CommandText = "SELECT cards_id FROM \"UserCardState\" WHERE user_id=@uid AND timestamp > @now"; timeCmd.Parameters.Add("@uid", Parent.CurrentUser.Id); timeCmd.Parameters.Add("@now", DateTime.Now); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(timeCmd); List <int> wrongCards = new List <int>(); while (reader.Read()) { wrongCards.Add(Convert.ToInt32(reader["cards_id"])); } reader.Close(); foreach (int cid in wrongCards) { SqlCeCommand updateCmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); updateCmd.CommandText = "UPDATE \"UserCardState\" SET timestamp = @time WHERE user_id=@uid AND cards_id=@cid"; updateCmd.Parameters.Add("@uid", Parent.CurrentUser.Id); updateCmd.Parameters.Add("@cid", cid); updateCmd.Parameters.Add("@time", DateTime.Now.AddSeconds(-Math.PI / 2)); MSSQLCEConn.ExecuteNonQuery(updateCmd); } //End of Workaround for Issue: ML-2458 SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT settings_id FROM \"UserProfilesLearningModulesSettings\" " + "WHERE user_id=@uid and lm_id=@lm_id;"; cmd.Parameters.Add("@uid", Parent.CurrentUser.Id); cmd.Parameters.Add("@lm_id", id); return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value); } else { int newSettings = MsSqlCeSettingsConnector.CreateNewSettings(Parent); SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "INSERT INTO \"UserProfilesLearningModulesSettings\" (user_id, lm_id, settings_id) " + "VALUES (@uid, @lm_id, @settings_id)"; cmd.Parameters.Add("@uid", Parent.CurrentUser.Id); cmd.Parameters.Add("@lm_id", id); cmd.Parameters.Add("@settings_id", newSettings); MSSQLCEConn.ExecuteNonQuery(cmd); cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT id FROM \"Chapters\" " + "WHERE lm_id=@lm_id;"; cmd.Parameters.Add("@lm_id", id); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); List <int> chapterIds = new List <int>(); while (reader.Read()) { chapterIds.Add(Convert.ToInt32(reader["id"])); } reader.Close(); foreach (int chapterId in chapterIds) { SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd2.CommandText = "INSERT INTO \"SelectedLearnChapters\" (chapters_id, settings_id) VALUES (@cid, @sid);"; cmd2.Parameters.Add("@cid", chapterId); cmd2.Parameters.Add("@sid", newSettings); MSSQLCEConn.ExecuteNonQuery(cmd2); } SqlCeCommand cmd3 = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd3.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " + "(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@lm_id)"; cmd3.Parameters.Add("@lm_id", id); SqlCeDataReader reader2 = MSSQLCEConn.ExecuteReader(cmd3); List <int> cardIds = new List <int>(); while (reader2.Read()) { cardIds.Add(Convert.ToInt32(reader2["id"])); } reader2.Close(); foreach (int cid in cardIds) { SqlCeCommand cmd4 = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd4.CommandText = "INSERT INTO \"UserCardState\" (user_id, cards_id, box, active) VALUES (@param_user_id, @param_cards_id, 0, 1);"; cmd4.Parameters.Add("@param_user_id", Parent.CurrentUser.Id); cmd4.Parameters.Add("@param_cards_id", cid); MSSQLCEConn.ExecuteNonQuery(cmd4); } Parent.CurrentUser.Cache.Clear(); return(newSettings); } }
public int AddNewLM(string guid, int categoryId, string title, string licenceKey, bool contentProtected, int calCount) { if (!Parent.CurrentUser.ConnectionString.ConnectionString.EndsWith(Helper.EmbeddedDbExtension)) { ConnectionStringStruct css = Parent.CurrentUser.ConnectionString; css.ConnectionString += @"\" + title.Replace(@"\", "_") + Helper.EmbeddedDbExtension; css.Typ = DatabaseType.MsSqlCe; Parent.CurrentUser.ConnectionString = css; } { //replace invalid filename characters ConnectionStringStruct css = Parent.CurrentUser.ConnectionString; css.ConnectionString = Helper.FilterInvalidFilenameCharacters(css.ConnectionString); Parent.CurrentUser.ConnectionString = css; } if (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString)) { int i = 1; while (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension))) { i++; } ConnectionStringStruct css = Parent.CurrentUser.ConnectionString; css.ConnectionString = css.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension); Parent.CurrentUser.ConnectionString = css; } if (!Directory.Exists(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString))) { Directory.CreateDirectory(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString)); } using (SqlCeEngine clientEngine = new SqlCeEngine("Data Source=" + Parent.CurrentUser.ConnectionString.ConnectionString)) { clientEngine.CreateDatabase(); clientEngine.Dispose(); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = Resources.MsSqlCeDbCreateScript; MSSQLCEConn.ExecuteNonQuery(cmd); } MSSQLCEConn.ApplyIndicesToDatabase(MSSQLCEConn.GetConnection(Parent.CurrentUser)); int cat_id; using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT id FROM Categories WHERE global_id=@cat_id;"; cmd.Parameters.Add("@cat_id", categoryId); cat_id = MSSQLCEConn.ExecuteScalar <int>(cmd).Value; } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "INSERT INTO LearningModules (guid, title, categories_id, default_settings_id, allowed_settings_id, licence_key, content_protected, cal_count) " + "VALUES (@guid, @title, @cat_id, @dset, @aset, @lk, @cp, @cals); SELECT @@IDENTITY;"; cmd.Parameters.Add("@guid", guid); cmd.Parameters.Add("@title", title); cmd.Parameters.Add("@cat_id", cat_id); cmd.Parameters.Add("@lk", licenceKey); cmd.Parameters.Add("@cp", contentProtected); cmd.Parameters.Add("@cals", calCount); cmd.Parameters.Add("@dset", MsSqlCeSettingsConnector.CreateNewDefaultSettings(Parent)); cmd.Parameters.Add("@aset", MsSqlCeSettingsConnector.CreateNewAllowedSettings(Parent)); return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value); } }