ADO.NET supplies you with a number of powerful objects including DataSet objects to provide a means of accessing and presenting data, but these objects fall short in situations where you need to provide a clear contract between the data and your object system. They also fall short in situations when you require complex validation, portability between multiple persistence mediums, or abstraction of data operation.
So, when does a programming problem require a clear contract to be defined between the data and the code? A good example is a situation where the data provides a means of recording history that then requires business logic to be applied to it before presenting it to the user or making a decision. For example, an order entry system requires that logic be applied to a user's entries before being stored into a database to determine if the user can purchase the selected items. This is a prime candidate for object-relational mapping.
Another situation when OR mapping can really help is when you have the need to enforce complicated data validation. Databases support enforcing relations and null vs. non-null values, but they do not (in general) have good support for complex validation. For example, if you need to make sure the user has provided at least one method of contact (such as a phone or email address), you can easily enforce this with a mapped object.
Many packaged applications need to be able to store the application data in multiple database systems and even be able to support situations where the user purchasing the software has no database at all (such as a flat text file). By keeping your application logic and data model in your objects, support for each persistence medium is as simple as adding handling in the data access components. If you use direct SQL or stored procedure calls throughout your code, you'll find portability a major problem.
The classic view of OR mapping involves abstracting a complex data model. A classic example is if you have the need to track employees, salespeople, and accountants. An employee is clearly the "base class" for the other types of employees. For example, all employees have name information, but only sales people have a sales territory code, and only accountants have a CPA certification flag. You would put the common parts in the Employee object (and related table in the database) and the task-specific information in a related object (and related table in the database).
In all of the previous cases, you'll find OR mapping beneficial in creating an easily manageable system.