Username, Password and UserType Validation I have two tables in the database:


Credentials (userid, password, usertype)
Customer (customername, userid(foreign key))


I need to validate username,password and usertype and create a session variable for customername.

I have organized the code and it works fine, but how can I minimize the code and make it efficient?

// Function that enables LOGON for a registered User into site
public string LogOn(UserInformation objLogOnUserInformation)
{
    ConnectionManager conCm = new ConnectionManager();
    try
    {
        SqlConnection con = conCm.OpenConnection();
        SqlCommand cmd,cmdOne;
        SqlDataReader dr;
        string _str = "U";
        cmd = new SqlCommand("select UserID  from Credentials", con);
        dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            //checking user name is present in database
            if (dr.GetValue(0).ToString() == objLogOnUserInformation.UserId)
            {
                dr.Close();
                //retriving Password for the existing user
                cmd = new SqlCommand("select Password  from Credentials where UserId='" + objLogOnUserInformation.UserId + "'", con);
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    //checking the password is matching with the database
                    if (dr.GetValue(0).ToString() == objLogOnUserInformation.Password)
                    {
                        dr.Close();
                        cmd = new SqlCommand("select UserType  from Credentials where userid='" + objLogOnUserInformation.UserId + "'", con);
                        dr = cmd.ExecuteReader();

                        while (dr.Read())
                        {
                            //checking user type 
                            if (dr.GetValue(0).ToString() == _str)
                            {
                                dr.Close();
                                cmdOne = new SqlCommand("select customername from customer where userid='" + objLogOnUserInformation.UserId + "'", con);
                                dr = cmdOne.ExecuteReader();
                                dr.Read();
                                objLogOnUserInformation.SessionUserName = dr.GetValue(0).ToString();
                                dr.Close();
                                //if usertype is customer, U is returned
                                return _str;
                            }
                            else
                            {
                                dr.Close();
                                cmdOne = new SqlCommand("select customername from customer where userid='" + objLogOnUserInformation.UserId + "'", con);
                                dr = cmdOne.ExecuteReader();
                                dr.Read();
                                objLogOnUserInformation.SessionUserName = dr.GetValue(0).ToString();
                                dr.Close();
                                //If usertype is Admin, A is returned
                                return "A";

                            }
                        }
                    }

                    else
                    {
                        return " Either User Name or Password is not Valid";
                    }
                }
            }
        }
Here are some observations:


you first iterate all rows in Credentials and then select the Password yet again - why not just select the pasword?
seems like your password is plain-text - BAD idea - hash your password together with a  salt please
don't concat strings to get SELECT queries - use parameters instead
you are using C#/ASP.NET so why don't you use the provided login mechanisms?
you return a string "A" if the login succeeds and the error message if not - this is IMO bad design - return a simple struct/class with more information instead