OleDbDataReader cmd.ExecuteReader() “Enumeration Yielded No Results”

Question

I tried to get data from excel file and set it in database

My code look like this answer

            var path = GetPath(activityId);
            path = Path.Combine(path, fileName);

            var strConnection = GetConnectionString();

            var excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", path);

            using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
            {
                excelConnection.Open();
                DataTable dtSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string firstSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();

                using (OleDbCommand cmd = new OleDbCommand("Select * from [" + firstSheetName + "]" , excelConnection))
                {
                    using (OleDbDataReader dReader = cmd.ExecuteReader())
                    {
                        using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                        {
                            sqlBulk.DestinationTableName = tableName;

                            while (dReader.Read())
                            {
                                sqlBulk.WriteToServer(dReader);
                            }
                        }
                    }
                }

But when I debug sqlBulk.WriteToServer(dReader); I show this error also data did not load to the table:

Empty = "Enumeration yielded no results"

I had tried many answer but without success

Note: my uploaded excel fields and my table look the same

Answer 1

I solved this issue by change my code as below (using OleDbDataAdapter instead OleDbDataReader) :

   DataTable Contents = new DataTable();
   using (OleDbDataAdapter cmd = new OleDbDataAdapter("Select * from [" + firstSheetName + "]", excelConnection))
   {
     cmd.Fill(Contents);
     using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
     {
       sqlBulk.DestinationTableName = tableName;
       sqlBulk.WriteToServer(Contents);
      }
   }