Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.Collections.Generic.IEnumerable'

Question

I've been trying to understand why it's giving me this error because I'm not using SqlDataReader, I get that I'm using SQL Server and the Interface is returning the specific type but still I'm telling .NET to use IDataReader instead.

Here's the code:

    public DataSet ExecuteSelectProcedure(string procedure, List<Parameter> parameters)
    {
        conexion.Open();

        try
        {
            IDbCommand command = conexion.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.Connection = conexion;
            command.CommandText = procedure;

            foreach (var parameter in parameters)
            {
                IDbDataParameter dataParameter = command.CreateParameter();
                dataParameter.Direction = (System.Data.ParameterDirection) parameter.Direction;
                dataParameter.Value = parameter.Value;
                dataParameter.ParameterName = parameter.Name;
                dataParameter.DbType = (DbType) parameter.Type;
                command.Parameters.Add(dataParameter);
            }


            IDataReader result = command.ExecuteReader();
            DataSet dataSet = new DataSet();
            foreach (var table in (IEnumerable<IDataReader>) result)
            {
                DataTable dataTable = new DataTable();
                dataTable.Load(table);
                dataSet.Tables.Add(dataTable);
            }

            result.Close();

            return dataSet;
        }
        finally
        {
            conexion.Close();
        }

The error is when casting IEnumerable on the foreach loop. Thanks in advance...

Jon Skeet · Accepted Answer · 2010-12-08 16:47:55Z

3

It is using SqlDataReader, because that's the actual type that command.ExecuteReader is returning. You're then trying to cast that to an IEnumerable<IDataReader>, but it's not clear why. Why did you expect that to work?

You iterate over multiple tables (etc) in an IDataReader by calling NextResult(). For example:

do
{
    // Read the current result; if reading manually, call Read()
    // to get to the next row within the result
} while (reader.NextResult());

answered Dec 8, 2010 at 16:47

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Victor Alejandria Over a year ago

The foreach loop was originally this do { var dataTable = new DataTable(); dataTable.Load(result); dataSet.Tables.Add(dataTable); if (result.IsClosed) break; } while (result.NextResult());

Jon Skeet Over a year ago

@Victor: Well then I suggest you go back to that :)

Victor Alejandria Over a year ago

The problem is that, for example, when the result has 3 tables the dataset is returning with table 1 and 3... Sorry for my english here, not my first language... :-D

Jon Skeet Over a year ago

@Victor: That suggests you're calling NextResult() twice instead of once.

Victor Alejandria Over a year ago

Ok, I replace NextResult() with Read() and the DataSet returns the correct set of tables... Thanks...

|

Collectives™ on Stack Overflow

Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.Collections.Generic.IEnumerable'

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related