C# – Unable to cast object of type ‘System.Data.SqlClient.SqlDataReader’ to type ‘System.Collections.Generic.IEnumerable’

ccastingidatareaderienumerablesqldatareader

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…

Best Answer

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());
Related Topic