Kye Comments - Answer Overflow

Kye

•Created by Kye on 4/5/2025 in #help

BackgroundService Question

the issue was that Discord.Addons.Hosting contains a WaitForReadyAsync method that is specifically designed for cases like this which is... eh. i missed that in the documentation somehow but yep.

8 replies

CC#

•Created by Kye on 4/5/2025 in #help

BackgroundService Question

well, i can probs close this actually.

8 replies

CC#

•Created by Kye on 4/5/2025 in #help

BackgroundService Question

hm.

8 replies

CC#

•Created by Kye on 4/5/2025 in #help

BackgroundService Question

Ah er- are they referencing from another?

8 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

hello, yes?

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

thank you mtreit :Pat_good_pet:

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

so I think for future resolutions, if I know for certain that I'm expecting things like channel/roles and perhaps even users, to break those up into procedures so I can call them individually where needed whilst keeping my code clean.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

i think the problem was to store all the INSERT INTO statements into a single procedure thus upon call it expects all parameters to be passed in with each new SqlCommand being created and called.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

correct.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

yes, i was planning to make methods for those.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

since I never worked with databases, do you think I could do this:

INSERT INTO role_config (id, role_id, role_name, permission_level, allow_commands)
VALUES (p_guild_id, p_role_id, p_role_name, p_permission_level, p_allow_commands);

INSERT INTO channel_config (id, channel_id, channel_name, allowed_members, allowed_roles, allow_commands)
VALUES (p_guild_id, p_channel_id, p_channel_name, p_allowed_members, p_allowed_roles, p_allow_commands);

INSERT INTO role_config (id, role_id, role_name, permission_level, allow_commands)
VALUES (p_guild_id, p_role_id, p_role_name, p_permission_level, p_allow_commands);

INSERT INTO channel_config (id, channel_id, channel_name, allowed_members, allowed_roles, allow_commands)
VALUES (p_guild_id, p_channel_id, p_channel_name, p_allowed_members, p_allowed_roles, p_allow_commands);

ofc i would separate those and create individual procs for those.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

you're definitely right.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

I was hoping to do this in one but I think separation would probably make it easier

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

damn 😭 didn't expect the code to be so long LOL

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

So if I was to say use a small helper method, I could do:

// Insert into role_config table
foreach (var role in guild.Roles)
{
    using var createRoleConfig = GetSqlCommandForGuildCreationSproc(connection, transaction);
    createRoleConfig.Parameters.AddWithValue("@p_guild_id", guild.Id);
    createRoleConfig.Parameters.AddWithValue("@p_role_id", role.Id);
    createRoleConfig.Parameters.AddWithValue("@p_role_name", role.Name);
    createRoleConfig.Parameters.AddWithValue("@p_permission_level",
        role.Permissions.Administrator ? 100 :
        role.Permissions.ManageGuild ? 90 :
        role.Permissions.ManageRoles ? 80 :
        role.Permissions.ManageChannels ? 70 :
        role.Permissions.BanMembers ? 50 :
        role.Permissions.KickMembers ? 25 :
        role.Permissions.ManageMessages ? 10 : 0
    );
    createRoleConfig.Parameters.AddWithValue("@p_allow_commands",
        role.Permissions.Administrator ||
        role.Permissions.ManageGuild ||
        role.Permissions.ManageRoles ||
        role.Permissions.ManageChannels ||
        role.Permissions.ManageMessages
    );

    await createRoleConfig.ExecuteNonQueryAsync();
}

