Question 21770 in Mysql Question posted in
The official MySQL documentation can be found here.

How to run multiple data readers on single my sql connection – Mysql

MySqlConnection con = GetConnection();
            string sql = "SELECT COUNT(ID) FROM booking WHERE DATE(Booking_Date) = CURDATE()";
            string sql2 = "SELECT SUM(Fare) FROM booking WHERE DATE(Booking_Date) = CURDATE()";
            MySqlCommand cmd = new MySqlCommand(sql, con);
            cmd.CommandType = System.Data.CommandType.Text;
            MySqlDataReader BookingToday = cmd.ExecuteReader();
            while (BookingToday.Read())
            {
                label6.Text = BookingToday.GetValue(0).ToString();
            }

This is my C# code and I want to run both the given queries to get result at once. But I don’t know how to run multiple data readers on single connection or run 2 connections at once. Anyone please help me in this regard

Tags:

3

Answers


  1. 0

    You can use one query:

    string sql = "SELECT COUNT(ID), SUM(Fare) FROM booking WHERE DATE(Booking_Date) = CURDATE()";
    Login or Signup to reply.
  2. 0

    In this specific case: you don’t need to – you can use the code shown in slaakso’s answer to perform both aggregates in one query.

    In the more general case:

    using (var reader = cmd1.ExecuteReader())
{
  // while .Read(), etc
}
using (var reader = cmd2.ExecuteReader())
{
  // while .Read(), etc
}

    i.e. sequentially and not overlapping (unless your provider supports the equivalent of "MARS").

    You can also often issue multiple queries (select) in a single command; you use NextResult() to move between them:

    using (var reader = cmd.ExecuteReader())
{
  // while .Read(), etc, first grid
  if (reader.NextResult())
  {
    // while .Read(), etc, second grid
  }
}
    Login or Signup to reply.
  3. 0

    You can’t open multiple data readers simultaneously from a single connection.

    If you want a single result, you can use ExecuteScalar.

    var result1 = (DateTime)new MySqlCommand("select now()", con).ExecuteScalar();
Console.WriteLine(result1);

var result2 = (DateTime)new MySqlCommand("select date_add(now(), interval 10 day)", con).ExecuteScalar();
Console.WriteLine(result2);

    For multi-row results, you can store them in a DataTable.

    var dt1 = new DataTable();
dt1.Load(new MySqlCommand("select id from table1", con).ExecuteReader());

var dt2 = new DataTable();
dt2.Load(new MySqlCommand("select name from table1", con).ExecuteReader());

foreach(var row in dt1.AsEnumerable())
{
    Console.WriteLine($"id:{row["id"]}");
}

foreach (var row in dt2.AsEnumerable())
{
    Console.WriteLine($"name:{row["name"]}");
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search