What are the key differences between connected and disconnected modes in ADO.NET, and in what scenarios would you choose one over the other when working with database operations?