c# - MySQL ADO.Net嵌套DataReaders

我想用一个SQL语句检索一个帐户列表，然后在运行更多SQL语句时循环遍历它们。当我尝试时，出现此错误：


  未处理的异常：MySql.Data.MySqlClient.MySqlException：已存在与此Connection关联的打开的DataReader，必须首先将其关闭。


这是一个例子：

using (MySqlConnection connection = new MySqlConnection("host=..."))
{

    connection.Open();

    using (MySqlCommand cmdAccounts = connection.CreateCommand())
    {

        cmdAccounts.CommandText = "SELECT id, name FROM accounts";

        using (MySqlDataReader accounts = cmdAccounts.ExecuteReader())
        {

            while (accounts.Read())
            {

                Console.WriteLine("Account {0}:", account.GetString("name"));

                using (MySqlCommand cmdPictures = connection.CreateCommand())
                {

                    cmdPictures.CommandText = "SELECT id, width, height FROM pictures WHERE account_id = @accountId";
                    cmdPictures.Parameters.AddWithValue("@accountId", accounts.GetInt32("id"));

                    using (MySqlDataReader pictures = cmdPictures.ExecuteReader())
                    {

                        while (pictures.Read())
                        {
                            Console.WriteLine("\tPicture #{0}: {1} x {2}", pictures.GetInt32("id"), picture2.GetInt32("width"), picture2.GetInt32("height"));
                        }

                    }
                }
            }
        }
    }
}

我是否必须使用DataSet，还是仅使用DataReaders在MySQL中有办法做到这一点？

最佳答案

您可以使用DataReaders进行此操作，但是两个阅读器级别都需要一个单独的连接对象：

using (MySqlConnection connection = new MySqlConnection("host=..."))
using (MySqlCommand cmdAccounts = MySqlCommand("SELECT id, name FROM accounts" , connection))
{
    connection.Open();

    using (MySqlConnection connection2 = new MySqlConnection("host=..."))
    using (MySqlCommand cmdPictures = new MySqlCommand("SELECT id, width, height FROM pictures WHERE account_id = @accountId", connection2))
    using (MySqlDataReader accounts = cmdAccounts.ExecuteReader())
    {
        cmdPictures.Parameters.Add("@accountId", MySqlDbType.Int32);
        connection2.Open()

        while (accounts.Read())
        {
            Console.WriteLine("Account {0}:", account.getString("name"));

            cmdPictures.Parameters["@accountId"].Value = accounts.GetInt32("id");

            using (MySqlDataReader pictures = cmdPictures.ExecuteReader())
            {
                while (pictures.Read())
                {
                    Console.WriteLine("\tPicture #{0}: {1} x {2}", pictures.GetInt32("id"), picture2.GetInt32("width"), picture2.GetInt32("height"));
                }
            }
        }
    }
}

但是，即使这样更好，但从一开始就思考问题仍然是错误的方法。您确实要联接数据库中的两个表：

string sql =
    "SELECT a.id as AccountID, a.name, p.id as PictureID, p.width, p.height" +
    " FROM accounts a" +
    " INNER JOIN pictures p on p.account_id = a.id" +
    " ORDER BY a.name, a.id";

using (var cn = new MySqlConnection("host=..."))
using (var cmd = new MySqlCommand(sql, cn))
{
    cn.Open();
    using (var rdr = cmd.ExecuteReader())
    {
        bool reading = rdr.Read();
        while (reading)
        {
            int CurrentAccount = rdr.GetInt32("AccountId");
            Console.WriteLine("Account {0}:", rdr.GetString("name"));

            while (reading && CurrentAccount == rdr.GetInt32("AccountId"))
            {
               Console.WriteLine("\tPicture #{0}: {1} x {2}",
                 rdr.GetInt32("PictureId"), rdr.GetInt32("width"), rdr.GetInt32("height"));
               reading = rdr.Read();
            }
        }
    }
}