private static BirdObservationFull ReadBirdObservation(IDataRecord reader)
{
int id = reader.GetInt32(0);
int birdId = reader.GetInt32(1);
string userId = ReadSafe(reader, 2);
DateTime?created; // DateTime is a struct (not a class). ?: does not work
if (reader.IsDBNull(3))
{
created = null;
}
else
{
created = reader.GetDateTime(3);
}
double latitude = reader.GetDouble(4);
double longitude = reader.GetDouble(5);
string placename = ReadSafe(reader, 6);
int population = reader.GetInt32(7);
string comment = ReadSafe(reader, 8);
string nameEN = reader.GetString(9);
string nameDA = reader.GetString(10);
BirdObservationFull birdObservation = new BirdObservationFull
{
Id = id,
BirdId = birdId,
UserId = userId,
Created = created,
Latitude = latitude,
Longitude = longitude,
Placename = placename,
Population = population,
Comment = comment,
NameEnglish = nameEN,
NameDanish = nameDA
};
return(birdObservation);
}
// TODO should include bird name
public List <BirdObservationFull> GetObservations(string userid = null)
{
string selectAllStudents;
bool useridIncluded = (userid != null && userid.Trim().Length > 0);
if (!useridIncluded)
{
selectAllStudents =
"select birdObservation.*, nameEn, nameDA from birdObservation join bird on birdobservation.birdId=bird.Id order by id desc";
}
else
{
selectAllStudents =
"select birdObservation.*, nameEn, nameDA from birdObservation join bird on birdobservation.birdId=bird.Id where birdobservation.userid = @userid order by id desc";
}
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectAllStudents, databaseConnection))
{
if (useridIncluded)
{
selectCommand.Parameters.AddWithValue("@userid", userid);
}
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
List <BirdObservationFull> observationsList = new List <BirdObservationFull>();
while (reader.Read())
{
BirdObservationFull observation = ReadBirdObservation(reader);
observationsList.Add(observation);
}
return(observationsList);
}
}
}
}