public override void Teardown()
{
MySqlCommand cmd = new MySqlCommand("DROP DATABASE IF EXISTS `modeldb`", rootConn);
cmd.ExecuteNonQuery();
base.Teardown();
}
public override void Close(MySqlDataReader reader)
{
base.Close(reader);
if (String.IsNullOrEmpty(outSelect)) return;
if ((reader.CommandBehavior & CommandBehavior.SchemaOnly) != 0) return;
MySqlCommand cmd = new MySqlCommand(outSelect, command.Connection);
using (MySqlDataReader rdr = cmd.ExecuteReader(reader.CommandBehavior))
{
ProcessOutputParameters(rdr);
}
}
public void FirstPredicate()
{
MySqlCommand cmd = new MySqlCommand("SELECT id FROM orders WHERE freight > 100", conn);
int id = (int)cmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
var q = from o in context.Orders
where o.Freight > 100
select o;
Order order = q.First() as Order;
Assert.AreEqual(id, order.Id);
}
}
/*
* Because the user should not be able to directly create a
* DataReader object, the constructors are
* marked as internal.
*/
internal MySqlDataReader(MySqlCommand cmd, PreparableStatement statement, CommandBehavior behavior)
{
this.command = cmd;
connection = (MySqlConnection)command.Connection;
commandBehavior = behavior;
driver = connection.driver;
affectedRows = -1;
this.statement = statement;
if (cmd.CommandType == CommandType.StoredProcedure &&
cmd.UpdatedRowSource == UpdateRowSource.FirstReturnedRecord)
{
disableZeroAffectedRows = true;
}
}
public void UpdateAllRows()
{
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM toys", conn);
object count = cmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
foreach (Toy t in context.Toys)
t.Name = "Top";
context.SaveChanges();
}
cmd.CommandText = "SELECT COUNT(*) FROM Toys WHERE name='Top'";
object newCount = cmd.ExecuteScalar();
Assert.AreEqual(count, newCount);
}
public void AverageSimple()
{
MySqlCommand trueCmd = new MySqlCommand("SELECT AVG(minAge) FROM Toys", conn);
object avgAge = trueCmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
string eSql = "SELECT VALUE Avg(t.MinAge) FROM Toys AS t";
ObjectQuery<Decimal> q = context.CreateQuery<Decimal>(eSql);
string sql = q.ToTraceString();
CheckSql(sql, SQLSyntax.AverageSimple);
foreach (Decimal r in q)
Assert.AreEqual(avgAge, r);
}
}
/// <summary>
/// Creates the or fetch application id.
/// </summary>
/// <param name="applicationName">Name of the application.</param>
/// <param name="applicationId">The application id.</param>
/// <param name="applicationDesc">The application desc.</param>
/// <param name="connection">The connection.</param>
public int EnsureId(MySqlConnection connection)
{
// first try and retrieve the existing id
if (FetchId(connection) <= 0)
{
MySqlCommand cmd = new MySqlCommand(
"INSERT INTO my_aspnet_Applications VALUES (NULL, @appName, @appDesc)", connection);
cmd.Parameters.AddWithValue("@appName", Name);
cmd.Parameters.AddWithValue("@appDesc", Description);
int recordsAffected = cmd.ExecuteNonQuery();
if (recordsAffected != 1)
throw new ProviderException(Resources.UnableToCreateApplication);
Id = Convert.ToInt32(cmd.LastInsertedId);
}
return Id;
}
/// <summary>
/// Retrieves parameter information from the stored procedure specified
/// in the MySqlCommand and populates the Parameters collection of the
/// specified MySqlCommand object.
/// This method is not currently supported since stored procedures are
/// not available in MySql.
/// </summary>
/// <param name="command">The MySqlCommand referencing the stored
/// procedure from which the parameter information is to be derived.
/// The derived parameters are added to the Parameters collection of the
/// MySqlCommand.</param>
/// <exception cref="InvalidOperationException">The command text is not
/// a valid stored procedure name.</exception>
public static void DeriveParameters(MySqlCommand command)
{
if (command.CommandType != CommandType.StoredProcedure)
throw new InvalidOperationException(Resources.CanNotDeriveParametersForTextCommands);
// retrieve the proc definition from the cache.
string spName = command.CommandText;
if (spName.IndexOf(".") == -1)
spName = command.Connection.Database + "." + spName;
try
{
DataSet ds = command.Connection.ProcedureCache.GetProcedure(command.Connection, spName, null);
DataTable parameters = ds.Tables["Procedure Parameters"];
DataTable procTable = ds.Tables["Procedures"];
command.Parameters.Clear();
foreach (DataRow row in parameters.Rows)
{
MySqlParameter p = new MySqlParameter();
p.ParameterName = String.Format("@{0}", row["PARAMETER_NAME"]);
if (row["ORDINAL_POSITION"].Equals(0) && p.ParameterName == "@")
p.ParameterName = "@RETURN_VALUE";
p.Direction = GetDirection(row);
bool unsigned = StoredProcedure.GetFlags(row["DTD_IDENTIFIER"].ToString()).IndexOf("UNSIGNED") != -1;
bool real_as_float = procTable.Rows[0]["SQL_MODE"].ToString().IndexOf("REAL_AS_FLOAT") != -1;
p.MySqlDbType = MetaData.NameToType(row["DATA_TYPE"].ToString(),
unsigned, real_as_float, command.Connection);
if (!row["CHARACTER_MAXIMUM_LENGTH"].Equals(DBNull.Value))
p.Size = (int)row["CHARACTER_MAXIMUM_LENGTH"];
if (!row["NUMERIC_PRECISION"].Equals(DBNull.Value))
p.Precision = Convert.ToByte(row["NUMERIC_PRECISION"]);
if (!row["NUMERIC_SCALE"].Equals(DBNull.Value))
p.Scale = Convert.ToByte(row["NUMERIC_SCALE"]);
if (p.MySqlDbType == MySqlDbType.Set || p.MySqlDbType == MySqlDbType.Enum)
p.PossibleValues = GetPossibleValues(row);
command.Parameters.Add(p);
}
}
catch (InvalidOperationException ioe)
{
throw new MySqlException(Resources.UnableToDeriveParameters, ioe);
}
}
public override void Setup()
{
base.Setup();
ResourceManager r = new ResourceManager("MariaDB.Data.Entity.Tests.Properties.Resources", typeof(BaseEdmTest).Assembly);
string schema = r.GetString("schema");
MySqlScript script = new MySqlScript(conn);
script.Query = schema;
script.Execute();
// now create our procs
schema = r.GetString("procs");
script = new MySqlScript(conn);
script.Delimiter = "$$";
script.Query = schema;
script.Execute();
MySqlCommand cmd = new MySqlCommand("DROP DATABASE IF EXISTS `modeldb`", rootConn);
cmd.ExecuteNonQuery();
}
protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection");
MySqlConnection conn = connection as MySqlConnection;
if (conn == null)
throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection");
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.ConnectionString = conn.ConnectionString;
string dbName = builder.Database;
builder.Database = null;
using (MySqlConnection c = new MySqlConnection(builder.ConnectionString))
{
c.Open();
MySqlCommand cmd = new MySqlCommand(String.Format("DROP DATABASE IF EXISTS `{0}`", dbName), c);
if (commandTimeout.HasValue)
cmd.CommandTimeout = commandTimeout.Value;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Adds the users to roles.
/// </summary>
/// <param name="usernames">The usernames.</param>
/// <param name="rolenames">The rolenames.</param>
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
if (rolenames == null || rolenames.Length == 0) return;
if (usernames == null || usernames.Length == 0) return;
foreach (string rolename in rolenames)
{
if (String.IsNullOrEmpty(rolename))
throw new ArgumentException(Resources.IllegalRoleName, "rolenames");
if (!RoleExists(rolename))
throw new ProviderException(Resources.RoleNameNotFound);
}
foreach (string username in usernames)
{
if (String.IsNullOrEmpty(username))
throw new ArgumentException(Resources.IllegalUserName, "usernames");
if (username.IndexOf(',') != -1)
throw new ArgumentException(Resources.InvalidCharactersInUserName);
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
throw new ProviderException(Resources.UserIsAlreadyInRole);
}
}
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
connection.Open();
txn = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand(
"INSERT INTO my_aspnet_UsersInRoles VALUES(@userId, @roleId)", connection);
cmd.Parameters.Add("@userId", MySqlDbType.Int32);
cmd.Parameters.Add("@roleId", MySqlDbType.Int32);
foreach (string username in usernames)
{
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection,
username, app.FetchId(connection), true);
foreach (string rolename in rolenames)
{
int roleId = GetRoleId(connection, rolename);
cmd.Parameters[0].Value = userId;
cmd.Parameters[1].Value = roleId;
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "AddUsersToRoles");
throw;
}
}
}
private int GetUserId(MySqlConnection connection, string username)
{
MySqlCommand cmd = new MySqlCommand(
"SELECT id FROM my_aspnet_Users WHERE name=@name AND applicationId=@appId",
connection);
cmd.Parameters.AddWithValue("@name", username);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
object id = cmd.ExecuteScalar();
return Convert.ToInt32(id);
}
/// <summary>
/// Executes this instance.
/// </summary>
/// <returns>The number of statements executed as part of the script.</returns>
public int Execute()
{
bool openedConnection = false;
if (connection == null)
throw new InvalidOperationException(ResourceStrings.ConnectionNotSet);
if (query == null || query.Length == 0)
return 0;
// next we open up the connetion if it is not already open
if (connection.State != ConnectionState.Open)
{
openedConnection = true;
connection.Open();
}
// since we don't allow setting of parameters on a script we can
// therefore safely allow the use of user variables. no one should be using
// this connection while we are using it so we can temporarily tell it
// to allow the use of user variables
bool allowUserVars = connection.Settings.AllowUserVariables;
connection.Settings.AllowUserVariables = true;
try
{
string mode = connection.driver.Property("sql_mode");
mode = mode.ToUpper();
bool ansiQuotes = mode.IndexOf("ANSI_QUOTES") != -1;
bool noBackslashEscapes = mode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
// first we break the query up into smaller queries
List<ScriptStatement> statements = BreakIntoStatements(ansiQuotes, noBackslashEscapes);
int count = 0;
MySqlCommand cmd = new MySqlCommand(null, connection);
foreach (ScriptStatement statement in statements)
{
if (String.IsNullOrEmpty(statement.text)) continue;
cmd.CommandText = statement.text;
try
{
cmd.ExecuteNonQuery();
count++;
OnQueryExecuted(statement);
}
catch (Exception ex)
{
if (Error == null)
throw;
if (!OnScriptError(ex))
break;
}
}
OnScriptCompleted();
return count;
}
finally
{
connection.Settings.AllowUserVariables = allowUserVars;
if (openedConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Removes the users from roles.
/// </summary>
/// <param name="usernames">The usernames.</param>
/// <param name="rolenames">The rolenames.</param>
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
if (rolenames == null || rolenames.Length == 0) return;
if (usernames == null || usernames.Length == 0) return;
foreach (string rolename in rolenames)
{
if (!(RoleExists(rolename)))
throw new ProviderException(Resources.RoleNameNotFound);
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!(IsUserInRole(username, rolename)))
throw new ProviderException(Resources.UserNotInRole);
}
}
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
connection.Open();
txn = connection.BeginTransaction();
string sql = @"DELETE uir FROM my_aspnet_UsersInRoles uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE u.name LIKE @username AND r.name LIKE @rolename
AND u.applicationId=@appId AND r.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.Add("@username", MySqlDbType.VarChar, 255);
cmd.Parameters.Add("@rolename", MySqlDbType.VarChar, 255);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
cmd.Parameters[0].Value = username;
cmd.Parameters[1].Value = rolename;
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (MySqlException e)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RemoveUsersFromRoles");
throw;
}
}
}
public Statement(MySqlCommand cmd, string text)
: this(cmd)
{
commandText = text;
}
/// <summary>
/// Gets the users in role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <returns>A string array containing the names of all the users
/// who are members of the specified role. </returns>
public override string[] GetUsersInRole(string rolename)
{
List<string> users = new List<string>();
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
int roleId = GetRoleId(connection, rolename);
string sql = @"SELECT u.name FROM my_aspnet_Users u JOIN
my_aspnet_UsersInRoles uir ON uir.userId=u.id AND uir.roleId=@roleId
WHERE u.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@roleId", roleId);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
users.Add(reader.GetString(0));
}
}
return users.ToArray();
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "GetUsersInRole");
throw;
}
}
/// <summary>
/// Determines whether [is user in role] [the specified username].
/// </summary>
/// <param name="username">The username.</param>
/// <param name="rolename">The rolename.</param>
/// <returns>
/// <c>true</c> if [is user in role] [the specified username]; otherwise, <c>false</c>.
/// </returns>
public override bool IsUserInRole(string username, string rolename)
{
try
{
// this will refresh the app id if necessary
if (!RoleExists(rolename)) return false;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string sql = @"SELECT COUNT(*) FROM my_aspnet_UsersInRoles uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE u.applicationId=@appId AND
u.name LIKE @userName AND r.name LIKE @roleName";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.Parameters.AddWithValue("@userName", username);
cmd.Parameters.AddWithValue("@roleName", rolename);
int count = Convert.ToInt32(cmd.ExecuteScalar());
return count > 0;
}
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "IsUserInRole");
throw;
}
}
/// <summary>
/// Finds the users in role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <param name="usernameToMatch">The username to match.</param>
/// <returns>A string array containing the names of all the users where the
/// user name matches usernameToMatch and the user is a member of the specified role. </returns>
public override string[] FindUsersInRole(string rolename, string usernameToMatch)
{
if (!RoleExists(rolename))
throw new ProviderException(Resources.RoleNameNotFound);
List<string> users = new List<string>();
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string sql = @"SELECT u.name FROM my_aspnet_UsersInRole uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE r.name LIKE @rolename AND
u.name LIKE @username AND
u.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@username", usernameToMatch);
cmd.Parameters.AddWithValue("@rolename", rolename);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
users.Add(reader.GetString(0));
}
}
return users.ToArray();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "FindUsersInRole");
throw;
}
}
/// <summary>
/// Deletes the role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <param name="throwOnPopulatedRole">if set to <c>true</c> [throw on populated role].</param>
/// <returns>true if the role was successfully deleted; otherwise, false. </returns>
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
if (!(RoleExists(rolename)))
throw new ProviderException(Resources.RoleNameNotFound);
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
throw new ProviderException(Resources.CannotDeleteAPopulatedRole);
connection.Open();
txn = connection.BeginTransaction();
// first delete all the user/role mappings with that roleid
MySqlCommand cmd = new MySqlCommand(
@"DELETE uir FROM my_aspnet_UsersInRoles uir JOIN
my_aspnet_Roles r ON uir.roleId=r.id
WHERE r.name LIKE @rolename AND r.applicationId=@appId", connection);
cmd.Parameters.AddWithValue("@rolename", rolename);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.ExecuteNonQuery();
// now delete the role itself
cmd.CommandText = @"DELETE FROM my_aspnet_Roles WHERE name=@rolename
AND applicationId=@appId";
cmd.ExecuteNonQuery();
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "DeleteRole");
throw;
}
}
return true;
}
/// <summary>
/// Creates the role.
/// </summary>
/// <param name="rolename">The rolename.</param>
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') != -1)
throw new ArgumentException(Resources.InvalidCharactersInUserName);
if (RoleExists(rolename))
throw new ProviderException(Resources.RoleNameAlreadyExists);
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Roles Values(NULL, @appId, @name)", connection);
cmd.Parameters.AddWithValue("@appId", app.EnsureId(connection));
cmd.Parameters.AddWithValue("@name", rolename);
cmd.ExecuteNonQuery();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateRole");
throw;
}
}
}
/// <include file='docs/MySqlTransaction.xml' path='docs/Commit/*'/>
public override void Commit()
{
if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed))
throw new InvalidOperationException("Connection must be valid and open to commit transaction");
if (!open)
throw new InvalidOperationException("Transaction has already been committed or is not pending");
MySqlCommand cmd = new MySqlCommand("COMMIT", conn);
cmd.ExecuteNonQuery();
open = false;
}
private static int GetSchemaVersion(string connectionString)
{
// retrieve the current schema version
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_SchemaVersion", conn);
try
{
object ver = cmd.ExecuteScalar();
if (ver != null)
return (int)ver;
}
catch (MySqlException ex)
{
if (ex.Number != (int)MySqlErrorCode.NoSuchTable)
throw;
string[] restrictions = new string[4];
restrictions[2] = "mysql_Membership";
DataTable dt = conn.GetSchema("Tables", restrictions);
if (dt.Rows.Count == 1)
return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]);
}
return 0;
}
}
/// <include file='docs/MySqlTransaction.xml' path='docs/Rollback/*'/>
public override void Rollback()
{
if (conn == null || (conn.State != ConnectionState.Open && !conn.SoftClosed))
throw new InvalidOperationException("Connection must be valid and open to rollback transaction");
if (!open)
throw new InvalidOperationException("Transaction has already been rolled back or is not pending");
MySqlCommand cmd = new MySqlCommand("ROLLBACK", conn);
cmd.ExecuteNonQuery();
open = false;
}
internal static void DeleteUserData(MySqlConnection connection, int userId)
{
MySqlCommand cmd = new MySqlCommand(
"DELETE FROM my_aspnet_UsersInRoles WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@userId", userId);
cmd.ExecuteNonQuery();
}
private Statement(MySqlCommand cmd)
{
command = cmd;
buffers = new ArrayList();
}
private int GetRoleId(MySqlConnection connection, string rolename)
{
MySqlCommand cmd = new MySqlCommand(
"SELECT id FROM my_aspnet_Roles WHERE name=@name AND applicationId=@appId",
connection);
cmd.Parameters.AddWithValue("@name", rolename);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
return (int)cmd.ExecuteScalar();
}
private string GetProcedureParameterLine(DataRow isRow)
{
string sql = "SHOW CREATE {0} `{1}`.`{2}`";
sql = String.Format(sql, isRow["ROUTINE_TYPE"], isRow["ROUTINE_SCHEMA"],
isRow["ROUTINE_NAME"]);
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
// if we are not the owner of this proc or have permissions
// then we will get null for the body
if (reader.IsDBNull(2)) return null;
string sql_mode = reader.GetString(1);
string body = reader.GetString(2);
MySqlTokenizer tokenizer = new MySqlTokenizer(body);
tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
string token = tokenizer.NextToken();
while (token != "(")
token = tokenizer.NextToken();
int start = tokenizer.StartIndex + 1;
token = tokenizer.NextToken();
while (token != ")" || tokenizer.Quoted)
{
token = tokenizer.NextToken();
// if we see another ( and we are not quoted then we
// are in a size element and we need to look for the closing paren
if (token == "(" && !tokenizer.Quoted)
{
while (token != ")" || tokenizer.Quoted)
token = tokenizer.NextToken();
token = tokenizer.NextToken();
}
}
return body.Substring(start, tokenizer.StartIndex - start);
}
}
private string[] GetRolesByUserName(MySqlConnection connection, string username)
{
List<string> roleList = new List<string>();
try
{
string sql = "SELECT r.name FROM my_aspnet_Roles r ";
if (username != null)
sql += "JOIN my_aspnet_UsersInRoles uir ON uir.roleId=r.id AND uir.userId=" +
GetUserId(connection, username);
sql += " WHERE r.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
roleList.Add(reader.GetString(0));
}
return (string[])roleList.ToArray();
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "GetRolesByUserName");
throw;
}
}
/// <summary>
/// Roles the exists.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <returns>true if the role name already exists in the database; otherwise, false. </returns>
public override bool RoleExists(string rolename)
{
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(
@"SELECT COUNT(*) FROM my_aspnet_Roles WHERE applicationId=@appId
AND name LIKE @name", connection);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.Parameters.AddWithValue("@name", rolename);
int count = Convert.ToInt32(cmd.ExecuteScalar());
return count != 0;
}
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "RoleExists");
throw;
}
}
/// <summary>
/// Creates the or fetch user id.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="username">The username.</param>
/// <param name="applicationId">The application id.</param>
/// <param name="authenticated">if set to <c>true</c> [authenticated].</param>
/// <returns></returns>
internal static int CreateOrFetchUserId(MySqlConnection connection, string username,
int applicationId, bool authenticated)
{
Debug.Assert(applicationId > 0);
// first attempt to fetch an existing user id
MySqlCommand cmd = new MySqlCommand(@"SELECT id FROM my_aspnet_Users
WHERE applicationId = @appId AND name = @name", connection);
cmd.Parameters.AddWithValue("@appId", applicationId);
cmd.Parameters.AddWithValue("@name", username);
object userId = cmd.ExecuteScalar();
if (userId != null) return (int)userId;
cmd.CommandText = @"INSERT INTO my_aspnet_Users VALUES
(NULL, @appId, @name, @isAnon, Now())";
cmd.Parameters.AddWithValue("@isAnon", !authenticated);
int recordsAffected = cmd.ExecuteNonQuery();
if (recordsAffected != 1)
throw new ProviderException(Resources.UnableToCreateUser);
cmd.CommandText = "SELECT LAST_INSERT_ID()";
return Convert.ToInt32(cmd.ExecuteScalar());
}