You'll want to store your passwords in your database as hashed values - don't store the passwords in plain text.
An easy class to use in .NET to hash any string is not in System.Security.Cryptography but rather in System.Web.Security.FormsAuthentication.
Use it like so:
string hashedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(myPassword, "MD5");
Then, just compare this hashed value with the one you've stored in your database. If they match, the user is authenticated.
Now, before you go asking me how to decrypt the hash and get the original password, you can't. It's a one-way operation. You only hash the password for the purposes of comparing it to an already hashed value.
Hashed passwords are still vulnerable to so-called 'dictionary' attacks, whereby the hacker simply computes the hash for every word in the dictionary and tries all of them. If your password is a simple english word, this sort of brute force attack will work. To thwart this, add a 'salt' value to your password before you hash it and store it in your user database. In other words, instead of storing the hash of 'password' you would store the hash of 'blahpassword' where 'blah' is the salt. This works against dictionary attacks because 'blahpassword' is not a real word. This is even more effective if your salt is a garble, like '4^e#t'. Add the same salt to the user's inputted password before you hash it and compare it to your stored hash.
You can use this same function to hash passwords as you save them in the database. If you manually create the passwords for the users, you could make a simple application to take the password and return to you the hashed value. If you automatically create the password, create a function to accept the password and return the hash string before you save it to the database.