C# NuoDbConnection.Close方法代码示例

        //
        // MembershipProvider.GetPassword
        //
        public override string GetPassword(string username, string answer)
        {
            if (!EnablePasswordRetrieval)
            {
                throw new ProviderException("Password Retrieval Not Enabled.");
            }

            if (PasswordFormat == MembershipPasswordFormat.Hashed)
            {
                throw new ProviderException("Cannot retrieve Hashed passwords.");
            }

            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            string password = "";
            string passwordAnswer = "";
            DbDataReader reader = null;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Password, PasswordAnswer, IsLockedOut " +
                                      "FROM Users " +
                                      "WHERE Username = ? AND ApplicationName = ?";

                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    if (reader.Read())
                    {
                        if (reader.GetBoolean(2))
                            throw new MembershipPasswordException("The supplied user is locked out.");

                        password = reader.GetString(0);
                        passwordAnswer = reader.GetString(1);
                    }
                    else
                    {
                        throw new MembershipPasswordException("The supplied user name is not found.");
                    }
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetPassword");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
            {
                UpdateFailureCount(username, "passwordAnswer");

                throw new MembershipPasswordException("Incorrect password answer.");
            }

            if (PasswordFormat == MembershipPasswordFormat.Encrypted)
            {
                password = UnEncodePassword(password);
            }

            return password;
        }

        //
        // MembershipProvider.GetUser(object, bool)
        //
        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            MembershipUser u = null;
            DbDataReader reader = null;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
                                      "       Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
                                      "       LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
                                      "FROM Users " +
                                      "WHERE PKID = ?";

                    IDbDataParameter pkidParameter = cmd.CreateParameter();
                    pkidParameter.Value = providerUserKey;
                    pkidParameter.DbType = DbType.String;
                    cmd.Parameters.Add(pkidParameter);

                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        u = GetUserFromReader(reader);

                        if (userIsOnline)
                        {
                            using (DbCommand updateCmd = conn.CreateCommand())
                            {
                                updateCmd.CommandText = "UPDATE Users " +
                                                        "SET LastActivityDate = ? " +
                                                        "WHERE PKID = ?";

                                IDbDataParameter lastActivityDateParameter = updateCmd.CreateParameter();
                                lastActivityDateParameter.Value = DateTime.Now;
                                lastActivityDateParameter.DbType = DbType.DateTime;
                                updateCmd.Parameters.Add(lastActivityDateParameter);

                                updateCmd.Parameters.Add(pkidParameter);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }

            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUser(Object, Boolean)");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }

                conn.Close();
            }

            return u;
        }

        //
        // MembershipProvider.GetAllUsers
        //
        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            MembershipUserCollection users = new MembershipUserCollection();
            DbDataReader reader = null;
            totalRecords = 0;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Count(*) " +
                                      "FROM Users " +
                                      "WHERE ApplicationName = ?";
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    totalRecords = (int)cmd.ExecuteScalar();

                    if (totalRecords <= 0) { return users; }

                    cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
                                      " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
                                      " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
                                      "FROM Users " +
                                      "WHERE ApplicationName = ? " +
                                      "ORDER BY Username Asc";

                    reader = cmd.ExecuteReader();

                    int counter = 0;
                    int startIndex = pageSize * pageIndex;
                    int endIndex = startIndex + pageSize - 1;

                    while (reader.Read())
                    {
                        if (counter >= startIndex)
                        {
                            MembershipUser u = GetUserFromReader(reader);
                            users.Add(u);
                        }

                        if (counter >= endIndex) { break; }

                        counter++;
                    }
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetAllUsers ");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            return users;
        }

        //
        // MembershipProvider.GetNumberOfUsersOnline
        //
        public override int GetNumberOfUsersOnline()
        {
            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            int numOnline = 0;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Count(*) " +
                                      "FROM Users " +
                                      "WHERE LastActivityDate > ? AND ApplicationName = ?";

                    IDbDataParameter compareDateParameter = cmd.CreateParameter();
                    compareDateParameter.Value = compareTime;
                    compareDateParameter.DbType = DbType.DateTime;
                    cmd.Parameters.Add(compareDateParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    numOnline = (int)cmd.ExecuteScalar();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return numOnline;
        }

//.........这里部分代码省略.........
                        IDbDataParameter passwordParameter = cmd.CreateParameter();
                        passwordParameter.Value = EncodePassword(password);
                        passwordParameter.DbType = DbType.String;
                        cmd.Parameters.Add(passwordParameter);
                        IDbDataParameter emailParameter = cmd.CreateParameter();
                        emailParameter.Value = email;
                        emailParameter.DbType = DbType.String;
                        cmd.Parameters.Add(emailParameter);
                        IDbDataParameter passwordQuestionParameter = cmd.CreateParameter();
                        passwordQuestionParameter.Value = passwordQuestion;
                        passwordQuestionParameter.DbType = DbType.String;
                        cmd.Parameters.Add(passwordQuestionParameter);
                        IDbDataParameter passwordAnswerParameter = cmd.CreateParameter();
                        passwordAnswerParameter.Value = EncodePassword(passwordAnswer);
                        passwordAnswerParameter.DbType = DbType.String;
                        cmd.Parameters.Add(passwordAnswerParameter);
                        IDbDataParameter isApprovedParameter = cmd.CreateParameter();
                        isApprovedParameter.Value = isApproved;
                        isApprovedParameter.DbType = DbType.Boolean;
                        cmd.Parameters.Add(isApprovedParameter);
                        IDbDataParameter commentParameter = cmd.CreateParameter();
                        commentParameter.Value = "";
                        commentParameter.DbType = DbType.String;
                        cmd.Parameters.Add(commentParameter);
                        IDbDataParameter creationDateParameter = cmd.CreateParameter();
                        creationDateParameter.Value = createDate;
                        creationDateParameter.DbType = DbType.DateTime;
                        cmd.Parameters.Add(creationDateParameter);
                        IDbDataParameter lastPasswordChangedDateParameter = cmd.CreateParameter();
                        lastPasswordChangedDateParameter.Value = createDate;
                        lastPasswordChangedDateParameter.DbType = DbType.DateTime;
                        cmd.Parameters.Add(lastPasswordChangedDateParameter);
                        IDbDataParameter lastActivityDateParameter = cmd.CreateParameter();
                        lastActivityDateParameter.Value = createDate;
                        lastActivityDateParameter.DbType = DbType.DateTime;
                        cmd.Parameters.Add(lastActivityDateParameter);
                        IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                        applicationNameParameter.Value = pApplicationName;
                        applicationNameParameter.DbType = DbType.String;
                        cmd.Parameters.Add(applicationNameParameter);
                        IDbDataParameter isLockedOutParameter = cmd.CreateParameter();
                        isLockedOutParameter.Value = false;
                        isLockedOutParameter.DbType = DbType.Boolean;
                        cmd.Parameters.Add(isLockedOutParameter);
                        IDbDataParameter lastLockedOutDateParameter = cmd.CreateParameter();
                        lastLockedOutDateParameter.Value = createDate;
                        lastLockedOutDateParameter.DbType = DbType.DateTime;
                        cmd.Parameters.Add(lastLockedOutDateParameter);
                        IDbDataParameter failedPasswordAttemptCountParameter = cmd.CreateParameter();
                        failedPasswordAttemptCountParameter.Value = 0;
                        failedPasswordAttemptCountParameter.DbType = DbType.Int32;
                        cmd.Parameters.Add(failedPasswordAttemptCountParameter);
                        IDbDataParameter failedPasswordAttemptWindowStartParameter = cmd.CreateParameter();
                        failedPasswordAttemptWindowStartParameter.Value = createDate;
                        failedPasswordAttemptWindowStartParameter.DbType = DbType.DateTime;
                        cmd.Parameters.Add(failedPasswordAttemptWindowStartParameter);
                        IDbDataParameter failedPasswordAnswerAttemptCountParameter = cmd.CreateParameter();
                        failedPasswordAnswerAttemptCountParameter.Value = 0;
                        failedPasswordAnswerAttemptCountParameter.DbType = DbType.Int32;
                        cmd.Parameters.Add(failedPasswordAnswerAttemptCountParameter);
                        IDbDataParameter failedPasswordAnswerAttemptWindowStartParameter = cmd.CreateParameter();
                        failedPasswordAnswerAttemptWindowStartParameter.Value = createDate;
                        failedPasswordAnswerAttemptWindowStartParameter.DbType = DbType.DateTime;
                        cmd.Parameters.Add(failedPasswordAnswerAttemptWindowStartParameter);

                        int recAdded = cmd.ExecuteNonQuery();

                        if (recAdded > 0)
                        {
                            status = MembershipCreateStatus.Success;
                        }
                        else
                        {
                            status = MembershipCreateStatus.UserRejected;
                        }
                    }
                }
                catch (NuoDbSqlException e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, "CreateUser");
                    }

                    status = MembershipCreateStatus.ProviderError;
                }
                finally
                {
                    conn.Close();
                }

                return GetUser(username, false);
            }
            else
            {
                status = MembershipCreateStatus.DuplicateUserName;
            }

            return null;
        }

        //
        // MembershipProvider.DeleteUser
        //
        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            int rowsAffected = 0;

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "DELETE " +
                                      "FROM Users " +
                                      "WHERE Username = ? AND Applicationname = ?";

                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    rowsAffected = cmd.ExecuteNonQuery();

                    if (deleteAllRelatedData)
                    {
                        // Process commands to delete all data for the user in the database.
                    }
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "DeleteUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            if (rowsAffected > 0)
                return true;

            return false;
        }

        //
        // UpdateFailureCount
        //   A helper method that performs the checks and updates associated with
        // password failure tracking.
        //
        private void UpdateFailureCount(string username, string failureType)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            DbDataReader reader = null;
            DateTime windowStart = new DateTime();
            int failureCount = 0;

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT FailedPasswordAttemptCount, " +
                                      "       FailedPasswordAttemptWindowStart, " +
                                      "       FailedPasswordAnswerAttemptCount, " +
                                      "       FailedPasswordAnswerAttemptWindowStart " +
                                      "FROM Users " +
                                      "WHERE Username = ? AND ApplicationName = ?";

                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    if (reader.HasRows)
                    {
                        reader.Read();

                        if (failureType == "password")
                        {
                            failureCount = reader.GetInt32(0);
                            windowStart = reader.GetDateTime(1);
                        }

                        if (failureType == "passwordAnswer")
                        {
                            failureCount = reader.GetInt32(2);
                            windowStart = reader.GetDateTime(3);
                        }
                    }

                    reader.Close();

                    DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

                    if (failureCount == 0 || DateTime.Now > windowEnd)
                    {
                        // First password failure or outside of PasswordAttemptWindow.
                        // Start a new password failure count from 1 and a new window starting now.

                        using (DbCommand updateCmd = conn.CreateCommand())
                        {
                            if (failureType == "password")
                                updateCmd.CommandText = "UPDATE Users " +
                                                        "SET FailedPasswordAttemptCount = ?, " +
                                                        "    FailedPasswordAttemptWindowStart = ? " +
                                                        "WHERE Username = ? AND ApplicationName = ?";

                            if (failureType == "passwordAnswer")
                                updateCmd.CommandText = "UPDATE Users " +
                                                        "SET FailedPasswordAnswerAttemptCount = ?, " +
                                                        "    FailedPasswordAnswerAttemptWindowStart = ? " +
                                                        "WHERE Username = ? AND ApplicationName = ?";

                            updateCmd.Parameters.Clear();

                            IDbDataParameter countParameter = updateCmd.CreateParameter();
                            countParameter.Value = 1;
                            countParameter.DbType = DbType.Int32;
                            updateCmd.Parameters.Add(countParameter);
                            IDbDataParameter windowStartParameter = updateCmd.CreateParameter();
                            windowStartParameter.Value = DateTime.Now;
                            windowStartParameter.DbType = DbType.DateTime;
                            updateCmd.Parameters.Add(windowStartParameter);
                            IDbDataParameter updateUsernameParameter = updateCmd.CreateParameter();
                            updateUsernameParameter.Value = username;
                            updateUsernameParameter.DbType = DbType.String;
                            updateCmd.Parameters.Add(updateUsernameParameter);

                            updateCmd.Parameters.Add(applicationNameParameter);

                            if (cmd.ExecuteNonQuery() < 0)
                                throw new ProviderException("Unable to update failure count and window start.");
                        }
                    }
                    else
