Resolved Mysql.Data Connection must be valid and open error when trying to ExecuteReader

fakeNoose

Member
Joined
Sep 19, 2019
Messages
8
Programming Experience
Beginner
Hey guys,

I'm fairly new to C#, and I'm completely failing to understand why I keep getting this Connection must be valid and open error when I try to run ExecuteReader.

I know the connection to the database is successful, for some reason this error only throws if I'm doing it within a using statement... Am I missing something?

Thank you for the help!

Here's the error output:
C#:
System.InvalidOperationException: Connection must be valid and open.
  at MySql.Data.MySqlClient.MySqlConnection.Throw (System.Exception ex) [0x00008] in <4b988c68d54e4aa0a0550cbca36bc286>:0
  at MySql.Data.MySqlClient.MySqlCommand.Throw (System.Exception ex) [0x0000a] in <4b988c68d54e4aa0a0550cbca36bc286>:0
  at MySql.Data.MySqlClient.MySqlCommand.CheckState () [0x0003e] in <4b988c68d54e4aa0a0550cbca36bc286>:0
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00035] in <4b988c68d54e4aa0a0550cbca36bc286>:0
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader () [0x00000] in <4b988c68d54e4aa0a0550cbca36bc286>:0
  at (wrapper remoting-invoke-with-check) MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
  at Sync_DB.Main.dbValidate (System.String resourceName) [0x0006d] in <013fb08bcbce4820b0b795ea43b9d1d6>:0
  at (wrapper managed-to-native) System.Reflection.RuntimeMethodInfo.InternalInvoke(System.Reflection.RuntimeMethodInfo,object,object[],System.Exception&)
  at System.Reflection.RuntimeMethodInfo.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x0006a] in <fbc4ec45371543bfba3678ebb82caf6d>:0
   Exception_EndOfInnerExceptionStack
  at System.Reflection.RuntimeMethodInfo.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00083] in <fbc4ec45371543bfba3678ebb82caf6d>:0
  at System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) [0x00000] in <fbc4ec45371543bfba3678ebb82caf6d>:0
  at System.Delegate.DynamicInvokeImpl (System.Object[] args) [0x000e7] in <fbc4ec45371543bfba3678ebb82caf6d>:0
  at System.MulticastDelegate.DynamicInvokeImpl (System.Object[] args) [0x00008] in <fbc4ec45371543bfba3678ebb82caf6d>:0
  at System.Delegate.DynamicInvoke (System.Object[] args) [0x00000] in <fbc4ec45371543bfba3678ebb82caf6d>:0
Here's the code:

C#:
private void dbValidate(string resourceName)
{
    if (GetCurrentResourceName() != resourceName) return;

    Debug.WriteLine("[MySQL] Validating MySql configuration...");
    try
    {
        using (MySqlConnection conn = new MySqlConnection(cs.ToString()))
        {
            Debug.WriteLine("[MySQL] MySQL connection established...");

            try
            {
                // Check if database exists
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'sharpdb';";
                    using (MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            if (rdr.GetInt32(0) < 1)
                            {
                                Debug.WriteLine("[MySQL] Database doesn't exist.");
                            }
                        }
                    }
                }

                Debug.WriteLine("[MySQL] Database exists");
            }
            catch (MySqlException ex)
            {
                Debug.WriteLine(ex.ToString());
            }
        }
    }
    catch (MySqlException ex)
    {
        Debug.WriteLine(ex.ToString());
    }
}
 
Last edited:

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
461
Location
Virginia Beach, VA
Programming Experience
10+
Actually this:
Code:
using (MySqlConnection conn = new MySqlConnection(cs.ToString()))
does not establish a connection. It just instantiates an object that can potentially make a connection. The connection is actually established when you call conn.Open(). I'm not seeing a call to open the connection above.
 

fakeNoose

Member
Joined
Sep 19, 2019
Messages
8
Programming Experience
Beginner
Well now I feel like an idiot.

Thanks for the help!
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
466
Location
UK
Programming Experience
10+
You also don't need to make extra work for the compiler. Remove your try block from inside your try block.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
461
Location
Virginia Beach, VA
Programming Experience
10+
I believe the OP was using the try-catch for the sake of logging, not try-finally for the sake of closing connections and/or disposing objects. The latter is what usually seen floating around the inter-webs. using is the appropriate replacement for the try-finally pattern.
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
466
Location
UK
Programming Experience
10+

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
461
Location
Virginia Beach, VA
Programming Experience
10+
Ah. So sorry. I've been reviewing some bad IT code the past few days. They were doing the same thing that the OP was doing above, except for one key difference: the bad IT code was re-throwing the exception while the OP's code was not. The bad IT code were purely using the try-catch for logging because they wanted to capture some contextual information that was only available down at that level of their deeply nested code. In the case of the OP's code, you are correct -- all he needs is the outermost try-catch.
 

fakeNoose

Member
Joined
Sep 19, 2019
Messages
8
Programming Experience
Beginner
To be honest, after I made this post I removed the nested try catch. Not even sure what I was thinking when I put that in.

If your query is only retrieving a single value then you should call ExecuteScalar rather than ExecuteReader.
Thank you for this, I figured the way I was doing it wasn't the right way.

Thanks for all of the suggestions. It's really helpful!
 
Top Bottom