How can I query the MySQL Max_Allowed_Packet through c#?

Question

I am trying to check the value of the max_allowed_packet size. This is my code:

public int MaxAllowedPacket()
{                
    var max = 0;
    using (var conn = new MySqlConnection(_ConnectionString))
    {
        var sql = conn.CreateCommand();
        sql.CommandText = "SHOW VARIABLES like 'max_allowed_packet'";

        try
        {
            conn.Open();
            var reader = sql.ExecuteReader();
            // not sure where to go from here
        }
        catch (Exception ex)
        {
            // I've got some logging here
        }
    }
    return max;
}

I'm suspecting the format of the query or the execution is wrong because my result is always

-1

EDIT:

I have edited the code to use sql.ExecuteReader() but the result is now:

"Enumeration yielded no results".

running executenonquery, doesnt return data - so ... you cant ask for "select stuff" to return you values. — BugFinder, Jul 26 '18 at 07:05
@BugFinder Thank you. I have changed it to `sql.ExecuteReader();` but the query returns "Enumeration yielded no results". What is the right way to read the value? — 4ndy, Jul 26 '18 at 08:05
if you just expect one single value back, then ExecuteScalar is probably easier to work with — ADyson, Jul 26 '18 at 08:20
@ADyson ExecuteScalar only seems to return the very first value, in this case the string `"max_allowed_packet"`. — 4ndy, Jul 26 '18 at 08:24
@ToshiBoy No problem, I wasn't sure exactly what it would return. Your answer is right, with a DataReader you have to execute the Read() command to move to the first line (and then execute it again to move to subsequent lines - people usually put `while (reader.Read()) { ... }` to read all rows in a multi-row resultset — ADyson, Jul 26 '18 at 08:26

score 1 · Accepted Answer · answered Jul 26 '18 at 08:20

Eventually figured it out myself, and thought to post it here, before this gets downvoted even more...

var reader = sql.ExecuteReader();
reader.Read();
max = reader.GetInt32(1);

It's best to put some try catches around and you can optionally query the first field through reader.GetString(0), which should return "max_allowed_packet".

How can I query the MySQL Max_Allowed_Packet through c#?

1 Answers1