public SQLite.SQLiteCommand getCommand (string pSQL)
{
var cmd = new SQLite.SQLiteCommand (this.database.getConnection());
cmd.CommandText = pSQL;
return cmd;
}
/// <summary>
/// Adds a new user account.
/// </summary>
/// <param name="username">Username.</param>
/// <param name="password">Password.</param>
public void AddAccount(string username, string password)
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "insert into accounts(username,password) values('" + username + "','" + password + "')";
cmd.ExecuteNonQuery();
}
} catch (Exception ex) {
Console.WriteLine ("Error:" + ex.Message);
}
}
//Function for executing qurey to the database, pass in query string
public void runQuery(string query)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath))
{
var cmd = new SQLite.SQLiteCommand(conn);
cmd.CommandText = query;
var rowcount = cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
}
}
public List<ToDO> ViewAllNotes()
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
var NoteList = cmd.ExecuteQuery<ToDO> ();
return NoteList;
}
} catch(Exception e)
{
Console.WriteLine ("Error: " + e.Message);
return null;
}
}
public List<ListInfo> ViewAll()
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) //-------------------Prepares connection to database
{
var cmd = new SQLite.SQLiteCommand(conn); //----------------------------Prepares communication with database
cmd.CommandText = "Select NoteID, Title, Body, Dt from NOTES_TBL"; //---SQLite statement to pass back
var NoteList = cmd.ExecuteQuery<ListInfo>(); //-------------------------Reads information from db and prepares it in a list
return NoteList; //-----------------------------------------------------Passes list to data adapter
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message); //--------------------------------Write error in english to output
return null;
}
}
public void editNote(string title, string body, string id)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) //----------------------------------------------------------Prepares connection to database
{
var cmd = new SQLite.SQLiteCommand(conn); //-------------------------------------------------------------------Prepares communication with database
string sql = "UPDATE NOTES_TBL SET Title = '" + title + "', Body = '" + body + "' WHERE NoteID = " + id; //---SQLite query to pass back
cmd.CommandText = sql; //--------------------------------------------------------------------------------------Passes query to db
cmd.ExecuteNonQuery(); //--------------------------------------------------------------------------------------Writes information to db
ViewAll(); //--------------------------------------------------------------------------------------------------Gets a list of the notes and passes it in
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message); //-----------------------------------------------------------------------Write error in english to output
}
}
public List<city_names> loadWords()
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "select * from cities";
var tempList = cmd.ExecuteQuery<city_names> ();
return tempList;
}
} catch (Exception ex) {
Console.WriteLine ("Error:" + ex.Message);
return null;
}
}
/// <summary>
/// Loads all the saved High scores
/// </summary>
/// <returns>highscores</returns>
public List<highscore> getScores()
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "select * from highscores order by score desc";
var tempList = cmd.ExecuteQuery<highscore> ();
return tempList;
}
} catch (Exception ex) {
Console.WriteLine ("Error:" + ex.Message);
return null;
}
}
/// <summary>
/// Gets the previous best highscore of the player.
/// </summary>
/// <returns>The previous highscore.</returns>
/// <param name="username">Username.</param>
public int getPrevHighscore(string username)
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "select max(score) from highscores where username like '%" + username + "%'";
int temp= cmd.ExecuteScalar<int> ();
return temp;
}
} catch (Exception ex) {
Console.WriteLine ("Error:" + ex.Message);
return 0;
}
}
//Function for reading locations from the database
public List<ClassLocations> readLocations()
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath))
{
var cmd = new SQLite.SQLiteCommand(conn);
//Select word from given category
cmd.CommandText = "SELECT * FROM LOCATIONS";
var LocationList = cmd.ExecuteQuery<ClassLocations>();
return LocationList;
}
}
catch (Exception e)
{
Console.WriteLine("Error:" + e.Message);
return null;
}
}
public List<ToDo> ViewAll()
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath))
{
var cmd = new SQLite.SQLiteCommand(conn);
// cmd.CommandText = "Select * from words";
//SELECT ONE WORD RANDOMLY
cmd.CommandText = "SELECT * FROM words ORDER BY RANDOM() LIMIT 1";
var NoteList = cmd.ExecuteQuery<ToDo>();
return NoteList;
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
return null;
}
}
public void writeNote(string title, string dt, string body)
{
try
{
using (var conn = new SQLite.SQLiteConnection(dbPath)) //--------------------------------------------------------------Prepares connection to database
{
var cmd = new SQLite.SQLiteCommand(conn); //-----------------------------------------------------------------------Prepares communication with database
string sql = "Insert into NOTES_TBL (Title, Body, Dt) values ('" + title + "','" + body + "', '" + dt + "')"; //---SQLite query to pass back
cmd.CommandText = sql; //------------------------------------------------------------------------------------------Passes query to db
cmd.ExecuteNonQuery(); //------------------------------------------------------------------------------------------Writes information to db
ViewAll(); //------------------------------------------------------------------------------------------------------Gets a list of the notes and passes it in
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message); //---------------------------------------------------------------------------Write error in english to output
}
}
/// <summary>
/// Sets the new highscore.
/// </summary>
/// <param name="username">Username.</param>
/// <param name="score">Score.</param>
public void setNewHighscore(string username, int score)
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "insert into highscores(username,score) values('" + username + "','" + score + "')";
cmd.ExecuteNonQuery();
}
} catch (Exception ex) {
Console.WriteLine ("Error:" + ex.Message);
}
}
/// <summary>
/// Check if the specified user exists
/// </summary>
/// <param name="username">Username.</param>
public List<accounts> login(string username)
{
try
{
using (var conn = new SQLite.SQLiteConnection (dbPath))
{
var cmd = new SQLite.SQLiteCommand (conn);
cmd.CommandText = "select * from accounts where username like '%" + username + "%'";
var tempList = cmd.ExecuteQuery<accounts> ();
return tempList;
}
} catch (Exception ex) {
Console.WriteLine ("Error:" + ex.Message);
return null;
}
}