How to load data from sql query to datagridview?

Question

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:

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

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

EDIT

I'd like to load that data with saving the code structure because i wanna to build that datagridview with calculating sickness, leaves times. (with TimeSpan object). Is that possible to write like that?

My codes:

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

Answer 1

There are a few problems in the code:

1. You have defined fname and lname as fields of form.
2. In calculate_sickness_leaves You set value of those fields in while(reader1.Read())
3. At the end return a single sick_leaves object from calculate_sickness_leaves.

So basically, fname and lname will always contain first name and last name of the last row of your table, because of 1 and 2.

Your DataGridView will always show a single record, because of 3.

To solve the problem:

1. Remove fname and lname as you don't need them.
2. Change output type of calculate_sickness_leaves to IEnumerable<sick_leaves>.
3. In the while loop, when reading field values from data reader, create a new instance of sick_leaves yield return it.

Side-note

• Always use parametrized queries to prevent a SQL Injection.
• Always use using statement when working with disposable objects like connection.
• If you are interested to work with typed entity objects, then you may want to take a look at MySQL Connector for Entity Framework.

Example

You can find a lot of examples about loading data into DataTable or using DataReader. Anyway I'll share two more examples here, showing you how you can get data from MySql and convert to a a list of a specific type.

In the following examples, I assume you have an Employee class like this:

public class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Example 1 - Using DataAdapter, DataTable and Select extension method

public IEnumerable<Employee> GetEmployees()
{
    string connectionString = "CONNECTION STRING";
    string commandText = "COMMAND TEXT";
    DataTable table = new DataTable();
    using (var adapter = new MySqlDataAdapter(commandText , connectionString))
        adapter.Fill(table);
    return table.AsEnumerable().Select(x => new Employee()
    {
        FirstName = x.Field<string>("FirstName"),
        LastName = x.Field<string>("LastName")
    });
}

Example 2 - Using DataReader and yield return new Employee

public IEnumerable<Employee> GetEmployees()
{
    string connectionString = "CONNECTION STRING";
    string commandText = "COMMAND TEXT";
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new MySqlCommand(commandText, connection))
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return new Employee()
                    {
                        FirstName = reader.GetFieldValue<string>(0),
                        LastName = reader.GetFieldValue<string>(1)
                    };
                }
            }
        }
    }
}

You can use either of above method like this:

bindingSource.DataSource = GetEmployees();
dataGridView.DataSource = bindingSource;

Answer 2

This might help

private void GetData(string selectCommand)
    {
        try
        {
            // Specify a connection string.  
            // Replace <SQL Server> with the SQL Server for your Northwind sample database.
            // Replace "Integrated Security=True" with user login information if necessary.
            String connectionString =
                "Data Source=<SQL Server>;Initial Catalog=Northwind;" +
                "Integrated Security=True";

            // Create a new data adapter based on the specified query.
            dataAdapter = new SqlDataAdapter(selectCommand, connectionString);

            // Create a command builder to generate SQL update, insert, and
            // delete commands based on selectCommand. 
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable
            {
                Locale = CultureInfo.InvariantCulture
            };
            dataAdapter.Fill(table);
            bindingSource1.DataSource = table;

            // Resize the DataGridView columns to fit the newly loaded content.
            dataGridView1.AutoResizeColumns(
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
        }
        catch (SqlException)
        {
            MessageBox.Show("To run this example, replace the value of the " +
                "connectionString variable with a connection string that is " +
                "valid for your system.");
        }
    }