Listing of UserSession.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

using System.Security.Cryptography;
using System.Text;

/// <summary>
///     Global session management, including login, logout
/// </summary>
public class UserSession : System.Web.UI.Page
{
    public SqlConnection connection;

   //-----------------------------------------------------------------------
    //  Open the connection
    //-----------------------------------------------------------------------
    public int Open() {
        //-------------------------------------------------------------------
        //  set up the sql connection. (I've replaced the real values)
         //-------------------------------------------------------------------
        string connString =
            @"server=<server ip address here>;
            uid=<userid>;
            pwd=<password>;
            database=<name of database>;";
        connection = new SqlConnection(connString);

        try {
            connection.Open();
        } catch {
            return 0;
        }
        return 1;
    }

    //-----------------------------------------------------------------------
    //  Login. I am less worried about an injection attack here, since
    //  this site runs on the hospital's site, inaccessible from the 
    //  outside world.
    //-----------------------------------------------------------------------
    public string Login(string userID, string password){
        string status = "";

        if (this.Open() == 0)
            return "noconnect";

        try {
            string sql =
                @"USE CASLog; 
                SELECT userSys, password, lastName, firstName, nickName 
                FROM Users WHERE userID = '" + userID + "';";

            SqlCommand command = new SqlCommand(sql, connection);
            SqlDataReader reader = command.ExecuteReader();

            if (!reader.Read())
                status = "nouser";

            //---------------------------------------------------------------
            //  User found. Check the password
            //---------------------------------------------------------------
            else { 
                string xPassword = Encrypt(password);
                if (xPassword == (string)reader["password"]) {
                    status = "good";
                    Session["userID"]   = userID;
                    Session["userName"] = reader["firstName"] + " " + reader["lastName"];
                    Session["userSys"]  = reader["userSys"];
                    Session["nickName"] = reader["nickName"];
                } else {
                    status = "badpassword";
                }
                reader.Close();
            }

        } catch (Exception err) {
            status = err.ToString();      // the real error.
            status = "noconnect";
        }

        //-------------------------------------------------------------------
        //  close the connection, return status
        //-------------------------------------------------------------------
        connection.Close();
        return status;
    }

    //-----------------------------------------------------------------------
    //  Encrypt string
    //-----------------------------------------------------------------------
    public string Encrypt(string s) {

        ASCIIEncoding enc = new ASCIIEncoding();
        byte[] buffer1 = enc.GetBytes(s);           // convert string to bytes

        SHA1 sha = new SHA1CryptoServiceProvider();
        byte[] buffer2 = sha.ComputeHash(buffer1);  // encrypt

        string t = "0x";                            // convert back to hex string
        for (int i = 0; i < buffer2.Length; i++) {
            t += buffer2[i].ToString("X2");
        }
        return t;
    }

    //-----------------------------------------------------------------------
    //  Generate Page Links
    //-----------------------------------------------------------------------
    public string PageLinks(string page) {
        string [] pages = 
            { "Solutions.aspx", "Tickets.aspx", "Queries.aspx", "Reports.aspx", "Reference.aspx", "Preferences.aspx", "Default.aspx" };
        string[] names = { "Solutions", "Tickets", "Queries", "Reports", "Library", "Preferences", "Logout" };

        string html = "";
        for (int i=0; i<pages.Length; i++) {
            if (i > 0)
                html += "  |  ";
            if (page == names[i])
                html += names[i];
            else 
                html += "<a href=\"" + pages[i] + "\">" + names[i] + "</a>";
        }
        return html;
    }

    //-----------------------------------------------------------------------
    //  Generate Footer Links
    //-----------------------------------------------------------------------
    public string FooterLinks(string page) {
        string[] pages = 
            { "Projects.aspx",
              "Solutions.aspx",
              <various internal links>};
        string[] names = { "Projects", "CAS Log", "Paging", "Careweb", "OR Schedule"};

        string html = "";
        for (int i = 0; i < pages.Length; i++) {
            if (i > 0)
                html += "  |  ";
            if (page == names[i])
                html += names[i];
            else {
                html += "<a href=\"" + pages[i] + "\"";
                if (pages[i].StartsWith("http"))
                    html += " target = \"_blank\"";
                html += " >" + names[i] + "</a>";
            }
        }
        return html;
    }

    //-----------------------------------------------------------------------
    //  see if user is logged in
    //-----------------------------------------------------------------------
    public int LoggedIn() {
        if (Session["userID"] == null)
            return 0;
        else
            return 1;
    }

    //-----------------------------------------------------------------------
    //  Return User Name, or redirect if not logged in
    //-----------------------------------------------------------------------
    public string UserName() {
        if (Session["userID"] == null || Session["userName"] == null) {
            return "not logged in";
        }
        else
            return Session["userName"].ToString();
    }

    //-----------------------------------------------------------------------
    //  return key variables from the database and format as javascript
    //-----------------------------------------------------------------------
    public string Varlist() {
        string list = 
            "CAS.userSys = " + Session["userSys"] + ";\n" +
            "CAS.select = [\n";
        
        try {
            Open();
            string sql =
                @"SELECT variable, ndx, value, filter
                FROM CASLog..Variables order by variable, sort, value;";

            SqlCommand command = new SqlCommand(sql, connection);
            SqlDataReader reader = command.ExecuteReader();

            //---------------------------------------------------------------
            //  build up the javascript for each variable
            //---------------------------------------------------------------
            int count = 0;
            while (reader.Read()) {
                if (count++ > 0)
                    list += ",\n";
                list +=
                    "{'variable' : '" + reader["variable"] + "'," +
                    "'index' : "     + reader["ndx"] + "," +
                    "'value' : '"    + reader["value"] + "'," +
                    "'filter' : '"   + reader["filter"] + "' }";
            }
            reader.Close();

        } finally {
            list += "];\n";
        }
        

        //-------------------------------------------------------------------
        //  close the connection, return status
        //-------------------------------------------------------------------
        connection.Close();
        return list;
    }
}