//
// 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;
}
//
// MembershipProvider.CreateUser
//
public override MembershipUser CreateUser(string username,
string password,
string email,
string passwordQuestion,
string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
MembershipUser u = GetUser(username, false);
if (u == null)
{
DateTime createDate = DateTime.Now;
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
EnsureTableIsCreated();
NuoDbConnection conn = null;
try
{
conn = new NuoDbConnection(connectionString);
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Users " +
" (PKID, Username, Password, Email, PasswordQuestion, " +
" PasswordAnswer, IsApproved," +
" Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," +
" ApplicationName, IsLockedOut, LastLockedOutDate," +
" FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " +
" FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
IDbDataParameter pkidParameter = cmd.CreateParameter();
pkidParameter.Value = providerUserKey;
pkidParameter.DbType = DbType.String;
cmd.Parameters.Add(pkidParameter);
IDbDataParameter usernameParameter = cmd.CreateParameter();
usernameParameter.Value = username;
usernameParameter.DbType = DbType.String;
cmd.Parameters.Add(usernameParameter);
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
{
if (failureCount++ >= MaxInvalidPasswordAttempts)
{
// Password attempts have exceeded the failure threshold. Lock out
// the user.
using (DbCommand updateCmd = conn.CreateCommand())
{
updateCmd.CommandText = "UPDATE Users " +
"SET IsLockedOut = ?, LastLockedOutDate = ? " +
"WHERE Username = ? AND ApplicationName = ?";
updateCmd.Parameters.Clear();
IDbDataParameter isLockedOutParameter = updateCmd.CreateParameter();
isLockedOutParameter.Value = true;
isLockedOutParameter.DbType = DbType.Boolean;
updateCmd.Parameters.Add(isLockedOutParameter);
IDbDataParameter lastLockedOutDateParameter = updateCmd.CreateParameter();
lastLockedOutDateParameter.Value = DateTime.Now;
lastLockedOutDateParameter.DbType = DbType.DateTime;
updateCmd.Parameters.Add(lastLockedOutDateParameter);
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 lock out user.");
}
}
else
{
// Password attempts have not exceeded the failure threshold. Update
// the failure counts. Leave the window the same.
using (DbCommand updateCmd = conn.CreateCommand())
{
if (failureType == "password")
updateCmd.CommandText = "UPDATE Users " +
"SET FailedPasswordAttemptCount = ? " +
"WHERE Username = ? AND ApplicationName = ?";
if (failureType == "passwordAnswer")
updateCmd.CommandText = "UPDATE Users " +
"SET FailedPasswordAnswerAttemptCount = ? " +
"WHERE Username = ? AND ApplicationName = ?";
updateCmd.Parameters.Clear();
IDbDataParameter countParameter = updateCmd.CreateParameter();
countParameter.Value = failureCount;
countParameter.DbType = DbType.Int32;
updateCmd.Parameters.Add(countParameter);
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.");
}
}
}
}
}
catch (NuoDbSqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateFailureCount");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
}
//
// 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;
}
//
// MembershipProvider.ResetPassword
//
public override string ResetPassword(string username, string answer)
{
if (!EnablePasswordReset)
{
throw new NotSupportedException("Password reset is not enabled.");
}
if (answer == null && RequiresQuestionAndAnswer)
{
UpdateFailureCount(username, "passwordAnswer");
throw new ProviderException("Password answer required for password reset.");
}
string newPassword =
System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs args =
new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(args);
if (args.Cancel)
if (args.FailureInformation != null)
throw args.FailureInformation;
else
throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
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;
}