C# Mysql multiple queries

Question

Im trying to build up a little status-tool. I need to get results of multiple queries (about 4-5). The general connection-setup and 'how-to-read-data' is already done but I cant figure out how the another query executed.

Everything I found while searching for it is for the SqlClient. Im totally overcharged with this.

Here is my code so far (be patient, im a newbie to this):

private void button1_Click(object sender, EventArgs e)
        {
            if(listView1.Items.Count > 1)
            {
                listView1.Items.Clear();
            }
            var listMember = new List<string>{};
            var listOnline = new List<string>{};

            // SQL PART //
            string connString = "Server=10*****;Port=3306;Database=e***;Uid=e***;password=********************;";
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "SELECT fullname,online FROM member WHERE active = '1' ORDER BY online DESC";
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                listView1.Items.Add("Error: " + ex);
            }
            MySqlDataReader reader = command.ExecuteReader();
            while(reader.Read())
            {
                listMember.Add(reader["fullname"].ToString());
                listOnline.Add(reader["online"].ToString());
            }
            conn.Close();
            // SQL ENDING //

            // SET ENTRIES TO LISTVIEW //
            int counter = 0;
            foreach(string member in listMember)
            {
                ListViewItem item = new ListViewItem(new[] { member, listOnline.ElementAt(counter) });
                item.ForeColor = Color.Green;
                listView1.Items.Add(item);

                counter++;
            }
        }

Im not really sure how the design/layout will look like in the end, so I would like to just append the results to lists in the sql-part to process the data later out of the lists.

Do I really have to setup a complete new connection after conn.Close()? Or is there any other way? I can just imagine: 5 queries with their own connection,try,catch and 2 loops... this will get about 100-200 lines just for getting the results out of 5 queries. Isnt that a bit too much for such an easy thing?

Hope for some help. Greetings.

According to the new comments my latest code:

Top:

public partial class Form1 : Form
{
    public static string connString = "Server=10****;Port=3306;Database=e****;Uid=e****;password=****;";
    public Form1()
    {
        InitializeComponent();
        MySqlConnection conn = new MySqlConnection(connString); // Error gone!
    }

Body part:

 public void QueryTwoFields(string s, List<string> S1, List<string> S2)
        {
            try
            {
                MySqlCommand cmd = conn.CreateCommand(); // ERROR: conn does not exist in the current context.
                cmd.CommandType = CommandType.Text;
                string command = s;
                cmd.CommandText = command;
                MySqlDataReader sqlreader = cmd.ExecuteReader();
                while (sqlreader.Read())
                {
                    S1.Add(sqlreader[0].ToString());
                    S2.Add(sqlreader[1].ToString());
                }
                sqlreader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

private void button1_Click(object sender, EventArgs e)
        {
            if(listView1.Items.Count > 1)
            {
                listView1.Items.Clear();
            }
            var listMember = new List<string>{};
            var listOnline = new List<string>{};

            using (conn) // ERROR: conn does not exist in the current context.
            {
            conn.Open();
            ///...1st Query
            QueryTwoFields("SELECT fullname,online FROM member WHERE active = '1' ORDER BY online DESC",listMember,listOnline);
            //...2nd query
            //QueryTwoFields("your new Select Statement", otherList, otherList); 
            }
        }

Answer 1

You don't have to close the connection every time you execute one query rather than close the SqlReader assigned to that connection. When all of your queries have been executed you close the connection. Consider also the use of using.

You can also define a method for the execution of your Query in order for your code not to be repetitive:

public void QueryTwoFields(string s, List<string> S1, List<string> S2)    
{
    // Select into List S1 and List S2 from  Database (2 fields)
    try
    {             
          MySqlCommand cmd = conn.CreateCommand();
          cmd.CommandType = CommandType.Text;
          string command = s;
          cmd.CommandText = command;
          MySqlDataReader sqlreader = cmd.ExecuteReader();
    
          while (sqlreader.Read())
          {
              S1.Add(sqlreader[0].ToString());
              S2.Add(sqlreader[1].ToString());            
          }
          sqlreader.Close();
      
    }
    catch (Exception ex)
    {                            
        MessageBox.Show(ex.ToString());            
    }                                           
}

private void button1_Click(object sender, EventArgs e)
{
    if(listView1.Items.Count > 1)
    {
        listView1.Items.Clear();
    }
    var listMember = new List<string>{};
    var listOnline = new List<string>{};
    
    // SQL PART //
    
    using (conn)
    {
        conn.Open();
        //...1st Query
        QueryTwoFields(
            "SELECT fullname,online FROM member WHERE active = '1' ORDER BY online DESC",
            listmember,
            listonline
        );
        
        //...2nd query
        QueryTwoFields(
            "your new Select Statement",
            myOtherList1,
            myOtherlist2
        );    
        
        //....
    }
}

---

EDIT: Keep in mind that you can't define the QueryTwoFields method inside the button handler. You must define it outside (see code above).

Also define your connection data at the start of the programm:

namespace MyProject
{
    /// <summary>
    /// Define your connectionstring and connection
    /// </summary>
    public partial class Form1 : Form
    {  
        public static string connString = "Server=10*****;Port=3306;Database=e***;Uid=e***;password=********************;";              
        MySqlConnection conn = new MySqlConnection(connString);
                           
        // .........

Answer 2

Datatables are fantastic

Using a data table is a nice way to do both read and write. And it comes with the luxury of eveything you can do with a datatable - like asssigning it directly to a datagrid control, sorting, selecting and deleting while disconnected.

The sample below assumes a MySqlConnection conection property managed by calls to your own OpenConnection() and CloseConnection() methods not shown.

Simple datatable read demo:

    public DataTable Select(string query = "")
    {
        //Typical sql: "SELECT * FROM motorparameter"


        DataTable dt = new DataTable();

        //Open connection
        if (this.OpenConnection() == true)
        {
            //Create Command
            MySqlCommand cmd = new MySqlCommand(query, connection);
            //Create a data reader and Execute the command
            MySqlDataReader dataReader = cmd.ExecuteReader();

            dt.Load(dataReader);

            //close Data Reader
            dataReader.Close();

            //close Connection
            this.CloseConnection();

            //return data table
            return dt;
        }
        else
        {
            return dt;
        }
    }

In case of writing back the datatable to the database - supply the SQL you used in the read (or would have used to read to the data table):

    public void Save(DataTable dt, string DataTableSqlSelect)
    {
        //Typically "SELECT * FROM motorparameter"
        string query = DataTableSqlSelect;

        //Open connection
        if (this.OpenConnection() == true)
        {
            //Create Command
            MySqlCommand mySqlCmd = new MySqlCommand(query, connection);


            MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCmd);
            MySqlCommandBuilder myCB = new MySqlCommandBuilder(adapter);
            adapter.UpdateCommand = myCB.GetUpdateCommand();

            adapter.Update(dt);

            //close Connection
            this.CloseConnection();

        }
        else
        {
        }
    }

The neat thing the datatable is extremely flexible. You can run your own selects against the table once it contains data and before writing back you can set or reset what rows needs updating and by default the datatable keeps track of what rows you update in the table. Do not forget primary key column(s) for all tables in the db.

For multiple queries consider if possible using a join between the database tables or same table if data related or use a UNION sql syntax if column count and type of data is the same. You can allways "create" your extra column in the select to differ what data comes from what part of the UNION.

Also consider using CASE WHEN sql syntax to conditionally select data from different sources.