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 string[] Storniraj(string dok)
{
string nazivPoslovnice = (String)Session["odabranaPoslovnicaBaza"];
string nazivGodine = (String)Session["odabranaGodina"];
nazivPoslovnice = nazivPoslovnice + "_" + nazivGodine;
MySqlConnection konekcija = new MySqlConnection(Konekcija.VratiPath2(nazivPoslovnice));
string[] poruka = new string[2];
string naredbaUpdate = "Update ulazni_racuni set Storno='D' where Dokument='" + dok + "'";
try
{
MySqlCommand komandaUpdate = new MySqlCommand(naredbaUpdate, konekcija);
konekcija.Open();
komandaUpdate.ExecuteNonQuery();
konekcija.Close();
poruka[0] = "D";
poruka[1] = "Uspešno ste stornirali ulazni račun!";
}
catch (Exception ero)
{
konekcija.Close();
poruka[0] = "N";
//poruka[1] = ero.ToString();
poruka[1] = "Greška prilikom storniranja ulaznog računa!";
}
return(poruka);
}
public List <T> GetAll()
{
List <T> result = new List <T>();
/*blok "using" automatski poziva funkciju "Dispose" pri njegovom završetku.
* Objekat u bloku "using" mora implementirati interface "IDisposable",
* koji obavezuje da se implementira funkcija "Dispose"*/
using (SqlConnection k = Konekcija.getKonekcija())
{
//type.Name sadrži naziv klase T
//"select * from Student"
string sql = "select * from " + type.Name;
SqlCommand cmd = new SqlCommand(sql, k);
//izvršava komandu cmd koja je tipa Query (select)
SqlDataReader rdr = cmd.ExecuteReader();
//funkcija Read promjera pokazivač na record/zapis
//ako Read vrati "false" znači da pokazivač došao na kraj, tj. ne pokazuje više niti na jedan red
while (rdr.Read())
{
//funkcija NapraviT radi slijedeće
//1. pravi instancu klase T,
//2. uzima vrijednosti iz trenutnog zapisa,
//3. setuje vrijednosti trenutnog zapisa u instancu T
T newT = NapraviT(rdr);
result.Add(newT);
}
}
return(result);
}
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 int UpdateCategory(Categories categories)
{
if (categories == null)
{
throw new NullReferenceException();
}
int categoryID;
try
{
Konekcija kon = new Konekcija();
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "UpdateCategory";
komanda.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = categories.CategoryID;
komanda.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = categories.CategoryName;
komanda.Parameters.Add("@Description", SqlDbType.NText).Value = categories.Description;
komanda.Parameters.Add("@Picture", SqlDbType.Image).Value = categories.Picture;
categoryID = Convert.ToInt32(komanda.ExecuteScalar());
kon.CloseConnection();
}
catch (Exception ex)
{
throw new Exception("Error while updating category to db");
}
return(categoryID);
}
public int AddCategory(Categories categories)
{
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "AddCategory1";
komanda.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = categories.CategoryName;
komanda.Parameters.Add("@Description", SqlDbType.NText).Value = categories.Description;
komanda.Parameters.Add("@Picture", SqlDbType.Image).Value = categories.Picture;
try
{
kon.OpenConnection();
komanda.ExecuteNonQuery();
return(0);
}
catch
{
return(-1);
}
finally
{
kon.CloseConnection();
}
}
public static DataTable Select3(string kolona, string tabela, string uslov)
{
string where = "";
if (uslov.Length > 2 && uslov.Substring(0, 3) != "ne,")
{
where = " where ";
}
else if (uslov.Length > 3 && uslov.Substring(0, 3) == "ne,")
{
where = "";
uslov = uslov.Substring(3);
}
else if (uslov.Length == 2)
{
where = "";
uslov = "";
}
MySqlConnection konekcija = new MySqlConnection(Konekcija.VratiPath3());
string sql = "Select " + kolona + " from " + tabela + " " + where + uslov;
MySqlCommand komanda = new MySqlCommand(sql, konekcija);
MySqlDataAdapter adapter = new MySqlDataAdapter(komanda);
DataTable dtRezultat = new DataTable();
adapter.Fill(dtRezultat);
return(dtRezultat);
}
public int DeleteCategory(int categoryID)
{
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "DeleteCategory";
komanda.Parameters.Add("@CategoryID", SqlDbType.Int);
komanda.Parameters["@CategoryID"].Value = categoryID;
try
{
kon.OpenConnection();
komanda.ExecuteNonQuery();
return(0);
}
catch
{
return(-1);
}
finally
{
kon.CloseConnection();
}
}
public string kreirajDokumentStavke(string Brdok, string iznos, string zsBrdok, string konto, string program, string prog_akt, string funkcija, string izvorFin)
{
string nazivPoslovnice = (String)Session["odabranaPoslovnicaBaza"];
string nazivGodine = (String)Session["odabranaGodina"];
nazivPoslovnice = nazivPoslovnice + "_" + nazivGodine;
MySqlConnection konekcija = new MySqlConnection(Konekcija.VratiPath2(nazivPoslovnice));
string poruka = "D";
string naredbaCreate = "Insert into namena_sredstava (Dokument1, Dokument, Iznos, Konto, IDprogram, IDprogramskaAktivnost, IDfunkcija, IDizvorFinansiranja) values ('" + Brdok + "','" + zsBrdok + "','" + iznos + "','" + konto + "','" + program + "','" + prog_akt + "','" + funkcija + "','" + izvorFin + "')";
try
{
MySqlCommand komandaCreate = new MySqlCommand(naredbaCreate, konekcija);
konekcija.Open();
komandaCreate.ExecuteNonQuery();
konekcija.Close();
poruka = "D";
}
catch (Exception ero)
{
konekcija.Close();
poruka = "N";
}
return(poruka);
}
public string kreirajDokument(string sifra, string ulazni, string ugovor)
{
string nazivPoslovnice = (String)Session["odabranaPoslovnicaBaza"];
string nazivGodine = (String)Session["odabranaGodina"];
nazivPoslovnice = nazivPoslovnice + "_" + nazivGodine;
MySqlConnection konekcija = new MySqlConnection(Konekcija.VratiPath2(nazivPoslovnice));
string poruka = "D";
string naredbaCreate = "Insert into zahtev_za_sredstva (SifraDokumenta, Datum, Korisnik, PoslednjaIzmena, Ugovor, UlazniRacun) values ('" + sifra + "', current_date, '" + Session["korisnickoIme"] + "', current_timestamp, '" + ugovor + "'," + ulazni + ")";
try
{
MySqlCommand komandaCreate = new MySqlCommand(naredbaCreate, konekcija);
konekcija.Open();
komandaCreate.ExecuteNonQuery();
konekcija.Close();
unetiZahtevi += "'" + sifra + "',";
poruka = "D";
}
catch (Exception ero)
{
konekcija.Close();
poruka = "N";
}
return(poruka);
}
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();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
MySqlConnection konekcija = new MySqlConnection(Konekcija.VratiPath());
string Korisnik = (String)Session["korisnickoIme"];
string aktivnaSesija = (String)Session["aktivnaSesija"];
bool jeAktivna = ProveriSesiju.ProveriAktivnuSesiju(Korisnik, aktivnaSesija);
if (jeAktivna == true)
{
Session["korisnickoIme"] = null;
string naredbaUpdate = "Update korisnici set Sesija='' where Sesija='" + aktivnaSesija + "' and Naziv='" + Korisnik + "'";
MySqlCommand komandaUpdate = new MySqlCommand(naredbaUpdate, konekcija);
konekcija.Open();
komandaUpdate.ExecuteNonQuery();
konekcija.Close();
}
}
if ((String)Session["odabranaGodina"] == null || (String)Session["odabranaGodina"] == "")
{
Response.Redirect("/Index.aspx");
}
if ((String)Session["odabranaPoslovnica"] == null || (String)Session["odabranaPoslovnica"] == "")
{
Response.Redirect("/izborJedinice.aspx");
}
}
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 List <Product> GetAllProducts()
{
List <Product> listaProdukata = new List <Product>();
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandText = "Select ProductID,ProductName from Products";
kon.OpenConnection();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Product products = new Product();
products.ProductID1 = int.Parse(dr["ProductID"].ToString());
products.ProductName1 = dr["ProductName"].ToString();
/* products.UnitsOnOrder1 = Convert.ToInt16(dr["UnitsOnOrder"].ToString());
* products.ReorderLevel1 = Convert.ToInt16(dr["ReorderLevel"].ToString());
* products.QuantityPerUnit1 = dr["QuantityPerUnit"].ToString();
* products.UnitsInStock1 = Convert.ToInt16(dr["UnitsInStock"].ToString());
* products.Discontinued1 = Convert.ToBoolean(dr["Discontinued"].ToString());
* products.CategoryID1 = Convert.ToInt32(dr["CategoryID"].ToString());
* products.SupplierID1 =Convert.ToInt32(dr["SupplierID"].ToString());
* products.UnitPrice1 = Convert.ToInt32(dr["UnitPrice"].ToString()); */
listaProdukata.Add(products);
}
kon.CloseConnection();
return(listaProdukata);
}
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 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 void Delete(T t)
{
using (SqlConnection k = Konekcija.getKonekcija())
{
//"delete from Student where Id = 4"
string sql = "delete from " + type.Name + " where Id = " + t.Id;
SqlCommand cmd = new SqlCommand(sql, k);
cmd.ExecuteNonQuery();
}
}
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 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 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 Product GetProductById(int productID)
{
Konekcija kon = new Konekcija();
try
{
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.Text;
komanda.Parameters.AddWithValue("@ProductID", productID);
komanda.Parameters["@ProductID"].SqlDbType = SqlDbType.Int;
komanda.CommandText =
@"SELECT [ProductID] ,[ProductName] ,[QuantityPerUnit],[UnitPrice], [UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued],[SupplierID],[CategoryID]
FROM[NORTHWND].[dbo].[Products]
WHERE [ProductID] = @ProductID";
SqlDataReader reader;
Product product = new Product();
reader = komanda.ExecuteReader();
while (reader.Read())
{
product.ProductID1 = (int)reader[0];
product.ProductName1 = reader[1].ToString();
product.QuantityPerUnit1 = reader[2].ToString();
product.UnitsInStock1 = (short)reader[3];
product.UnitsOnOrder1 = (short)reader[3];
product.ReorderLevel1 = (short)reader[4];
product.Discontinued1 = (bool)reader[5];
product.ReorderLevel1 = (short)reader[6];
product.UnitPrice1 = (int)reader[7];
product.CategoryID1 = (int)reader[8];
product.UnitPrice1 = (int)reader[9];
}
kon.CloseConnection();
return(product);
}
catch (Exception ex)
{
throw new Exception("Error ");
}
}
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 Supplier GetSupplierByID(int supplierID)
{
Konekcija kon = new Konekcija();
try
{
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.Text;
komanda.Parameters.AddWithValue("@SupplierID", supplierID);
komanda.Parameters["@SupplierId"].SqlDbType = SqlDbType.Int;
komanda.CommandText =
@"SELECT [SupplierID] ,[CompanyName] ,[ContactName],[ContactTitle], [Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax],[HomePage]
FROM[NORTHWND].[dbo].[Suppliers]
WHERE [SupplierID] = @SupplierID";
SqlDataReader reader;
Supplier supplier = new Supplier();
reader = komanda.ExecuteReader();
while (reader.Read())
{
supplier.SupplierID1 = (int)reader[0];
supplier.CompanyName1 = reader[1].ToString();
supplier.ContactName1 = reader[2].ToString();
supplier.ContactTitle1 = reader[3].ToString();
supplier.Address1 = reader[4].ToString();
supplier.City1 = reader[5].ToString();
supplier.Region1 = reader[6].ToString();
supplier.PostalCode1 = reader[7].ToString();
supplier.Country1 = reader[8].ToString();
supplier.Phone1 = reader[9].ToString();
supplier.Fax1 = reader[10].ToString();
supplier.HomePage1 = reader[11].ToString();
}
kon.CloseConnection();
return(supplier);
}
catch (Exception ex)
{
throw new Exception("Error ");
}
}
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 string changePAK(string program)
{
string vrati = "";
string nazivPoslovnice = (String)Session["odabranaPoslovnicaBaza"];
string nazivGodine = (String)Session["odabranaGodina"];
nazivPoslovnice = nazivPoslovnice + "_" + nazivGodine;
MySqlConnection konekcija = new MySqlConnection(Konekcija.VratiPath2(nazivPoslovnice));
DataTable dtTabela = Upiti.Select2("*", "namena_sredstava_programska_aktivnost", "IDprograma = '" + program + "'", nazivPoslovnice);
foreach (DataRow redP in dtTabela.Rows)
{
vrati += "<option value=' " + redP["Sifra"].ToString() + "'>" + redP["ProgramskaAktivnost"].ToString() + "</option>";
}
konekcija.Close();
return(vrati);
}
public void Insert(T t)
{
string kolone = "";
foreach (PropertyInfo p in type.GetProperties())
{
//U tabeli je uključen Identity (autoinkrement) za kolonu Id, pa je potrebno preskočiti ovu kolonu
if (p.Name == "Id")
{
continue;
}
if (kolone.Length > 0)
{
kolone += ",";
}
kolone += p.Name;
}
string vrijednosti = "";
foreach (PropertyInfo p in type.GetProperties())
{
if (p.Name == "Id")
{
continue;
}
if (vrijednosti.Length > 0)
{
vrijednosti += ",";
}
object v = p.GetValue(t);
vrijednosti += "'" + v + "'";
}
//"Insert into Student(Ime,Prezime,BrojIndeksa,IsDeleted) values ('A','B','IB130434','False')"
string sql = "insert into " + type.Name + "(" + kolone + ") values (" + vrijednosti + ")";
using (SqlConnection k = Konekcija.getKonekcija())
{
SqlCommand cmd = new SqlCommand(sql, k);
cmd.ExecuteNonQuery();
}
}
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);
}
}
