public List <Picture> getPictures()
{
List <Picture> pictureList = new List <Picture>();
NpgsqlConnection db = DBConnection.Instance.initialize();
// mit NpgsqlCommand wird die query gesetzt
NpgsqlCommand cmd_pic = new NpgsqlCommand("select * from picture left join fotograf on picture.fk_pk_fotograf_id=fotograf.pk_fotograf_id left join exif on picture.fk_pk_exif_id=exif.pk_exif_id left join iptc on picture.fk_pk_iptc_id=iptc.pk_iptc_id", db);
try
{
// prepare Statement
cmd_pic.Prepare();
}
catch
{
Console.WriteLine("Invalid query");
}
// damit die Daten von der Query gelesen werden können, muss erst ein reader deklariert werden und mit Read() gelesen werden
NpgsqlDataReader reader_pic = cmd_pic.ExecuteReader();
// reader ist deklariert, somit können wir das Command disposen
cmd_pic.Dispose();
while (reader_pic.Read())
{
// mit Get werden die Daten von den Rows gelesen, typus muss im Vorhinein bestimmt werden
Picture temp_pic = new Picture();
temp_pic.setId(reader_pic.GetInt32(0));
temp_pic.setDirectory(reader_pic.GetString(4));
temp_pic.setPhotographerId(reader_pic.GetInt32(3));
Exif temp_exif = new Exif();
temp_exif.setId(reader_pic.GetInt32(1));
temp_exif.setIsoSpeedRating(reader_pic.GetInt32(11));
temp_exif.setMake(reader_pic.GetString(12));
temp_exif.setDateTime(reader_pic.GetDateTime(13));
temp_exif.setFlash(reader_pic.GetBoolean(14));
temp_exif.setExposureTime(reader_pic.GetString(15));
temp_pic.setExif(temp_exif);
Iptc temp_iptc = new Iptc();
temp_iptc.setId(reader_pic.GetInt32(2));
temp_iptc.setDate(reader_pic.GetDateTime(17));
temp_iptc.setTime(reader_pic.GetTimeSpan(18));
temp_iptc.setByLine(reader_pic.GetString(19));
temp_iptc.setCopyright(reader_pic.GetString(20));
temp_pic.setIptc(temp_iptc);
pictureList.Add(temp_pic);
}
reader_pic.Close();
return(pictureList);
}
public Picture getPicture(int ID)
{
Picture picture = new Picture();
NpgsqlConnection db = DBConnection.Instance.initialize();
NpgsqlCommand cmd_pic = new NpgsqlCommand("select * from picture left join fotograf on picture.fk_pk_fotograf_id=fotograf.pk_fotograf_id left join exif on picture.fk_pk_exif_id=exif.pk_exif_id left join iptc on picture.fk_pk_iptc_id=iptc.pk_iptc_id WHERE picture_id = @q", db);
cmd_pic.Parameters.AddWithValue("q", ID);
try
{
cmd_pic.Prepare();
}
catch
{
Console.WriteLine("Invalid query");
}
NpgsqlDataReader reader_pic = cmd_pic.ExecuteReader();
cmd_pic.Dispose();
while (reader_pic.Read())
{
picture.setId(reader_pic.GetInt32(0));
picture.setDirectory(reader_pic.GetString(4));
picture.setPhotographerId(reader_pic.GetInt32(3));
Exif temp_exif = new Exif();
temp_exif.setId(reader_pic.GetInt32(1));
temp_exif.setIsoSpeedRating(reader_pic.GetInt32(11));
temp_exif.setMake(reader_pic.GetString(12));
temp_exif.setDateTime(reader_pic.GetDateTime(13));
temp_exif.setFlash(reader_pic.GetBoolean(14));
temp_exif.setExposureTime(reader_pic.GetString(15));
picture.setExif(temp_exif);
Iptc temp_iptc = new Iptc();
temp_iptc.setId(reader_pic.GetInt32(2));
temp_iptc.setDate(reader_pic.GetDateTime(17));
temp_iptc.setTime(reader_pic.GetTimeSpan(18));
temp_iptc.setByLine(reader_pic.GetString(19));
temp_iptc.setCopyright(reader_pic.GetString(20));
picture.setIptc(temp_iptc);
}
reader_pic.Close();
return(picture);
}