Hashing passwords provides defense against your passwords being compromised when a database has been compromised. It does this in two ways, 1. it hides the users passwords by making it computationally impossible to get the password from the hash, and 2. It can slow down the generation of rainbow tables for lookup against known passwords.
Hash functions are one way functions, meaning that if you know the original data you can produce the hash but having the hash won't give you the original string (in theory anyway). The easiest way think of this is to think of the mod function in programming (%), If I were to ask you what values give you n % 6 = 5 there are lots of answers, 5%6 = 5, 11%6 = 5, 16%6 = 5 and so on... but you can't figure out which n I'm using just by knowing that n%6 = 5. Cryptographic hash functions provide this sort of data hiding, except that it's extremely difficult to find collisions (two values that produce the same hash). For example SHA2 provides 128 bits of entropy, meaning that it should take 2^128 tries to find a collision (although SHA2 is a 256 bit algorithm it like all hash functions is susceptible to the birthday paradox).
Because cryptographic hash functions have this property of data hiding, when someone looks at the passwords in your database they typically wouldn't be able to tell what those passwords are just by looking at them
Rainbow Tables
Rainbow tables are tables of pre-hashed passwords. Because most people use easy to remember passwords with low entropy a bad guy could just have a table of known passwords and check your hashed value against what is in the table. To mitigate against that you use a salt, which is just a random string that you concatenate to the password. It's suggested to use a different random salt for each users password. The salt should be generated by passing a random value (for C# you can use RNGCryptoServiceProvider ) into a pseudo random number generator (such as a Hash function) any time a password is created or changes. Lots of programmers think that salt has to be a string of characters, it does not, you can use bytes that are Base64 encoded in your db and just decode them before you do your check. This would change the original string enough to make it so the hash doesn't match what would be in a rainbow table and make it useless to do a simple lookup. Good Cryptographic hash functions have a property called the "Avalanche effect" this basically means that a 1 bit change causes at least 50% of the bits to change after it. So hashing with salt increases your overall security by making it harder to figure out what those passwords are by a simple lookup.
Fast Computation
These days computers are extremely fast, making it feasible to compute a rainbow table on the fly. Because the salt is stored in plaintext usually in another column on the same table as the hashed password, a bad guy who has access to your database can compute a rainbow table on the fly with a known dictionary and the salt you have in your database.
For this case we want a way to make it harder for them to do it. And while many of us programmers toil over making our application faster this is where we would actually want things to go a little slower. Hash functions such as the SHA family are made to hash large amounts of data very fast, so they are not ideal for password hashing. Hash functions such as BCrypt introduce a work factor, a higher work factor causes the algorithm to perform slower so it's extremely hard to generate a rainbow table. As computers get faster you simply increase your work factor and rehash the hash you have. A nice side effect with BCrypt is that it will generate the salt for you, so you don't have to mess with RNGCryptoServiceProvider etc, and if you use BCrypt.NET you won't need an additional column to store the salt as it is part of the resulting string you get back. The idea is to have a work factor that's tuned so a user doesn't notice the delay when logging in, but a bad guy is really slowed down when they are trying to brute force a password or trying to generate a rainbow table on the fly.