public override void CloseQuery(MySqlConnection connection, int statementId)
{
base.CloseQuery(connection, statementId);
MySqlTrace.TraceEvent(TraceEventType.Information, MySqlTraceEventType.QueryClosed,
Resources.TraceQueryDone, driverId);
}
public DataSet GetProcedure(MySqlConnection conn, string spName, string cacheKey)
{
DataSet ds = null;
if (cacheKey != null)
{
int hash = cacheKey.GetHashCode();
lock (procHash.SyncRoot)
{
ds = (DataSet)procHash[hash];
}
}
if (ds == null)
{
ds = AddNew(conn, spName);
#if !CF
conn.PerfMonitor.AddHardProcedureQuery();
#endif
if (conn.Settings.Logging)
MySqlTrace.LogInformation(conn.ServerThread,
String.Format(Resources.HardProcQuery, spName));
}
else
{
#if !CF
conn.PerfMonitor.AddSoftProcedureQuery();
#endif
if (conn.Settings.Logging)
MySqlTrace.LogInformation(conn.ServerThread,
String.Format(Resources.SoftProcQuery, spName));
}
return ds;
}
/*
* 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;
}
}
/// <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;
}
public PerformanceMonitor(MySqlConnection connection)
{
this.connection = connection;
string categoryName = Resources.PerfMonCategoryName;
if (connection.Settings.UsePerformanceMonitor && procedureHardQueries == null)
{
try
{
procedureHardQueries = new PerformanceCounter(categoryName,
"HardProcedureQueries", false);
procedureSoftQueries = new PerformanceCounter(categoryName,
"SoftProcedureQueries", false);
}
catch (Exception ex)
{
MySqlTrace.LogError(connection.ServerThread, ex.Message);
}
}
}
/// <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());
}
/// <summary>
/// Initializes a new instance of the
/// <see cref="MySqlScript"/> class.
/// </summary>
/// <param name="connection">The connection.</param>
public MySqlScript(MySqlConnection connection)
: this()
{
this.connection = connection;
}
internal MySqlTransaction(MySqlConnection c, IsolationLevel il)
{
conn = c;
level = il;
open = true;
}
protected override bool DbDatabaseExists(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();
DataTable table = c.GetSchema("Databases", new string[] { dbName });
if (table != null && table.Rows.Count == 1) return true;
return false;
}
}
/// <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 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;
}
}
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 static void UpgradeToCurrent(string connectionString, int version)
{
ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources",
typeof(SchemaManager).Assembly);
if (version == Version) return;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
for (int ver = version + 1; ver <= Version; ver++)
{
string schema = r.GetString(String.Format("schema{0}", ver));
MySqlScript script = new MySqlScript(connection);
script.Query = schema;
script.Execute();
}
}
}
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;
}
}
/// <summary>
/// Closes the MySqlDataReader object.
/// </summary>
public void Close()
{
if (!isOpen) return;
bool shouldCloseConnection = (commandBehavior & CommandBehavior.CloseConnection) != 0;
commandBehavior = CommandBehavior.Default;
// clear all remaining result sets
try
{
while (NextResult()) { }
}
catch (MySqlException ex)
{
// Ignore aborted queries
if (!ex.IsQueryAborted)
{
// ignore IO exceptions.
// We are closing or disposing reader, and do not
// want exception to be propagated to used. If socket is
// is closed on the server side, next query will run into
// IO exception. If reader is closed by GC, we also would
// like to avoid any exception here.
bool isIOException = false;
for (Exception exception = ex; exception != null;
exception = exception.InnerException)
{
if (exception is System.IO.IOException)
{
isIOException = true;
break;
}
}
if (!isIOException)
{
// Ordinary exception (neither IO nor query aborted)
throw;
}
}
}
catch (System.IO.IOException)
{
// eat, on the same reason we eat IO exceptions wrapped into
// MySqlExceptions reasons, described above.
}
finally
{
// always ensure internal reader is null (Bug #55558)
connection.Reader = null;
}
// we now give the command a chance to terminate. In the case of
// stored procedures it needs to update out and in out parameters
command.Close(this);
if (this.command.Canceled && connection.driver.Version.isAtLeast(5, 1, 0))
{
// Issue dummy command to clear kill flag
ClearKillFlag();
}
if (shouldCloseConnection)
connection.Close();
command = null;
connection.IsInUse = false;
connection = null;
isOpen = false;
}
/// <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;
}
}
}
/// <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 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;
}
}
}
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();
}
/// <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;
}
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>
/// 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;
}
}
protected override void DbCreateDatabase(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");
string query = DbCreateDatabaseScript(null, storeItemCollection);
using (MySqlConnection c = new MySqlConnection())
{
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder(conn.ConnectionString);
string dbName = sb.Database;
sb.Database = null;
c.ConnectionString = sb.ConnectionString;
c.Open();
string fullQuery = String.Format("CREATE DATABASE `{0}`; USE `{0}`; {1}", dbName, query);
MySqlScript s = new MySqlScript(c, fullQuery);
s.Execute();
}
}
/// <summary>
/// Gets a list of all the roles for the configured applicationName.
/// </summary>
/// <returns>
/// A string array containing the names of all the roles stored in the data source for the configured applicationName.
/// </returns>
public override string[] GetAllRoles()
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
return GetRolesByUserName(connection, null);
}
}
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>
/// Gets a list of the roles that a specified user is in for the configured applicationName.
/// </summary>
/// <param name="username">The user to return a list of roles for.</param>
/// <returns>
/// A string array containing the names of all the roles that the specified user is in for the configured applicationName.
/// </returns>
public override string[] GetRolesForUser(string username)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
return GetRolesByUserName(connection, username);
}
}
/// <summary>
/// Initializes a new instance of the
/// <see cref="MySqlScript"/> class.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="query">The query.</param>
public MySqlScript(MySqlConnection connection, string query)
: this()
{
this.connection = connection;
this.query = query;
}
/// <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;
}
}
public ISSchemaProvider(MySqlConnection connection)
: base(connection)
{
}
/// <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;
}
}