Is it wrong to create an explicit transaction (Begin Trans/Commit Trans) in .NET?
No, not at all. This is pessimistic concurrency and it just won't scale as well as optimistic concurrency. However, it might be what your .NET application requires.
What type of applications should I be using optimistic concurrency with?
Using an optimistic concurrency approach is highly successful when the application's data has minimal contention situations (when the application's users rarely work on the same data at the same time) and when there is a high volume of users ”very optimal for Web applications.