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); }