Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 9
0
        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());
        }
Ejemplo n.º 10
0
        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);
        }