解决 尝试执行ExecuteReader时,Mysql.Data连接必须有效并打开错误

假鼻子

成员
已加入
2019年9月19日
留言内容
10
编程经验
Beginner
大家好,

我对C#相当陌生,并且完全无法理解为什么我在尝试运行ExecuteReader时始终保持此连接必须有效并且打开错误的原因。

我知道与数据库的连接是成功的,由于某种原因,仅当我在using语句中进行操作时,才会引发此错误……我丢失了什么吗?

感谢您的帮助!

这是错误输出:
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

这是代码:

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:

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
10+
实际上是这样的:
C#:
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.
 

羊皮

退休程序员
工作人员
已加入
2018年9月5日
留言内容
1,933
地点
英国
编程经验
10+
您也不需要为编译器做额外的工作。从try块内部删除try块。
 

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
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.
 

羊皮

退休程序员
工作人员
已加入
2018年9月5日
留言内容
1,933
地点
英国
编程经验
10+

跳伞

工作人员
已加入
2019年4月6日
留言内容
2,538
地点
弗吉尼亚州切萨皮克
编程经验
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.
 

假鼻子

成员
已加入
2019年9月19日
留言内容
10
编程经验
Beginner
老实说,发布这篇文章后,我删除了嵌套的try catch。甚至不确定我在想什么。

If your query is only retrieving a single value then you should call ExecuteScalar rather than ExecuteReader.

谢谢您,我认为这样做的方法不正确。

感谢您的所有建议。真的很有帮助!
 
最佳 底部