public ISettings GetUserSettings(int id) { DbSettings settingsCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserLearningModuleSettings, id)] as DbSettings; if (settingsCache != null) { return(settingsCache); } using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT \"GetUserSettings\"(:uid, :lm_id);"; cmd.Parameters.Add("uid", Parent.CurrentUser.Id); cmd.Parameters.Add("lm_id", id); int?settingsid = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser); if (!settingsid.HasValue) { return(null); } DbSettings settings = new DbSettings(settingsid.Value, false, Parent); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserLearningModuleSettings, id, Cache.DefaultSettingsValidationTime)] = settings; return(settings); } } }
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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT id FROM \"Cards\" WHERE id IN (SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=:id)"; cmd.Parameters.Add("id", id); NpgsqlDataReader reader; try { reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); } catch { throw new IdAccessException(id); } List <ICard> cards = new List <ICard>(); while (reader.Read()) { cards.Add(new DbCard(Convert.ToInt32(reader["id"]), false, Parent)); } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardsList, id)] = cards; return(cards); } } }
public IWord CreateNewWord(int id, string word, Side side, WordType type, bool isDefault) { if (word != null) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "INSERT INTO \"TextContent\" (cards_id, text, side, type, position, is_default) VALUES (:id, :text, :side, :type, " + "(COALESCE((SELECT position FROM \"TextContent\" WHERE cards_id=:id AND side=:side AND type=:type ORDER BY position DESC LIMIT 1), 0) + 10), " + ":isdefault) RETURNING id"; 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("isdefault", isDefault); Parent.CurrentUser.Cache.Uncache(ObjectLifetimeIdentifier.GetIdentifier(ObjectLifetimeIdentifier.GetCacheObject(side, type), id)); return(new DbWord(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)), word, type, isDefault, Parent)); } } } else { return(null); } }
/// <summary> /// Gets the extension stream. /// </summary> /// <param name="guid">The GUID.</param> /// <returns></returns> public Stream GetExtensionStream(Guid guid) { MemoryStream stream = null; using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { int noid = 0; using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT data FROM \"Extensions\" WHERE guid=:guid;"; cmd.Parameters.Add("guid", guid.ToString()); object obj = PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser); if (obj == null || obj == DBNull.Value || !(obj as long?).HasValue) { return(stream); } noid = Convert.ToInt32((obj as long?).Value); } NpgsqlTransaction tran = con.BeginTransaction(); try { LargeObjectManager lbm = new LargeObjectManager(con); LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE); byte[] buffer = LargeObjectToBuffer(largeObject); stream = new MemoryStream(buffer); largeObject.Close(); } catch { } finally { tran.Commit(); } } return(stream); }
/// <summary> /// Sets the extension actions. /// </summary> /// <param name="guid">The GUID.</param> /// <param name="extensionActions">The extension actions.</param> public void SetExtensionActions(Guid guid, IList <ExtensionAction> extensionActions) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { NpgsqlTransaction tran = con.BeginTransaction(); using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM \"ExtensionActions\" WHERE guid=:guid"; cmd.Parameters.Add("guid", guid.ToString()); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } foreach (ExtensionAction action in extensionActions) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "INSERT INTO \"ExtensionActions\" (guid, action, execution) VALUES (:guid, :action, :execution)"; cmd.Parameters.Add("guid", guid.ToString()); cmd.Parameters.Add("action", action.Kind.ToString()); cmd.Parameters.Add("execution", action.Execution.ToString()); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } } tran.Commit(); } }
private BoxSizes GetCurrentBoxContent() { BoxSizes sizes; using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { sizes = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0); cmd.CommandText = @"SELECT CS.box AS box, count(*) AS count FROM ""UserCardState"" CS INNER JOIN ""Cards"" C ON C.id = CS.cards_id AND C.lm_id=:lm_id WHERE CS.active = TRUE AND CS.user_id = :user_id GROUP BY CS.box"; cmd.Parameters.Add("user_id", Parent.CurrentUser.Id); cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId); NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); while (reader.Read()) { sizes.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]); } } } return(sizes); }
public void RecalculateBoxSizes(int sessionId) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { 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; } PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } } }
/// <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; } using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { 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 PostgreSQLConn.ExecuteNonQuery(cmd, parent.CurrentUser); } } }
/// <summary> /// Gets the number of all Media Objects/Files in this LearningModules. /// </summary> /// <param name="id">The id.</param> /// <returns></returns> /// <remarks>Documented by Dev08, 2008-10-02</remarks> public int GetDictionaryMediaObjectsCount(int id) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { int?mediaSizeObjectsCount; cmd.CommandText = "SELECT COUNT(*) AS LearningModuleMediaObjectsCount" + " FROM \"MediaContent\", \"Cards_MediaContent\", \"MediaProperties\"" + " WHERE \"Cards_MediaContent\".media_id = \"MediaContent\".id AND \"Cards_MediaContent\".cards_id IN" + " (SELECT id FROM \"Cards\", \"LearningModules_Cards\" WHERE \"LearningModules_Cards\".cards_id = \"Cards\".id AND" + " \"LearningModules_Cards\".lm_id = :id) AND \"MediaProperties\".media_id = \"MediaContent\".id AND" + "\"MediaProperties\".property = 'MediaSize'"; cmd.Parameters.Add("id", id); mediaSizeObjectsCount = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser); if (!mediaSizeObjectsCount.HasValue) { return(0); } return(mediaSizeObjectsCount.Value); } } }
public IList <int> GetLMIds() { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { IList <int> ids = new List <int>(); cmd.CommandText = "SELECT * FROM \"LearningModules\""; NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); int id; while (reader.Read()) { id = Convert.ToInt32(reader["id"]); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.DefaultLearningModuleSettings, id, new TimeSpan(0, 10, 0))] = new DbSettings(Convert.ToInt32(reader["default_settings_id"]), false, Parent); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LearningModuleAuthor, id)] = Convert.ToString(reader["author"]); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.LearningModuleTitle, id)] = Convert.ToString(reader["title"]); ids.Add(id); } return(ids); } } }
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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT id FROM \"Chapters\" WHERE lm_id=:lmid ORDER BY position ASC"; cmd.Parameters.Add("lmid", lmid); NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); while (reader.Read()) { list.Add(Convert.ToInt32(reader["id"])); } } } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.ChaptersList, lmid)] = list; return(list); }
public int AddNewLM(string guid, int categoryId, string title, string licenceKey, bool contentProtected, int calCount) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { int?lmId; using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT \"CreateNewLearningModule\"(:guid, (SELECT id FROM \"Categories\" WHERE global_id=:categoryid), :title)"; cmd.Parameters.Add("guid", guid); cmd.Parameters.Add("categoryid", categoryId); cmd.Parameters.Add("title", title); lmId = PostgreSQLConn.ExecuteScalar <int>(cmd, Parent.CurrentUser); } using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "UPDATE \"LearningModules\" SET licence_key=:lk, content_protected=:cp, cal_count=:cals WHERE id=:id"; cmd.Parameters.Add("id", lmId); cmd.Parameters.Add("lk", licenceKey); cmd.Parameters.Add("cp", contentProtected); cmd.Parameters.Add("cals", calCount); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } return(lmId.Value); } }
public string GetDescription(int id) { string descriptionCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterDescription, id)] as string; if (descriptionCache != null) { return(descriptionCache); } using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT id, description FROM \"Chapters\" WHERE lm_id=(SELECT lm_id FROM \"Chapters\" WHERE id=:id)"; cmd.Parameters.Add("id", id); NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); 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; } return(chapterDescription); } } }
/// <summary> /// Gets the start time stamp. /// </summary> /// <param name="sessionId">The session id.</param> /// <returns></returns> /// <remarks>Documented by Dev08, 2009-01-28</remarks> public DateTime?GetStartTimeStamp(int sessionId) { object startTimeStamp = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticStartTime, sessionId)]; if (startTimeStamp == null || RunningSession == sessionId) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT starttime FROM \"LearningSessions\" WHERE \"LearningSessions\".id = :sessId AND user_id=:uid AND lm_id=:lmid"; cmd.Parameters.Add("sessId", sessionId); cmd.Parameters.Add("uid", parent.CurrentUser.Id); cmd.Parameters.Add("lmid", parent.GetParentDictionary().Id); DateTime?dt = PostgreSQLConn.ExecuteScalar <DateTime>(cmd, parent.CurrentUser); //Save to Cache parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticStartTime, sessionId, Cache.DefaultStatisticValidationTime)] = dt; return(dt); } } } return(startTimeStamp as DateTime?); }
/// <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, MediaProperty property, string value) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { NpgsqlTransaction tran = con.BeginTransaction(); using (NpgsqlCommand cmd = con.CreateCommand()) { 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;"; } cmd.Parameters.Add("media_id", id); cmd.Parameters.Add("property", property.ToString()); cmd.Parameters.Add("value", value); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } tran.Commit(); } }
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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { sizes = new BoxSizes(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0); cmd.CommandText = @"SELECT box, count(*) AS count FROM ""UserCardState"" WHERE active=true and user_id=:user_id and cards_id IN (SELECT cards_id FROM ""LearningModules_Cards"" WHERE lm_id=:lm_id) GROUP BY box"; cmd.Parameters.Add("user_id", Parent.CurrentUser.Id); cmd.Parameters.Add("lm_id", Parent.CurrentUser.ConnectionString.LmId); NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser, false); while (reader.Read()) { sizes.Value.Sizes[Convert.ToInt32(reader["box"])] = Convert.ToInt32(reader["count"]); } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CurrentBoxSizes, Parent.CurrentUser.ConnectionString.LmId)] = sizes.Value; return(sizes.Value.Sizes[id]); } } }
/// <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) { using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { int noid = 0; using (NpgsqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT data FROM \"MediaContent\" WHERE id=:id;"; cmd.Parameters.Add("id", id); noid = Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)); } NpgsqlTransaction tran = conn.BeginTransaction(); LargeObjectManager lbm = new LargeObjectManager(conn); lbm.Delete(noid); using (NpgsqlCommand deletecmd = conn.CreateCommand()) { deletecmd.CommandText = "DELETE FROM \"MediaContent\" WHERE id=:id;"; deletecmd.Parameters.Add("id", id); PostgreSQLConn.ExecuteNonQuery(deletecmd, Parent.CurrentUser); } tran.Commit(); } }
/// <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 <MediaProperty, string> properties) { using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { NpgsqlTransaction tran = conn.BeginTransaction(); using (NpgsqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "DELETE FROM \"MediaProperties\" WHERE media_id=:id;"; cmd.Parameters.Add("id", id); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } foreach (KeyValuePair <MediaProperty, string> item in properties) { using (NpgsqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO \"MediaProperties\" (media_id, property, \"value\") VALUES (:media_id, :property, :value);"; cmd.Parameters.Add("media_id", id); cmd.Parameters.Add("property", item.Key.ToString()); cmd.Parameters.Add("value", item.Value); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } } tran.Commit(); } }
/// <summary> /// Creates a new media object. /// </summary> /// <param name="media">The memory stream containing the media.</param> /// <param name="type">The media type.</param> /// <param name="rpu">A delegate of type <see cref="StatusMessageReportProgress"/> used to send messages back to the calling object.</param> /// <param name="caller">The calling object.</param> /// <returns>The id for the new media object.</returns> /// <remarks>Documented by Dev03, 2008-08-05</remarks> /// <remarks>Documented by Dev03, 2009-01-13</remarks> public int CreateMedia(Stream media, EMedia type, StatusMessageReportProgress rpu, object caller) { using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { NpgsqlTransaction tran = conn.BeginTransaction(); LargeObjectManager lbm = new LargeObjectManager(conn); int noid = lbm.Create(LargeObjectManager.READWRITE); LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE); byte[] buffer = new byte[media.Length]; media.Read(buffer, 0, (int)media.Length); BufferToLargeObject(buffer, largeObject, rpu, caller); largeObject.Close(); int newId = 0; using (NpgsqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO \"MediaContent\" (data, media_type) VALUES (:data, :type) RETURNING id;"; cmd.Parameters.Add("data", noid); cmd.Parameters.Add("type", type.ToString()); newId = Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)); } tran.Commit(); return(newId); } }
/// <summary> /// Updates the media. /// </summary> /// <param name="id">The id.</param> /// <param name="media">The media.</param> /// <remarks>Documented by Dev02, 2008-08-06</remarks> /// <remarks>Documented by Dev03, 2009-01-13</remarks> public void UpdateMedia(int id, Stream media) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { NpgsqlTransaction tran = con.BeginTransaction(); int noid; using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT data FROM \"MediaContent\" WHERE id=:id"; cmd.Parameters.Add("id", id); noid = Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)); } LargeObjectManager lbm = new LargeObjectManager(con); lbm.Delete(noid); noid = lbm.Create(LargeObjectManager.READWRITE); LargeObject largeObject = lbm.Open(noid, LargeObjectManager.READWRITE); byte[] buffer = new byte[media.Length]; media.Read(buffer, 0, (int)media.Length); BufferToLargeObject(buffer, largeObject); largeObject.Close(); using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "UPDATE \"MediaContent\" SET data=:data WHERE id=:id"; cmd.Parameters.Add("id", id); cmd.Parameters.Add("data", noid); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } tran.Commit(); } }
/// <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 Dev03, 2009-01-13</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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT id FROM \"Cards\" WHERE id IN " + "(SELECT cards_id FROM \"LearningModules_Cards\" WHERE lm_id=(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=:id))"; cmd.Parameters.Add("id", id); NpgsqlDataReader reader; try { reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); } catch { throw new IdAccessException(id); } List <int> cardIds = new List <int>(); while (reader.Read()) { cardIds.Add(Convert.ToInt32(reader["id"])); } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardIdsList, 0)] = cardIds; } } }
public string GetTitle(int id) { string titleCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.ChapterTitle, id)] as string; if (titleCache != null) { return(titleCache); } using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT id, title FROM \"Chapters\" WHERE lm_id=(SELECT lm_id FROM \"Chapters\" WHERE id=:id)"; cmd.Parameters.Add("id", id); NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); 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; } return(title); } } }
public IList <UserStruct> GetUserList() { IList <UserStruct> users = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.UserList, 0)] as IList <UserStruct>; if (users != null) { return(users); } using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM \"GetUserList\"()"; NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser, false); users = new List <UserStruct>(); while (reader.Read()) { UserStruct user = new UserStruct(reader["username"].ToString(), (UserAuthenticationTyp)Enum.Parse(typeof(UserAuthenticationTyp), reader["typ"].ToString())); users.Add(user); } reader.Close(); Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.UserList, 0, new TimeSpan(0, 0, 30))] = users; return(users); } } }
/// <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 Dev03, 2009-01-13</remarks> public void SetChapter(int id, int chapter) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { NpgsqlTransaction transaction = con.BeginTransaction(); using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT count(*) FROM \"Chapters\" WHERE id=:chapterid"; cmd.Parameters.Add("chapterid", chapter); if (Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser)) < 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 (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM \"Chapters_Cards\" WHERE cards_id=:id; "; cmd.CommandText += "INSERT INTO \"Chapters_Cards\" (chapters_id, cards_id) VALUES (:chapterid, :id);"; cmd.CommandText += "UPDATE \"Cards\" SET chapters_id=:chapterid WHERE id=:id;"; cmd.Parameters.Add("chapterid", chapter); cmd.Parameters.Add("id", id); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } transaction.Commit(); } }
/// <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 Dev03, 2009-01-13</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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM \"Chapters_Cards\" WHERE chapters_id IN " + "(SELECT chapters_id FROM \"Chapters\" WHERE lm_id=(SELECT lm_id FROM \"LearningModules_Cards\" WHERE cards_id=:id))"; cmd.Parameters.Add("id", id); NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd, Parent.CurrentUser); Dictionary <int, int> cardChapters = new Dictionary <int, int>(); while (reader.Read()) { int chid = Convert.ToInt32(reader["chapters_id"]); int cid = Convert.ToInt32(reader["cards_id"]); if (!cardChapters.ContainsKey(cid)) { cardChapters.Add(cid, chid); } } Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.CardChapterList, 0)] = cardChapters; return(cardChapters[id]); } } }
/// <summary> /// Gets the wrong cards. /// </summary> /// <param name="sessionId">The session id.</param> /// <returns></returns> /// <remarks>Documented by Dev08, 2009-01-28</remarks> public int?GetWrongCards(int sessionId) { object wrongCardsCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.StatisticWrongCards, sessionId)]; if (wrongCardsCache == null || RunningSession == sessionId) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT sum_wrong FROM \"LearningSessions\" WHERE \"LearningSessions\".id=:sessId AND user_id=:uid AND lm_id=:lmid"; cmd.Parameters.Add("sessId", sessionId); cmd.Parameters.Add("uid", parent.CurrentUser.Id); cmd.Parameters.Add("lmid", parent.GetParentDictionary().Id); int?wrongCards = PostgreSQLConn.ExecuteScalar <int>(cmd, parent.CurrentUser); //Save to Cache parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.StatisticWrongCards, sessionId, Cache.DefaultStatisticValidationTime)] = wrongCards; return(wrongCards); } } } return(wrongCardsCache as int?); }
public bool CheckUserSession() { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { return(PostgreSQLConn.CheckSession(cmd, Parent.CurrentUser)); } } }
public int GetLMCount() { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT count(*) as count FROM \"LearningModules\""; return(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser))); } } }
public int GetLmId(int id) { using (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT lm_id FROM \"Chapters\" WHERE id=:id"; cmd.Parameters.Add("id", id); return(Convert.ToInt32(PostgreSQLConn.ExecuteScalar(cmd, Parent.CurrentUser))); } } }
/// <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 (NpgsqlConnection con = PostgreSQLConn.CreateConnection(Parent.CurrentUser)) { using (NpgsqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM \"MediaContent_CardStyles\" WHERE cardstyles_id = :cardstyles_id"; cmd.Parameters.Add("cardstyles_id", Id); PostgreSQLConn.ExecuteNonQuery(cmd, Parent.CurrentUser); } } }