C#•5d ago

✅ MySQL Database Procedure Problem

Heyo :) I'm creating this post to get some assistance around database procedures and creating the necessary data without conflicting already defined parameters. The code below is not optimized yet, i'm still fixing the problem I currently have before I optimize it. Any help is appreciated!

33 Replies

kyeedeOP•5d ago

code.txt

kyeedeOP•5d ago

so yeah, i was removing chunks of code and removed comments to reducee the character-count to ~4000 but eh, seems like discord has a good way of displaying this message :Kek:

mtreit•5d ago

$code

MODiX•5d ago

To post C# code type the following: ```cs // code here ``` Get an example by typing $codegif in chat For longer snippets, use: https://paste.mod.gg/

kyeedeOP•5d ago

oo, i see.

kyeedeOP•5d ago

https://paste.mod.gg/cdytfyvcbjhv/0

BlazeBin - cdytfyvcbjhv

A tool for sharing your source code with the world!

kyeedeOP•5d ago

i think that should do the trick. thank you mtreit :)

mtreit•5d ago

What's your actual question about the code?

kyeedeOP•5d ago

So the issue i'm currently experience is the way I'm inserting data into the database. As you can see, i'm basically adding the same parameters for each role/channel which causes an exception for already defined parameters. The solution I guess for this would be to use a for loop and apply an index number at each parameter but that wouldn't work as my procedure has a fixed parameter name and doesn't support (atm) p_role_id_1 for instance. This causes parameter p_role_id_x not defined exception i've barely worked with databases but I'd like to get back into it. so yeah i have a hard time trying to do it the right way idk how :Loopy_sadge:

mtreit•5d ago

You need to call the sproc multiple times. One for each channel or whatever.

kyeedeOP•5d ago

that's actually something I tried (maybe in the wrong way) but I believe calling the sproc would expect all parameters to be called again, no?

mtreit•5d ago

Create a new SqlCommand for each call.

kyeedeOP•5d ago

so for the loop, create a new SqlCommand

mtreit•5d ago

I would probably completely refactor this code.

kyeedeOP•5d ago

yes, it's very unorganized i'll be fair :Sadge: i just wanna try to fix this issue and then re-factor it using the proper way of implementation

mtreit•5d ago

You probably want something like:

foreach (var channel in channels)
{
    foreach (var role in roles)
    {
        using var cmd = GetSqlCommandForGuildCreationSproc();
        // Fill out all of the parameters for the call to create the guild

        // Now execute it
        cmd.ExecuteNonQuery(); // Or whatever if it returns data
    }
}

foreach (var channel in channels)
{
    foreach (var role in roles)
    {
        using var cmd = GetSqlCommandForGuildCreationSproc();
        // Fill out all of the parameters for the call to create the guild

        // Now execute it
        cmd.ExecuteNonQuery(); // Or whatever if it returns data
    }
}

Are you actually intending to create multiple guilds?

kyeedeOP•5d ago

yes, my integration will be storing data for each guild.

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

something like this perchance?

mtreit•5d ago

Sure, I just stuck that in as pseudo-code, you don't necessarily need to make a method for that - although it usually is cleaner and easy to read if you make small methods and chain them together in your overall flow.

kyeedeOP•5d ago

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

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

mtreit•5d ago

Why don't you have separate sprocs for creating roles vs. creating channels? spCreateChannel spCreateRole

kyeedeOP•5d ago

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

mtreit•5d ago

Yes it would 🙂

kyeedeOP•5d ago

you're definitely right. 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.

mtreit•5d ago

And you can break that code up into more methods like:

AddParametersForChannel(int guildId, int channelId, string channelName, string allowedMembersJson, SqlCommand command);

AddParametersForChannel(int guildId, int channelId, string channelName, string allowedMembersJson, SqlCommand command);

kyeedeOP•5d ago

yes, i was planning to make methods for those.

mtreit•5d ago

In your stored procedure? Sure.

kyeedeOP•5d ago

correct. 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. 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. thank you mtreit :Pat_good_pet:

Mąż Zuzanny Harmider Szczęście•5d ago

$close

MODiX•5d ago

If you have no further questions, please use /close to mark the forum thread as answered

Mąż Zuzanny Harmider Szczęście•5d ago

@kyeede

kyeedeOP•5d ago

hello, yes?

Mąż Zuzanny Harmider Szczęście•5d ago

$close

MODiX•5d ago

If you have no further questions, please use /close to mark the forum thread as answered

Gaming

Programming

✅ MySQL Database Procedure Problem

Did you find this page helpful?