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); } }