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=");
}
}
4
Answers
This code works for me when downloading data from SQL database and presening it in a DataGridView:
This might help
C# data access layer (simplified example with only 1 sub in DAl.cs in this example):
Same in VB.NET:
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#):
VB.NET:
Note, that I used
DataTable
as a data object, betweenDataSet
andDataGridView
. 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 yourDataGridView
GUI-defined columns, if theDataSet
table happens to be empty. You can also perform client-side data operations way more productively and reliably onDataTable
, then onDataGridView
.One can also consider, if he should use
BindingSource
, rather then aDataTable
. It’s implementation works very similarly to DataTable, so if you get this example working, you can then switch toBindingSource
, 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.
There are a few problems in the code:
fname
andlname
as fields of form.calculate_sickness_leaves
You set value of those fields inwhile(reader1.Read())
sick_leaves
object fromcalculate_sickness_leaves
.So basically,
fname
andlname
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:
fname
andlname
as you don’t need them.calculate_sickness_leaves
toIEnumerable<sick_leaves>
.sick_leaves
yield return it.Side-note
using
statement when working with disposable objects like connection.Example
You can find a lot of examples about loading data into
DataTable
or usingDataReader
. 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:Example 1 – Using DataAdapter, DataTable and Select extension method
Example 2 – Using DataReader and yield return new Employee
You can use either of above method like this: