public void Load()
{
mDB.Open();
mLog.Debug("DB file opened successfully ");
using (SQLiteCommand load = new SQLiteCommand(@"SELECT CharacterId,CharacterName,AccessToken,Expires,RefreshToken,UnreadCount,Permissions,LastMailId,FolderExpanded FROM Accounts ORDER BY CharacterName", mDB))
{
using (SQLiteDataReader reader = (SQLiteDataReader)load.ExecuteReader())
{
while (reader.Read())
{
mAccounts.Add(new Account(this, reader));
}
}
}
using (SQLiteCommand load = new SQLiteCommand(@"SELECT Drafts.DraftId,Drafts.CharacterId,Drafts.IsReply,Drafts.LastModified,Drafts.Subject,Drafts.Body,DraftRecipients.RecipientType,DraftRecipients.RecipientId FROM Drafts LEFT JOIN DraftRecipients ON(Drafts.DraftId = DraftRecipients.DraftId)", mDB))
{
using (SQLiteDataReader reader = load.ExecuteReader())
{
DraftMessage msg = null;
while (reader.Read())
{
Guid id = reader.GetGuid(0);
if (msg == null || msg.DraftId != id)
{
msg = new DraftMessage(id)
{
DB = this
};
mDrafts.Add(msg);
msg.AccountId = reader.GetInt64(1);
msg.IsReply = reader.GetBoolean(2);
msg.LastModified = reader.GetDateTime(3);
msg.Subject = reader.GetString(4);
msg.Body = reader.GetString(5);
}
if (!reader.IsDBNull(6) && !reader.IsDBNull(7))
{
msg.Recipients.Add(new UserCache.EntityInfo()
{
EntityType = (Common.EntityType)reader.GetInt32(6),
EntityID = reader.GetInt64(7)
});
}
}
}
}
}
public async Task SaveDraftAsync(DraftMessage msg)
{
int count = 0;
using (var trns = mDB.BeginTransaction())
{
using (var dbop = new DBOperation())
{
try
{
var Cmd_SaveDraft = new SQLiteCommand(@"INSERT OR REPLACE INTO Drafts (DraftId,CharacterId,LastModified,Subject,IsReply,Body) VALUES(@draftid,@charid,@lastmodified,@subject,@isreply,@body)", mDB);
Cmd_SaveDraft.Transaction = trns;
Cmd_SaveDraft.Parameters.AddWithValue("@draftid", msg.DraftId.ToString());
Cmd_SaveDraft.Parameters.AddWithValue("@lastmodified", msg.LastModified = DateTime.Now);
Cmd_SaveDraft.Parameters.AddWithValue("@charid", msg.AccountId);
Cmd_SaveDraft.Parameters.AddWithValue("@subject", msg.Subject);
Cmd_SaveDraft.Parameters.AddWithValue("@isreply", msg.IsReply);
Cmd_SaveDraft.Parameters.AddWithValue("@body", msg.Body);
count = await Cmd_SaveDraft.ExecuteNonQueryAsync();
var Cmd_DeleteDraftReceipients = new SQLiteCommand(@"DELETE FROM DraftRecipients WHERE DraftId=@draftid", mDB);
Cmd_DeleteDraftReceipients.Transaction = trns;
Cmd_DeleteDraftReceipients.Parameters.AddWithValue("@draftid", msg.DraftId.ToString());
count = await Cmd_DeleteDraftReceipients.ExecuteNonQueryAsync();
var Cmd_AddDraftReceipient = new SQLiteCommand(@"INSERT OR REPLACE INTO DraftRecipients (DraftId,RecipientType,RecipientId) VALUES(@draftid,@type,@id)", mDB);
Cmd_AddDraftReceipient.Transaction = trns;
Cmd_AddDraftReceipient.Parameters.AddWithValue("@draftid", null);
Cmd_AddDraftReceipient.Parameters.AddWithValue("@type", null);
Cmd_AddDraftReceipient.Parameters.AddWithValue("@id", null);
foreach (var i in msg.Recipients)
{
Cmd_AddDraftReceipient.Parameters["@draftid"].Value = msg.DraftId.ToString();
Cmd_AddDraftReceipient.Parameters["@type"].Value = (int)i.EntityType;
Cmd_AddDraftReceipient.Parameters["@id"].Value = i.EntityID;
count = await Cmd_AddDraftReceipient.ExecuteNonQueryAsync();
}
trns.Commit();
}
catch (SQLiteException e)
{
trns.Rollback();
throw new AccountDatabaseException(string.Format("Unable to save draft. Database error({0}).", e.ErrorCode));
}
}
}
}
public async Task DeleteDraftAsync(DraftMessage msg)
{
using (var dbop = new DBOperation())
{
try
{
mCmd_DeleteDraft.Parameters["@draftid"].Value = msg.DraftId.ToString();
await mCmd_DeleteDraft.ExecuteNonQueryAsync();
mDrafts.Remove(msg);
}
catch (SQLiteException e)
{
throw new AccountDatabaseException(string.Format("Unable to save draft. Database error({0}).", e.ErrorCode));
}
}
}