/// <summary> /// Gets the user list. /// </summary> /// <returns></returns> /// <remarks>Documented by Dev05, 2009-01-16</remarks> public IList <UserStruct> GetUserList() { IList <UserStruct> users = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserList, 0)] as IList <UserStruct>; if (users != null) { return(users); } SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT * FROM \"UserProfiles\";"; SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); users = new List <UserStruct>(); users.Add(new UserStruct(Resources.CREATE_NEW_USER, UserAuthenticationTyp.ListAuthentication)); while (reader.Read()) { UserStruct user = new UserStruct(reader["username"].ToString(), reader["local_directory_id"].ToString(), UserAuthenticationTyp.ListAuthentication); users.Add(user); } reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserList, 0, new TimeSpan(0, 0, 30))] = users; return(users); }
/// <summary> /// Gets the chapter ids. /// </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 IList <int> GetChapterIds(int lmid) { IList <int> chaptersCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid)] as IList <int>; if (chaptersCache != null) { return(chaptersCache); } IList <int> list = new List <int>(); using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=@lmid ORDER BY position ASC"; cmd.Parameters.Add("@lmid", lmid); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); while (reader.Read()) { list.Add(Convert.ToInt32(reader["id"])); } reader.Close(); } parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChaptersList, lmid)] = list; return(list); }
/// <summary> /// Gets a single property value for a media object. /// </summary> /// <param name="id">The id.</param> /// <param name="property">The property.</param> /// <returns></returns> /// <remarks>Documented by Dev02, 2008-08-07</remarks> /// <remarks>Documented by Dev03, 2009-01-13</remarks> public string GetPropertyValue(int id, MLifter.DAL.Interfaces.MediaProperty property) { Dictionary <MediaProperty, string> properties = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.MediaProperties, id)] as Dictionary <MediaProperty, string>; if (properties != null && properties.ContainsKey(property)) { return(properties[property]); } SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT property, value FROM MediaProperties WHERE media_id=@id"; cmd.Parameters.Add("@id", id); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); properties = new Dictionary <MediaProperty, string>(); while (reader.Read()) { properties[(MediaProperty)Enum.Parse(typeof(MediaProperty), reader["property"].ToString())] = reader["value"].ToString(); } reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.MediaProperties, id, new TimeSpan(1, 0, 0))] = properties; if (properties.ContainsKey(property)) { return(properties[property]); } else { return(null); } }
/// <summary> /// Sets a single property value for a media object. /// </summary> /// <param name="id">The id.</param> /// <param name="property">The property.</param> /// <param name="value">The value.</param> /// <remarks>Documented by Dev02, 2008-08-07</remarks> /// <remarks>Documented by Dev03, 2009-01-13</remarks> public void SetPropertyValue(int id, MLifter.DAL.Interfaces.MediaProperty property, string value) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); SqlCeTransaction tran = cmd.Connection.BeginTransaction(); if (GetPropertyValue(id, property) == null) { cmd.CommandText = "INSERT INTO MediaProperties (media_id, property, value) VALUES (@media_id, @property, @value);"; } else { cmd.CommandText = "UPDATE MediaProperties SET value=@value WHERE media_id=@media_id AND property=@property;"; } Dictionary <MediaProperty, string> properties = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.MediaProperties, id)] as Dictionary <MediaProperty, string>; if (properties == null) { properties = new Dictionary <MediaProperty, string>(); } properties[property] = value; Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.MediaProperties, id, new TimeSpan(1, 0, 0))] = properties; cmd.Parameters.Add("@media_id", id); cmd.Parameters.Add("@property", property.ToString()); cmd.Parameters.Add("@value", value); MSSQLCEConn.ExecuteNonQuery(cmd); tran.Commit(); }
/// <summary> /// Gets the settings. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev05, 2009-01-15</remarks> public ISettings GetSettings(int id) { int?chapterSettingsId = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterSetting, id)] as int?; if (chapterSettingsId.HasValue) { return(new DbSettings(chapterSettingsId.Value, false, Parent)); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT settings_id FROM \"Chapters\" WHERE id=@id"; cmd.Parameters.Add("@id", id); int?sid = MSSQLCEConn.ExecuteScalar <int>(cmd); if (sid.HasValue) { Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterSetting, id)] = sid; return(new DbSettings(sid.Value, false, Parent)); } else { return(null); } } }
/// <summary> /// Sets the properties for a media object. /// </summary> /// <param name="id">The id of the media object.</param> /// <param name="properties">The properties for the media object.</param> /// <remarks>Documented by Dev03, 2008-08-05</remarks> /// <remarks>Documented by Dev03, 2009-01-13</remarks> public void SetProperties(int id, Dictionary <MLifter.DAL.Interfaces.MediaProperty, string> properties) { SqlCeCommand cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser); SqlCeTransaction tran = cmd1.Connection.BeginTransaction(); cmd1.CommandText = "DELETE FROM MediaProperties WHERE media_id=@id;"; cmd1.Parameters.Add("@id", id); MSSQLCEConn.ExecuteNonQuery(cmd1); SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "INSERT INTO MediaProperties (media_id, property, value) VALUES (@media_id, @property, @value);"; cmd.Parameters.Add("@media_id", SqlDbType.Int, 4); cmd.Parameters.Add("@property", SqlDbType.NVarChar, 100); cmd.Parameters.Add("@value", SqlDbType.NVarChar, 100); foreach (KeyValuePair <MediaProperty, string> item in properties) { cmd.Parameters["@media_id"].Value = id; cmd.Parameters["@property"].Value = item.Key.ToString(); cmd.Parameters["@value"].Value = item.Value; MSSQLCEConn.ExecuteNonQuery(cmd); } tran.Commit(); }
/// <summary> /// Gets the size of the current. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> /// <remarks>Documented by Dev08, 2009-01-09</remarks> public int GetCurrentSize(int id) { BoxSizes?sizes = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CurrentBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] as BoxSizes?; if (sizes.HasValue) { return(sizes.Value.Sizes[id]); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { sizes = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0); //filled with temp/default values cmd.CommandText = "SELECT CS.box, count(*) AS count FROM UserCardState CS INNER JOIN Cards C ON CS.cards_id = C.id WHERE CS.active=1 and CS.user_id=@user_id and C.lm_id=@lm_id GROUP BY CS.box"; cmd.Parameters.Add("@user_id", Parent.CurrentUser.Id); cmd.Parameters.Add("@lm_id", Parent.CurrentUser.ConnectionString.LmId); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); while (reader.Read()) { sizes.Value.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]); } reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CurrentBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] = sizes.Value; return(sizes.Value.Sizes[id]); } }
/// <summary> /// Copies the statistics. /// </summary> /// <param name="lmId">The lm id.</param> /// <param name="statistic">The statistic.</param> /// <remarks>Documented by Dev08, 2009-02-09</remarks> public void CopyStatistics(int lmId, IStatistic statistic) { if (statistic.StartTimestamp == null || statistic.EndTimestamp == null) //do not save invalid sessions { return; } SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser); cmd.CommandText = "INSERT INTO LearningSessions(user_id, lm_id, starttime, endtime, sum_right, sum_wrong, " + "pool_content, box1_content, box2_content, box3_content, box4_content, box5_content, box6_content, box7_content, box8_content, box9_content, box10_content)" + "VALUES(@userid, @lmid, @starttime, @endtime, @sumright, @sumwrong, @pool, @b1, @b2, @b3, @b4, @b5, @b6, @b7, @b8, @b9, @b10)"; cmd.Parameters.Add("@userid", parent.CurrentUser.Id); cmd.Parameters.Add("@lmid", lmId); cmd.Parameters.Add("@starttime", statistic.StartTimestamp); cmd.Parameters.Add("@endtime", statistic.EndTimestamp); cmd.Parameters.Add("@sumright", statistic.Right); cmd.Parameters.Add("@sumwrong", statistic.Wrong); int counter = 0; foreach (int box in statistic.Boxes) { ++counter; cmd.Parameters.Add("@b" + counter.ToString(), box); } cmd.Parameters.Add("@pool", parent.GetParentDictionary().Boxes.Box[0].MaximalSize); //pool max size => cards in pools MSSQLCEConn.ExecuteNonQuery(cmd); }
/// <summary> /// Gets the size. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public int GetSize(int id) { BoxSizes?sizes = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.BoxSizes, Parent.CurrentUser.ConnectionString.LmId)] as BoxSizes?; if (sizes.HasValue) { return(sizes.Value.Sizes[id]); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { sizes = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0); cmd.CommandText = @"SELECT UserCardState.box AS box, count(*) AS count FROM UserCardState, Chapters_Cards WHERE UserCardState.active=1 and Chapters_Cards.cards_id=UserCardState.cards_id and UserCardState.user_id=@user_id and Chapters_Cards.chapters_id IN ( SELECT chapters_id FROM SelectedLearnChapters INNER JOIN UserProfilesLearningModulesSettings ON SelectedLearnChapters.settings_id=UserProfilesLearningModulesSettings.settings_id WHERE UserProfilesLearningModulesSettings.user_id=@user_id and UserProfilesLearningModulesSettings.lm_id=@lm_id ) GROUP BY UserCardState.box" ; cmd.Parameters.Add("@user_id", Parent.CurrentUser.Id); cmd.Parameters.Add("@lm_id", Parent.CurrentUser.ConnectionString.LmId); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); while (reader.Read()) { sizes.Value.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]); } reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.BoxSizes, Parent.CurrentUser.ConnectionString.LmId, cacheLifeSpan)] = sizes.Value; return(sizes.Value.Sizes[id]); } }
/// <summary> /// Gets the size of the default. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public int GetDefaultSize(int id) { BoxSizes?sizes = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.DefaultBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] as BoxSizes?; if (sizes.HasValue) { return(sizes.Value.Sizes[id]); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT Boxes.* FROM Boxes INNER JOIN Settings ON Boxes.id = Settings.boxes INNER JOIN LearningModules ON Settings.id = LearningModules.default_settings_id WHERE LearningModules.id = @lm_id"; cmd.Parameters.Add("@lm_id", Parent.CurrentUser.ConnectionString.LmId); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); reader.Read(); sizes = new BoxSizes(int.MaxValue, Convert.ToInt32(reader["box1_size"]), Convert.ToInt32(reader["box2_size"]), Convert.ToInt32(reader["box3_size"]), Convert.ToInt32(reader["box4_size"]), Convert.ToInt32(reader["box5_size"]), Convert.ToInt32(reader["box6_size"]), Convert.ToInt32(reader["box7_size"]), Convert.ToInt32(reader["box8_size"]), Convert.ToInt32(reader["box9_size"]), int.MaxValue); reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.DefaultBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] = sizes.Value; return(sizes.Value.Sizes[id]); } }
public List <ICard> GetCards(int id) { List <ICard> cardsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardsList, id)] as List <ICard>; if (cardsCache != null) { return(cardsCache); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@id"; cmd.Parameters.Add("@id", id); SqlCeDataReader reader; try { reader = MSSQLCEConn.ExecuteReader(cmd); } catch { throw new IdAccessException(id); } List <ICard> cards = new List <ICard>(); while (reader.Read()) { cards.Add(new DbCard(Convert.ToInt32(reader["cards_id"]), false, Parent)); } reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardsList, id)] = cards; return(cards); } }
public void RecalculateBoxSizes(int sessionId) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "UPDATE LearningSessions SET pool_content=@pool, box1_content=@b1, box2_content=@b2, box3_content=@b3, " + "box4_content=@b4, box5_content=@b5, box6_content=@b6, box7_content=@b7, box8_content=@b8, box9_content=@b9, box10_content=@b10 WHERE id=@sid AND user_id=@uid AND lm_id=@lmid"; cmd.Parameters.Add("sid", sessionId); cmd.Parameters.Add("@uid", Parent.CurrentUser.Id); cmd.Parameters.Add("@lmid", Parent.GetParentDictionary().Id); int counter = 0; int cardsInBoxes = 0; BoxSizes boxContent = GetCurrentBoxContent(); foreach (int box in boxContent.Sizes) { if (counter == 0) { cmd.Parameters.Add("@pool", box); ++counter; continue; } cmd.Parameters.Add("@b" + Convert.ToString(counter++), box); cardsInBoxes += box; } MSSQLCEConn.ExecuteNonQuery(cmd); }
/// <summary> /// Gets the learn sessions. /// </summary> /// <param name="lmId">The lm id.</param> /// <returns></returns> /// <remarks>Documented by Dev08, 2009-01-13</remarks> public List <int> GetLearnSessions(int lmId) { object learnSessionsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticsLearnSessions, lmId)]; MsSqlCeStatisticConnector c = MsSqlCeStatisticConnector.GetInstance(parent); //The data of GetLearnSession can be cached until a new session was created. if (runningSessionCopy == c.RunningSession && learnSessionsCache != null) { return(learnSessionsCache as List <int>); } //if cache is empty or the RunningSession has changed... SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser); cmd.CommandText = "SELECT id FROM \"LearningSessions\" WHERE lm_id = @lmId AND user_id=@uid ORDER BY endtime ASC"; cmd.Parameters.Add("@lmId", lmId); cmd.Parameters.Add("@uid", parent.CurrentUser.Id); List <int> output = new List <int>(); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); while (reader.Read()) { object id = reader["id"]; int id_converted = Convert.ToInt32(id); output.Add(id_converted); } reader.Close(); runningSessionCopy = c.RunningSession; //Save to Cache parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticsLearnSessions, lmId, Cache.DefaultStatisticValidationTime)] = output; return(output); }
/// <summary> /// Deletes the chapter. /// </summary> /// <param name="lmid">The id of the learning module.</param> /// <param name="id">The id.</param> /// <remarks>Documented by Dev02, 2008-08-05</remarks> /// <remarks>Documented by Dev08, 2009-01-12</remarks> public void DeleteChapter(int lmid, int id) { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "DELETE FROM LearningModules_Cards WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); "; cmd.CommandText += "DELETE FROM Cards_MediaContent WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); "; cmd.CommandText += "DELETE FROM UserCardState WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); "; cmd.CommandText += "DELETE FROM TextContent WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); "; cmd.CommandText += "DELETE FROM LearnLog WHERE cards_id IN (SELECT cards_id FROM Chapters_Cards WHERE chapters_id=@id); "; cmd.CommandText += "DELETE FROM Chapters_Cards WHERE chapters_id=@id; "; cmd.CommandText += "DELETE FROM SelectedLearnChapters WHERE chapters_id=@id; "; cmd.CommandText += "DELETE FROM Cards_MediaContent WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); "; cmd.CommandText += "DELETE FROM UserCardState WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); "; cmd.CommandText += "DELETE FROM TextContent WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); "; cmd.CommandText += "DELETE FROM LearnLog WHERE cards_id NOT IN (SELECT cards_id FROM LearningModules_Cards); "; cmd.CommandText += "DELETE FROM Cards WHERE id NOT IN (SELECT cards_id FROM LearningModules_Cards); "; cmd.CommandText += "DELETE FROM Chapters WHERE id=@id"; cmd.Parameters.Add("@id", id); if (MSSQLCEConn.ExecuteNonQuery(cmd) < 1) { throw new IdAccessException(id); } parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChaptersList, lmid)); } }
/// <summary> /// Gets the description of the chapter. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev02, 2008-08-05</remarks> /// <remarks>Documented by Dev05, 2009-01-15</remarks> public string GetDescription(int id) { string descriptionCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterDescription, id)] as string; if (descriptionCache != null) { return(descriptionCache); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT id, description FROM \"Chapters\" WHERE lm_id IN (SELECT lm_id FROM \"Chapters\" WHERE id=@id)"; cmd.Parameters.Add("@id", id); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); string chapterDescription = string.Empty; while (reader.Read()) { int chip = Convert.ToInt32(reader["id"]); string chapterDesc = reader["description"].ToString(); if (id == chip) { chapterDescription = chapterDesc; } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterDescription, chip)] = chapterDesc; } reader.Close(); return(chapterDescription); } }
/// <summary> /// Creates the new word. /// </summary> /// <param name="id">The id.</param> /// <param name="word">The word.</param> /// <param name="side">The side.</param> /// <param name="type">The type.</param> /// <param name="isDefault">if set to <c>true</c> [is default].</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public IWord CreateNewWord(int id, string word, Side side, WordType type, bool isDefault) { if (word != null) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT position FROM TextContent WHERE cards_id=@id AND side=@side AND type=@type ORDER BY position DESC"; cmd.Parameters.Add("@id", id); cmd.Parameters.Add("@side", side.ToString()); cmd.Parameters.Add("@type", type.ToString()); int currentPos = 0; object retval = MSSQLCEConn.ExecuteScalar(cmd); if (retval != DBNull.Value) { currentPos = Convert.ToInt32(retval); } cmd.Parameters.Clear(); cmd.CommandText = @"INSERT INTO TextContent (cards_id, text, side, type, position, is_default) VALUES (@id, @text, @side, @type, @position, @isdefault); SELECT @@IDENTITY;"; cmd.Parameters.Add("@id", id); cmd.Parameters.Add("@text", word); cmd.Parameters.Add("@side", side.ToString()); cmd.Parameters.Add("@type", type.ToString()); cmd.Parameters.Add("@position", currentPos + 10); cmd.Parameters.Add("@isdefault", isDefault); Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(ObjectLifetimeIdentifier.GetCacheObject(side, type), id)); return(new DbWord(Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)), word, type, isDefault, Parent)); } else { return(null); } }
/// <summary> /// Checks if the card exists and throws an IdAccessException if not. /// </summary> /// <param name="id">The card id.</param> /// <remarks>Documented by Dev03, 2008-08-06</remarks> /// <remarks>Documented by Dev08, 2009-01-09</remarks> public void CheckCardId(int id) { List <int> cardIdsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardIdsList, 0)] as List <int>; if (cardIdsCache != null && cardIdsCache.Contains(id)) { return; } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=@id)"; cmd.Parameters.Add("@id", id); int lmId = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)); cmd.Parameters.Clear(); cmd.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " + "(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=@lm_id)"; cmd.Parameters.Add("@lm_id", lmId); SqlCeDataReader reader; try { reader = MSSQLCEConn.ExecuteReader(cmd); } catch { throw new IdAccessException(id); } List <int> cardIds = new List <int>(); while (reader.Read()) { cardIds.Add(Convert.ToInt32(reader["id"])); } reader.Close(); parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardIdsList, 0)] = cardIds; } }
/// <summary> /// Gets the title of the chapter. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev02, 2008-08-05</remarks> /// <remarks>Documented by Dev05, 2009-01-15</remarks> public string GetTitle(int id) { string titleCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterTitle, id)] as string; if (titleCache != null) { return(titleCache); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT id, title FROM \"Chapters\" WHERE lm_id IN (SELECT lm_id FROM \"Chapters\" WHERE id=@id);"; cmd.Parameters.Add("@id", id); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); string title = string.Empty; while (reader.Read()) { int chapterId = Convert.ToInt32(reader["id"]); string chapterTitle = Convert.ToString(reader["title"]); if (id == chapterId) { title = chapterTitle; } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChapterTitle, chapterId, new TimeSpan(0, 10, 0))] = chapterTitle; } reader.Close(); return(title); } }
/// <summary> /// Sets the chapter for a card. /// </summary> /// <param name="id">The card id.</param> /// <param name="chapter">The chapter id.</param> /// <remarks>Documented by Dev03, 2008-08-06</remarks> /// <remarks>Documented by Dev08, 2009-01-09</remarks> public void SetChapter(int id, int chapter) { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { SqlCeTransaction transaction = cmd.Connection.BeginTransaction(); cmd.CommandText = "SELECT count(*) FROM \"Chapters\" WHERE id=@chapterid"; cmd.Parameters.Add("chapterid", chapter); if (Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)) < 1) { throw new IdAccessException(chapter); } Dictionary <int, int> cardChapterCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardChapterList, 0)] as Dictionary <int, int>; if (cardChapterCache != null) { cardChapterCache[id] = chapter; } using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd2.CommandText = "DELETE FROM \"Chapters_Cards\" WHERE cards_id=@id; "; cmd2.CommandText += "INSERT INTO \"Chapters_Cards\" (chapters_id, cards_id) VALUES (@chapterid, @id);"; cmd2.CommandText += "UPDATE Cards SET chapters_id=@chapterid WHERE id=@id;"; cmd2.Parameters.Add("@chapterid", chapter); cmd2.Parameters.Add("@id", id); MSSQLCEConn.ExecuteNonQuery(cmd2); } transaction.Commit(); } }
/// <summary> /// Deletes the media object. /// </summary> /// <param name="id">The id.</param> /// <remarks>Documented by Dev03, 2008-08-05</remarks> /// <remarks>Documented by Dev03, 2009-01-13</remarks> public void DeleteMedia(int id) { SqlCeCommand deletecmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); deletecmd.CommandText = "DELETE FROM MediaProperties WHERE media_id=@id; DELETE FROM MediaContent WHERE id=@id;"; deletecmd.Parameters.Add("@id", id); MSSQLCEConn.ExecuteNonQuery(deletecmd); }
/// <summary> /// Gets the default. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public bool GetDefault(int id) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT is_default FROM TextContent WHERE id=@id"; cmd.Parameters.Add("@id", id); return(Convert.ToBoolean(MSSQLCEConn.ExecuteScalar(cmd))); }
/// <summary> /// Gets the word. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public string GetWord(int id) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT text FROM TextContent WHERE id=@id"; cmd.Parameters.Add("@id", id); return(MSSQLCEConn.ExecuteScalar(cmd).ToString()); }
/// <summary> /// Gets the type. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public WordType GetType(int id) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT type FROM TextContent WHERE id=@id"; cmd.Parameters.Add("@id", id); return((WordType)Enum.Parse(typeof(WordType), MSSQLCEConn.ExecuteScalar(cmd).ToString(), true)); }
/// <summary> /// Gets the chapter for a card. /// </summary> /// <param name="id">The card id.</param> /// <returns></returns> /// <remarks>Documented by Dev03, 2008-08-06</remarks> /// <remarks>Documented by Dev08, 2009-01-09</remarks> public int GetChapter(int id) { Dictionary <int, int> cardChaptersCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardChapterList, 0)] as Dictionary <int, int>; if (cardChaptersCache != null && cardChaptersCache.ContainsKey(id)) { return(cardChaptersCache[id]); } using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=@id)"; cmd.Parameters.Add("@id", id); int lmid = Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)); cmd.Parameters.Clear(); cmd.CommandText = "SELECT chapters_id, cards_id FROM Chapters_Cards WHERE chapters_id IN " + "(SELECT chapters_id FROM Chapters WHERE lm_id=@lmid)"; cmd.Parameters.Add("@lmid", lmid); SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd); Dictionary <int, int> cardChapters = new Dictionary <int, int>(); while (reader.Read()) { object[] chapters = new object[2]; reader.GetValues(chapters); int chid = Convert.ToInt32(chapters[0]); int cid = Convert.ToInt32(chapters[1]); if (!cardChapters.ContainsKey(cid)) { cardChapters[cid] = chid; } } reader.Close(); // this should fix the bug where for some reason a card didn't have chapter assigned [ML-1708] (and similar) int chapterId = 0; if (!cardChapters.TryGetValue(id, out chapterId)) { lock (cardChapters) { foreach (int c in cardChapters.Values) { chapterId = c; break; } SetChapter(id, chapterId); cardChapters.Add(id, chapterId); } } parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardChapterList, 0, new TimeSpan(23, 59, 59))] = cardChapters; return(chapterId); } }
/// <summary> /// Gets the GUID. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev08, 2009-01-12</remarks> public string GetGuid(int id) { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "SELECT guid FROM \"LearningModules\" WHERE id=@id"; cmd.Parameters.Add("@id", id); return(Convert.ToString(MSSQLCEConn.ExecuteScalar(cmd))); } }
/// <summary> /// Sets the default. /// </summary> /// <param name="id">The id.</param> /// <param name="Default">if set to <c>true</c> [default].</param> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public void SetDefault(int id, bool Default) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "UPDATE TextContent SET is_default=@isdefault WHERE id=@id"; cmd.Parameters.Add("@id", id); cmd.Parameters.Add("@isdefault", Default); MSSQLCEConn.ExecuteNonQuery(cmd); }
/// <summary> /// Determines whether this media is available. /// </summary> /// <param name="id">The id.</param> /// <returns> /// <c>true</c> if media is available; otherwise, <c>false</c>. /// </returns> /// <remarks>Documented by Dev05, 2009-03-30</remarks> public bool IsMediaAvailable(int id) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "SELECT count(*) FROM MediaContent WHERE id=@id AND data IS NOT NULL"; cmd.Parameters.Add("@id", id); return(MSSQLCEConn.ExecuteScalar <int>(cmd).Value > 0); }
/// <summary> /// Gets the associated lm id of the chapter. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev02, 2008-08-05</remarks> /// <remarks>Documented by Dev05, 2009-01-15</remarks> public int GetLmId(int id) { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser)) { cmd.CommandText = "SELECT lm_id FROM \"Chapters\" WHERE id=@id"; cmd.Parameters.Add("@id", id); return(Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd))); } }
/// <summary> /// Clears the media for card style. /// </summary> /// <param name="Id">The style id.</param> /// <remarks>Documented by Dev03, 2009-03-05</remarks> /// <remarks>Documented by Dev03, 2009-03-05</remarks> public void ClearMediaForCardStyle(int Id) { using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser)) { cmd.CommandText = "DELETE FROM \"MediaContent_CardStyles\" WHERE cardstyles_id = @cardstyle_id"; cmd.Parameters.Add("@cardstyle_id", Id); MSSQLCEConn.ExecuteNonQuery(cmd); } }
/// <summary> /// Sets the word. /// </summary> /// <param name="id">The id.</param> /// <param name="Word">The word.</param> /// <remarks>Documented by Dev03, 2009-01-09</remarks> public void SetWord(int id, string Word) { SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser); cmd.CommandText = "UPDATE TextContent SET text=@text WHERE id=@id"; cmd.Parameters.Add("@id", id); cmd.Parameters.Add("@text", Word); MSSQLCEConn.ExecuteNonQuery(cmd); }