C# visual studio and multiple mySQL query's

Question

Just started coding C# and I'm an absolute beginner at it. That being said, I have a question about using MySqlConnector and mysql query's.

I currently have a query and with it I fill a listbox with the results. But what I like to do is add more query's to the reader and put the result of those other query in a combobox. So how do I do this? I searched google and couldn't find an answer.

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace Badminton_stand
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        private void timer1_Tick(object sender, EventArgs e)
        {
            labelCurrentTime.Text = DateTime.Now.ToString();
        }



        private void Form1_Load(object sender, EventArgs e)
        /*  {
              string MyConString = "Server=localhost;Port=3307;Database=database1;UID=root;Password=toor";
              MySqlConnection connection = new MySqlConnection(MyConString);
              MySqlCommand command = connection.CreateCommand();
              MySqlDataReader Reader;
              command.CommandText = @"select Club from teams;
                  select team from teams";
              connection.Open();
              Reader = command.ExecuteReader();
              while (Reader.Read())
              {
                  string thisrow = "";
                  for (int i = 0; i < Reader.FieldCount; i++)
                      thisrow += Reader.GetValue(i).ToString() + " ";
                  clubSelectorBox1.Items.Add(thisrow);
              }
              while (Reader.NextResult()) ;
              {
                  string thisrow = "";
                  for (int i = 0; i < Reader.FieldCount; i++)
                      thisrow += Reader.GetValue(i).ToString() + " ";
                  cbTeam1.Items.Add(thisrow); 
              }
              connection.Close();
          } */
        {
            string cmdText = @"SELECT Club from teams;
                   SELECT team from teams";
            using (MySqlConnection cnn = new MySqlConnection("Server=localhost;Port=3307;Database=badminton;UID=root;Password=usbw"))
            using (MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
            {
                cnn.Open();
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    do
                    {
                        while (reader.Read())
                        {
                            string thisrow = "";
                            for (int i = 0; i < reader.FieldCount; i++)
                                thisrow += reader.GetValue(i).ToString() + " ";
                            clubSelectorBox1.Items.Add(thisrow);
                        }

                    }
                    while (reader.NextResult());
                    {

                    }
                }
            }

        }
    }
}

Thanks in advance!

Steve · Accepted Answer · 2015-12-03 10:29:59Z

3

You could put multiple SELECT statement in your query text and loop over the result using the NextResult method of the MySqlDataReader.

This is an example how to do it, of course you should adapt to your data and controls

string cmdText = @"SELECT * from TABLE_A;
                   SELECT * from TABLE_B";
using(MySqlConnection cnn = new MySqlConnection(......))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cnn))
{
    int curTable = 0;
    cnn.Open();
    using(MySqlDataReader reader = cmd.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                if(curTable == 0)
                    Console.WriteLine("Update first list based on the first table");
                else if(curTable == 1)
                    Console.WriteLine("Update second list based on second table");
            }
            Console.WriteLine("Go to next result");
            curTable++;
        }
        while(reader.NextResult());
    }
}

edited Dec 3, 2015 at 10:29

answered Dec 1, 2015 at 20:59

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dennis VW Over a year ago

This is what I was looking for! Thank you Steve

Dennis VW Over a year ago

Trying to get this to work with what I would like, but it isn't working. When I loop over the reader.Read, it also takes the result from the second query string and places it in my listBox. Could you help me out? See my first post for updated code.

Steve Over a year ago

A little busy now, you should keep a counter of the current loop and update the correct list

celerno · Accepted Answer · 2015-12-01 21:01:21Z

Use a function.

 private void Form1_Load(object sender, EventArgs e)
        {
            LoadCombo(clubSelectorbox1, "select club from teams");
            //now just change the control and the query as needed
            LoadCombo(clubSelectorbox2, "select club from teams");
        }

 private function LoadCombo(DropDownControl myCombo, string query){
string MyConString = "Server=localhost;Port=3307;Database=database1;UID=root;Password=toor";
            MySqlConnection connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = query; // use query here
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                string thisrow = "";
                for (int i = 0; i < Reader.FieldCount; i++)
                    thisrow += Reader.GetValue(i).ToString() + " ";

                myCombo.Items.Add(thisrow); //use myControl here
            }
            connection.Close();
       }

Ariel Batista · Accepted Answer · 2015-12-01 20:59:42Z

0

initialize a new MySqlCommand for each aditional query.

MySqlCommand command2 = connection.CreateCommand();
command2.CommandText = ""; // here the new query

answered Dec 1, 2015 at 20:59

Ariel Batista

1541 silver badge5 bronze badges

Collectives™ on Stack Overflow

C# visual studio and multiple mySQL query's

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related