The absolute simplest way is to have the ADSL modem running at all times, and then unplug the cable modem from the Wifi router when the connection goes down, and connect the ADSL modem in its place.
This is silly though. Requires manual intervention etc.
You could have a device that loadbalances your connections, The Netgear FVX 538 can loadbalance across two WAN links, but I don't personally recommend it.
You could do some very clever things with a Cisco router to manage both connections instead. You do however have the problem that your external IP changes when you switch connections. With a small network setup, there's not a lot you can do about this, but with the correct providers and cooperation, it is sometimes possible to have seamless failover and not change IP when changing provider.
You could probably do everything quite cheaply using a computer as a firewall/router device with a custom linux distribution like pfSense or similar.