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);
}
}
