/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
internal static MySqlConnectionHolder GetConnection(string connectionStringName, bool revertImpersonation)
{
connectionStringName = connectionStringName.Trim();
/////////////////////////////////////////////////
// Lock the connections table, and see if it already exists
lock (_Connections) {
MySqlConnectionHolder holder = (MySqlConnectionHolder)_Connections[connectionStringName];
if (holder != null)
{
_Connections.Remove(connectionStringName);
holder = null;
}
if (holder == null)
{
string s_conn = GetConnectionString(connectionStringName, true);
if (IsValidConnectionString(s_conn))
{
MySqlConnection conn = new MySqlConnection(s_conn);
_Connections.Add(connectionStringName, new MySqlConnectionHolder(conn));
holder = (MySqlConnectionHolder)_Connections[connectionStringName];
}
}
if (holder == null)
{
return(null);
}
holder.Open(null);
return(holder);
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override bool RoleExists(string roleName)
{
try {
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
}
catch {
return(false);
}
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
try {
int appId = GetApplicationId(holder);
int roleId = GetRoleId(connection, appId, roleName);
return(roleId != 0);
}
catch (Exception e) {
throw e;
}
finally {
try {
holder.Close();
} catch (Exception e) {
throw e;
}
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override string[] GetRolesForUser(string username)
{
SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
if (username.Length < 1)
{
return(new string[0]);
}
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
MySqlDataReader reader = null;
try {
try {
int appId = GetApplicationId(holder);
string userId = MySqlConnectionHelper.GetUserID(connection, appId, username, false);
if (userId == "0")
{
return(new string[0]);
}
MySqlCommand command;
StringCollection sc = new StringCollection();
String[] strReturn;
string s = "SELECT RoleName FROM aspnet_UsersInRoles ur, aspnet_Roles r " +
"WHERE ur.UserId = '" + userId + "' AND ur.RoleId = r.RoleId " +
"ORDER BY RoleName";
command = new MySqlCommand("SELECT RoleName FROM aspnet_UsersInRoles ur, aspnet_Roles r " +
"WHERE ur.UserId = '" + userId + "' AND ur.RoleId = r.RoleId " +
"ORDER BY RoleName",
connection);
//command.Parameters.Add(new MySqlParameter("?UserId", userId));
reader = (MySqlDataReader)command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
sc.Add(reader.GetString(0));
}
strReturn = new String[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
catch (Exception e) {
throw e;
}
finally {
if (reader != null)
{
reader.Close();
}
holder.Close();
}
}
catch {
throw;
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override void CreateRole(string roleName)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
bool fBeginTransCalled = false;
try {
try {
int appId = GetApplicationId(holder);
MySqlCommand command;
int roleId = GetRoleId(connection, appId, roleName);
if (roleId != 0)
{
throw new ProviderException("Provider role already exists: " + roleName);
}
command = new MySqlCommand("START TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = true;
command = new MySqlCommand("INSERT INTO aspnet_Roles (ApplicationId, RoleName, LoweredRoleName) VALUES (?AppId, ?RName, ?LRoleName)", connection);
command.Parameters.Add(new MySqlParameter("?AppId", appId));
command.Parameters.Add(new MySqlParameter("?RName", roleName));
command.Parameters.Add(new MySqlParameter("?LRoleName", roleName.ToLower()));
int returnValue = command.ExecuteNonQuery();
command = new MySqlCommand("COMMIT", connection);
command.ExecuteNonQuery();
fBeginTransCalled = false;
if (returnValue == 1)
{
return;
}
throw new ProviderException("Unknown provider failure");
}
catch (Exception e) {
if (fBeginTransCalled)
{
try {
MySqlCommand command = new MySqlCommand("ROLLBACK", connection);
command.ExecuteNonQuery();
}
catch { }
}
throw e;
}
finally {
holder.Close();
}
}
catch {
throw;
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override string[] FindUsersInRole(string roleName, string usernameToMatch)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");
StringCollection sc = new StringCollection();
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlDataReader reader = null;
MySqlConnection connection = holder.Connection;
try {
try {
int appId = GetApplicationId(holder);
int roleId = GetRoleId(connection, appId, roleName);
MySqlCommand command;
if (roleId == 0)
{
throw new ProviderException("Role not found " + roleName);
}
command = new MySqlCommand("SELECT UserName " +
"FROM aspnet_UsersInRoles ur, aspnet_Users u " +
"WHERE ur.RoleId = ?RoleId AND ur.UserId = u.UserId AND u.UserName LIKE ?UserNameToMatch " +
"ORDER BY UserName", connection);
command.Parameters.Add(new MySqlParameter("?RoleId", roleId));
command.Parameters.Add(new MySqlParameter("?UserNameToMatch", usernameToMatch));
reader = (MySqlDataReader)command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
sc.Add((string)reader.GetString(0));
}
}
catch (Exception e) {
throw e;
}
finally {
if (reader != null)
{
reader.Close();
}
holder.Close();
}
}
catch {
throw;
}
string[] allUsers = new string[sc.Count];
sc.CopyTo(allUsers, 0);
return(allUsers);
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override bool IsUserInRole(string username, string roleName)
{
SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
if (username.Length < 1)
{
return(false);
}
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
try {
try {
int appId = GetApplicationId(holder);
string userId = MySqlConnectionHelper.GetUserID(connection, appId, username, false);
int roleId = GetRoleId(connection, appId, roleName);
MySqlCommand command;
if (userId == "0")
{
return(false);
}
if (roleId == 0)
{
return(false);
}
command = new MySqlCommand("SELECT UserId FROM aspnet_UsersInRoles WHERE UserId = ?UserId AND RoleId = ?RoleId", connection);
command.Parameters.Add(new MySqlParameter("?UserId", userId));
command.Parameters.Add(new MySqlParameter("?RoleId", roleId));
object result = command.ExecuteScalar();
if (result == null || result.ToString() != userId)
{
return(false);
}
return(true);
}
catch (Exception e) {
throw e;
}
finally {
holder.Close();
}
}
catch {
throw;
}
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private int GetApplicationId(MySqlConnectionHolder holder)
{
if (_ApplicationId != 0 && holder.CreateDate < _ApplicationIDCacheDate) // Already cached?
{
return(_ApplicationId);
}
string appName = _AppName;
if (appName.Length > 255)
{
appName = appName.Substring(0, 255);
}
_ApplicationId = MySqlConnectionHelper.GetApplicationID(holder.Connection, appName, true);
_ApplicationIDCacheDate = DateTime.Now;
if (_ApplicationId != 0)
{
return(_ApplicationId);
}
throw new ProviderException("Provider Error");
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override string[] GetAllRoles()
{
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
MySqlDataReader reader = null;
try {
try {
int appId = GetApplicationId(holder);
MySqlCommand command;
StringCollection sc = new StringCollection();
String[] strReturn = null;
command = new MySqlCommand("SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = ?AppId ORDER BY RoleName", connection);
command.Parameters.Add(new MySqlParameter("?AppId", appId));
reader = (MySqlDataReader)command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
sc.Add(reader.GetString(0));
}
strReturn = new String[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
catch (Exception e) {
throw e;
}
finally {
if (reader != null)
{
reader.Close();
}
holder.Close();
}
}
catch {
throw;
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames");
SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
bool fBeginTransCalled = false;
try {
try {
int appId = GetApplicationId(holder);
string[] userIds = new string[usernames.Length];
int[] roleIds = new int[roleNames.Length];
MySqlCommand command;
command = new MySqlCommand("START TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = true;
for (int iterU = 0; iterU < usernames.Length; iterU++)
{
userIds[iterU] = MySqlConnectionHelper.GetUserID(connection, appId, usernames[iterU], false);
if (userIds[iterU] == "0")
{
throw new ProviderException("User not found: " + usernames[iterU]);
}
}
for (int iterR = 0; iterR < roleNames.Length; iterR++)
{
roleIds[iterR] = GetRoleId(connection, appId, roleNames[iterR]);
if (roleIds[iterR] == 0)
{
throw new ProviderException("Role not found: " + roleNames[iterR]);
}
}
for (int iterU = 0; iterU < usernames.Length; iterU++)
{
for (int iterR = 0; iterR < roleNames.Length; iterR++)
{
command = new MySqlCommand("SELECT UserId FROM aspnet_UsersInRoles WHERE UserId = ?UserId AND RoleId = ?RoleId",
connection);
command.Parameters.Add(new MySqlParameter("?UserId", userIds[iterU]));
command.Parameters.Add(new MySqlParameter("?RoleId", roleIds[iterR]));
object result = command.ExecuteScalar();
if (result == null || result.ToString() != userIds[iterU]) // doesn't exist!
{
throw new ProviderException("The user " + usernames[iterU] + " is already not in role " + roleNames[iterR]);
}
}
}
for (int iterU = 0; iterU < usernames.Length; iterU++)
{
for (int iterR = 0; iterR < roleNames.Length; iterR++)
{
command = new MySqlCommand("DELETE FROM aspnet_UsersInRoles WHERE UserId = ?UserId AND RoleId = ?RoleId",
connection);
command.Parameters.Add(new MySqlParameter("?UserId", userIds[iterU]));
command.Parameters.Add(new MySqlParameter("?RoleId", roleIds[iterR]));
if (command.ExecuteNonQuery() != 1)
{
throw new ProviderException("Unknown failure");
}
}
}
command = new MySqlCommand("COMMIT", connection);
command.ExecuteNonQuery();
}
catch (Exception e) {
try {
if (fBeginTransCalled)
{
MySqlCommand command = new MySqlCommand("ROLLBACK", connection);
command.ExecuteNonQuery();
}
}
catch { }
throw e;
}
finally {
holder.Close();
}
}
catch {
throw;
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(_ConnectionStringName, true);
MySqlConnection connection = holder.Connection;
bool fBeginTransCalled = false;
try {
try {
int appId = GetApplicationId(holder);
MySqlCommand command;
int roleId = GetRoleId(connection, appId, roleName);
if (roleId == 0)
{
return(false);
}
if (throwOnPopulatedRole)
{
command = new MySqlCommand("SELECT COUNT(*) " +
"FROM aspnet_UsersInRoles ur, aspnet_Users u " +
"WHERE ur.RoleId = ?RoleId AND ur.UserId = u.UserId",
connection);
command.Parameters.Add(new MySqlParameter("?RoleId", roleId));
int num = Convert.ToInt32(command.ExecuteScalar());
if (num != 0)
{
return(false);
}
}
command = new MySqlCommand("START TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = true;
command = new MySqlCommand("DELETE FROM aspnet_Roles WHERE RoleId = ?RoleId", connection);
command.Parameters.Add(new MySqlParameter("?RoleId", roleId));
int returnValue = command.ExecuteNonQuery();
command = new MySqlCommand("COMMIT", connection);
command.ExecuteNonQuery();
fBeginTransCalled = false;
return(returnValue == 1);
}
catch (Exception e) {
if (fBeginTransCalled)
{
try {
MySqlCommand command = new MySqlCommand("ROLLBACK", connection);
command.ExecuteNonQuery();
}
catch { }
}
throw e;
}
finally {
holder.Close();
}
}
catch {
throw;
}
}
