SqlitePerson.SelectAllPersonsRecuperable C# (CSharp) Method

    public static ArrayList SelectAllPersonsRecuperable(string sortedBy, int except, int inSession, string searchFilterName)
    {
        //sortedBy = name or uniqueID (= creation date)

        //1st select all the person.uniqueID of people who are in CurrentSession (or none if except == -1)
        //2n select all names in database (or in one session if inSession != -1)
        //3d filter all names (save all found in 2 that is not in 1)
        //
        //probably this can be made in only one time... future
        //
        //1

        string tp = Constants.PersonTable;
        string tps = Constants.PersonSessionTable;

        Sqlite.Open();
        dbcmd.CommandText = "SELECT " + tp + ".uniqueID " +
            " FROM " + tp + "," + tps +
            " WHERE " + tps + ".sessionID == " + except +
            " AND " + tp + ".uniqueID == " + tps + ".personID ";
        LogB.SQL(dbcmd.CommandText.ToString());

        SqliteDataReader reader;
        reader = dbcmd.ExecuteReader();

        ArrayList arrayExcept = new ArrayList(2);

        while(reader.Read())
            arrayExcept.Add (reader[0].ToString());

        reader.Close();
        Sqlite.Close();

        //2
        //sort no case sensitive when we sort by name
        if(sortedBy == "name") {
            sortedBy = "lower(" + tp + ".name)" ;
        } else {
            sortedBy = tp + ".uniqueID" ;
        }

        Sqlite.Open();
        if(inSession == -1) {
            string nameLike = "";
            if(searchFilterName != "")
                nameLike = " WHERE LOWER(" + tp + ".name) LIKE LOWER (\"%" + searchFilterName + "%\") ";

            dbcmd.CommandText =
                "SELECT * FROM " + tp +
                nameLike +
                " ORDER BY " + sortedBy;

        } else {
            dbcmd.CommandText =
                "SELECT " + tp + ".* FROM " + tp + ", " + tps +
                " WHERE " + tps + ".sessionID == " + inSession +
                " AND " + tp + ".uniqueID == " + tps + ".personID " +
                " ORDER BY " + sortedBy;
        }
        LogB.SQL(dbcmd.CommandText.ToString());

        SqliteDataReader reader2;
        reader2 = dbcmd.ExecuteReader();

        ArrayList arrayReturn = new ArrayList(2);

        bool found;

        //3
        while(reader2.Read()) {
            found = false;
            foreach (string line in arrayExcept) {
                if(line == reader2[0].ToString()) {
                    found = true;
                    goto finishForeach;
                }
            }

        finishForeach:

            if (!found) {
                Person p = new Person(
                        Convert.ToInt32(reader2[0].ToString()), //uniqueID
                        reader2[1].ToString(), 			//name
                        reader2[2].ToString(), 			//sex
                        UtilDate.FromSql(reader2[3].ToString()),//dateBorn
                        Convert.ToInt32(reader2[4].ToString()), //race
                        Convert.ToInt32(reader2[5].ToString()), //countryID
                        reader2[6].ToString(), 			//description
                        Convert.ToInt32(reader2[9].ToString()) //serverUniqueID
                        );
                arrayReturn.Add(p);
            }
        }

        reader2.Close();
        Sqlite.Close();

        return arrayReturn;
    }