public static async void UpdateBoard(Models.Board board)
{
try
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = @"UPDATE Boards SET
Name = @Name,
Color = @Color
WHERE ID = @ID;";
insertCommand.Parameters.AddWithValue("@Name", board.Name);
insertCommand.Parameters.AddWithValue("@Color", board.Color);
insertCommand.Parameters.AddWithValue("@ID", board.ID);
await insertCommand.ExecuteReaderAsync();
}
}
catch (Exception eSql)
{
System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}");
}
}
public async static Task <List <Board> > InitializeDatabase()
{
await ApplicationData.Current.LocalFolder.CreateFileAsync(DBName, CreationCollisionOption.OpenIfExists);
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
String tableCommand = @"CREATE TABLE IF NOT
EXISTS Tasks (ID INTEGER NOT NULL PRIMARY KEY,
Title Text,
Description Text,
Position Text NOT NULL,
SprintPosition Text,
BoardID INTEGER NOT NULL,
Status Integer NOT NULL,
SprintRelevant Integer NOT NULL,
CreationDate Text NOT NULL,
DueDate Text,
Priority Integer NOT NULL);
CREATE TABLE IF NOT EXISTS
Boards (ID INTEGER NOT NULL PRIMARY KEY,
Name Text NOT NULL,
Color INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS
Notes (ID INTEGER NOT NULL PRIMARY KEY,
Title Text,
Content Text);";
SqliteCommand createTable = new SqliteCommand(tableCommand, db);
await createTable.ExecuteReaderAsync();
db.Close();
List <Models.Board> boards = (await GetBoards()).ToList();
Models.Board defaultBoard = boards.Find(b => b.ID == 1);
if (defaultBoard == null)
{
defaultBoard = new Models.Board
{
Name = "Unassigned",
Color = Models.Color.Transparent
};
await AddBoard(defaultBoard);
boards.Add(defaultBoard);
}
return(boards);
}
}
public static async Task <bool> DeleteBoard(Models.Board board)
{
try
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand
("SELECT COUNT(ID) FROM Tasks WHERE Tasks.BoardID = @BoardID", db);
selectCommand.Parameters.AddWithValue("@BoardID", board.ID);
SqliteDataReader query = selectCommand.ExecuteReader();
long count = 0;
if (await query.ReadAsync())
{
count = query.GetInt32(0);
}
if (count > 0)
{
throw new Exception("Cant delete board because it is still in use");
}
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = @"DELETE FROM Boards
WHERE ID = @ID;";
insertCommand.Parameters.AddWithValue("@ID", board.ID);
await insertCommand.ExecuteReaderAsync();
return(true);
}
}
catch (Exception eSql)
{
System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}");
return(false);
}
}
public static async Task <IEnumerable <Models.Board> > GetBoards()
{
string getDataQuery = @"
SELECT Boards.ID,
Boards.Name,
Boards.Color
FROM Boards;";
List <Models.Board> entries = new List <Models.Board>();
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
try
{
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand selectCommand = new SqliteCommand
(getDataQuery, db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (await query.ReadAsync())
{
Models.Board board = new Models.Board()
{
ID = query.GetInt32(0),
Name = query.GetString(1),
Color = (Models.Color)System.Enum.Parse(typeof(Models.Color), query.GetString(2))
};
entries.Add(board);
}
db.Close();
}
}
catch (Exception eSql)
{
// Your code may benefit from more robust error handling or logging.
// This logging is just a reminder that you should handle exceptions when connecting to remote data.
System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}");
}
return(entries);
}
public static async Task <long> AddBoard(Models.Board board)
{
try
{
string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DBName);
using (SqliteConnection db =
new SqliteConnection($"Filename={dbpath}"))
{
db.Open();
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = @"INSERT INTO Boards VALUES (NULL, @Name, @Color);";
insertCommand.Parameters.AddWithValue("@Name", board.Name);
insertCommand.Parameters.AddWithValue("@Color", board.Color);
await insertCommand.ExecuteReaderAsync();
SqliteCommand selectCommand = new SqliteCommand
("SELECT last_insert_rowid()", db);
SqliteDataReader query = selectCommand.ExecuteReader();
long id;
if (await query.ReadAsync())
{
id = query.GetInt32(0);
}
else
{
id = -1;
}
db.Close();
return(id);
}
}
catch (Exception eSql)
{
System.Diagnostics.Debug.WriteLine($"Exception: {eSql.Message} {eSql.InnerException?.Message}");
return(-1);
}
}
