/// <summary>
/// Insert a WorkItemJournalEntry record into the database.
/// </summary>
/// <param name="journalEntry"></param>
/// <returns></returns>
public int InsertWorkItemJournalEntry(WorkItemJournalEntry journalEntry)
{
int workItemJournalEntryID = -1;
DateTime creation = DateTime.Now;
using (var connection = new SQLiteConnection(dbConnectionString))
{
using (var cmd = new SQLiteCommand(connection))
{
connection.Open();
cmd.CommandText = "INSERT INTO WorkItemJournal (WorkItem_ID, Header, Entry, CreationDateTime) " +
"VALUES (@wID, @header, @entry, @creation)";
cmd.Parameters.AddWithValue("@wID", journalEntry.WorkItem_ID);
cmd.Parameters.AddWithValue("@header", journalEntry.Title);
cmd.Parameters.AddWithValue("@entry", journalEntry.Entry);
cmd.Parameters.AddWithValue("@creation", creation);
cmd.ExecuteNonQuery();
journalEntry.CreationDateTime = creation;
// Get the identity value (to return)
cmd.CommandText = "SELECT last_insert_rowid()";
workItemJournalEntryID = (int)(Int64)cmd.ExecuteScalar();
journalEntry.WorkItemJournalID = workItemJournalEntryID;
}
connection.Close();
}
return(workItemJournalEntryID);
}
/// <summary>
/// Update a WorkItemJournalEntry record into the database.
/// </summary>
/// <param name="journalEntry"></param>
public void UpdateWorkItemJournalEntry(WorkItemJournalEntry journalEntry)
{
DateTime modificationDate = DateTime.Now;
using (var connection = new SQLiteConnection(dbConnectionString))
{
using (var cmd = new SQLiteCommand(connection))
{
connection.Open();
cmd.CommandText = "UPDATE WorkItemJournal" +
" SET Header = @header," +
" Entry = @entry," +
" ModificationDateTime = @mod" +
" WHERE Journal_ID = @journalID";
cmd.Parameters.AddWithValue("@header", journalEntry.Title);
cmd.Parameters.AddWithValue("@entry", journalEntry.Entry);
cmd.Parameters.AddWithValue("@mod", modificationDate);
cmd.Parameters.AddWithValue("@journalID", journalEntry.WorkItemJournalID);
cmd.ExecuteNonQuery();
journalEntry.CreationDateTime = modificationDate;
}
connection.Close();
}
}
/// <summary>
/// Load all of the Journals for a specified WorkItem.
/// </summary>
/// <param name="workItemID"></param>
/// <returns></returns>
public List <WorkItemJournalEntry> SelectWorkItemJournals(int workItemID)
{
List <WorkItemJournalEntry> rValue = new List <WorkItemJournalEntry>();
using (var connection = new SQLiteConnection(BaseRepository.dbConnectionString))
{
using (var cmd = new SQLiteCommand(connection))
{
connection.Open();
cmd.CommandText = "SELECT *" +
" FROM vwActiveWorkItemJournal" +
" WHERE WorkItem_ID = @workItemID" +
" ORDER BY CreationDateTime ASC";
cmd.Parameters.AddWithValue("@workItemID", workItemID);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
WorkItemJournalEntry journal = new WorkItemJournalEntry();
journal.WorkItem_ID = Convert.ToInt32(reader["WorkItem_ID"]);
if (reader["Header"].GetType() != typeof(DBNull))
{
journal.Title = (string)reader["Header"];
}
if (reader["Entry"].GetType() != typeof(DBNull))
{
journal.Entry = (string)reader["Entry"];
}
journal.CreationDateTime = DateTime.Parse(reader["CreationDateTime"].ToString());
if (reader["ModificationDateTime"].GetType() != typeof(DBNull))
{
journal.ModificationDateTime = DateTime.Parse(reader["ModificationDateTime"].ToString());
}
rValue.Add(journal);
}
}
connection.Close();
}
}
return(rValue);
}
