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

C# data access layer (simplified example with only 1 sub in DAl.cs in this example):

Using System.Data.SqlClient;

Public Class DAL
{
    Public Static void GetQueryResults(String cmdText)
    {
        SqlConnection oConn = New SqlConnection();
        oConn.ConnectionString = MainW.MyConnection;  // get connection string
        SqlCommand cmd = New SqlCommand(cmdText, oConn);
        DataSet ds = New DataSet();
        SqlDataAdapter da = New SqlDataAdapter(cmd);

        Try
        {
            oConn.Open();
            da.Fill(ds);       // retrive data
            oConn.Close();
        }
        Catch (Exception ex)
        {
            SysErrScreen errform = New SysErrScreen();
            errform.ChybaText.Text = ex.Message + Constants.vbCrLf + Constants.vbCrLf + cmdText;
            errform.ShowDialog();
            oConn.Close();
        }

        Return ds;
    }
}

Same in VB.NET:

Imports System.Data.SqlClient

Public Class DAL

Public Shared Function GetQueryResults(cmdText As String)
    Dim oConn As New SqlConnection
    oConn.ConnectionString = MainW.MyConnection  ' get connection string
    Dim cmd As New SqlCommand(cmdText, oConn)
    Dim ds As New DataSet()
    Dim da As New SqlDataAdapter(cmd)

    Try
        oConn.Open()
        da.Fill(ds)       ' retrive data
        oConn.Close()
    Catch ex As Exception
        Dim errform As New SysErrScreen
        errform.ChybaText.Text = ex.Message & vbCrLf & vbCrLf & cmdText
        errform.ShowDialog()
        oConn.Close()
    End Try

    Return ds
End Function

End Class

Note, that I defined a ConnectionString elsewhere (MainW.MyConnection), where you can set it for all application. You'd usually retrieve it during start-up from some settings (file, application variable).

Then I'ts easy to use it over and over (C#):

Private void FillDGV()
{
    String cmdText = "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\"; ";
        DataSet ds;
        ds = DAL.GetQueryResults(cmdText);
        DataTable dt;
        if (ds.Tables.Count > 0)
        {
            dt = ds.Tables(0);
            this.DataGridView1.DataSource = dt;   // fill DataGridView
        }
    }

VB.NET:

Private Sub FillDGV()
    Dim cmdText As String = "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""; "
    Dim ds As DataSet
    ds = DAL.GetQueryResults(cmdText)
    Dim dt As DataTable
    If ds.Tables.Count > 0 Then
        dt = ds.Tables(0)
        Me.DataGridView1.DataSource = dt    ' fill DataGridView
    End If
End Sub

Note, that I used DataTable as a data object, between DataSet and DataGridView. It's good to get in habit to use it (unless other more advanced ways are used) due to number of reasons. One major is, that it won't earase your DataGridView GUI-defined columns, if the DataSet table happens to be empty. You can also perform client-side data operations way more productively and reliably on DataTable, then on DataGridView.

One can also consider, if he should use BindingSource, rather then a DataTable. It's implementation works very similarly to DataTable, so if you get this example working, you can then switch to BindingSource, if you need it.

Another consideration is to use parametric queries. If you (your users) operate your desktop application in closed environment, then it's OK. However with exposed applications you can be sure you'll get some SQL injection attacks.

Answer 3

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

Answer 4

This code works for me when downloading data from SQL database and presening it in a DataGridView:

            string connectionString;
            String sql = "";
            SqlConnection cnn;

            sql = "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"";
            connectionString = @"datasource=localhost;port=3306;username=root;password=";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlDataAdapter dataadapter = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dataadapter.Fill(ds, "workers");
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "workers";
            cnn.Close();