public override bool GetPasswordHash(string pLoginName, out int pUserId, out string pPasswordHash) { bool bResult = false; pUserId = 0; pPasswordHash = null; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "SELECT [id],[password_hash] FROM [mlac_tbl_users] WHERE LOWER([login_name])=@pLoginName;"; cmd.Parameters.Add(new SQLiteParameter("@pLoginName", pLoginName.ToLower())); cnn.Open(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { pUserId = reader.GetInt32(0); pPasswordHash = reader.GetString(1); bResult = true; } } } } return(bResult); }
public override ACUser LoadUserInfo(int pUserId) { ACUser result = null; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "SELECT [login_name],[first_name],[last_name],[email] FROM [mlac_tbl_users] WHERE [id]=@pUserId;"; cmd.Parameters.Add(new SQLiteParameter("@pUserId", pUserId)); cnn.Open(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { result = new ACUser() { Id = pUserId, LoginName = reader.GetString(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), Email = reader.GetString(3) }; } } } } return(result); }
public override int CreateUser(string pLoginName, string pPasswordHash, string pFirstName, string pLastName, string pEmail, DateTime pCreatedOn, bool pIsActive) { int iResult = -1; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "INSERT INTO [mlac_tbl_users] ([login_name],[password_hash],[first_name],[last_name],[email],[created_on],[is_active]) " + "VALUES (@pLoginName,@pPasswordHash,@pFirstName,@pLastName,@pEmail,@pCreatedOn,@pIsActive)" + ";SELECT last_insert_rowid() AS [id];"; cmd.Parameters.Add(new SQLiteParameter("@pLoginName", pLoginName)); cmd.Parameters.Add(new SQLiteParameter("@pPasswordHash", pPasswordHash)); cmd.Parameters.Add(new SQLiteParameter("@pFirstName", pFirstName)); cmd.Parameters.Add(new SQLiteParameter("@pLastName", pLastName)); cmd.Parameters.Add(new SQLiteParameter("@pEmail", pEmail)); cmd.Parameters.Add(new SQLiteParameter("@pCreatedOn", pCreatedOn)); cmd.Parameters.Add(new SQLiteParameter("@pIsActive", pIsActive)); cnn.Open(); iResult = Convert.ToInt32(cmd.ExecuteScalar()); } } return(iResult); }
public override bool IsEmailAvailable(string pEmail) { bool bResult = false; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "SELECT COUNT(*) FROM [mlac_tbl_users] WHERE LOWER([email])=@pEmail;"; cmd.Parameters.Add(new SQLiteParameter("@pEmail", pEmail.ToLower())); cnn.Open(); bResult = (Convert.ToInt32(cmd.ExecuteScalar()) == 0); } } return(bResult); }
public override int DeleteAllSessions() { int iResult = 0; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "DELETE FROM [mlac_tbl_sessions];"; cnn.Open(); iResult = cmd.ExecuteNonQuery(); } } return(iResult); }
public override bool DeleteSession(Guid pSessionGuid) { bool bResult = false; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "DELETE FROM [mlac_tbl_sessions] WHERE [hash]=@pSessionGuid;"; cmd.Parameters.Add(new SQLiteParameter("@pSessionGuid", pSessionGuid)); cnn.Open(); bResult = (cmd.ExecuteNonQuery() > 0); } } return(bResult); }
public override int DeleteSessions(TimeSpan pOlderThan) { int iResult = 0; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "DELETE FROM [mlac_tbl_sessions] WHERE [last_updated] < @pOlderThanDate;"; DateTime dtOlderThanDate = DateTime.Now - pOlderThan; cmd.Parameters.Add(new SQLiteParameter("@pOlderThanDate", dtOlderThanDate)); cnn.Open(); iResult = cmd.ExecuteNonQuery(); } } return(iResult); }
public override bool UpdateSession(Guid pSessionGuid) { bool bResult = false; using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "UPDATE [mlac_tbl_sessions] SET [last_updated]=@pLastUpdated WHERE [hash]=@pSessionGuid;"; DateTime dtNow = DateTime.Now; cmd.Parameters.Add(new SQLiteParameter("@pLastUpdated", dtNow)); cmd.Parameters.Add(new SQLiteParameter("@pSessionGuid", pSessionGuid)); cnn.Open(); bResult = (cmd.ExecuteNonQuery() > 0); } } return(bResult); }
public override ACRole[] ListRoles(RoleListingOptions pListingOptions) { List <ACRole> lstResult = new List <ACRole>(); using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "SELECT [id],[name],[is_system],[is_deletable],[is_hidden] FROM [mlac_tbl_roles]"; if (pListingOptions != RoleListingOptions.All) { cmd.CommandText += " WHERE [is_hidden]=@pListHidden;"; if (pListingOptions == RoleListingOptions.Hidden) { cmd.Parameters.Add(new SQLiteParameter("@pListHidden", true)); } else if (pListingOptions == RoleListingOptions.Non_Hidden) { cmd.Parameters.Add(new SQLiteParameter("@pListHidden", false)); } } cnn.Open(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { lstResult.Add(new ACRole() { Id = reader.GetInt32(0), Name = reader.GetString(1), IsSystem = reader.GetBoolean(2), IsDeletable = reader.GetBoolean(3), IsHidden = reader.GetBoolean(4) }); } } } } return(lstResult.ToArray()); }
public override Guid CreateSession(int pUserId, string pAccessPoint) { Guid result = Guid.NewGuid(); using (ConnectionWrapper cnn = ((DBManager)_dbManager).GetConnection()) { using (SQLiteCommand cmd = cnn.CreateCommand()) { cmd.CommandText = "INSERT INTO [mlac_tbl_sessions] ([hash],[user_id],[access_point],[date_created],[last_updated]) " + "VALUES (@pHash,@pUserId,@pAccessPoint,@pDateCreated,@pLastUpdated);"; DateTime dtNow = DateTime.Now; cmd.Parameters.Add(new SQLiteParameter("@pHash", result)); cmd.Parameters.Add(new SQLiteParameter("@pUserId", pUserId)); cmd.Parameters.Add(new SQLiteParameter("@pAccessPoint", pAccessPoint));//(pAccessPoint == null) ? DBNull.Value : (object)pAccessPoint)); cmd.Parameters.Add(new SQLiteParameter("@pDateCreated", dtNow)); cmd.Parameters.Add(new SQLiteParameter("@pLastUpdated", dtNow)); cnn.Open(); cmd.ExecuteNonQuery(); } } return(result); }