Ejemplo n.º 1
0
        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);
            }
        }
Ejemplo n.º 3
0
        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);
            }
        }
Ejemplo n.º 4
0
        /*
         * 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;
            }
        }
Ejemplo n.º 5
0
        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);
            }
        }
Ejemplo n.º 7
0
        /// <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);
            }
        }
Ejemplo n.º 9
0
        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();
            }
        }
Ejemplo n.º 11
0
        /// <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;
                }
            }
        }
Ejemplo n.º 12
0
 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);
 }
Ejemplo n.º 13
0
        /// <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();
                }
            }
        }
Ejemplo n.º 14
0
        /// <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;
                }
            }
        }
Ejemplo n.º 15
0
 public Statement(MySqlCommand cmd, string text)
     : this(cmd)
 {
     commandText = text;
 }
Ejemplo n.º 16
0
        /// <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;
            }
        }
Ejemplo n.º 17
0
        /// <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;
            }
        }
Ejemplo n.º 18
0
        /// <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;
            }
        }
Ejemplo n.º 19
0
        /// <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;
        }
Ejemplo n.º 20
0
        /// <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;
                }
            }
        }
Ejemplo n.º 21
0
 /// <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;
 }
Ejemplo n.º 22
0
        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;
            }
        }
Ejemplo n.º 23
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;
 }
Ejemplo n.º 24
0
 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();
 }
Ejemplo n.º 25
0
 private Statement(MySqlCommand cmd)
 {
     command = cmd;
     buffers = new ArrayList();
 }
Ejemplo n.º 26
0
 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);
            }
        }
Ejemplo n.º 28
0
        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;
            }
        }
Ejemplo n.º 29
0
 /// <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;
     }
 }
Ejemplo n.º 30
0
        /// <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());
        }