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