The idea of building a canal to connect the Mediterranean sea with the Atlantic ocean has been around since Antiquity. The problem wasn't how to dig 2 or 3-hundred kms of canal, but how to keep water in it.
The section between Toulouse and Bordeaux paralled the Garonne river (the Garonne Lateral Canal), where water would be supplied by the river in the normal manner. Between Toulouse and the Mediterranean (the Royal Languedoc Canal) had no large river, and crossed a part of France known for its low rainfall.
The solution that Pierre-Paul Riquet came up with was to capture the water from Black Mountain (la Montagne Noire) and channel it south to the high point at Naurouze. Riquet built dams and channels to collect the water from two watersheds on the Montagne Noire in the Bassin de St Férol. From there the water is channeled south to Naurouze where it compensates for the canal water lost to leakage and evaporation.
The Royal Languedoc Canal
The southeastern part of the canal, between Toulouse and Sète, was built during 1666-1681, completed a few months after the death of Riquet in 1680.
The Garonne Lateral Canal
The northwestern part of the canal, between Toulouse and Castets-en-Dorthe (near Bordeaux) was built during 1839-1856. Joining the Royal Languedoc Canal between Toulouse and Sète, the complete Canal du Midi provided safe inland transportation between the Atlantic and the Mediterranean. This northwestern section, the Garonne Lateral Canal, had been envisioned by Vauban in 1861, following the completion of the Toulouse-Sète part. But Louis XIV had emptied the royal treasury, and it wasn't until 1832 that funding and political motivation came together and began the 17-year project to dig the 211 kms of waterway.
In the beginning the barges were pulled by horses, using the tow paths along the sides of the canal. An early customer of the canal was the mail service, the malle-poste, a water-borne version of stage coaches. The trip between Toulouse and Sète took about four days, but some of the malle-poste barges had first-class salons with dining to while away the time. Around 1855, a system was used with fast horses, changed every 10 km, much like Early America's Pony Express, reducing the trip time to 32 hours. In the 1930s, motor barges were introduced, replacing the horses and introducing our modern era of canal travel.
We wouldn't say the Canal du Midi was an economic failure, but events and timing combined to prevent it being a success.
About the time the canal was completed, to possibility of war with Spain prevented the region from developing as planned. Then the railways began offering serious alternatives for transport, aggrevated by assigning control of the Canal du Midi in 1852 to the railway company (the "Compagnie des Chemins de fer du Midi et du Canal Latéral à la Garonne"). In 1858, the Compagnie des Chemins de Fer du Midi was given complete control over transportation in the Languedoc. Since it wasn't in their interest to have canal transport competing with their own railway, and the Canal du Midi was effectively sidelined for commerce.