Getting multiple MySQL Query results on Visual C# Console App

Question

I am trying to print out results from a MySQL query on a Visual C# Console application. I am able to get multiple columns on one line as you can see below, but I am wondering how I can get multiple results (rows). You see, my table holds more records that meet the query criteria. Can someone help me out?

    class Program
{
    static void Main(string[] args)
    {

        string ConnectionString = "Server=localhost; Database=world; Uid=root; Pwd=password"; // giving connection string
        MySqlConnection connection = new MySqlConnection(ConnectionString); 
        MySqlCommand cmd = connection.CreateCommand(); 
        cmd.CommandText = "SELECT name, population FROM city where population > 4000000"; 

        try 
        { 
        connection.Open(); 
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        MySqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine("City name is: " + reader["name"].ToString() + " " + reader["population"].ToString());
            Console.Read();
        }

    }

Is the Console.Read() really wanted in the while loop ? You know this blocks waiting for user input ? Is this maybe why you only see one result ? — Luc Morin
– Luc Morin, Commented Feb 18, 2014 at 3:32
You're correct, can you respond formally so I can accept your question? It's working now. Thanks. — user2917239
– user2917239, Commented Feb 18, 2014 at 3:33

Luc Morin · Accepted Answer · 2014-02-18 03:35:44Z

2

Your call to Console.Read() is blocking the while loop, so you're only printing one line to console, and then waiting for user input.

Cheers

answered Feb 18, 2014 at 3:35

Luc Morin

5,39024 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Derek W · Accepted Answer · 2014-02-18 03:51:01Z

You want to remove that Console.Read();, it is blocking your application from continuing on until it reads another character input on the Console.

Other things to consider: Using statements ensure that the unmanaged resources consumed by the MySql objects are released when the objects are no longer in use. Use Parameterized queries (aka Prepared Statements) as they performs better and are more secure.

   string sql = "SELECT name, population FROM city WHERE population > @population"
   using (var conn = new MySqlConnection(/*Connection String*/))
   { 
        conn.Open();
        using (var cmd  = new MySqlCommand(sql, conn))
        {
             cmd.Parameters.AddWithValue("@population", 4000000);
             using (var reader = cmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                      Console.WriteLine("City: {0} Population: {1}", 
                                       reader["name"], reader["population"]);
                 }
             }
        }
    }

Dan Drews · Accepted Answer · 2014-02-18 03:37:54Z

0

There may be other ways to do this, but the best way that I know of is loading the datareader into a datatable.

DataTable dt = new DataTable("City");
dt.Fill(reader);

foreach (DataRow row in dt.Rows){
  Console.WriteLine("City name is: " + row["name"].ToString() + " " + row["population"].ToString());
  Console.Read();
}

EDIT While answering, I realized that the Console.Read() may be the issue. This code will work, but an input to the console will need to be given for each line.

answered Feb 18, 2014 at 3:37

Dan Drews

1,97617 silver badges39 bronze badges

Collectives™ on Stack Overflow

Getting multiple MySQL Query results on Visual C# Console App

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related