public static int izbrisiAerdrom(Aerodrom aerodrom) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE Aerodrom SET "); sb.AppendLine("Deleted = 1 WHERE AerodromId = @Id"); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@Id", aerodrom.Id); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static int napraviAerodrom(Aerodrom aerodrom) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("INSERT INTO Aerodrom VALUES "); sb.AppendLine("(@Sifra,@Naziv, @Grad , 0)"); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@Sifra", aerodrom.Sifra); komanda.Parameters.AddWithValue("@Naziv", aerodrom.Naziv); komanda.Parameters.AddWithValue("@Grad", aerodrom.Grad); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static int IzbrisiLet(Let let) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE Let SET "); sb.AppendLine("Deleted= 1 "); sb.AppendLine("WHERE LetId = @LetId "); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@LetId", let.Id); try { cnn.Open(); komanda.ExecuteNonQuery(); cnn.Close(); return(0); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(-1); } }
public static int IzmeniAvion(Avion avion) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE Avion SET "); sb.AppendLine("Naziv = @Naziv , AvioKompanijaId = @AvioKompanijaId "); sb.AppendLine("WHERE AvionId = @AvionId"); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@Naziv", avion.Naziv); komanda.Parameters.AddWithValue("@AvioKompanijaId", avion.AvioKompanija.Id); komanda.Parameters.AddWithValue("@AvionId", avion.Id); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static ObservableCollection <AvioKompanija> vratiAvioKompanije() { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM AvioKompanija WHERE Deleted = 0", cnn); ObservableCollection <AvioKompanija> lista = new ObservableCollection <AvioKompanija>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { AvioKompanija av = new AvioKompanija(); av.Id = (int)dr["AvioKompanijaId"]; av.Naziv = dr["Naziv"].ToString(); av.Deleted = Convert.ToBoolean(dr["Deleted"]); lista.Add(av); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(lista); }
public static AvioKompanija vratiAvioKompanijuPrekoImena(string naziv) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM AvioKompanija WHERE Naziv = @Naziv AND Deleted = 0", cnn); komanda.Parameters.AddWithValue("Naziv", naziv); AvioKompanija av = new AvioKompanija(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { av.Id = (int)dr["AvioKompanijaId"]; av.Naziv = dr["Naziv"].ToString(); av.Deleted = Convert.ToBoolean(dr["Deleted"]); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(av); }
public static ObservableCollection <Let> VratiLetove() { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Let WHERE Deleted = 0", cnn); ObservableCollection <Let> letovi = new ObservableCollection <Let>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Let l = new Let(); l.Id = (int)dr["LetId"]; l.Sifra = dr["Sifra"].ToString(); l.Pilot = dr["Pilot"].ToString(); l.vremePolaska = DateTime.Parse(dr["VremePolaska"].ToString()); l.vremeDolaska = DateTime.Parse(dr["VremeDolaska"].ToString()); l.PolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["PolazniAerodrom"]); l.DolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["DolazniAerodrom"]); l.Avion = AvionDAO.vratiAvion((int)dr["AvionId"]); l.Cena = (decimal)dr["Cena"]; l.Deleted = Convert.ToBoolean(dr["Deleted"]); if (l.DolazniAerodrom.Deleted || l.DolazniAerodrom.Naziv == null) { IzbrisiLet(l); } else if (l.PolazniAerodrom.Deleted || l.PolazniAerodrom.Naziv == null) { IzbrisiLet(l); } else if (l.Avion.Deleted || l.Avion == null) { IzbrisiLet(l); } else { letovi.Add(l); } } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(letovi); }
public static ObservableCollection<Karta> vratiKarte() { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Karta WHERE Deleted = 0", cnn); ObservableCollection<Karta> karte = new ObservableCollection<Karta>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Karta k = new Karta(); k.KartaId = (int)dr["KartaId"]; k.Let = LetDAO.vratiLet((int)dr["LetId"]); Sediste s = new Sediste(); s.Id = 1; s.Red = (int)dr["BrojReda"]; s.SedisteURedu = (int)dr["BrojSedista"]; EtipSedista tip; Enum.TryParse(dr["Klasa"].ToString(), out tip); s.tipSedista = tip; s.Deleted = false; k.Sediste = s; k.Korisnik = KorisnikDAO.vratiKorisnika((int)dr["KorisnikId"]); k.Kapija = dr["Kapija"].ToString(); k.Cena = (decimal)dr["Cena"]; k.Deleted = Convert.ToBoolean(dr["Deleted"]); if (k.Let.Deleted || k.Let.Sifra == null) { IzbrisiKartu(k); } else { karte.Add(k); } } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return null; } return karte; }
public static Korisnik vratiKorisnikaPrekoUserName(string userName) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Korisnik WHERE KorisnickoIme = @KorisnickoIme AND Deleted = 0", cnn); komanda.Parameters.AddWithValue("KorisnickoIme", userName); Korisnik k = new Korisnik(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { k.Id = (int)dr["KorisnikId"]; k.Ime = dr["Ime"].ToString(); k.Prezime = dr["Prezime"].ToString(); k.Email = dr["Email"].ToString(); k.Adresa = dr["Adresa"].ToString(); Epol pol; Enum.TryParse(dr["Pol"].ToString(), out pol); k.Pol = pol; k.UserName = dr["KorisnickoIme"].ToString(); k.Password = dr["Lozinka"].ToString(); Etip tip; Enum.TryParse(dr["TipKorisnika"].ToString(), out tip); k.Tip = tip; k.Deleted = Convert.ToBoolean(dr["Deleted"]); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } if (k.Id != 0) { return(k); } return(null); }
public static List<Karta> VratiKarteULetu(Let let) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Karta WHERE LetId = @LetID AND Deleted = 0", cnn); komanda.Parameters.AddWithValue("@LetID", let.Id); List<Karta> karte = new List<Karta>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Karta k = new Karta(); k.KartaId = (int)dr["KartaId"]; k.Let = LetDAO.vratiLet((int)dr["LetId"]); Sediste s = new Sediste(); s.Id = 1; s.Red = (int)dr["BrojReda"]; s.SedisteURedu = (int)dr["BrojSedista"]; EtipSedista tip; Enum.TryParse(dr["Klasa"].ToString(), out tip); s.tipSedista = tip; s.Deleted = false; k.Sediste = s; k.Korisnik = KorisnikDAO.vratiKorisnika((int)dr["KorisnikId"]); k.Kapija = dr["Kapija"].ToString(); k.Cena = (decimal)dr["Cena"]; k.Deleted = Convert.ToBoolean(dr["Deleted"]); karte.Add(k); } } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return null; } return karte; }
public static ObservableCollection <Avion> vratiAvione() { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Avion WHERE Deleted = 0", cnn); ObservableCollection <Avion> avioni = new ObservableCollection <Avion>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Avion a = new Avion(); a.Id = (int)dr["AvionId"]; int brRedovaEklase = (int)dr["BrojRedovaEkonomskeKlase"]; int brSedistaEklase = (int)dr["BrojSedistaUReduEkonomskeKlase"]; a.puniListuEkon(brRedovaEklase, brSedistaEklase); int brRedovaBiznis = (int)dr["BrojRedovaBiznisKlase"]; int brSedistaBiznis = (int)dr["BrojSedistaUReduBiznisKlase"]; a.puniListuBiz(brRedovaBiznis, brSedistaBiznis); a.AvioKompanija = AvioKompanijaDAO.vratiAvioKompaniju((int)dr["AvioKompanijaId"]); a.Naziv = dr["Naziv"].ToString(); a.Deleted = Convert.ToBoolean(dr["Deleted"]); if (a.AvioKompanija.Deleted || a.AvioKompanija.Naziv == null) { IzbirisAvio(a); } else { avioni.Add(a); } } } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(avioni); }
public static ObservableCollection <Korisnik> VratiKorisnike() { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("Select * FROM Korisnik WHERE Deleted = 0", cnn); ObservableCollection <Korisnik> korisnici = new ObservableCollection <Korisnik>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Korisnik k = new Korisnik(); k.Id = (int)dr["KorisnikId"]; k.Ime = dr["Ime"].ToString(); k.Prezime = dr["Prezime"].ToString(); k.Email = dr["Email"].ToString(); k.Adresa = dr["Adresa"].ToString(); Epol pol; Enum.TryParse(dr["Pol"].ToString(), out pol); k.Pol = pol; k.UserName = dr["KorisnickoIme"].ToString(); k.Password = dr["Lozinka"].ToString(); Etip tip; Enum.TryParse(dr["TipKorisnika"].ToString(), out tip); k.Tip = tip; k.Deleted = Convert.ToBoolean(dr["Deleted"]); korisnici.Add(k); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(korisnici); }
public static Let vratiLetPrekoSifre(string sifra) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Let WHERE Sifra = @Sifra AND Deleted = 0", cnn); komanda.Parameters.AddWithValue("@Sifra", sifra.ToString()); Let l = new Let(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { l.Id = (int)dr["LetId"]; l.Sifra = dr["Sifra"].ToString(); l.Pilot = dr["Pilot"].ToString(); l.vremePolaska = DateTime.Parse(dr["VremePolaska"].ToString()); l.vremeDolaska = DateTime.Parse(dr["VremeDolaska"].ToString()); l.PolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["PolazniAerodrom"]); l.DolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["DolazniAerodrom"]); l.Avion = AvionDAO.vratiAvion((int)dr["AvionId"]); l.Cena = (decimal)dr["Cena"]; l.Deleted = Convert.ToBoolean(dr["Deleted"]); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } if (l.Id != 0) { return(l); } return(null); }
public static int IzmeniKorisnika(Korisnik k) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE Korisnik SET "); sb.AppendLine("Ime = @Ime, Prezime = @Prezime, Email = @Email, Adresa = @Adresa, Pol = @Pol, "); sb.AppendLine("KorisnickoIme = @KorisnickoIme, Lozinka = @Lozinka, TipKorisnika = @Tip"); sb.AppendLine("WHERE KorisnikId = @KorisnikId "); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@Ime", k.Ime); komanda.Parameters.AddWithValue("@Prezime", k.Prezime); komanda.Parameters.AddWithValue("@Email", k.Email); komanda.Parameters.AddWithValue("@Adresa", k.Adresa); komanda.Parameters.AddWithValue("@Pol", k.Pol.ToString()); komanda.Parameters.AddWithValue("@KorisnickoIme", k.UserName); komanda.Parameters.AddWithValue("@Lozinka", k.Password); komanda.Parameters.AddWithValue("@Tip", k.Tip.ToString()); komanda.Parameters.AddWithValue("@KorisnikId", k.Id); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static int IzmeniLet(Let let) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE Let SET "); sb.AppendLine("Sifra=@Sifra,Pilot = @Pilot,VremePolaska = @VremePolaska,VremeDolaska = @VremeDolaska, "); sb.AppendLine("PolazniAerodrom = @PolazniAerodrom,DolazniAerodrom = @DolazniAerodrom, "); sb.AppendLine("AvionId = @AvionId,Cena = @Cena, Deleted = @Deleted "); sb.AppendLine("WHERE LetId = @LetId "); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@Sifra", let.Sifra); komanda.Parameters.AddWithValue("@Pilot", let.Pilot); komanda.Parameters.AddWithValue("@VremePolaska", let.vremePolaska); komanda.Parameters.AddWithValue("@VremeDolaska", let.vremeDolaska); komanda.Parameters.AddWithValue("@PolazniAerodrom", let.PolazniAerodrom.Id); komanda.Parameters.AddWithValue("@DolazniAerodrom", let.DolazniAerodrom.Id); komanda.Parameters.AddWithValue("@AvionId", let.Avion.Id); komanda.Parameters.AddWithValue("@Cena", let.Cena); komanda.Parameters.AddWithValue("@Deleted", let.Deleted); komanda.Parameters.AddWithValue("@LetId", let.Id); try { cnn.Open(); komanda.ExecuteNonQuery(); cnn.Close(); return(0); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(-1); } }
public static Avion vratiAvion(int id) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Avion WHERE AvionId = @AvionId AND Deleted = 0", cnn); komanda.Parameters.AddWithValue("AvionId", id); Avion a = new Avion(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { a.Id = (int)dr["AvionId"]; int brRedovaEklase = (int)dr["BrojRedovaEkonomskeKlase"]; int brSedistaEklase = (int)dr["BrojSedistaUReduEkonomskeKlase"]; a.puniListuEkon(brRedovaEklase, brSedistaEklase); int brRedovaBiznis = (int)dr["BrojRedovaBiznisKlase"]; int brSedistaBiznis = (int)dr["BrojSedistaUReduBiznisKlase"]; a.puniListuBiz(brRedovaBiznis, brSedistaBiznis); a.AvioKompanija = AvioKompanijaDAO.vratiAvioKompaniju((int)dr["AvioKompanijaId"]); a.Naziv = dr["Naziv"].ToString(); a.Deleted = Convert.ToBoolean(dr["Deleted"]); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(a); }
public static List <int> vratiBrSedita(Avion a) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Avion WHERE AvionId = @AvionId", cnn); komanda.Parameters.AddWithValue("AvionId", a.Id); List <int> lista = new List <int>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { int brRedovaEklase = (int)dr["BrojRedovaEkonomskeKlase"]; int brSedistaEklase = (int)dr["BrojSedistaUReduEkonomskeKlase"]; int brRedovaBiznis = (int)dr["BrojRedovaBiznisKlase"]; int brSedistaBiznis = (int)dr["BrojSedistaUReduBiznisKlase"]; lista.Add(brRedovaEklase); lista.Add(brSedistaEklase); lista.Add(brRedovaBiznis); lista.Add(brSedistaBiznis); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(lista); }
public static int NaparviKartu(Karta k) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("INSERT INTO Karta VALUES "); sb.AppendLine("(@LetId, @BrojReda, @BrojSedista, @KorisnikId, @Klasa,@Kapija,@Cena, @Deleted)"); SqlCommand komanda = new SqlCommand(sb.ToString(),cnn); komanda.Parameters.AddWithValue("@LetId", k.Let.Id); komanda.Parameters.AddWithValue("@BrojReda", k.Sediste.Red); komanda.Parameters.AddWithValue("@BrojSedista", k.Sediste.SedisteURedu); komanda.Parameters.AddWithValue("@KorisnikId", k.Korisnik.Id); komanda.Parameters.AddWithValue("@Klasa", k.Sediste.tipSedista.ToString()); komanda.Parameters.AddWithValue("@Kapija", k.Kapija); komanda.Parameters.AddWithValue("@Cena", k.Cena); komanda.Parameters.AddWithValue("@Deleted", 0); try { cnn.Open(); komanda.ExecuteNonQuery(); cnn.Close(); return 0; } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return -1; } }
public static int NapraviLet(Let let) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("INSERT INTO Let VALUES "); sb.AppendLine("(@Sifra, @Pilot, @VremePolaska, @VremeDolaska,@PolazniAerodrom, "); sb.AppendLine("@DolazniAerodrom, @AvionId, @Cena, 0)"); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@Sifra", let.Sifra); komanda.Parameters.AddWithValue("@Pilot", let.Pilot); komanda.Parameters.AddWithValue("@VremePolaska", let.vremePolaska); komanda.Parameters.AddWithValue("@VremeDolaska", let.vremeDolaska); komanda.Parameters.AddWithValue("@PolazniAerodrom", let.PolazniAerodrom.Id); komanda.Parameters.AddWithValue("@DolazniAerodrom", let.DolazniAerodrom.Id); komanda.Parameters.AddWithValue("@AvionId", let.Avion.Id); komanda.Parameters.AddWithValue("@Cena", let.Cena); try { cnn.Open(); komanda.ExecuteNonQuery(); cnn.Close(); return(0); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(-1); } }
public static ObservableCollection <Aerodrom> vratiAerodrome() { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Aerodrom WHERE Deleted = 0", cnn); ObservableCollection <Aerodrom> lista = new ObservableCollection <Aerodrom>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Aerodrom ar = new Aerodrom(); ar.Id = (int)dr["AerodromId"]; ar.Sifra = dr["Sifra"].ToString(); ar.Naziv = dr["Naziv"].ToString(); ar.Grad = dr["Grad"].ToString(); ar.Deleted = Convert.ToBoolean(dr["Deleted"]); lista.Add(ar); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(lista); }
public static int UbaciKorisnika(Korisnik k) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("INSERT INTO Korisnik Values(@Ime, @Prezime, @Email, @Adresa, @Pol, @UserName, @Passoword, @Tip, @Deleted) ; SELECT SCOPE_IDENTITY();", cnn); komanda.Parameters.AddWithValue("@Ime", k.Ime); komanda.Parameters.AddWithValue("@Prezime", k.Prezime); komanda.Parameters.AddWithValue("@Email", k.Email); komanda.Parameters.AddWithValue("@Adresa", k.Adresa); if (k.Pol == null) { komanda.Parameters.AddWithValue("@Pol", string.Empty); } else { komanda.Parameters.AddWithValue("@Pol", k.Pol.ToString()); } komanda.Parameters.AddWithValue("@UserName", k.UserName); komanda.Parameters.AddWithValue("@Passoword", k.Password); komanda.Parameters.AddWithValue("@Tip", k.Tip.ToString()); komanda.Parameters.AddWithValue("@Deleted", 0); try { cnn.Open(); int id = Convert.ToInt32(komanda.ExecuteScalar()); return(id); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static int NapraviAvion(Avion avion, List <int> lista) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("INSERT INTO Avion VALUES "); sb.AppendLine("(@BrojRedovaEkonKlase, @BrojSEdistaUReduEkonKlase,@BrojRedovaBizKlase, "); sb.AppendLine(" @BrojSEdistaUReduBizKlase, @AvioKompanijaId, @Naziv, @Deleted);"); SqlCommand komanda = new SqlCommand("INSERT INTO Avion VALUES (@BrojRedovaEkonKlase, @BrojSEdistaUReduEkonKlase, @BrojRedovaBizKlase, @BrojSEdistaUReduBizKlase,@AvioKompanijaId , @Naziv, 0)", cnn); komanda.Parameters.AddWithValue("@BrojRedovaEkonKlase", lista[0]); komanda.Parameters.AddWithValue("@BrojSEdistaUReduEkonKlase", lista[1]); komanda.Parameters.AddWithValue("@BrojRedovaBizKlase", lista[2]); komanda.Parameters.AddWithValue("@BrojSEdistaUReduBizKlase", lista[3]); komanda.Parameters.AddWithValue("@AvioKompanijaId", avion.AvioKompanija.Id); komanda.Parameters.AddWithValue("@Naziv", avion.Naziv); //komanda.Parameters.AddWithValue("@Deleted", 0); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static int DodajAvioKompaniju(AvioKompanija av) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("INSERT INTO AvioKompanija VALUES (@Naziv,@Deleted)", cnn); komanda.Parameters.AddWithValue("@Naziv", av.Naziv); komanda.Parameters.AddWithValue("@Deleted", 0); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static Aerodrom vratiAerodrom(int id) { SqlConnection cnn = Konekcija.KreirajKoekciju(); SqlCommand komanda = new SqlCommand("SELECT * FROM Aerodrom WHERE AerodromId = @AerodromId AND Deleted = 0", cnn); komanda.Parameters.AddWithValue("AerodromId", id); Aerodrom ar = new Aerodrom(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { ar.Id = (int)dr["AerodromId"]; ar.Sifra = dr["Sifra"].ToString(); ar.Naziv = dr["Naziv"].ToString(); ar.Grad = dr["Grad"].ToString(); ar.Deleted = Convert.ToBoolean(dr["Deleted"]); } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(ar); }
public static int IzbrisiKorisnika(Korisnik k) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("UPDATE Korisnik SET "); sb.AppendLine("Deleted = 1"); sb.AppendLine("WHERE KorisnikId = @KorisnikId;"); sb.AppendLine("UPDATE Karta SET "); sb.AppendLine("Deleted = 1"); sb.AppendLine("WHERE KorisnikId = @KorisnikId;"); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@KorisnikId", k.Id); try { cnn.Open(); komanda.ExecuteNonQuery(); return(0); } catch (Exception xcp) { MessageBox.Show(xcp.Message); return(-1); } finally { cnn.Close(); } }
public static ObservableCollection <Let> VratiPovratneLetove(Let let) { SqlConnection cnn = Konekcija.KreirajKoekciju(); StringBuilder sb = new StringBuilder(); sb.AppendLine("SELECT Let.LetId, Let.Sifra, Let.Pilot,Let.VremePolaska,Let.VremeDolaska, "); sb.AppendLine("Let.PolazniAerodrom,Let.DolazniAerodrom,Let.AvionId,Let.Cena,Let.Deleted FROM Let "); sb.AppendLine("INNER JOIN Avion ON let.AvionId = Avion.AvionId WHERE "); sb.AppendLine("Avion.AvioKompanijaId = @AvioKompanijaId AND Let.PolazniAerodrom = @PolazniAerodrom "); sb.AppendLine("AND Let.DolazniAerodrom = @DolazniAerodrom AND Let.Deleted = 0;"); SqlCommand komanda = new SqlCommand(sb.ToString(), cnn); komanda.Parameters.AddWithValue("@AvioKompanijaId", let.Avion.AvioKompanija.Id); komanda.Parameters.AddWithValue("@PolazniAerodrom", let.DolazniAerodrom.Id); komanda.Parameters.AddWithValue("@DolazniAerodrom", let.PolazniAerodrom.Id); ObservableCollection <Let> letovi = new ObservableCollection <Let>(); try { cnn.Open(); SqlDataReader dr = komanda.ExecuteReader(); while (dr.Read()) { Let l = new Let(); l.Id = (int)dr["LetId"]; l.Sifra = dr["Sifra"].ToString(); l.Pilot = dr["Pilot"].ToString(); l.vremePolaska = DateTime.Parse(dr["VremePolaska"].ToString()); l.vremeDolaska = DateTime.Parse(dr["VremeDolaska"].ToString()); l.PolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["PolazniAerodrom"]); l.DolazniAerodrom = AerodromDAO.vratiAerodrom((int)dr["DolazniAerodrom"]); l.Avion = AvionDAO.vratiAvion((int)dr["AvionId"]); l.Cena = (decimal)dr["Cena"]; l.Deleted = Convert.ToBoolean(dr["Deleted"]); if (l.DolazniAerodrom.Deleted || l.DolazniAerodrom.Naziv == null) { IzbrisiLet(l); } else if (l.PolazniAerodrom.Deleted || l.PolazniAerodrom.Naziv == null) { IzbrisiLet(l); } else if (l.Avion.Deleted || l.Avion == null) { IzbrisiLet(l); } else { letovi.Add(l); } } cnn.Close(); } catch (Exception xcp) { if (cnn.State == ConnectionState.Open) { cnn.Close(); } MessageBox.Show(xcp.Message); return(null); } return(letovi); }