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 UsersInRoles_GetUsersInRoles(DbConnection connection, string applicationName, string rolename, out DbDataReader reader) { reader = null; string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(1); } string roleId = GetRoleId(connection, appId, rolename); if (roleId == null) { return(2); } string querySelect = "SELECT usr.UserName FROM aspnet_Users usr, aspnet_UsersInRoles uir " + "WHERE usr.UserId = uir.UserId AND usr.ApplicationId = ? AND uir.RoleId = ? ORDER BY usr.UserName"; OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "ApplicationId", appId); AddParameter(cmdSelect, "RoleId", roleId); reader = cmdSelect.ExecuteReader(); return(0); }
public static int Roles_DeleteRole(DbConnection connection, string applicationName, string rolename, bool deleteOnlyIfRoleIsEmpty) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(1); } string roleId = GetRoleId(connection, appId, rolename); if (roleId == null) { return(2); } if (deleteOnlyIfRoleIsEmpty) { string querySelect = "SELECT RoleId FROM aspnet_UsersInRoles WHERE RoleId = ?"; OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "RoleId", roleId); using (OleDbDataReader reader = cmdSelect.ExecuteReader()) { if (reader.Read()) { // role is not empty return(3); } } } string queryDelUsers = "DELETE FROM aspnet_UsersInRoles WHERE RoleId = ?"; OleDbCommand cmdDelUsers = new OleDbCommand(queryDelUsers, (OleDbConnection)connection); AddParameter(cmdDelUsers, "RoleId", roleId); cmdDelUsers.ExecuteNonQuery(); string queryDelRole = "DELETE FROM aspnet_Roles WHERE ApplicationId = ? AND RoleId = ? "; OleDbCommand cmdDelRole = new OleDbCommand(queryDelRole, (OleDbConnection)connection); AddParameter(cmdDelRole, "ApplicationId", appId); AddParameter(cmdDelRole, "RoleId", roleId); cmdDelRole.ExecuteNonQuery(); return(0); }
public static int Roles_GetAllRoles(DbConnection connection, string applicationName, out DbDataReader reader) { reader = null; string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(1); } string querySelect = "SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = ? ORDER BY RoleName"; OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "ApplicationId", appId); reader = cmdSelect.ExecuteReader(); return(0); }
public static int Roles_RoleExists(DbConnection connection, string applicationName, string rolename) { string appId = DerbyApplicationsHelper.GetApplicationId(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); } } return(0); }
public static int UsersInRoles_IsUserInRole(DbConnection connection, string applicationName, string username, string rolename) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(1); } string userId = GetUserId(connection, appId, username); if (userId == null) { return(2); } string roleId = GetRoleId(connection, appId, rolename); if (roleId == null) { return(3); } string querySelect = "SELECT UserId FROM aspnet_UsersInRoles WHERE UserId = ? AND RoleId = ?"; OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "UserId", userId); AddParameter(cmdSelect, "RoleId", roleId); using (OleDbDataReader reader = cmdSelect.ExecuteReader()) { if (reader.Read()) { return(4); } } return(0); }
public static int UsersInRoles_RemoveUsersFromRoles(DbConnection connection, string applicationName, string [] userNames, string [] roleNames) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(1); } string [] userIds = new string [userNames.Length]; string [] roleIds = new string [roleNames.Length]; string querySelUsers = "SELECT UserId FROM aspnet_Users WHERE ApplicationId = ? AND LoweredUserName in " + GetPrms(userNames.Length); OleDbCommand cmdSelUsers = new OleDbCommand(querySelUsers, (OleDbConnection)connection); AddParameter(cmdSelUsers, "ApplicationId", appId); for (int i = 0; i < userNames.Length; i++) { AddParameter(cmdSelUsers, "LoweredUserName", userNames [i].ToLowerInvariant()); } using (OleDbDataReader reader = cmdSelUsers.ExecuteReader()) { int i = 0; while (reader.Read()) { userIds [i++] = reader.GetString(0); } if (userNames.Length != i) { return(2); // one or more users not found } } string querySelRoles = "SELECT RoleId FROM aspnet_Roles WHERE ApplicationId = ? AND LoweredRoleName in " + GetPrms(roleNames.Length); OleDbCommand cmdSelRoles = new OleDbCommand(querySelRoles, (OleDbConnection)connection); AddParameter(cmdSelRoles, "ApplicationId", appId); for (int i = 0; i < roleNames.Length; i++) { AddParameter(cmdSelRoles, "LoweredRoleName", roleNames [i].ToLowerInvariant()); } using (OleDbDataReader reader = cmdSelRoles.ExecuteReader()) { int i = 0; while (reader.Read()) { roleIds [i++] = reader.GetString(0); } if (roleNames.Length != i) { return(3); // one or more roles not found } } string querySelCount = "SELECT COUNT(*) FROM aspnet_UsersInRoles WHERE UserId in " + GetPrms(userNames.Length) + " AND RoleId in " + GetPrms(roleNames.Length); OleDbCommand cmdSelCount = new OleDbCommand(querySelCount, (OleDbConnection)connection); foreach (string userId in userIds) { AddParameter(cmdSelCount, "UserId", userId); } foreach (string roleId in roleIds) { AddParameter(cmdSelCount, "RoleId", roleId); } using (OleDbDataReader reader = cmdSelCount.ExecuteReader()) { if (reader.Read()) { if (userNames.Length * roleNames.Length > reader.GetInt32(0)) { return(4); } } } string queryDelete = "DELETE FROM aspnet_UsersInRoles WHERE UserId in " + GetPrms(userNames.Length) + " AND RoleId in " + GetPrms(roleNames.Length); OleDbCommand cmdDelete = new OleDbCommand(queryDelete, (OleDbConnection)connection); foreach (string userId in userIds) { AddParameter(cmdDelete, "UserId", userId); } foreach (string roleId in roleIds) { AddParameter(cmdDelete, "RoleId", roleId); } cmdDelete.ExecuteNonQuery(); return(0); }
public static int UsersInRoles_AddUsersToRoles(DbConnection connection, string applicationName, string [] userNames, string [] roleNames, DateTime currentTimeUtc) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(1); } string [] userIds = new string [userNames.Length]; string [] loweredUsernames = new string [userNames.Length]; string [] roleIds = new string [roleNames.Length]; string querySelUsers = "SELECT UserId, LoweredUserName FROM aspnet_Users WHERE ApplicationId = ? AND LoweredUserName in " + GetPrms(userNames.Length); OleDbCommand cmdSelUsers = new OleDbCommand(querySelUsers, (OleDbConnection)connection); AddParameter(cmdSelUsers, "ApplicationId", appId); for (int i = 0; i < userNames.Length; i++) { AddParameter(cmdSelUsers, "LoweredUserName", userNames [i].ToLowerInvariant()); } int userIndex = 0; using (OleDbDataReader reader = cmdSelUsers.ExecuteReader()) { while (reader.Read()) { userIds [userIndex] = reader.GetString(0); loweredUsernames [userIndex] = reader.GetString(1); userIndex++; } } if (userNames.Length != userIndex) { // find not existing users and create them for (int j = 0; j < userNames.Length; j++) { if (Array.IndexOf(loweredUsernames, userNames [j].ToLowerInvariant()) < 0) { string newUserId = Guid.NewGuid().ToString(); string queryAddUser = "******" + "LoweredUserName, IsAnonymous, LastActivityDate) VALUES (?, ?, ?, ?, ?, ?)"; OleDbCommand cmdAddUser = new OleDbCommand(queryAddUser, (OleDbConnection)connection); AddParameter(cmdAddUser, "ApplicationId", appId); AddParameter(cmdAddUser, "UserId", newUserId); AddParameter(cmdAddUser, "UserName", userNames [j]); AddParameter(cmdAddUser, "LoweredUserName", userNames [j].ToLowerInvariant()); AddParameter(cmdAddUser, "IsAnonymous", 0); AddParameter(cmdAddUser, "LastActivityDate", DateTime.UtcNow); cmdAddUser.ExecuteNonQuery(); userIds [userIndex++] = newUserId; } } } string querySelRoles = "SELECT RoleId FROM aspnet_Roles WHERE ApplicationId = ? AND LoweredRoleName in " + GetPrms(roleNames.Length); OleDbCommand cmdSelRoles = new OleDbCommand(querySelRoles, (OleDbConnection)connection); AddParameter(cmdSelRoles, "ApplicationId", appId); for (int i = 0; i < roleNames.Length; i++) { AddParameter(cmdSelRoles, "LoweredRoleName", roleNames [i].ToLowerInvariant()); } using (OleDbDataReader reader = cmdSelRoles.ExecuteReader()) { int i = 0; while (reader.Read()) { roleIds [i++] = reader.GetString(0); } if (roleNames.Length != i) { return(2); // one or more roles not found } } string querySelCount = "SELECT COUNT(*) FROM aspnet_UsersInRoles WHERE UserId in " + GetPrms(userNames.Length) + " AND RoleId in " + GetPrms(roleNames.Length); OleDbCommand cmdSelCount = new OleDbCommand(querySelCount, (OleDbConnection)connection); foreach (string userId in userIds) { AddParameter(cmdSelCount, "UserId", userId); } foreach (string roleId in roleIds) { AddParameter(cmdSelCount, "RoleId", roleId); } using (OleDbDataReader reader = cmdSelCount.ExecuteReader()) { if (reader.Read()) { if (reader.GetInt32(0) > 0) { return(3); } } } string valuesExp = string.Empty; int pairs = userNames.Length * roleNames.Length; for (int i = 0; i < pairs; i++) { valuesExp += "(?, ?),"; } string queryInsert = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES " + valuesExp.Trim(','); OleDbCommand cmdInsert = new OleDbCommand(queryInsert, (OleDbConnection)connection); foreach (string roleId in roleIds) { foreach (string userId in userIds) { AddParameter(cmdInsert, "UserId", userId); AddParameter(cmdInsert, "RoleId", roleId); } } 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); }