Questions regarding PreparedStatements

[OAguinagalde]

Hello, sorry this is not an issue or a bug report but couln't find an appropriate place where I can find help.

I'm playing around with code generation for communicating with the database.
The idea is that given a query string and a precise list of the input and output of that query, a class is generated for calling that query whenever necessary. With a public API such as this:

namespace Database.SomeNamespace {
    public class TheQuerysName {
        
        public static class Input { . . . }
        public static class Output { . . . }

        public static List<Output> QueryAll(Input input) { . . . }
        public static Output QueryFirst(Input input) { . . . }
    }
}

I have done this before in Java and it worked great.
Now I'm doing the same in a different project, in c# and I'm having some questions regarding PreparedStatements and MySqlConnector:

The whole point of PreparedStatements is to be able to reuse them.
When creating my PreparedStatements in PreparedStatementFactory I pass the connection db on the constructor of MySqlCommand. But MySqlConnections are not reusable, even though I'm trying to reuse my MySqlCommands, which contain a reference to the connection.

So, am I supposed to assign the connection to the currently using one, like I'm doing here in QueryAll?

// The data that generated the following class is this:
// 
//     function/var_name/input_output |sql_type/namespace |database |write_required |query_source
//     _______________________________|___________________|_________|_______________|____________
//     ClanCountFromRegion            |Clan               |player   |false          |"SELECT COUNT(*) AS cnt FROM clan WHERE region_code = @region_code"
//     input                          |                   |         |               |            
//     region_code                    |VARCHAR            |         |               |
//     output                         |                   |         |               |            
//     cnt                            |INT                |         |               |
// 
namespace DB.Player {

    public class ClanCountFromRegion  {

        // **********
        // Public API
        // **********

        public class Output {
            public int cnt;
        }

        public class Input {
            public string region_code;
        }

        /// <summary>
        /// Returns a list of rows for the given query. Returns an empty list if no data is found.
        /// </summary>
        public static List<Output> QueryAll(MySqlConnection db, Input input) {

            // Lazily initialized MySqlCommand pool
            if (PreparedStatementPool == null) {
                PreparedStatementPool = new ObjectPool<MySqlCommand>(
                    () => { return PreparedStatementFactory(db); }
                );
            }

            // Since this method might be called form different threads, the MySqlCommand might be in use
            // hence the ObjectPool being used here
            var command = PreparedStatementPool.Get();

            // CONCERN: The whole point of PreparedStatements is to be able to reuse them.
            //     When creating my PreparedStatements in `PreparedStatementFactory` I pass the connection `db`
            //     on the constructor of `MySqlCommand`. But `MySqlConnections` are not reusable!! what the heck?
            //     So, am I supposed to assign the connection to the currently using one, like I'm doing here?
            command.Connection = db;
            
            try {
                SetInput(command, input);
                var reader = command.ExecuteReader();
                List<Output> rows = ParseAll(reader);
                return rows;
            }
            // I'm not catching any Exception on purpose, since there is no way for us to handle it here.
            finally {
                PreparedStatementPool.Return(command);
            }
        }

        /// <summary>
        /// Returns the first row of the query, or null if there are no rows
        /// </summary>
        public static Output QueryFirst(MySqlConnection db, Input input) {
            var rows = QueryAll(db, input);
            if (rows.Count == 0) return null;
            return rows[0];
        }



        // ***********
        // Private API
        // ***********
        
        // If the query is not created as an Stored Procedure, we can instead just defined them in the class itself,
        // and use them as prepared statements. This will keep them almost as secure and performant.
        private readonly static string Query = "SELECT COUNT(*) AS cnt FROM clan WHERE region_code = @region_code";
        private readonly static bool ReadOnly = true;
        
        // Use a concurrent object pool since there might be more than one thread executing the same query at a given time
        private static ObjectPool<MySqlCommand> PreparedStatementPool = null;
        
        private static void SetInput(MySqlCommand command, Input input) {
            command.Parameters.AddWithValue("@region_code", input.region_code);
            command.Parameters["@region_code"].Direction = ParameterDirection.Input;
        }

        private static List<Output> ParseAll(MySqlDataReader reader) {
            var result = new List<Output>();
            while (reader.Read()) {
                var output = new Output();
                output.cnt = reader.GetInt32("cnt");
                result.Add(output);
            }
            return result;
        }
        
        private static Output Parse(MySqlDataReader reader) {
            Output output = new Output();
            reader.Read();
            output.cnt = reader.GetInt32("cnt");
            return output;
        }

        private static MySqlCommand PreparedStatementFactory(MySqlConnection db) {

            var preparedStatement = new MySqlCommand(Query, db);
            preparedStatement.CommandType = CommandType.Text;
            
            // The first time we need to add placeholders in the parameters
            preparedStatement.Parameters.Add("@region_code");
            preparedStatement.Parameters["@region_code"].Direction = ParameterDirection.Input;
            
            preparedStatement.Prepare();

            return preparedStatement;
        }

    }
}

Sorry for the wall of code, I wasn't sure I could explain the situation without posting the code.
Thanks and regards.

[bgrainger]

I'm not entirely sure what you mean by "But MySqlConnections are not reusable". MySqlConnection is not a one-time use class; you can reuse an open connection as much as you want (just not simultaneously by two different threads), or even Close and re-Open it.

You'd have to profile its use with your particular database, but my general feeling is that prepared statements in MySQL are not worth it. Yes, you do save a little bit of time from not having to re-parse the SQL when you re-execute the statement. But on the other hand, they're scoped to just one physical database connection. You'd have to prepare each of the n commands your application will use on each of the m concurrent DB connections your client will have open.

I'd probably recommend removing all the code that tries to prepare commands; this would simplify these classes quite a bit and remove the need for the object pool. Invoking the method would first get an open DB connection (e.g., using application configuration to obtain the query string), and dispose it as soon as the method was done. This would use MySqlConnector's connection pool to efficiently retrieve and reuse existing connections to the database.

using var connection = new MySqlConnection(GetConnectionString());
connection.Open();
var output = ClanCountFromRegion.QueryAll(connection, new() { region_code = "us" });

One final remark: if you do want to proceed with having prepared statements, you may want to disable MySqlConnector's connection pooling, and completely manage it yourself (with an ObjectPool or similar). This way, you could control which connections each SQL statement gets prepared on, and make sure they're never closed, etc. This would be done by adding ;Pooling=false to your connection string. Again, you'd need to profile this to see if it was better in your environment.

[OAguinagalde]

Thanks for the detailed answer. I think I'll stick to non-prepared commands then.