How do I return multiple tables using MySqlDataReader?

I want to return two tables using a mySqlDataReader and load the results into two datatables.

 using (MySqlConnection connMySql = new MySqlConnection(global.g_connString))
            {
               MySqlCommand cmd = connMySql.CreateCommand();
                cmd.CommandText = @"
                            开发者_C百科        SELECT * FROM table1;
                                    SELECT * FROM table2;
                                    "; 
                connMySql.Open();
                using (MySqlDataReader dr = cmd.ExecuteReader())
                {
                        DataTable dt1 = new DataTable();
                        dt1.Load(dr);
                        dr.NextResult();
                        DataTable dt2 = new DataTable();
                        dt2.Load(dr);

                        gridView1.DataSource = dt1;
                        gridView1.DataBind();
                        gridView2.DataSource = dt2;
                        gridView2.DataBind();
                 }

However, when I run this, only one gridView is populated. Can I use NextResult in this way, or is there a better way to acheive this?

Thanks in advance,

Ben

---

Don't call dr.NextResult() between the two Load calls. The reader is already advanced to the next resultset.

From MSDN : The Load method consumes the first result set from the loaded IDataReader, and after successful completion, sets the reader's position to the next result set, if any.

Steven

[edit]

Checking the result of dr.NextResult() will also make it easier to detect whats exactly happening in your code.

---

Rather than using MySqlDataReader, you could return a DataSet like:

            connMySql.Open();

            MySqlCommand cmd = new MySqlCommand(SQL, connMySql);
            DataSet ds = new DataSet();
            MySqlDataAdapter objDataAdapter = new MySqlDataAdapter(cmd);
            objDataAdapter.Fill(ds, "reading");
            connMySql.Close();

            // Each SQL statement result set 
            // will be in a DataTable in the DataSet
            gridView1.DataSource = ds.Tables[0];
            gridView1.DataBind();
            gridView2.DataSource = ds.Tables[1];
            gridView2.DataBind();

Hope that helps. FYI, you can alternately return a dataset this way:

DataSet ds = MySql.Data.MySqlClient.MySqlHelper.ExecuteDataset(oConn, SQL);