public static int Roles_CreateRole(DbConnection connection, string applicationName, string rolename)
{
string appId = (string)DerbyApplicationsHelper.Applications_CreateApplication(connection, applicationName);
if (appId == null)
{
return(1);
}
string querySelect = "SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = ? AND LoweredRoleName = ?";
OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection);
AddParameter(cmdSelect, "ApplicationId", appId);
AddParameter(cmdSelect, "LoweredRoleName", rolename.ToLowerInvariant());
using (OleDbDataReader reader = cmdSelect.ExecuteReader()) {
if (reader.Read())
{
return(2); // role already exists
}
}
string queryInsert = "INSERT INTO aspnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName) VALUES (?, ?, ?, ?)";
OleDbCommand cmdInsert = new OleDbCommand(queryInsert, (OleDbConnection)connection);
AddParameter(cmdInsert, "ApplicationId", appId);
AddParameter(cmdInsert, "RoleId", Guid.NewGuid().ToString());
AddParameter(cmdInsert, "RoleName", rolename);
AddParameter(cmdInsert, "LoweredRoleName", rolename.ToLowerInvariant());
cmdInsert.ExecuteNonQuery();
return(0);
}
public static int Membership_CreateUser(DbConnection connection, string applicationName, string username, string password, string passwordSalt, string email, string pwdQuestion, string pwdAnswer, bool isApproved, DateTime currentTimeUtc, DateTime createDate, bool uniqueEmail, int passwordFormat, ref object userId)
{
string applicationId = (string)DerbyApplicationsHelper.Applications_CreateApplication(connection, applicationName);
string newUserId = (string)userId;
OleDbTransaction trans = (OleDbTransaction)connection.BeginTransaction();
try {
int returnValue = Users_CreateUser(connection, trans, applicationId, username, false, createDate, ref userId);
if (returnValue == 1)
{
// the user exists in users table, this can occure when user
// does not have membership information, but has other information
// like roles, etc.
if (userId != null && newUserId != null && newUserId != (string)userId)
{
trans.Rollback();
return(9); // wrong userid provided
}
}
else if (returnValue == 2)
{
// another user with provided id already exists
trans.Rollback();
return(10); // wrong userid provided
}
newUserId = (string)userId;
string selectQueryMbrUserId = "SELECT UserId FROM aspnet_Membership WHERE UserId = ?";
OleDbCommand selectCmdMbrUserId = new OleDbCommand(selectQueryMbrUserId, (OleDbConnection)connection);
selectCmdMbrUserId.Transaction = trans;
AddParameter(selectCmdMbrUserId, "UserId", newUserId);
using (OleDbDataReader reader = selectCmdMbrUserId.ExecuteReader()) {
if (reader.Read())
{
trans.Rollback();
return(2); // user with such userId already exists
}
}
if (uniqueEmail)
{
string queryMbrEmail = "SELECT * FROM aspnet_Membership WHERE ApplicationId = ? AND LoweredEmail = ?";
OleDbCommand cmdMbrEmail = new OleDbCommand(queryMbrEmail, (OleDbConnection)connection);
cmdMbrEmail.Transaction = trans;
AddParameter(cmdMbrEmail, "ApplicationId", applicationId);
AddParameter(cmdMbrEmail, "LoweredEmail", email.ToLowerInvariant());
using (OleDbDataReader reader = cmdMbrEmail.ExecuteReader()) {
if (reader.Read())
{
trans.Rollback();
return(3); // user with such email already exists
}
}
}
if (returnValue == 1)
{
// if user was not created, but found existing and correct
// update it's activity (membership create) time.
string queryUpdActivity = "UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?";
OleDbCommand cmdUpdActivity = new OleDbCommand(queryUpdActivity, (OleDbConnection)connection);
cmdUpdActivity.Transaction = trans;
AddParameter(cmdUpdActivity, "LastActivityDate", createDate);
AddParameter(cmdUpdActivity, "UserId", newUserId);
cmdUpdActivity.ExecuteNonQuery();
}
string queryInsertMbr = "INSERT INTO aspnet_Membership (ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, Email, " +
"LoweredEmail, PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, " +
"LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPwdAttemptWindowStart, " +
"FailedPwdAnswerAttemptCount, FailedPwdAnswerAttWindowStart) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
OleDbCommand cmdInsertMbr = new OleDbCommand(queryInsertMbr, (OleDbConnection)connection);
cmdInsertMbr.Transaction = trans;
AddParameter(cmdInsertMbr, "ApplicationId", applicationId);
AddParameter(cmdInsertMbr, "UserId", newUserId);
AddParameter(cmdInsertMbr, "Password", password);
AddParameter(cmdInsertMbr, "PasswordFormat", passwordFormat);
AddParameter(cmdInsertMbr, "PasswordSalt", passwordSalt);
AddParameter(cmdInsertMbr, "Email", email);
AddParameter(cmdInsertMbr, "LoweredEmail", email != null ? email.ToLowerInvariant() : null);
AddParameter(cmdInsertMbr, "PasswordQuestion", pwdQuestion);
AddParameter(cmdInsertMbr, "PasswordAnswer", pwdAnswer);
AddParameter(cmdInsertMbr, "IsApproved", isApproved);
AddParameter(cmdInsertMbr, "IsLockedOut", 0);
AddParameter(cmdInsertMbr, "CreateDate", createDate);
AddParameter(cmdInsertMbr, "LastLoginDate", DefaultDateTime);
AddParameter(cmdInsertMbr, "LastPasswordChangedDate", createDate);
AddParameter(cmdInsertMbr, "LastLockoutDate", DefaultDateTime);
AddParameter(cmdInsertMbr, "FailedPasswordAttemptCount", 0);
AddParameter(cmdInsertMbr, "FailedPwdAttemptWindowStart", DefaultDateTime);
AddParameter(cmdInsertMbr, "FailedPwdAnswerAttemptCount", 0);
AddParameter(cmdInsertMbr, "FailedPwdAnswerAttWindowStart", DefaultDateTime);
cmdInsertMbr.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e) {
trans.Rollback();
throw e;
}
return(0);
}