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!
Details: " + 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 以获得从所有表单访问数据库的全局方式，而无需复制和粘贴连接信息.现在，当我到达我的 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!
Details: " + ex);
    }
}

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