Lists on C# problem

[Karbust 4714]

Hello

I'm doing a work for my programming class, but I'm having some troubles with mysql...

Well, this is the whole function:

        public List<string>[] SelectUsers()
        {
            //string tabela = data;
            string query = "SELECT * FROM users";

            //Create a list to store the result
            List<string>[] list = new List<string>[6];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();
            list[3] = new List<string>();
            list[4] = new List<string>();
            list[5] = new List<string>();

            //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();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id_user"] + "");
                    list[1].Add(dataReader["login"] + "");
                    list[2].Add(dataReader["password"] + "");
                    list[3].Add(dataReader["is_admin"] + "");
                    list[4].Add(dataReader["game"] + "");
                    list[5].Add(dataReader["rank_ingame"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }

The users' table structure: 

The games table structure: 

The field "jogo" on user table is a foreign key of the table games...

My ideia is to get the game name instead the ID of the game...

The query I use on navicat to get the game name: 

SELECT * FROM jogos WHERE id_jogo = (SELECT jogo FROM users WHERE id_user=1);

But, I would like to use it like this on the code:

"SELECT * FROM jogos WHERE id_jogo = (SELECT jogo FROM users WHERE id_user=" + dataReader["id_user"] + ")"

I already tried this (adding a second datareader):

				string query1 = "SELECT * FROM jogos WHERE id_jogo = (SELECT jogo FROM users WHERE id_user=1)";

                MySqlCommand cmd1 = new MySqlCommand(query1, connection);
                MySqlDataReader dataReader1 = cmd1.ExecuteReader();

                while (dataReader1.Read())
                {
                    list[4].Add(dataReader1["nome"] + "");
                }

                dataReader1.Close();

But I keep getting this error: 

System.ArgumentOutOfRangeException was unhandled
  HResult=-2146233086
  Message=Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
  ParamName=index
  Source=mscorlib
  StackTrace:
       at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
       at Projeto_24___Jogos_com_DB.Utilizadores..ctor() in C:\Users\Ragnorak\Documents\Visual Studio 2013\Projects\Projeto 24 - Jogos com DB\Projeto 24 - Jogos com DB\Utilizadores.cs:line 16
       at Projeto_24___Jogos_com_DB.Form2.button1_Click(Object sender, EventArgs e) in C:\Users\Ragnorak\Documents\Visual Studio 2013\Projects\Projeto 24 - Jogos com DB\Projeto 24 - Jogos com DB\Form2.cs:line 26
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Projeto_24___Jogos_com_DB.Program.Main() in C:\Users\Ragnorak\Documents\Visual Studio 2013\Projects\Projeto 24 - Jogos com DB\Projeto 24 - Jogos com DB\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Anyone can help me please? This is my first work on c# using database connections, and I'm a little confuse...

Thanks

Kind Regards,

charparodar

Edited August 20, 2022 by Metin2 Dev
Core X - External 2 Internal

[NoFr1ends 731]

You could use a join, so you only need 1 statement to execute:

SELECT u.*, j.name as game_name FROM users as u
INNER JOIN jogos as j on j.id_jogo = u.jogo 

 

[Karbust 4714]

Thanks, it's working!

 

I've never used JOIN on classes, I didn't knew that could be used like that...

Edited August 24, 2022 by Metin2 Dev
Core X - External 2 Internal