public ObservableCollection <ListaPonudbModel> DobiListoPonudb() { var connectionString = ConfigurationManager.ConnectionStrings["myDatabaseConnection"].ConnectionString; var query = "select id_ponudbe, group_concat(v.vrsta_dela separator ', ') as vrsta_dela, cena, datum_zacetka, datum_konca, ustvarjeno, opis, dimenzije, teza, material, tip_oglasa, id_uporabnika, " + "u.podjetje as podjetje, u.odgovorna_oseba as odgovorna_oseba, u.kraj as kraj, u.tel_st as tel_st, u.email as email, " + "u.logo as logo, priponke from lista_ponudb l " + "join uporabniki u on l.id_uporabnika = u.id " + "join delo_mapping m on l.vrsta_dela = m.id " + "join vrste_dela v on m.vrste_dela_id = v.id " + "where izbrisana_ponudba != 1 " + izbrane_ponudbe + " and l.id_uporabnika != " + (Application.Current as App).uporabnikId + " group by l.id_ponudbe order by id_ponudbe desc; "; using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand command = new MySqlCommand(query, connection); //try //{ connection.Open(); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ListaPonudbModel ponudbe = new ListaPonudbModel(); if (reader["id_ponudbe"] != DBNull.Value) { ponudbe.IdPonudbe = Convert.ToInt32(reader["id_ponudbe"]); } if (reader["vrsta_dela"] != DBNull.Value) { ponudbe.VrstaDela = Convert.ToString(reader["vrsta_dela"]); } if (reader["cena"] != DBNull.Value) { ponudbe.Cena = Convert.ToString(reader["cena"]); } if (reader["datum_zacetka"] != DBNull.Value) { ponudbe.DatumZacetka = Convert.ToDateTime(reader["datum_zacetka"]); } if (reader["datum_konca"] != DBNull.Value) { ponudbe.DatumKonca = Convert.ToDateTime(reader["datum_konca"]); } if (reader["ustvarjeno"] != DBNull.Value) { ponudbe.Ustvarjeno = Convert.ToDateTime(reader["ustvarjeno"]); } if (reader["opis"] != DBNull.Value) { ponudbe.Opis = Convert.ToString(reader["opis"]); } if (reader["dimenzije"] != DBNull.Value) { ponudbe.Dimenzije = Convert.ToString(reader["dimenzije"]); } if (reader["teza"] != DBNull.Value) { ponudbe.Teza = Convert.ToString(reader["teza"]); } if (reader["material"] != DBNull.Value) { ponudbe.Material = Convert.ToString(reader["material"]); } if (reader["tip_oglasa"] != DBNull.Value) { //ponudbe.TipOglasa = Convert.ToInt32(reader["tip_oglasa"]); ponudbe.TipOglasa = "NUDIM DELO"; } if (reader["id_uporabnika"] != DBNull.Value) { ponudbe.IdUporabnika = Convert.ToInt32(reader["id_uporabnika"]); } if (reader["podjetje"] != DBNull.Value) { ponudbe.UporabnikiPodjetje = Convert.ToString(reader["podjetje"]); } if (reader["odgovorna_oseba"] != DBNull.Value) { ponudbe.UporabnikiOdgovornaOseba = Convert.ToString(reader["odgovorna_oseba"]); } if (reader["kraj"] != DBNull.Value) { ponudbe.UporabnikiKraj = Convert.ToString(reader["kraj"]); } if (reader["tel_st"] != DBNull.Value) { ponudbe.UporabnikiTelSt = Convert.ToString(reader["tel_st"]); } if (reader["email"] != DBNull.Value) { ponudbe.UporabnikiEmail = Convert.ToString(reader["email"]); } if (reader["priponke"] != DBNull.Value) { ponudbe.Priponke = Convert.ToInt32(reader["priponke"]); } if (reader["logo"] != DBNull.Value) { byte[] blob = reader["logo"] as byte[]; using (MemoryStream ms = new MemoryStream()) { ms.Write(blob, 0, blob.Length); Bitmap bm = (Bitmap)Image.FromStream(ms); using (MemoryStream msJpg = new MemoryStream()) { bm.Save(msJpg, ImageFormat.Jpeg); ponudbe.Logo = msJpg.GetBuffer(); } } } lista.Add(ponudbe); } //} //catch (Exception ex) //{ // MessageBox.Show(ex.Message); //} connection.Close(); return(lista); } }
public ObservableCollection <ListaPonudbModel> DobiListoPonudb() { var query = "select id_ponudbe, group_concat(v.vrsta_dela separator ', ') as vrsta_dela, cena, datum_zacetka, datum_konca, opis, dimenzije, teza, material, id_uporabnika, " + "u.podjetje as podjetje, u.odgovorna_oseba as odgovorna_oseba, u.tel_st as tel_st, u.email as email, " + "u.logo as logo from lista_ponudb l " + "join uporabniki u on l.id_uporabnika = u.id " + "join delo_mapping m on l.vrsta_dela = m.id " + "join vrste_dela v on m.vrste_dela_id = v.vrsta_dela_id " + "where izbrisana_ponudba != 1 and l.id_uporabnika != " + (Application.Current as App).uporabnikId + " and tip_oglasa = 1 group by l.id_ponudbe order by id_ponudbe desc; "; using (MySqlConnection connection = new MySqlConnection(connectionString)) { MySqlCommand command = new MySqlCommand(query, connection); try { connection.Open(); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ListaPonudbModel ponudbe = new ListaPonudbModel(); if (reader["id_ponudbe"] != DBNull.Value) { ponudbe.IdPonudbe = Convert.ToInt32(reader["id_ponudbe"]); } if (reader["vrsta_dela"] != DBNull.Value) { ponudbe.VrstaDela = Convert.ToString(reader["vrsta_dela"]); } if (reader["cena"] != DBNull.Value) { ponudbe.Cena = Convert.ToString(reader["cena"]); } if (reader["datum_zacetka"] != DBNull.Value) { ponudbe.DatumZacetka = Convert.ToDateTime(reader["datum_zacetka"]); } if (reader["datum_konca"] != DBNull.Value) { ponudbe.DatumKonca = Convert.ToDateTime(reader["datum_konca"]); } if (reader["opis"] != DBNull.Value) { ponudbe.Opis = Convert.ToString(reader["opis"]); } if (reader["dimenzije"] != DBNull.Value) { ponudbe.Dimenzije = Convert.ToString(reader["dimenzije"]); } if (reader["teza"] != DBNull.Value) { ponudbe.Teza = Convert.ToString(reader["teza"]); } if (reader["material"] != DBNull.Value) { ponudbe.Material = Convert.ToString(reader["material"]); } if (reader["id_uporabnika"] != DBNull.Value) { ponudbe.IdUporabnika = Convert.ToInt32(reader["id_uporabnika"]); } if (reader["podjetje"] != DBNull.Value) { ponudbe.UporabnikiPodjetje = Convert.ToString(reader["podjetje"]); } if (reader["odgovorna_oseba"] != DBNull.Value) { ponudbe.UporabnikiOdgovornaOseba = Convert.ToString(reader["odgovorna_oseba"]); } if (reader["tel_st"] != DBNull.Value) { ponudbe.UporabnikiTelSt = Convert.ToString(reader["tel_st"]); } if (reader["email"] != DBNull.Value) { ponudbe.UporabnikiEmail = Convert.ToString(reader["email"]); } if (!reader.IsDBNull(reader.GetOrdinal("logo"))) { using (MemoryStream ms = new MemoryStream(reader.GetOrdinal("logo"))) { //var imageSource = new BitmapImage(); //imageSource.BeginInit(); //imageSource.StreamSource = ms; //imageSource.CacheOption = BitmapCacheOption.OnLoad; //imageSource.EndInit(); //ponudbe.Logo = imageSource; // Assign the Source property of your image //yourImage.Source = imageSource; } } lista.Add(ponudbe); } } catch (Exception ex) { MessageBox.Show(ex.Message); } connection.Close(); return(lista); } }