private List<string> GetPeopleWhereClauses(InternalPeopleQuery query, IDbCommand cmd)
{
var whereClauses = new List<string>();
if (query.ItemId != Guid.Empty)
{
whereClauses.Add("ItemId=@ItemId");
cmd.Parameters.Add(cmd, "@ItemId", DbType.Guid).Value = query.ItemId;
}
if (query.AppearsInItemId != Guid.Empty)
{
whereClauses.Add("Name in (Select Name from People where ItemId=@AppearsInItemId)");
cmd.Parameters.Add(cmd, "@AppearsInItemId", DbType.Guid).Value = query.AppearsInItemId;
}
if (query.PersonTypes.Count == 1)
{
whereClauses.Add("PersonType=@PersonType");
cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.PersonTypes[0];
}
if (query.PersonTypes.Count > 1)
{
var val = string.Join(",", query.PersonTypes.Select(i => "'" + i + "'").ToArray());
whereClauses.Add("PersonType in (" + val + ")");
}
if (query.ExcludePersonTypes.Count == 1)
{
whereClauses.Add("PersonType<>@PersonType");
cmd.Parameters.Add(cmd, "@PersonType", DbType.String).Value = query.ExcludePersonTypes[0];
}
if (query.ExcludePersonTypes.Count > 1)
{
var val = string.Join(",", query.ExcludePersonTypes.Select(i => "'" + i + "'").ToArray());
whereClauses.Add("PersonType not in (" + val + ")");
}
if (query.MaxListOrder.HasValue)
{
whereClauses.Add("ListOrder<=@MaxListOrder");
cmd.Parameters.Add(cmd, "@MaxListOrder", DbType.Int32).Value = query.MaxListOrder.Value;
}
if (!string.IsNullOrWhiteSpace(query.NameContains))
{
whereClauses.Add("Name like @NameContains");
cmd.Parameters.Add(cmd, "@NameContains", DbType.String).Value = "%" + query.NameContains + "%";
}
return whereClauses;
}
public List<PersonInfo> GetPeople(InternalPeopleQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
CheckDisposed();
using (var cmd = _connection.CreateCommand())
{
cmd.CommandText = "select ItemId, Name, Role, PersonType, SortOrder from People";
var whereClauses = GetPeopleWhereClauses(query, cmd);
if (whereClauses.Count > 0)
{
cmd.CommandText += " where " + string.Join(" AND ", whereClauses.ToArray());
}
cmd.CommandText += " order by ListOrder";
var list = new List<PersonInfo>();
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
{
while (reader.Read())
{
list.Add(GetPerson(reader));
}
}
return list;
}
}
