public class User
{
    private string _username;
    public string Username
    {
        get { return _username; }
        set { _username = value; }
    }

    private string _password;
    public string Password
    {
        get { return _password; }
        set { _password = value; }
    }

    public User()
    {
    }

    public bool LogIn(String username, String password)
    {
        bool success = false;

        if (UserMapper.UsernameExists(username))
        {
            success = UserMapper.UsernamePasswordExists(username, password);
        }
        else
        {
            //do nothing
        }

        return success;
    }

    public bool LogOut()
    {
           bool success;
        //----some logic
           return success;
    }

    public static User GetUserByUsername(string username)
    {
        return UserMapper.GetUserByUsername(username);
    }

    public static UserCollection GetByUserTypeCode(string code)
    {
        return UserMapper.GetByUserTypeCode(code);
    }
}

public static class UserMapper
{
    public static bool UsernameExists(String username)
    {
        bool exists = false;

        if (UserDA.CountUsername(username) == 1)
        {
            exists = true;
        }

        return exists;
    }

    public static bool UsernamePasswordExists(String username, String password)
    {
        bool exists = false;

        if (UserDA.CountUsernameAndPassword(username, password) == 1)
        {
            exists = true;
        }

        return exists;
    }
}

public static class UserDA
{
    public static int CountUsername(string username)
    {
        int count = -1;

        SqlConnection conn = DBConn.Connection;

        if (conn != null)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"SELECT COUNT(*)
                                FROM User
                                WHERE User_name = @User_name";
                command.Parameters.AddWithValue("@User_name", username);

                command.Connection.Open();
                object idRaw = command.ExecuteScalar();
                command.Connection.Close();

                if (idRaw == DBNull.Value)
                {
                    count = 0;
                }
                else
                {
                    count = (int)idRaw;
                }
            }
            catch (Exception ex)
            {
                count = -1;
            }
        }

        return count;
    }

    public static int CountUsernameAndPassword(string username, string password)
    {
        int count = 0;

        SqlConnection conn = DBConn.Connection;

        if (conn != null)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"SELECT COUNT(*)
                                FROM User
                                WHERE User_name = @User_name AND Pass_word = @Pass_word";
                command.Parameters.AddWithValue("@User_name", username);
                command.Parameters.AddWithValue("@Pass_word", password);

                command.Connection.Open();
                object idRaw = command.ExecuteScalar();
                command.Connection.Close();

                if (idRaw == DBNull.Value)
                {
                    count = 0;
                }
                else
                {
                    count = (int)idRaw;
                }
            }
            catch (Exception ex)
            {
                count = 0;
            }
        }

        return count;
    }

    public static int InsertUser(params object[] objects)
    {
        int count = -1;

        SqlConnection conn = DBConn.Connection;

        if (conn != null)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"INSERT INTO User(ID, User_name, Pass_word, RegDate, UserTypeCode, ActualCodeOrRoll)
                                                            VALUES(@ID, @User_name, @Pass_word, @RegDate, @UserTypeCode, @ActualCodeOrRoll)";
                command.Parameters.AddWithValue("@ID", objects[0]);
                command.Parameters.AddWithValue("@User_name", objects[1]);
                command.Parameters.AddWithValue("@Pass_word", objects[2]);
                command.Parameters.AddWithValue("@RegDate", objects[3]);
                command.Parameters.AddWithValue("@UserTypeCode", objects[4]);
                command.Parameters.AddWithValue("@ActualCodeOrRoll", objects[5]);

                command.Connection.Open();
                count = command.ExecuteNonQuery();
                command.Connection.Close();
            }
            catch (Exception ex)
            {
                count = -1;
            }
        }

        return count;
    }

    public static SqlDataReader GetUserByUsername(string username)
    {
        SqlDataReader dataReader = null;

        SqlConnection conn = DBConn.Connection;

        if (conn != null)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"SELECT * FROM User WHERE User_name = @User_name";
                command.Parameters.AddWithValue("@User_name", username);

                command.Connection.Open();

                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {
                dataReader.Close();
                dataReader.Dispose();
            }
        }

        return dataReader;
    }

    public static SqlDataReader GetUserByUserTypeCode(string userTypeCode)
    {
        SqlDataReader dataReader = null;

        SqlConnection conn = DBConn.Connection;

        if (conn != null)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"SELECT * FROM User WHERE UserTypeCode = @UserTypeCode";
                command.Parameters.AddWithValue("@UserTypeCode", userTypeCode);

                command.Connection.Open();

                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {
                dataReader.Close();
                dataReader.Dispose();
            }
        }

        return dataReader;
    }
}