Bug #22016 Cannot get SchemaTable from MySqlDataReader

• Submitted: 5 Sep 2006 12:26
• Modified: 12 Sep 2006 6:38
• Reporter: Igor Kondrasovas
• Status: Not a Bug
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 1.0.7
• OS: Windows (Windows2003 Standard)
• CPU Architecture: Any
• Tags: GetSchemaTable, MySqlDataReader

[5 Sep 2006 12:26] Igor Kondrasovas

Description:
I'm making a simple query against a MySQL Database using the .NET Connector. The problem I'm experiencing is that I cannot get the table schema from a MySQLDataReader. 
The Columns and Table names on the DataTable object does not match the MySQL server database and columns after calling MySQLDataReader.GetSchemaTable(). 
As you can see in the following sample code, the colum name is always equals "ColumnName".

How to repeat:
Use the following code to reproduce the problem:

string sqlCommand = String.Format("SELECT * FROM CONFIG");
System.Data.DataTable table = null;
                
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
    MySqlCommand cmd = new MySqlCommand(sqlCommand, conn);
    conn.Open();
    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
         table = reader.GetSchemaTable();

         string s = table.Columns[0].ColumnName;

         while (reader.Read())
         {
              table.Rows.Add(reader);
         }
    }
}

[8 Sep 2006 14:17] Tonci Grgin

Hi Igor and thanks for your problem report.
Verified as described by reporter on:
  MySQL 5.0.24 BK
  Win XP Pro SP2, fully updated
  connector/NET 1.0.7 svn, updated today
Reader has correct table info, System.Data.DataTable does not.

[11 Sep 2006 12:01] Tonci Grgin

Igor, I might have been a bit quick here...
I can't find any suggestion in MS docs that your code should work, ie. reading DataTable from custom reader. c/NET (ver. 1 and ver. 5) correctly fills values into DataTable. You can test by yourself adding following code to your test case:
...
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                table = reader.GetSchemaTable();
                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        Console.WriteLine(row[column]);
                    }
                }
...
or even with:
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                table = reader.GetSchemaTable();
                System.Data.DataTableReader dtr = table.CreateDataReader();
                dtr.Read();
                MessageBox.Show(dtr.GetValue(0).ToString());
            }
I will consult more but it seems not to be a bug.

[12 Sep 2006 6:38] Tonci Grgin

Hi Igor. MySqlDataReader.GetSchemaTable will return a data table holding the schema, not looking like the schema. If you want to create a datatable that has the same schema as one on the server, then you need to look at the MySqlDataAdapter.FillSchema methods.

Quotes from manual:
 MySqlDataAdapter
Represents a set of data commands and a database connection that are used to fill a dataset and update a MySQL database. This class cannot be inherited. The MySQLDataAdapter, serves as a bridge between a System.Data.DataSet and MySQL for
retrieving and saving data. The MySQLDataAdapter provides this bridge by mapping DbDataAdapter.
Fill, which changes the data in the DataSet to match the data in the data source, and Db-DataAdapter.Update, which changes the data in the data source to match the data in the DataSet, using the appropriate SQL statements against the data source.
When the MySQLDataAdapter fills a DataSet, it will create the necessary tables and columns for the returned data if they do not already exist. However, primary key information will not be included in the implicitly created schema unless the System.Data.MissingSchemaAction property is set to System.Data.MissingSchemaAction.AddWithKey. You may also have the MySQLDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using System.Data.Common.DbDataAdapter.FillSchema.

  FillSchema(inherited from DbDataAdapter) Overloaded. Configures the schema of the specified DataTable based on the specified Schema-Type.