I’m trying to get the data from a database that I created in MYSQL. I believe the DML in my code is correct. I saw on another thread that I need to use arrays so I can get all the rows from my table, but I’m struggling on how to include them in my code:
string cs = "server...";
MySqlConnection connection = new MySqlConnection(cs);
try
{
Console.WriteLine("Connection sur MySQL...");
connection.Open();
string Insertion ="insert into Passager(...)values(...)";
string Insert2 = "insert into Reservation(...)values(...)";
string req1 = "SELECT CodePassager,";
string req2 = "SELECT CodePassager,...";
string req3 = "SELECT CodePassager,...";
string joint = "SELECT Reservation...";
MySqlCommand NQuery = new MySqlCommand(Insertion, connection);
MySqlCommand NQuery2 = new MySqlCommand(Insert2, connection);
MySqlCommand requete1 = new MySqlCommand(req1, connection);
MySqlCommand requete2 = new MySqlCommand(req2, connection);
MySqlCommand requete3 = new MySqlCommand(req3, connection);
MySqlCommand reqjoint = new MySqlCommand(joint, connection);
NQuery.ExecuteNonQuery();
NQuery2.ExecuteNonQuery();
//1st request
using (MySqlDataReader reader1 = requete1.ExecuteReader())
{
reader1.Read();
int CodePassager;
string Nom, Prenom;
CodePassager = (int)reader1[0];
Nom = (string)reader1[1];
Prenom = (string)reader1[2];
Console.WriteLine("code: {0},nom:{1},prenom:{2}", CodePassager, Nom, Prenom);
reader1.Close();
}
//2nd request
using (MySqlDataReader reader2 = requete2.ExecuteReader())
{
reader2.Read();
int CodePassager;
string Nom, Prenom, Adresse;
CodePassager = (int)reader2[0];
Nom = (string)reader2[1];
Prenom = (string)reader2[2];
Adresse = (string)reader2[3];
Console.WriteLine("code:{0},...);
reader2.Close();
}
//3rd request
using (MySqlDataReader reader3 = requete3.ExecuteReader())
{
reader3.Read();
int CodePassager;
string Nom, Prenom, Adresse;
CodePassager = (int)reader3[0];
Nom = (string)reader3[1];
Prenom = (string)reader3[2];
Adresse = (string)reader3[3];
Console.WriteLine("code: {0}...);
Console.WriteLine("---------------------------------------------------");
Console.WriteLine("Reservation(s) pour ce client:");
Console.WriteLine("---------------------------------------------------");
reader3.Close();
//request to show every reservation per passenger
using (MySqlDataReader reader4 = reqjoint.ExecuteReader())
{
reader4.Read();
int CodeReservation;
string StatutReservation, DateReservation;
CodeReservation = (int)reader4[0];
StatutReservation = (string)reader4[1];
DateReservation = (string)reader4[2];
Console.WriteLine("code reservation:{0}...);
reader4.Close();
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
connection.Close();
}
With my current code, I get only one result for each request, but I need all the data.
(Sorry, English is not my first language.)
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
As noted above, the answer is in the Examples section of MySqlDataReader but the code is not very well written. A better version should make it clear how to read multiple values from a select query:
public void ReadMyData(string myConnString) {
string mySelectQuery = "SELECT OrderID, CustomerID FROM Orders";
using(MySqlConnection myConnection = new MySqlConnection(myConnString))
using(MySqlCommand myCommand = new MySqlCommand(mySelectQuery,myConnection))
{
myConnection.Open();
using(MySqlDataReader myReader = myCommand.ExecuteReader())
{
// Always call Read before accessing data.
while (myReader.Read()) {
Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
}
// always call Close when done reading.
myReader.Close();
}
// Close the connection when done with it.
myConnection.Close();
}
}Many of the types are disposable so they can be used with a using section. Also, the MySqlConnection.Close method indicates it is the preferred way to close a connection so it should be manually closed rather than depend on the disposal. I have assumed the same for MySqlDataReader.Close though the documentation does not say anything.
Method 2
thank you to everyone who answered, i found my error, i hadn’t fully understood the while() until you pointed it out and i got it fixed except for the last while which require me to have a while loop in an open reader ( 3rd and 4th request) and i only found this and this and i’m not sure if i’m heading the right way.
this is how i fixed my answer:
//1st request
using (MySqlDataReader reader1 = requete1.ExecuteReader())
{
while (reader1.Read())
{
int CodePassager;
string Nom, Prenom;
CodePassager = (int)reader1[0];
Nom = (string)reader1[1];
Prenom = (string)reader1[2];
Console.WriteLine("code: {0}...");
}
reader1.Close();
}
//2nd request
using (MySqlDataReader reader2 = requete2.ExecuteReader())
{
while (reader2.Read())
{
int CodePassager;
string Nom, Prenom, Adresse;
CodePassager = (int)reader2[0];
Nom = (string)reader2[1];
Prenom = (string)reader2[2];
Adresse = (string)reader2[3];
Console.WriteLine("code: {0}...");
}
reader2.Close();
}
//3rd request
using (MySqlDataReader reader3 = requete3.ExecuteReader())
{
while (reader3.Read())
{
int CodePassager;
string Nom, Prenom, Adresse;
CodePassager = (int)reader3[0];
Nom = (string)reader3[1];
Prenom = (string)reader3[2];
Adresse = (string)reader3[3];
Console.WriteLine("code: {0}...");
reader3.Close();
using (MySqlDataReader reader4 = reqjoint.ExecuteReader())
{
while (reader4.Read())
{
int CodeReservation;
string StatutReservation, DateReservation;
CodeReservation = (int)reader4[0];
StatutReservation = (string)reader4[1];
DateReservation = (string)reader4[2];
Console.WriteLine("code...");
}
reader4.Close();
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
connection.Close();
}
}}
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0