//.........这里部分代码省略.........

        //
        // MembershipProvider.ChangePasswordQuestionAndAnswer
        //
        public override bool ChangePasswordQuestionAndAnswer(string username,
                      string password,
                      string newPwdQuestion,
                      string newPwdAnswer)
        {
            if (!ValidateUser(username, password))
                return false;

            int rowsAffected = 0;
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE Users " +
                                      "SET PasswordQuestion = ?, PasswordAnswer = ? " +
                                      "WHERE Username = ? AND ApplicationName = ?";
                    IDbDataParameter questionParameter = cmd.CreateParameter();
                    questionParameter.Value = newPwdQuestion;
                    questionParameter.DbType = DbType.String;
                    cmd.Parameters.Add(questionParameter);
                    IDbDataParameter passwordParameter = cmd.CreateParameter();
                    passwordParameter.Value = EncodePassword(newPwdAnswer);
                    passwordParameter.DbType = DbType.String;
                    cmd.Parameters.Add(passwordParameter);
                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    rowsAffected = cmd.ExecuteNonQuery();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            if (rowsAffected > 0)
            {
                return true;
            }

            return false;
        }

        //
        // MembershipProvider.ValidateUser
        //
        public override bool ValidateUser(string username, string password)
        {
            bool isValid = false;

            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            DbDataReader reader = null;
            bool isApproved = false;
            string pwd = "";

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Password, IsApproved " +
                                      "FROM Users " +
                                      "WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False";

                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    if (reader.Read())
                    {
                        pwd = reader.GetString(0);
                        isApproved = reader.GetBoolean(1);
                    }
                    else
                    {
                        return false;
                    }

                    reader.Close();

                    if (CheckPassword(password, pwd))
                    {
                        if (isApproved)
                        {
                            isValid = true;

                            using (DbCommand updateCmd = conn.CreateCommand())
                            {
                                updateCmd.CommandText = "UPDATE Users " +
                                                        "SET LastLoginDate = ? " +
                                                        "WHERE Username = ? AND ApplicationName = ?";

                                IDbDataParameter lastLoginDateParameter = updateCmd.CreateParameter();
                                lastLoginDateParameter.Value = DateTime.Now;
                                lastLoginDateParameter.DbType = DbType.DateTime;
                                updateCmd.Parameters.Add(lastLoginDateParameter);

                                updateCmd.Parameters.Add(usernameParameter);

                                updateCmd.Parameters.Add(applicationNameParameter);

                                updateCmd.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            conn.Close();

                            UpdateFailureCount(username, "password");
                        }
                    }
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ValidateUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            return isValid;
//.........这里部分代码省略.........

        //
        // EnsureTableIsCreated
        //   A helper function that creates the table if it is not yet present
        //
        private void EnsureTableIsCreated()
        {
            NuoDbConnection conn = null;

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS Users " +
                                      " (PKID String NOT NULL PRIMARY KEY, "+
                                      "  Username Text (255) NOT NULL, " +
                                      "  ApplicationName Text (255) NOT NULL, " +
                                      "  Email Text (128) NOT NULL, " +
                                      "  Comment Text (255), " +
                                      "  Password Text (128) NOT NULL, " +
                                      "  PasswordQuestion Text (255), " +
                                      "  PasswordAnswer Text (255), " +
                                      "  IsApproved Boolean, " +
                                      "  LastActivityDate DateTime, " +
                                      "  LastLoginDate DateTime, " +
                                      "  LastPasswordChangedDate DateTime, " +
                                      "  CreationDate DateTime, " +
                                      "  IsOnLine Boolean, " +
                                      "  IsLockedOut Boolean, " +
                                      "  LastLockedOutDate DateTime, " +
                                      "  FailedPasswordAttemptCount Integer, " +
                                      "  FailedPasswordAttemptWindowStart DateTime, " +
                                      "  FailedPasswordAnswerAttemptCount Integer, " +
                                      "  FailedPasswordAnswerAttemptWindowStart DateTime " +
                                      ")";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "EnsureTableIsCreated");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
        }

        //
        // MembershipProvider.UpdateUser
        //
        public override void UpdateUser(MembershipUser user)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE Users " +
                                      "SET Email = ?, Comment = ?, IsApproved = ? " +
                                      "WHERE Username = ? AND ApplicationName = ?";

                    IDbDataParameter emailParameter = cmd.CreateParameter();
                    emailParameter.Value = user.Email;
                    emailParameter.DbType = DbType.String;
                    cmd.Parameters.Add(emailParameter);
                    IDbDataParameter commentParameter = cmd.CreateParameter();
                    commentParameter.Value = user.Comment;
                    commentParameter.DbType = DbType.String;
                    cmd.Parameters.Add(commentParameter);
                    IDbDataParameter isApprovedParameter = cmd.CreateParameter();
                    isApprovedParameter.Value = user.IsApproved;
                    isApprovedParameter.DbType = DbType.Boolean;
                    cmd.Parameters.Add(isApprovedParameter);
                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = user.UserName;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UpdateUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }
        }

        //
        // System.Web.Security.MembershipProvider methods.
        //
        //
        // MembershipProvider.ChangePassword
        //
        public override bool ChangePassword(string username, string oldPwd, string newPwd)
        {
            if (!ValidateUser(username, oldPwd))
                return false;

            ValidatePasswordEventArgs args =
              new ValidatePasswordEventArgs(username, newPwd, true);

            OnValidatingPassword(args);

            if (args.Cancel)
                if (args.FailureInformation != null)
                    throw args.FailureInformation;
                else
                    throw new MembershipPasswordException("Change password canceled due to new password validation failure.");

            int rowsAffected = 0;

            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE Users " +
                                      "SET Password = ?, LastPasswordChangedDate = ? " +
                                      "WHERE Username = ? AND ApplicationName = ?";
                    IDbDataParameter passwordParameter = cmd.CreateParameter();
                    passwordParameter.Value = EncodePassword(newPwd);
                    passwordParameter.DbType = DbType.String;
                    cmd.Parameters.Add(passwordParameter);
                    IDbDataParameter lastPasswordChangedDataParameter = cmd.CreateParameter();
                    lastPasswordChangedDataParameter.Value = DateTime.Now;
                    lastPasswordChangedDataParameter.DbType = DbType.DateTime;
                    cmd.Parameters.Add(lastPasswordChangedDataParameter);
                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    rowsAffected = cmd.ExecuteNonQuery();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ChangePassword");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            if (rowsAffected > 0)
            {
                return true;
            }

            return false;
        }

        //
        // MembershipProvider.UnlockUser
        //
        public override bool UnlockUser(string username)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            int rowsAffected = 0;
            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE Users " +
                                      "SET IsLockedOut = False, LastLockedOutDate = ? " +
                                      "WHERE Username = ? AND ApplicationName = ?";

                    IDbDataParameter lastLockedOutDateParameter = cmd.CreateParameter();
                    lastLockedOutDateParameter.Value = DateTime.Now;
                    lastLockedOutDateParameter.DbType = DbType.DateTime;
                    cmd.Parameters.Add(lastLockedOutDateParameter);
                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    rowsAffected = cmd.ExecuteNonQuery();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "UnlockUser");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            if (rowsAffected > 0)
                return true;

            return false;
        }

//.........这里部分代码省略.........

            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            int rowsAffected = 0;
            string passwordAnswer = "";
            DbDataReader reader = null;

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT PasswordAnswer, IsLockedOut " +
                                      "FROM Users " +
                                      "WHERE Username = ? AND ApplicationName = ?";

                    IDbDataParameter usernameParameter = cmd.CreateParameter();
                    usernameParameter.Value = username;
                    usernameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(usernameParameter);
                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    if (reader.Read())
                    {
                        if (reader.GetBoolean(1))
                            throw new MembershipPasswordException("The supplied user is locked out.");

                        passwordAnswer = reader.GetString(0);
                    }
                    else
                    {
                        throw new MembershipPasswordException("The supplied user name is not found.");
                    }

                    if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
                    {
                        UpdateFailureCount(username, "passwordAnswer");

                        throw new MembershipPasswordException("Incorrect password answer.");
                    }

                    using (DbCommand updateCmd = conn.CreateCommand())
                    {
                        updateCmd.CommandText = "UPDATE Users " +
                                                "SET Password = ?, LastPasswordChangedDate = ? " +
                                                "WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False";

                        IDbDataParameter passwordParameter = updateCmd.CreateParameter();
                        passwordParameter.Value = EncodePassword(newPassword);
                        passwordParameter.DbType = DbType.String;
                        updateCmd.Parameters.Add(passwordParameter);

                        IDbDataParameter lastPasswordChangedDateParameter = updateCmd.CreateParameter();
                        lastPasswordChangedDateParameter.Value = DateTime.Now;
                        lastPasswordChangedDateParameter.DbType = DbType.DateTime;
                        updateCmd.Parameters.Add(lastPasswordChangedDateParameter);

                        updateCmd.Parameters.Add(usernameParameter);

                        updateCmd.Parameters.Add(applicationNameParameter);

                        rowsAffected = updateCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ResetPassword");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            if (rowsAffected > 0)
            {
                return newPassword;
            }
            else
            {
                throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
            }
        }

        //
        // MembershipProvider.GetUserNameByEmail
        //
        public override string GetUserNameByEmail(string email)
        {
            EnsureTableIsCreated();
            NuoDbConnection conn = null;
            string username = "";

            try
            {
                conn = new NuoDbConnection(connectionString);
                conn.Open();

                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Username " +
                                      "FROM Users " +
                                      "WHERE Email = ? AND ApplicationName = ?";

                    IDbDataParameter emailParameter = cmd.CreateParameter();
                    emailParameter.Value = email;
                    emailParameter.DbType = DbType.String;
                    cmd.Parameters.Add(emailParameter);

                    IDbDataParameter applicationNameParameter = cmd.CreateParameter();
                    applicationNameParameter.Value = pApplicationName;
                    applicationNameParameter.DbType = DbType.String;
                    cmd.Parameters.Add(applicationNameParameter);

                    username = (string)cmd.ExecuteScalar();
                }
            }
            catch (NuoDbSqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetUserNameByEmail");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            if (username == null)
                username = "";

            return username;
        }