// Insert into channel_config table
foreach (var channel in guild.Channels)
{
    using var createChannelConfig = GetSqlCommandForGuildCreationSproc(connection, transaction);
    createChannelConfig.Parameters.AddWithValue("@p_guild_id", guild.Id);
    createChannelConfig.Parameters.AddWithValue("@p_channel_id", channel.Id);
    createChannelConfig.Parameters.AddWithValue("@p_channel_name", channel.Name);
    createChannelConfig.Parameters.AddWithValue("@p_allowed_members", JsonSerializer.Serialize(
        guild.Users
            .Where(user => user.GuildPermissions.Administrator && !user.IsBot)
            .OrderByDescending(user => user.Username)
            .Select(user => user.Id)
            .ToList()
    ));
    createChannelConfig.Parameters.AddWithValue("@p_allowed_roles", JsonSerializer.Serialize(
        guild.Roles
            .Where(role => role.Permissions.Administrator && !role.IsManaged)
            .OrderByDescending(role => role.Name)
            .Select(role => role.Id)
            .ToList()
    ));
    createChannelConfig.Parameters.AddWithValue("@p_allow_commands", false);

    await createChannelConfig.ExecuteNonQueryAsync();
}

// Insert into role_config table
foreach (var role in guild.Roles)
{
    using var createRoleConfig = GetSqlCommandForGuildCreationSproc(connection, transaction);
    createRoleConfig.Parameters.AddWithValue("@p_guild_id", guild.Id);
    createRoleConfig.Parameters.AddWithValue("@p_role_id", role.Id);
    createRoleConfig.Parameters.AddWithValue("@p_role_name", role.Name);
    createRoleConfig.Parameters.AddWithValue("@p_permission_level",
        role.Permissions.Administrator ? 100 :
        role.Permissions.ManageGuild ? 90 :
        role.Permissions.ManageRoles ? 80 :
        role.Permissions.ManageChannels ? 70 :
        role.Permissions.BanMembers ? 50 :
        role.Permissions.KickMembers ? 25 :
        role.Permissions.ManageMessages ? 10 : 0
    );
    createRoleConfig.Parameters.AddWithValue("@p_allow_commands",
        role.Permissions.Administrator ||
        role.Permissions.ManageGuild ||
        role.Permissions.ManageRoles ||
        role.Permissions.ManageChannels ||
        role.Permissions.ManageMessages
    );

    await createRoleConfig.ExecuteNonQueryAsync();
}

// Insert into channel_config table
foreach (var channel in guild.Channels)
{
    using var createChannelConfig = GetSqlCommandForGuildCreationSproc(connection, transaction);
    createChannelConfig.Parameters.AddWithValue("@p_guild_id", guild.Id);
    createChannelConfig.Parameters.AddWithValue("@p_channel_id", channel.Id);
    createChannelConfig.Parameters.AddWithValue("@p_channel_name", channel.Name);
    createChannelConfig.Parameters.AddWithValue("@p_allowed_members", JsonSerializer.Serialize(
        guild.Users
            .Where(user => user.GuildPermissions.Administrator && !user.IsBot)
            .OrderByDescending(user => user.Username)
            .Select(user => user.Id)
            .ToList()
    ));
    createChannelConfig.Parameters.AddWithValue("@p_allowed_roles", JsonSerializer.Serialize(
        guild.Roles
            .Where(role => role.Permissions.Administrator && !role.IsManaged)
            .OrderByDescending(role => role.Name)
            .Select(role => role.Id)
            .ToList()
    ));
    createChannelConfig.Parameters.AddWithValue("@p_allow_commands", false);

    await createChannelConfig.ExecuteNonQueryAsync();
}

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

got it okay, i was under the impression that creating another instance of a MySqlCommand and calling the procedure would mean that it expects all parameters to be called in one.

47 replies

CC#

•Created by Kye on 2/26/2025 in #help

✅ MySQL Database Procedure Problem

private MySqlCommand GetSqlCommandForGuildCreationSproc(MySqlConnection connection, MySqlTransaction transaction)
{
    var command = new MySqlCommand("register_guild", connection, transaction)
    {
        CommandType = CommandType.StoredProcedure
    };

    return command;
}

private MySqlCommand GetSqlCommandForGuildCreationSproc(MySqlConnection connection, MySqlTransaction transaction)
{
    var command = new MySqlCommand("register_guild", connection, transaction)
    {
        CommandType = CommandType.StoredProcedure
    };

    return command;
}