C# Mysql Connection must be valid and open

Question

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.

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?

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!

Answer 1

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
}

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.

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);
    }
}

Answer 2

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

Answer 3

Try to restructure your Services class as follows

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

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

 // Rather than executing result here, return the result to LoginForm - Future improvement
  public static void DB_Select(MySqlConnection conn ,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);
            }
        }

In LoginForm.cs use returning connection and store it there. When you need to execute query, use

           MySqlConnection conn=Services.conn(); // Get a new connection
           Services.DB_Select(conn,"..a short select statement.."); // Executing requirement
           Services.conn.Close(); 

Additional - I suggest you need to return MySqlDataReader to LoginForm and handle results there