/// <summary>
/// Takes, as input, a user name and returns the names of the roles to which the user
/// belongs. If the user is not assigned to any roles, <c>GetRolesForUser</c> returns an empty
/// string array (a string array with no elements). If the user name does not exist,
/// <c>GetRolesForUser</c> throws a <c>ProviderException</c>.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetRolesForUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username we want to get the roles for.</param>
/// <returns>Any array of all of the roles for the given user.</returns>
internal string[] GetRolesForUser(string username)
{
// Validate the username
ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username");
// Validate that the user exists and throw exception if he doesn't
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserNotFound));
}
// Build up the required SQL
string sql = @"
SELECT
aspnet_Roles.RoleName
FROM
aspnet_Roles,
aspnet_UsersInRoles
WHERE
aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId
AND aspnet_Roles.ApplicationId = ?
AND aspnet_UsersInRoles.UserId = ?
ORDER BY
aspnet_Roles.RoleName
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("UserId", DbType.String).Value = this.GetUserIdByName(username);
// Instantiate a new reader and read in the query
IngresDataReader reader = cmd.ExecuteReader();
// Get the roles out of the reader
List <string> roleNamesList = new List <string>();
while (reader.Read())
{
roleNamesList.Add(DBUtil.ColValAsString(reader, "RoleName"));
}
reader.Close();
// Return an appropriate string array
if (roleNamesList.Count > 0)
{
return(roleNamesList.ToArray());
}
// we had no roles and so just return an empty string array.
return(new string[0]);
}
/// <summary>
/// Creates a role in the database.
/// </summary>
/// <param name="roleName">The rolename to create.</param>
/// <param name="roleid">The role id.</param>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
private void CreateRole(string roleName, out string roleid, IngresConnection conn, IngresTransaction tran)
{
// Validate the roleName
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Ensure that the proposed roleName does not already exist
if (this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleAlreadyExists, roleName));
}
string sql = @"
INSERT INTO aspnet_Roles
(ApplicationId,
RoleId,
RoleName,
LoweredRoleName,
Description)
VALUES
(?,
?,
?,
?,
NULL)
";
// Create the command with the current connection and enrol in the transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Generate a new role Id - this will be the sent out
roleid = Guid.NewGuid().ToString().ToLower();
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
cmd.Parameters.Add("RoleName", DbType.String).Value = roleName;
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
// Execute the query
int rows = cmd.ExecuteNonQuery();
// Validate that the query affected the correct numbber of rows.
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
/// <summary>
/// Helper method to create a user.
/// </summary>
/// <param name="username">The username that we wish to create.</param>
/// <returns>The Id for the newly created user.</returns>
private string CreateUser(string username)
{
// Validate the username
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
string id = Guid.NewGuid().ToString().ToLower();
string sql = @"
INSERT INTO aspnet_Users
(ApplicationId,
UserId,
UserName,
LoweredUserName,
MobileAlias,
IsAnonymous,
LastActivityDate)
VALUES
(?,
?,
?,
?,
NULL,
'0',
?)
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
cmd.Parameters.Add("UserId", DbType.String).Value = id;
cmd.Parameters.Add("UserName", DbType.String).Value = username;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LastActivity", DbType.Date).Value = DateTime.Now;
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
// the user has been successfully created and so we return the id.
return(id);
}
/// <summary>
/// Helper method to determine whether a user exists for a given
/// </summary>
/// <param name="username">The username that we want to check the existence of.</param>
/// <returns>A boolean indicating whether the user exists.</returns>
private bool UserExists(string username)
{
// Validate the input
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
// assume that the user doesn't exist
bool exists = false;
// build up the parameterised SQL string
string sql = @"
SELECT
UserName
FROM
aspnet_Users
WHERE
LoweredUserName = ?
AND ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Instantiate a new reader and execute the query
IngresDataReader datareader = cmd.ExecuteReader();
// If we have returned anything then the user exists
if (datareader.HasRows)
{
exists = true;
}
// close the datareader and return
datareader.Close();
return(exists);
}
/// <summary>
/// Takes, as input, a role name and determines whether the role exists.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>RoleExists</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">role name to check the existence of</param>
/// <returns>Whether the given role exists.</returns>
internal bool RoleExists(string roleName)
{
// Validate the Parameter
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Assume that the role does not exist
bool exists = false;
string sql = @"
SELECT
RoleName
FROM
aspnet_Roles
WHERE
LoweredRoleName = ?
AND ApplicationId = ?
";
// Instantiate a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters.
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
// Execute the query.
IngresDataReader reader = cmd.ExecuteReader();
// Determine whether the role exists.
if (reader.HasRows)
{
exists = true;
}
reader.Close();
return(exists);
}
/// <summary>
/// Takes, as input, a search pattern and a role name and returns a list of users belonging
/// to the specified role whose user names match the pattern. Wildcard syntax is
/// data-source-dependent and may vary from provider to provider. User names are returned
/// in alphabetical order.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>FindUsersInRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The rolename to find users for.</param>
/// <param name="usernameToMatch">The username wildcard to match.</param>
/// <returns>Returns a list of users belonging to the specified role whose user names match the pattern.</returns>
internal string[] FindUsersInRole(string roleName, string usernameToMatch)
{
// Validate the input parameters
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
ValidationUtil.CheckParameterIsOK(ref usernameToMatch, true, true, false, 256, "usernameToMatch");
// Check if the role exists and throw an exception if it doesn't
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// Get the role id
string roleId = this.GetRoleIdByName(roleName);
// Adjust the username so that it is in the correct format for an Ingres "LIKE"
usernameToMatch = String.Format("%{0}%", usernameToMatch);
string sql = @"
SELECT
aspnet_Users.UserName
FROM
aspnet_Users,
aspnet_UsersInRoles
WHERE
aspnet_Users.Username LIKE ?
AND aspnet_Users.UserId = aspnet_UsersInRoles.UserId
AND aspnet_UsersInRoles.RoleId = ?
AND aspnet_Users.ApplicationId = ?
ORDER BY
aspnet_Users.UserName
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the requires parameters
cmd.Parameters.Add("Username", DbType.String).Value = usernameToMatch;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleId;
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
// Instantiate a new reader and execute the query
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
// Get the results out of the reader
List <string> userNamesList = new List <string>();
while (reader.Read())
{
userNamesList.Add(DBUtil.ColValAsString(reader, "UserName"));
}
reader.Close();
// return an appropriate string array
if (userNamesList.Count > 0)
{
return(userNamesList.ToArray());
}
}
return(new string[0]);
}
/// <summary>
/// Takes, as input, a user name and a role name and determines whether the specified user
/// is associated with the specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>IsUserInRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">The username that we wish to check.</param>
/// <param name="roleName">The role which we wish to check.</param>
/// <returns>Whether the given user is in the given role.</returns>
internal bool IsUserInRole(string username, string roleName)
{
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username");
if (username.Length < 1)
{
return(false);
}
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserNotFound));
}
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// ensure that we have an appication id
if (this.ApplicationId == null)
{
throw new ArgumentNullException("ApplicationId");
}
// Instantiate a bool for whether the user is in the role - assume not.
bool userIsInRole = false;
// Build up the SQL string
string sql = @"
SELECT
COUNT(*)
FROM
aspnet_UsersInRoles
WHERE
UserId = ?
AND RoleId = ?
";
// Create a new command and enrol in the current transaction.
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Get the user and role id's for the specified user and role names.
string userid = this.GetUserIdByName(username);
string roleid = this.GetRoleIdByName(roleName);
cmd.Parameters.Add("UserId", DbType.String).Value = userid;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
// Execute the query and determine is the user is in the role.
int rows = Convert.ToInt32(cmd.ExecuteScalar());
if (rows > 0)
{
userIsInRole = true;
}
return(userIsInRole);
}
/// <summary>
/// Takes, as input, a role name and returns the names of all users assigned to that role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetUsersInRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The rolename that we wish to get all the users for.</param>
/// <returns>An array of all of the users for the given role.</returns>
internal string[] GetUsersInRole(string roleName)
{
// If we don't have an application id then we aren't going to have any roles.
if (this.ApplicationId == null)
{
return(new string[0]);
}
// Validate the roleName
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Check if the role exists and throw an exception if it doesn't
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
string sql = @"
SELECT
aspnet_Users.UserName
FROM
aspnet_Users,
aspnet_UsersInRoles,
aspnet_Roles
WHERE
aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId
AND aspnet_UsersInRoles.UserId = aspnet_Users.UserId
AND aspnet_Roles.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Users.ApplicationId = ?
AND aspnet_Roles.LoweredRoleName = ?
ORDER BY
aspnet_Users.UserName
";
// Create a new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
// Instantiate a new reader and execute the query
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
// Get the users from the reader
List <string> userList = new List <string>();
while (reader.Read())
{
userList.Add(DBUtil.ColValAsString(reader, "UserName"));
}
// Close the reader
reader.Close();
// retun an appropriate string array dependent on whether we managed to return any users or not
if (userList.Count > 0)
{
return(userList.ToArray());
}
}
// no users so return an empty array
return(new string[0]);
}
/// <summary>
/// Takes, as input, a role name and a Boolean value that indicates whether to throw an
/// exception if there are users currently associated with the role, and then deletes the
/// specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>DeleteRole</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="roleName">The user name that we wish to delete.</param>
/// <param name="throwOnPopulatedRole">Whether we should throw an exception if the role
/// we wish to delete has any users in the role or not.</param>
/// <returns>Whether the role was successfully deleted.</returns>
internal bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
// Validate the rolename
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// The roleName should exist
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// If we have to throw errors for a poupulated roll then we must check if there are
// any users in the role.
if (throwOnPopulatedRole && (this.GetUsersInRole(roleName).Length > 0))
{
throw new ProviderException(string.Format(Messages.RoleNotEmpty));
}
string sqlDeleteUsersInRoles = @"
DELETE FROM aspnet_UsersInRoles
WHERE
RoleId = ?
";
// Create a new command and enrol in the current transactions
IngresCommand cmdDeleteUsersInRoles = new IngresCommand(sqlDeleteUsersInRoles, this.conn);
cmdDeleteUsersInRoles.Transaction = this.tran;
cmdDeleteUsersInRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required parameter
cmdDeleteUsersInRoles.Parameters.Add("RoleId", DbType.String).Value = this.GetRoleIdByName(roleName);
// Delete the users
cmdDeleteUsersInRoles.ExecuteNonQuery();
string sqlDeleteRoles = @"
DELETE FROM aspnet_Roles
WHERE
LoweredRolename = ?
AND ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmdDeleteRoles = new IngresCommand(sqlDeleteRoles, this.conn);
cmdDeleteRoles.Transaction = this.tran;
cmdDeleteRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required paramaters
cmdDeleteRoles.Parameters.Add("LoweredRolename", DbType.String).Value = roleName.ToLower();
cmdDeleteRoles.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Finally delete the role
int rows = cmdDeleteRoles.ExecuteNonQuery();
// If more than one row was effected then throw an error
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
return(true);
}
