How to return multiple tables using MySqlDataReader?

Question

I want to return two tables using mySqlDataReader and load the results into two data types.

using (MySqlConnection connMySql = new MySqlConnection(global.g_connString)) { MySqlCommand cmd = connMySql.CreateCommand(); cmd.CommandText = @" 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 this way, or is there a better way to achieve this?

Thanks in advance,

Ben

Answer 1

Instead of using MySqlDataReader, you can return a DataSet, for example:

  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 this helps. FYI, you can alternately return the data set this way:

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

Answer 2

Do not call dr.NextResult () between two calls to Load. The reader has already moved on to the next set of results.

From MSDN: the Load method uses the first result set from the loaded IDataReader and, upon successful completion, sets the position of the reader to the next result set, if any.

Stephen

[edit]

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