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()); }