Hi guys,

I'm reading userid that match a certain conditions and trying to return the corresponding array of UserInfo.

So far, I'm not succeding. Here's what I'm doing:

public static string AdvancedSearchStatement = "SELECT up.userid " +
                                                        "FROM ProfilePropertyDefinition ppd " +
                                                        "JOIN UserProfile up ON ppd.PropertyDefinitionID = up.PropertyDefinitionID " +
                                                        "WHERE ppd.PropertyName = 'Country' AND up.PropertyValue = '{0}' " +
                                                        "AND EXISTS ( " +
                                                                      "SELECT 1 " +
                                                                      "FROM  ProfilePropertyDefinition ppd1 " +
                                                                      "JOIN UserProfile up1 ON ppd1.PropertyDefinitionID = up1.PropertyDefinitionID " +
                                                                      "WHERE ppd1.PropertyName = 'Region' AND up1.PropertyValue = '{1}' " +
                                                                      "AND up1.userid  = up.userid " +
                                                                    ") " +
                                                        "AND EXISTS ( " +
                                                                      "SELECT 1 " +
                                                                      "FROM  ProfilePropertyDefinition ppd2 " +
                                                                      "JOIN UserProfile up2 ON ppd2.PropertyDefinitionID = up2.PropertyDefinitionID " +
                                                                      "WHERE ppd2.PropertyName = 'City' AND up2.PropertyValue = '{2}' " +
                                                                      "AND up2.userid  = up.userid " +
                                                                    ") "; 

public IEnumerable<UserInfo> GetUserByAdvancedSearchCriteria(AdvancedSearchCriteria searchCriteria, int portalId)
        {
            IEnumerable<UserInfo> userCollection = null;
            string sqlStatement = String.Format(SqlCommands.AdvancedSearchStatement,             searchCriteria.Country, searchCriteria.State, searchCriteria.City);
            using (IDataReader reader = this._dataLayer.ExecuteSQL(sqlStatement))
            {               
               userCollection = UserController.FillUserCollection(portalId, reader);
            }

            return userCollection;
        }

Since I'm using dnn 6.1

the following instruction warns me that the method FillUserCollection is obsolete:

userCollection = UserController.FillUserCollection(portalId, reader);

The same line gives me a runtime error saying that it cannot convert its return value to IEnumerable<UserInfo>

Thanks for any help on this issue

Thanks for the reply DnnModule,

I finally did it this way:

using (IDataReader reader = this._dataLayer.ExecuteSQL(sqlStatement))
{

             List<UserInfo> list = new List<UserInfo>();
            while (reader.Read())
            {
                list.Add( UserController.GetUserByName(Int32.Parse(reader[0].ToString()), reader[1].ToString() ));           
            }
            
 }

return list;

It works. My only concern with it is that I have 2 database access for each record.

this statement does it: this._dataLayer.ExecuteSQL(sqlStatement))

 this one also: UserController.GetUserByName(Int32.Parse(reader[0].ToString()), reader[1].ToString()

Is there a better way of doing it. I'm using dnn  6.1 api

Thanks