0

When I use the MySQL Connector/NET in my C# application and I call a stored procedure with it using the ExecuteReader method and an Extention on a DataTable to fill it. Here is the code:

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                DataTable dt;
                while (!reader.IsClosed)
                {
                    dt = new DataTable();
                    Extensions.Fill(dt, reader, true);
                    ds.Tables.Add(dt);
                } if(!reader.IsClosed) reader.NextResult();
            }

And the Extention:

public static class Extensions
{
    #region Extensions
    public static void Fill(this DataTable table, IDataReader reader, bool createColumns)
    {
        if (createColumns)
        {
            table.Columns.Clear();
            var schemaTable = reader.GetSchemaTable();
            foreach (DataRowView row in schemaTable.DefaultView)
            {
                var columnName = (string)row["ColumnName"];
                var type = (Type)row["DataType"];
                table.Columns.Add(columnName, type);
            }
        }
        table.Load(reader);
    }

    #endregion

The problem with this code is, if you select twice from the same table name, you don't know which name it gets in the dataset (problably an error because the same table name is used twice. For example the stored procedure:

CREATE PROCEDURE `test`.`mytestproc` ()
BEGIN
    SELECT NOW() FROM DUAL;
    SELECT NOW() FROM DUAL;
END

The extention will problably see the same table name ('DUAL') twice. I wan't to hint in my procedure what the table name is, i tried the following:

CREATE PROCEDURE `test`.`mytestproc` ()
BEGIN
    SELECT * FROM (SELECT NOW() FROM DUAL) a;
    SELECT * FROM (SELECT NOW() FROM DUAL) b;
END

But this doesn't work. Is there a workaround for this?

Michiel van Vaardegem
  • 2,260
  • 20
  • 35
  • In your procedure table names are arbitrary. Try to use result-set count (reader.NextResult()) to name the tables in c# code; e.g.: `table1`, `table2`... – Devart Jan 29 '13 at 07:35
  • That isn't a really nice solution I think. If I have to join the user table to two other tables, so I have two result sets. I'll get user1 and user2. In my procedure I want to hint the table name, so I'm sure what name it gets. If this is possible ofcourse – Michiel van Vaardegem Jan 29 '13 at 08:22
  • What about SELECT NOW() FROM DUAL a; SELECT NOW() FROM DUAL b; – Jirilmon Jan 29 '13 at 15:24
  • @OMG With 'normal' tables, this doesn't work either. I still get the table name itself – Michiel van Vaardegem Jan 30 '13 at 07:10

0 Answers0