public Funkce SelectById(int?fid) { if (fid == null) { return(null); } using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT fid, nazev,povinnosti FROM Funkce WHERE fid = :fid AND rownum = 1"); command.Parameters.AddWithValue(":fid", fid); Funkce data = null; var reader = command.ExecuteReader(); while (reader.Read()) { data = new Funkce(reader.GetInt32(0), reader.GetString(1), reader.GetString(2)); } reader.Close(); return(data); } }
public Log SelectById(int lid) { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT l.lid, l.pocet_vedoucich, l.pocet_deti, l.datum_zalohy, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM Log l LEFT JOIN vedouci v ON v.vid = l.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE lid = :lid"); command.Parameters.AddWithValue(":lid", lid); Log data = null; int?funkceID = -1; var reader = command.ExecuteReader(); while (reader.Read()) { if (!reader.IsDBNull(5)) { funkceID = reader.GetInt32(5); } Funkce f = new Funkce(reader.GetInt32(9), reader.GetString(10), reader.GetString(11)); Vedouci v = new Vedouci(reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetString(8), f); data = new Log(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetDateTime(3), v); } reader.Close(); return(data); } }
public Deti TryLogin(string nick, string heslo) { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT d.did, d.Jmeno, d.Nickname, d.Heslo, d.Datum_Narozeni, d.stav, d.reg_akci,h.hid, h.nazev, h.minimalni_vek, s.sid, s.nazev_druziny, s.pocet_deti, s.datum_konani,v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt,f.fid, f.nazev, f.povinnosti,r.rid, r.jmeno, r.login, r.heslo, r.kontakt FROM Deti d LEFT JOIN hodnosti h ON h.hid = d.hodnosti_hid LEFT JOIN Schuzky s ON s.sid = d.schuzky_sid LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid LEFT JOIN rodic r ON r.rid = d.rodic_rid WHERE d.Nickname = :nick AND d.Heslo = :heslo AND rownum = 1 AND d.stav = 0"); command.Parameters.AddWithValue(":nick", nick); command.Parameters.AddWithValue(":heslo", heslo); var reader = command.ExecuteReader(); Deti data = null; while (reader.Read()) { Hodnosti h = new Hodnosti(reader.GetInt32(7), reader.GetString(8), reader.GetInt32(9)); Funkce f = new Funkce(reader.GetInt32(19), reader.GetString(20), reader.GetString(21)); Vedouci v = new Vedouci(reader.GetInt32(14), reader.GetString(15), reader.GetString(16), reader.GetDateTime(17), reader.GetString(18), f); Schuzky s = new Schuzky(reader.GetInt32(10), reader.GetString(11), reader.GetInt32(12), reader.GetInt32(13), v); Rodic r = new Rodic(reader.GetInt32(22), reader.GetString(23), reader.GetString(24), reader.GetString(25), reader.GetString(26)); data = new Deti(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetDateTime(4), reader.GetInt32(5), h, s, reader.GetInt32(6), r); } reader.Close(); return(data); } }
//UPDATE 4.3 public void Update(Funkce Funkce) { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("UPDATE Funkce SET Nazev = :Nazev, Povinnosti = :Povinnosti WHERE fid = :fid"); command.Parameters.AddWithValue(":fid", Funkce.Fid); command.Parameters.AddWithValue(":Nazev", Funkce.Nazev); command.Parameters.AddWithValue(":Povinnosti", Funkce.Povinnosti); command.ExecuteNonQuery(); } }
//DELETE NOT USED! 4.2 public void ExportToCSV(string path) { using (db.GetConnection()) { db.Connect(); using (var w = new StreamWriter(path)) { List <Funkce> toCSV = SelectAll(); for (int i = 0; i < toCSV.Count; i++) { Funkce v = toCSV[i]; string line = v.Fid + ", " + v.Nazev + ", " + v.Povinnosti; w.WriteLine(line); w.Flush(); } } } }
public Vedouci TryLogin(string username, string heslo) { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM vedouci v LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE v.Jmeno = :nick AND v.Heslo = :heslo"); command.Parameters.AddWithValue(":nick", username); command.Parameters.AddWithValue(":heslo", heslo); var reader = command.ExecuteReader(); while (reader.Read()) { Funkce f = new Funkce(reader.GetInt32(5), reader.GetString(6), reader.GetString(7)); return(new Vedouci(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3), reader.GetString(4), f)); } return(null); } }
public Vedouci SelectById(int vid) { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM vedouci v LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE v.vid = :vid"); command.Parameters.AddWithValue(":vid", vid); Vedouci data = null; var reader = command.ExecuteReader(); while (reader.Read()) { Funkce f = new Funkce(reader.GetInt32(5), reader.GetString(6), reader.GetString(7)); data = new Vedouci(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3), reader.GetString(4), f); } reader.Close(); return(data); } }
//SELECTALL 6.4 public List <Vedouci> SelectAll() { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM vedouci v LEFT JOIN funkce f ON f.fid = v.funkce_fid"); List <Vedouci> data = new List <Vedouci>(); var reader = command.ExecuteReader(); while (reader.Read()) { Funkce f = new Funkce(reader.GetInt32(5), reader.GetString(6), reader.GetString(7)); data.Add(new Vedouci(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDateTime(3), reader.GetString(4), f)); } reader.Close(); return(data); } }
//SelectAll 5.4 public List <Schuzky> SelectAll() { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT s.sid,s.nazev_druziny, s.pocet_deti, s.datum_konani, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM Schuzky s LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid"); List <Schuzky> data = new List <Schuzky>(); var reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); //nová instance vedoucího, upravit select Funkce f = new Funkce(reader.GetInt32(9), reader.GetString(10), reader.GetString(11)); Vedouci v = new Vedouci(reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetString(8), f); data.Add(new Schuzky(id, reader.GetString(1), reader.GetInt32(2), reader.GetInt32(3), v)); } reader.Close(); return(data); } }
//SelectWithDeti 5.5 public List <Tuple <Deti, Schuzky> > SelectWithDeti() { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT d.did, d.Jmeno, d.Nickname, d.Heslo, d.Datum_Narozeni, d.stav, d.reg_akci,h.hid, h.nazev, h.minimalni_vek, s.sid, s.nazev_druziny, s.pocet_deti, s.datum_konani,v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt,f.fid, f.nazev, f.povinnosti,r.rid, r.jmeno, r.login, r.heslo, r.kontakt FROM Deti d LEFT JOIN hodnosti h ON h.hid = d.hodnosti_hid LEFT JOIN Schuzky s ON s.sid = d.schuzky_sid LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid LEFT JOIN rodic r ON r.rid = d.rodic_rid"); List <Tuple <Deti, Schuzky> > data = new List <Tuple <Deti, Schuzky> >(); var reader = command.ExecuteReader(); while (reader.Read()) { Hodnosti h = new Hodnosti(reader.GetInt32(7), reader.GetString(8), reader.GetInt32(9)); Funkce f = new Funkce(reader.GetInt32(19), reader.GetString(20), reader.GetString(21)); Vedouci v = new Vedouci(reader.GetInt32(14), reader.GetString(15), reader.GetString(16), reader.GetDateTime(17), reader.GetString(18), f); Schuzky s = new Schuzky(reader.GetInt32(10), reader.GetString(11), reader.GetInt32(12), reader.GetInt32(13), v); Rodic r = new Rodic(reader.GetInt32(22), reader.GetString(23), reader.GetString(24), reader.GetString(25), reader.GetString(26)); data.Add(new Tuple <Deti, Schuzky>(new Deti(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetDateTime(4), reader.GetInt32(5), h, s, reader.GetInt32(6), r), s)); } reader.Close(); return(data); } }
public Schuzky SelectById(int sid) { using (db.GetConnection()) { db.Connect(); OracleCommand command = db.CreateCommand("SELECT s.sid,s.nazev_druziny, s.pocet_deti, s.datum_konani, v.vid, v.jmeno, v.heslo, v.datum_narozeni, v.kontakt, f.fid, f.nazev, f.povinnosti FROM Schuzky s LEFT JOIN vedouci v ON v.vid = s.vedouci_vid LEFT JOIN funkce f ON f.fid = v.funkce_fid WHERE sid = :sid"); command.Parameters.AddWithValue(":sid", sid); Schuzky data = null; var reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); Funkce f = new Funkce(reader.GetInt32(9), reader.GetString(10), reader.GetString(11)); Vedouci v = new Vedouci(reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetDateTime(7), reader.GetString(8), f); data = new Schuzky(id, reader.GetString(1), reader.GetInt32(2), reader.GetInt32(3), v); } reader.Close(); return(data); } }