How to load data from sql query to datagridview?

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;

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

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.SqlClientPublic Class DALPublic 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 dsEnd FunctionEnd 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 IfEnd 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.