C#Mysql连接必须有效且打开 [英] C# Mysql Connection must be valid and open

问题描述

首先:我在不使用oop的情况下运行了我的代码.我在同一个类中声明了所有变量，并在将查询传递给数据库之前和之后打开/关闭了连接.奏效了！现在有了一些新的经验，我试图将代码分成不同的类.现在它不再工作了.

First of all: I got my code running without using oop. I declared all my variables inside the same class and opened/closed the connection right before and after passing the query to the db. That worked! Now with some new experiences I tried to split my code into different classes. Now it wont work anymore.

它告诉我连接必须有效且打开".足够的文字，这是我当前的代码:

It tells me "Connection must be valid and open". Enough text, here's my current code:

Services.cs

public static MySqlConnection conn // Returns the connection itself
        {
            get
            {
                MySqlConnection conn = new MySqlConnection(Services.ServerConnection);
                return conn;
            }
        }

public static string ServerConnection // Returns the connectin-string
        {
            get
            {    
                return String.Format("Server={0};Port=XXXX;Database=xxx;Uid=xxx;password=xxXxxXxXxxXxxXX;", key);
            }
        }

public static void DB_Select(string s, params List<string>[] lists)
        {
            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string command = s;
                cmd.CommandText = command;
                MySqlDataReader sqlreader = cmd.ExecuteReader();
                while (sqlreader.Read())
                {
                    if (sqlreader[0].ToString().Length > 0)
                    {
                        for (int i = 0; i < lists.Count(); i++)
                        {
                            lists[i].Add(sqlreader[i].ToString());
                        }
                    }
                    else
                    {
                        foreach (List<string> save in lists)
                        {
                            save.Add("/");
                        }
                    }
                }
                sqlreader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error while selecting data from database!\nDetails: " + ex);
            }
        }

LoginForm.cs

private void checkUser(string username, string password)
        {
        using (Services.conn)
                    {
                        Services.conn.Open();
                        Services.DB_Select("..a short select statement..");
                        Services.conn.Close();
                    }

---

我想这就是我们所需要的.我缩短了代码以专注于该问题.

---

I guess this is all we need. I have shortened my code to get a focus on the problem.

我创建了Services.cs，以便获得一种从所有形式访问db的全局方法，而无需复制并粘贴连接信息.现在，当我到达LoginForm.cs时，它会引发错误连接必须有效且打开" .我已经调试了我的代码.都关门了即使传递conn.Open()，它也保持关闭状态.为什么?

I created Services.cs to get a global way to access the db from all forms without copy&pasting the connection info. Now when I reach my LoginForm.cs it throws an error "Connection must be valid and open". I've already debugged my code. It's all time closed. Even when passing conn.Open() it stays closed. Why?

另一种尝试:我也尝试过将conn.Open()和conn.Close()放在Services.DB_Select(..)的开头和结尾.同样的错误在这里.

Another try: I've also tried placing conn.Open() and conn.Close() inside Services.DB_Select(..) at the beginning and end. Same error here.

我不得不说:该代码以前工作过，并且我使用了相同的连接字符串.因此，字符串本身肯定是有效的.

I have to say: The code worked before and I've used the same connection-string. So the string itself is surely valid.

感谢您在这里提供的任何帮助！

I appreciate any help given here!

推荐答案

问题是您没有存储从工厂属性返回的连接.但是不要使用诸如方法之类的属性.而是以这种方式使用它:

The problem is that you don't store the connection that was returned from your factory property. But don't use a property like a method. Instead use it in this way:

using (var con = Services.conn)
{
    Services.conn.Open();
    Services.DB_Select("..a short select statement..", con ));
    //Services.conn.Close(); unnecessary with using
}

因此，在using中使用与从属性返回的连接相同的连接(或更好地在using中创建的连接)，并将其传递给使用它的方法.顺便说一句，将属性用作工厂方法并不是最佳实践.

So use the same connection in the using that was returned from the property(or better created in the using) and pass it to the method which uses it. By the way, using a property as factory method is not best practise.

但是在我看来，最好在您使用它的地方创建连接，最好的位置是在using语句中.并将con属性扔到垃圾桶中，这是毫无意义的，并且是令人讨厌的错误的根源.

But in my opinion it's much better to create the connection where you use it, best place is in the using statement. And throw the con property to the garbage can, it is pointless and a source for nasty errors.

public static void DB_Select(string s, params List<string>[] lists)
{
    try
    {
         using(var conn = new MySqlConnection(Services.ServerConnection))
         {
            conn.Open();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = s;
            using( var sqlreader = cmd.ExecuteReader())
            while (sqlreader.Read())
            {
                if (sqlreader[0].ToString().Length > 0)
                {
                    for (int i = 0; i < lists.Count(); i++)
                    {
                        lists[i].Add(sqlreader[i].ToString());
                    }
                }
                else
                {
                    foreach (List<string> save in lists)
                    {
                        save.Add("/");
                    }
                }
            } // unnecessary to close the connection
        }     // or the reader with the using-stetement
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error while selecting data from database!\nDetails: " + ex);
    }
}

这篇关于C#Mysql连接必须有效且打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！