/// <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>
/// Gets the Id for the current application.
/// </summary>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
/// <returns>The Id for the current application.</returns>
private string GetApplicationId(IngresConnection conn, IngresTransaction tran)
{
string id = null;
string sql = @"
SELECT
ApplicationId
FROM
aspnet_Applications
WHERE LoweredApplicationName = ?
";
// Create the new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
conn.Open();
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
reader.Read();
// Retrieve the Id
id = DBUtil.ColValAsString(reader, "ApplicationId");
reader.Close();
}
else
{
// Close the reader.
reader.Close();
// We don't have an application so create one.
this.CreateApplication(this.config.ApplicationName, out id);
}
}
// Mark the application Id as current so that we don't have to fetch it from the database
// again unless it changes.
this.config.IsApplicationIdCurrent = true;
// Close the connection
conn.Close();
return(id);
}
/// <summary>
/// Returns the names of all existing roles. If no roles exist, <c>GetAllRoles</c> returns an
/// empty string array (a string array with no elements).
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetAllRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <returns>A string array with all of the roles for the application.</returns>
internal string[] GetAllRoles()
{
// Ensure that we have an application Id
if (this.ApplicationId == null)
{
throw new Exception(Messages.ApplicationIdNotFound);
}
// build the SQL command
string sql = @"
SELECT
Rolename
FROM
aspnet_Roles
WHERE
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 requires parameteters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Instantiate a reader and execute the query
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
// Get all roles from the reader
List <string> roleNamesList = new List <string>();
while (reader.Read())
{
roleNamesList.Add(DBUtil.ColValAsString(reader, "Rolename"));
}
reader.Close();
// If we have some role names then return them as a string array
if (roleNamesList.Count > 0)
{
return(roleNamesList.ToArray());
}
}
// Otherwise just return a new empty string array
return(new string[0]);
}
/// <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);
}
protected override CustomListColumnCollection GetColumnsOverride(CustomListData data)
{
data.Properties.TryGetValue("SQLStatement", StringComparison.OrdinalIgnoreCase, out var SQLStatement);
var cols = new CustomListColumnCollection();
var con = GetConnection(data);
var command = new IngresCommand(SQLStatement, con);
var reader = command.ExecuteReader();
var schemaTable = reader.GetSchemaTable();
foreach (DataRow sqlcol in schemaTable.Rows)
{
var columnName = (string)sqlcol["ColumnName"];
var dataType = (Type)sqlcol["DataType"];
var listColumnType = CustomListColumnTypes.String;
// We convert the types to one of the three Peakboard types (string, number or boolean)
if (dataType == typeof(string))
{
listColumnType = CustomListColumnTypes.String;
}
else if (dataType == typeof(bool))
{
listColumnType = CustomListColumnTypes.Boolean;
}
else
{
listColumnType = DataTypeHelper.IsNumericType(dataType) ? CustomListColumnTypes.Number : CustomListColumnTypes.String;
}
cols.Add(new CustomListColumn(columnName, listColumnType));
}
con.Close();
return(cols);
}
/// <summary>
/// Finds the Id for a given rolename.
/// </summary>
/// <param name="rolename">The name of the role to find the Id for.</param>
/// <returns>The Id for the given role.</returns>
private string GetRoleIdByName(string rolename)
{
string id = null;
string sql = @"
SELECT
RoleId
FROM
aspnet_Roles
WHERE
LoweredRoleName = ?
AND ApplicationId = ?
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = rolename.ToLower();
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
IngresDataReader datareader = cmd.ExecuteReader();
if (datareader != null)
{
if (datareader.HasRows)
{
datareader.Read();
id = datareader.GetString(0);
}
datareader.Close();
}
return(id);
}
static void TestDbProc(string connstring, Type type)
{
int recsaffected;
string strSeq;
string strName;
IngresConnection conn = new IngresConnection(connstring);
IngresCommand cmd;
Console.WriteLine("\nTestDbProc (using " + type.ToString()
+ ") to database: " + conn.Database);
conn.Open();
cmd = new IngresCommand(
"declare global temporary table session.tab (seq integer," +
"tabname varchar(32), tabowner varchar(32), numpages integer)" +
" on commit preserve rows with norecovery", conn);
recsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("ExecuteNonQuery(\"declare gtt\") returned " +
recsaffected.ToString());
cmd = new IngresCommand(
"insert into session.tab (tabname,tabowner,numpages)" +
" select table_name,table_owner,number_pages from iitables", conn);
recsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("ExecuteNonQuery(\"insert into gtt\") returned " +
recsaffected.ToString());
cmd = new IngresCommand(
"{ call gttproc(gtt1 = session.tab)}", conn);
// "{ call gttproc(session.tab)}", conn);
cmd.CommandType = CommandType.Text;
recsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("ExecuteNonQuery(\"execute proc\") returned " +
recsaffected.ToString());
cmd = new IngresCommand(
"select seq,tabname from session.tab", conn);
IngresDataReader reader = cmd.ExecuteReader();
Console.Write(reader.GetName(0) + "\t");
Console.Write(reader.GetName(1));
Console.WriteLine();
while (reader.Read())
{
strSeq = reader.IsDBNull(0)?
"<none>":reader.GetInt32(0).ToString();
strName = reader.IsDBNull(1)?
"<none>":reader.GetString(1);
Console.WriteLine(strSeq + "\t" + strName);
} // end while loop through result set
reader.Close();
conn.Close();
}
/// <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 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>
/// Creates an application in the database.
/// </summary>
/// <param name="name">The name of the application to create.</param>
/// <param name="id">The Id of the application to create.</param>
private void CreateApplication(string name, out string id)
{
// Ensure that the proposed rolename is of a valid form and does not already exist
if (name.IndexOf(',') > 0)
{
throw new ArgumentException(Messages.ApplicationNamesCannotContainCommas);
}
// Build up the command and connection details
id = null;
string sql = @"
SELECT
ApplicationId
FROM
aspnet_Applications
WHERE
LoweredApplicationName = ?
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = name.ToLower();
IngresDataReader datareader = cmd.ExecuteReader();
if (datareader != null)
{
if (datareader.HasRows)
{
datareader.Read();
id = datareader.GetString(0);
datareader.Close();
return;
}
id = Guid.NewGuid().ToString();
}
if (datareader != null)
{
datareader.Close();
}
sql = @"
INSERT INTO aspnet_Applications
(ApplicationId,
ApplicationName,
LoweredApplicationName)
VALUES
(?,
?,
?)
";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("ApplicationId", DbType.String).Value = id;
cmd.Parameters.Add("ApplicationName", DbType.String).Value = name;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = name.ToLower();
cmd.ExecuteNonQuery();
return;
}