CODEWITHSUNDEEP
c#mysql
aghtczst
aghtczst

Reputation: 85

C# ,mysql ,how to select and read all data from one table?

I want to select and show( let's say in Debug console) all data from one table which is in MySQL database.

I can read data using column names or indexes, but only in one fetched row. Can somebody tell me how to loop trough rows? So I can read it's data..

Thank you for any help.

public class DatabaseHandler
    {
      private bool query_status;
        private int? count;
        private DataTable data_table;
        private static MySqlConnection connection;
        private MySqlDataReader data_reader;
        private MySqlCommand command;
        private static DatabaseHandler databasehandler;
    public void insert(string table, Dictionary<string, string> data)...

    public void select(string columns, string table, string where)
    {
        try
        {
            connection.Open();
            this.command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT * FROM user";
            this.data_reader = command.ExecuteReader();
            if (this.data_reader.HasRows) {
                this.count = data_reader.FieldCount;
                do
                {
                    this.data_reader.NextResult();
                    for (var i = 0; i < count; i++)
                    {
                        Debug.WriteLine(this.data_reader.GetValue(i));
//only returns one row. How to view all data?
                    }
                }
                while (this.data_reader.Read());           
            }
            connection.Close();
        }
        catch (Exception e)
        {
            Debug.WriteLine(e.Message);
        }
    }
    public void delete(string table, string where)...
    public void update(string table, string new_data, string where)... 
    public bool get_query_status()...
    public string get_custom_string()...
    public int? get_query_count()...
    public DataTable get_result()...

Upvotes: 0

Views: 27411

Answers (6)

Ислам Тарек
Ислам Тарек

Reputation: 11

try
{
    listView1.Items.Clear();
    MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;port=3306;uid=sql3305088;pwd=KwxUiLHnKt;database=sql3305088;");

    con.Open();
    MySqlCommand com = con.CreateCommand();

    com.CommandType = System.Data.CommandType.Text;
    com.CommandText = "SELECT * From eslamtarek";
    MySql.Data.MySqlClient.MySqlDataReader reader = com.ExecuteReader();


    if (reader.HasRows)
    {
        while (reader.Read())
        {
            ListViewItem lv = new ListViewItem();
            lv.Text = reader.GetString(0);
            lv.SubItems.Add(reader.GetString(1));
            lv.SubItems.Add(reader.GetString(2));
            lv.SubItems.Add(reader.GetString(3));
            lv.SubItems.Add(reader.GetString(4));
            listView1.Items.Add(lv);
        }
        reader.Close();
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Upvotes: 1

Ramgy Borja
Ramgy Borja

Reputation: 2458

you can also try in this way of coding

try
{

     DataTable data = new DataTable();

     connection.Open();
     MySqlCommand command = new MySqlCommand("SELECT * FROM User", connection);
     data.load(command.ExecuteReader());

     foreach(Datarow row in data.rows)
     {
        // access your record colums by using reader
        Console.WriteLine(row["COLUMN NAME"]);
     }

}
catch (Exception ex)
{
     // handle exception here
}
finally
{
     connection.Close();
}

Upvotes: 0

Alex
Alex

Reputation: 1461

You can access database records sequentially by using DataReader

try
{
    connection.Open();
    MySqlCommand command = new MySqlCommand("SELECT * FROM User", connection);
    using (MySqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // access your record colums by using reader
            Console.WriteLine(reader["COLUMN_NAME"]);
        }
    }
}
catch (Exception ex)
{
    // handle exception here
}
finally
{
    connection.Close();
}

Or you can use DataAdapter to fill local DataSet or DataTable and loop your rows from there:

try
{
    connection.Open();
    MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM User", connection);
    DataTable data = new DataTable();
    adapter.Fill(data);
    foreach (DataRow row in data.Rows)
    {
        Console.WriteLine(row["COLUMN_NAME"]);
    }
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    connection.Close();
}

Also always remember to make sure that you close all active connections to your database.

Upvotes: 2

daniell89
daniell89

Reputation: 2282

Maybe try to use MySqlDataAdapter:

 DataTable temp = new DataTable();
 adapter = new MySqlDataAdapter(command);
 adapter.Fill(temp);

 foreach(DataColumn column in  temp.Columns) 
 {
     foreach(DataRow row in temp.Rows)
     {
          Console.WriteLine(row[column]);
     }
 }

Upvotes: 1

Seth
Seth

Reputation: 481

The FieldCount() returns the number of columns in the table. I think your table has only 1 column, which is why it is not iterating through the rest of the records.

I suggest you remove the usage of the count variable and just go with the boolean returned from the Read() method.

Upvotes: 2

toby
toby

Reputation: 40

You are using NextResult, this will move to the next result set.

See Difference between SqlDataReader.Read and SqlDataReader.NextResult

In your example if you change your code to be something like 

this.data_reader = command.ExecuteReader();
        if (this.data_reader.HasRows) {
            this.count = data_reader.FieldCount;
            while (this.data_reader.Read());           
            {
                for (var i = 0; i < count; i++)
                {
                    Debug.WriteLine(this.data_reader.GetValue(i));
                }
            }
           this.data_reader.Close();
        }

Upvotes: 1

Related Questions