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); }