c# using mysql plus return data

Vergel Aranas

Hi I have a problem in retrieving data from mysql and return data.

This the scenario for details.

I have a class called database then under that class i have function class public string listh() so that i can return a data to anywhere that will instantiate and call this method. I want to make this function dynamic. That's why i separate this. the query is good no errror but the problem is how to return and foreach data so that i can put it into datagridview . My code is this.

public class database {

public string list() {

confirm = db_conn.Cs;

try

{

conn = new MySqlConnection(confirm);

conn.Open();

string query = "SELECT * FROM palma_user";

command = new MySqlCommand(query, conn);

dr = command.ExecuteReader();

return dr.ToString();

}

catch (MySqlException ex)

{

return ex.ToString();

}

finally {

if(conn != null){

conn.Close();

}

so if is instantiated something like this.

Database db = new Database();

db.list();

with that, i want to loop something like this.

while (dr.Read())

{

dr.GetString(1));

}

that code is good only if the the query is not separated from the Form or let us direct loop and put into grid.

In my case i separate the code from the class and return the value.

Please help me. Thanks.

Regards,

Vergel

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vergel Aranas11y ago
Hi, i got it. It should not be close the connection in finally { conn.Close() } just let the connection still open. Yeh.. :D

Thank you Sir for your reply and answer. It really help me. Thanks.

Regards,
Vergel
Vergel Aranas11y ago
Hi Sir,

I got the error says:
Invalid attempt to Read when reader is closed.
if(dr != null){
while(dr.Read()){ //in this line the error occur.

}
dr.Close();
dr = null
}

Regards,
Vergel
Vulpes11y ago
One way to do it would be to change the method so that it returns a MySqlDataReader rather than a string:

public class database {

public MySqlDataReader list() {
confirm = db_conn.Cs;
try
{
conn = new MySqlConnection(confirm);
conn.Open();
string query = "SELECT * FROM palma_user";
command = new MySqlCommand(query, conn);
dr = command.ExecuteReader();
return dr;

}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
return null;
}
finally
{
if(conn != null){
conn.Close();
}
}
}
}

You can then call it with:

Database db = new Database();
MySqlDataReader dr = db.list();

if(dr != null)
{
while (dr.Read())
{
string s = dr.GetString(1);
// code to insert in grid
}

dr.Close();
dr = null;
}