Click here to Skip to main content
15,063,085 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I've declared 2 string variables:

string fname;
string lname;


When i wrote MySQL query in phpMyAdmin database:

SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN
project1.order_status ON workers.ID_WORKER = order_status.ID_WORKER 
INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER 
WHERE orders.ORDER_NUMBER = 'TEST' GROUP BY workers.FNAME, workers.LNAME


I've got 2 wokers:

-"Adam Gax" and
-"Andrew Worm"

Then i'd like to store object from this query and to load that data to datagridview:


SQLdata.connection.Open();
   using (var command = new MySqlCommand(query1, SQLdata.connection))
   {
       using (var reader1 = command.ExecuteReader())
       {
           while (reader1.Read())
           {
               fname = Convert.ToString(reader1[0]);
               lname = Convert.ToString(reader1[1]);
           }
       }
   }


I've taken the breakpoints in lines of code in while loop and reads all FNAME's and LNAME's. Then it loads all data correctly. Next I want to load them to datagridview.

SQLdata.connection.Close();
       sick_leaves x = new sick_leaves();
       x.FNAME = fname;
       x.LNAME = lname;
       return x;


and bind them like this:

sick_leaves a = calculate_sickness_leaves(txt_NrOrder.Text);

     cu = calculate_sickness_leaves(txt_NrOrder.Text);
     var source = new BindingSource();
     source.DataSource = cu;
     dataGridView2.DataSource = source;


then using data from Orders.cs file:

public class sick_leaves
{
    public string FNAME { get; set; }
    public string LNAME { get; set; }
}


After compiling it in datagridview i have loaded only 1 Worker: "Andrew Worm". That should be that 2 workers, so it didn't load all data from sql query.

Now: How can I load all data from sql query to datagridview? Any ideas?
Warning! I need help in Desktop Application

What I have tried:

GenerateOrder.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Windows.Forms.DataVisualization.Charting;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Diagnostics;

namespace ControlDataBase
{
    public partial class GenerateChartsOfOrders : Form
    {
        string fname;
        string lname;
        sick_leaves cu = new sick_leaves();
        public GenerateChartsOfOrders()
        {
            InitializeComponent();
        }        

        public void loaddata2()
        {
            string connect = "datasource=localhost;port=3306;username=root;password=";

            MySqlConnection connection = new MySqlConnection(connect);
            connection.Open();

            sick_leaves a = calculate_sickness_leaves(txt_NrOrder.Text);

            cu = calculate_sickness_leaves(txt_NrOrder.Text);
            var source = new BindingSource();
            source.DataSource = cu;
            dataGridView2.DataSource = source;

            connection.Close();
        }

        private sick_leaves calculate_sickness_leaves(string NrOrder)
        {
            string query1 = string.Format("SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN project1.order_status " +
            "ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER " +
            "WHERE orders.ORDER_NUMBER = '"+ NrOrder +"' GROUP BY workers.FNAME, workers.LNAME");

            SQLdata.connection.Open();
            using (var command = new MySqlCommand(query1, SQLdata.connection))
            {
                using (var reader1 = command.ExecuteReader())
                {
                    while (reader1.Read())
                    {
                        fname = Convert.ToString(reader1[0]);
                        lname = Convert.ToString(reader1[1]);
                    }
                }
            }

            SQLdata.connection.Close();
            sick_leaves x = new sick_leaves();
            x.FNAME = fname;
            x.LNAME = lname;
            return x;         
        }
    }
}


Orders.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

    namespace ControlDataBase
    {
        public class sick_leaves
        {
            public string FNAME { get; set; }
            public string LNAME { get; set; }
        }
    }


SQLData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql;
using MySql.Data.MySqlClient;

namespace ControlDataBase
{
    class SQLdata
    {
        public static MySqlConnection connection = new MySqlConnection
        ("datasource=localhost;port=3306;username=root;password=");
    }
}
Posted
Updated 22-Jul-19 18:07pm

When you browse datareader's results, you are assigning fname and lname variables but do not do anything with them. Their values are replaced at next iteration. You end up with these variables holding the values of the last record only.

In order to keep all results, you need to store their values in a list:
C#
var names = new List<Tuple<string, string>>();
SQLdata.connection.Open();
using (var command = new MySqlCommand(query1, SQLdata.connection))
{
   using (var reader1 = command.ExecuteReader())
   {
      while (reader1.Read())
      {
         names.Add(Tuple.create((string)reader1[0], (string)reader1[1]));
      }
   }
}

You can then iterate the list to get all fetched results:
C#
foreach (var name in names)
{
   fname = name.Item1;
   lname = name.Item2;
   // ...
}
   
Look at what you are doing:
C#
SQLdata.connection.Open();
   using (var command = new MySqlCommand(query1, SQLdata.connection))
   {
       using (var reader1 = command.ExecuteReader())
       {
           while (reader1.Read())
           {
               fname = Convert.ToString(reader1[0]);
               lname = Convert.ToString(reader1[1]);
           }
       }
   }
...
       SQLdata.connection.Close();
       sick_leaves x = new sick_leaves();
       x.FNAME = fname;
       x.LNAME = lname;
       return x;
You go through them all in the loop, but you only "keep" the last one - each time round the loop overwrites the previous data.
What you would need to do is assemble a collection of sick_leaves objects - a List<sick_leaves> perhaps - add new instances to that inside your loop, and return the collection instead of a single item.

And do yourself a favour: follow C# naming conventions.
Your class shouldn't be "sick_leaves" it should be "SickLeave" - the plural implies a collection of items, which your class isn't; C# uses CamelCase not unscore and all lowercase.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900