public void CreateDatabase()
{
SQLiteDbConnection dbConnection = new SQLiteDbConnection();
//SQLiteDbConnection dbConnection = (SQLiteDbConnection) DatabaseConnection;
SQLiteCommand cmd;
try
{
if (!dbConnection.IsValidDbConnection)
{
using (var conn = (SQLiteConnection)dbConnection.Connect())
{
cmd = new SQLiteCommand(conn);
conn.Open();
cmd.CommandText = "DROP TABLE IF EXISTS Users";
cmd.ExecuteNonQuery();
cmd.CommandText = @"
CREATE TABLE Users (id INTEGER PRIMARY KEY,
username TEXT, password TEXT, usertype INTEGER) ";
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddUser(User obj)
{
SQLiteDbConnection dbConnection = new SQLiteDbConnection();
SQLiteCommand cmd;
bool result;
try
{
using (var conn = (SQLiteConnection)dbConnection.Connect())
{
cmd = new SQLiteCommand(conn);
conn.Open();
cmd.CommandText = "INSERT INTO Users(username,password,usertype) VALUES(@username,@password,@usertype)";
cmd.Parameters.AddWithValue("@username", obj.username);
cmd.Parameters.AddWithValue("@password", obj.password);
cmd.Parameters.AddWithValue("@usertype", obj.usertype);
cmd.Prepare();
cmd.ExecuteNonQuery();
result = true;
}
}
catch (Exception ex)
{
result = false;
throw ex;
}
return(result);
}
public static SQLiteDbConnection SetupConnection()
{
var dbFilename = Path.GetTempFileName();
var connection = new SQLiteDbConnection(new LoggerFactory().CreateLogger <SQLiteDbConnection>(),
new Service.Configuration.DatabaseConfiguration()
{
DatabasePath = dbFilename
}
);
new DbPreDeploy(new LoggerFactory().CreateLogger <DbPreDeploy>()).Execute(connection);
return(connection);
}
public User GetUser(string user)
{
SQLiteDbConnection dbConnection = new SQLiteDbConnection();
SQLiteCommand cmd;
User ReturnUser = new User();
try
{
if (!dbConnection.IsValidDbConnection)
{
using (var conn = (SQLiteConnection)dbConnection.Connect())
{
cmd = new SQLiteCommand(conn);
conn.Open();
cmd.CommandText = "SELECT * FROM Users Where username=@username";
cmd.Parameters.AddWithValue("@username", user);
//cmd.Prepare();
SQLiteDataReader resultset = cmd.ExecuteReader();
while (resultset.Read())
{
ReturnUser.username = resultset.GetString(1);
ReturnUser.password = resultset.GetString(2);
ReturnUser.usertype = resultset.GetInt16(3);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(ReturnUser);
}