public static Entertainment GetOneTopEntertainmentByTypeAndReviewCount(Entertainment.Type type, uint reviewCount)
{
lock (_locker)
{
_dataAdapter.SelectCommand.CommandText = "SELECT Entertainment." + _idColumnName + " FROM " + _tableName + ",Review WHERE Review." + _idColumnName + "=Entertainment." + _idColumnName + " AND Review.Publication IS NOT NULL AND EntertainmentType=@type GROUP BY Entertainment." + _idColumnName + " HAVING COUNT(Review.ReviewId)>=" + reviewCount + "ORDER BY AVG(Review.Point) DESC";
if (!_dataAdapter.SelectCommand.Parameters.Contains("@type"))
{
_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@type", type.ToString()));
}
else
{
_dataAdapter.SelectCommand.Parameters["@type"].Value = type.ToString();
}
DataTable dataTable = new DataTable();
if (_dataAdapter.Fill(dataTable) == 0)
{
return(null);
}
DataRow[] row = dataTable.AsEnumerable().Take(1).ToArray();
return(Entertainment.GetById((Guid)row[0][_idColumnName]));
}
}
public static Entertainment[] GetLastNEntertainmentByTypeAndReviewCount(Entertainment.Type type, uint N, uint reviewCount)
{
lock (_locker)
{
_dataAdapter.SelectCommand.CommandText = "SELECT TOP(" + N + ") Entertainment." + _idColumnName + ", Entertainment.ReleaseDate FROM " + _tableName + ",Review WHERE Review." + _idColumnName + "=Entertainment." + _idColumnName + " AND Review.Publication IS NOT NULL AND EntertainmentType=@type GROUP BY Entertainment." + _idColumnName + ", Entertainment.ReleaseDate HAVING COUNT(Review.ReviewId)>=" + reviewCount + " ORDER BY Entertainment.ReleaseDate DESC";
if (!_dataAdapter.SelectCommand.Parameters.Contains("@type"))
{
_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@type", type.ToString()));
}
else
{
_dataAdapter.SelectCommand.Parameters["@type"].Value = type.ToString();
}
DataTable dataTable = new DataTable();
if (_dataAdapter.Fill(dataTable) == 0)
{
return(null);
}
List <Guid> ids = new List <Guid>();
foreach (DataRow dataRow in dataTable.Rows)
{
ids.Add((Guid)dataRow[_idColumnName]);
}
List <Entertainment> result = new List <Entertainment>();
result.AddRange(Entertainment.GetByIds(ids.ToArray()));
return(result.OrderByDescending(entertainment => entertainment.ReleaseDate).ToArray());
}
}
public static Genre[] GetByNameExceptId(string partOfName, Entertainment.Type type, Guid id)
{
lock (_locker)
{
List <Genre> result = new List <Genre>();
partOfName = partOfName.ToLower();
_dataAdapter.SelectCommand.CommandText = "SELECT * FROM " + _tableName + " WHERE LOWER(" + _nameColumnName + ") LIKE '%' + @partOfName + '%' AND GenreType=@type AND " + _idColumnName + "!=@id";
if (!_dataAdapter.SelectCommand.Parameters.Contains("@partOfName"))
{
_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@partOfName", partOfName));
}
else
{
_dataAdapter.SelectCommand.Parameters["@partOfName"].Value = partOfName;
}
if (!_dataAdapter.SelectCommand.Parameters.Contains("@type"))
{
_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@type", type.ToString()));
}
else
{
_dataAdapter.SelectCommand.Parameters["@type"].Value = type.ToString();
}
if (!_dataAdapter.SelectCommand.Parameters.Contains("@id"))
{
_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@id", id));
}
else
{
_dataAdapter.SelectCommand.Parameters["@id"].Value = id;
}
_dataAdapter.Fill(_dataTable);
var selectedRows = from row in _dataTable.AsEnumerable().AsParallel()
where ((Entertainment.Type)Enum.Parse(typeof(Entertainment.Type), row["GenreType"].ToString()) == type) &&
(row[_nameColumnName].ToString().ToLower().Contains(partOfName)) &&
((Guid)row[_idColumnName] != id)
select row;
foreach (DataRow dr in selectedRows)
{
result.Add(new Genre(dr));
}
if (result.Count != 0)
{
return(result.ToArray());
}
return(null);
}
}
