thank you for all your postings and contribution. Yes, basically there should be a way doing this in data access layer not application layer. I got this in microsoft aspnet discussion forum.

In asp.net, there is a class called sqltransaction which has commit and rollback transaction.

So on doubt, we can do this in application other than database layer.

Betty