public static Termin Create(Termin t)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Termin (TipNastave,VremeZauzecaOd,VremeZauzecaDo,Dan,KorisnikId,Obrisano) VALUES (@TipNastave,@VremeZauzecaOd,@VremeZauzecaDo,@Dan,@KorisnikId,@Obrisano);";
cmd.CommandText += "SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@TipNastave", t.TipNastave.ToString());
cmd.Parameters.AddWithValue("@VremeZauzecaOd", t.VremeZauzecaOd);
cmd.Parameters.AddWithValue("@VremeZauzecaDo", t.VremeZauzecaDo);
cmd.Parameters.AddWithValue("@Dan", t.Dan);
cmd.Parameters.AddWithValue("@KorisnikId", t.KorisnikId);
cmd.Parameters.AddWithValue("@Obrisano", t.Obrisano);
t.Id = int.Parse(cmd.ExecuteScalar().ToString()); //executeScalar izvrsava upit
}
Data.Instance.Termini.Add(t);
return(t);
}
catch (Exception)
{
MessageBox.Show("Upis u bazu nije uspeo.\n Pokusajte ponovo.", "Greska", MessageBoxButton.OK, MessageBoxImage.Warning);
return(null);
}
}
public static ObservableCollection <Termin> GetAll()
{
var termini = new ObservableCollection <Termin>();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = "SELECT * FROM Termin WHERE Obrisano = 0;";
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Termin"); // Izvrsavanje upita
foreach (DataRow row in ds.Tables["Termin"].Rows)
{
var t = new Termin();
t.Id = int.Parse(row["Id"].ToString());
t.TipNastave = (ETipNastave)Enum.Parse(typeof(ETipNastave), (row["TipNastave"].ToString()));
t.VremeZauzecaOd = DateTime.Parse(row["VremeZauzecaOd"].ToString());
t.VremeZauzecaDo = DateTime.Parse(row["VremeZauzecaDo"].ToString());
t.Dan = row["Dan"].ToString();
t.KorisnikId = int.Parse(row["KorisnikId"].ToString());
t.Obrisano = bool.Parse(row["Obrisano"].ToString());
termini.Add(t);
}
}
return(termini);
}
public static ObservableCollection <Termin> PretragaTermina(string unos, TipPretrage tipPretrage)
{
var termini = new ObservableCollection <Termin>();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
switch (tipPretrage)
{
case TipPretrage.TIPNASTAVE:
cmd.CommandText = "SELECT * FROM Termin WHERE TipNastave LIKE @unos AND Obrisano = 0;";
break;
case TipPretrage.VREMEZAUZECAOD:
cmd.CommandText = "SELECT * FROM Termin WHERE VremeZauzecaOd LIKE @unos AND Obrisano = 0;";
break;
case TipPretrage.VREMEZAUZECADO:
cmd.CommandText = "SELECT * FROM Termin WHERE VremeZauzecaDo LIKE @unos AND Obrisano = 0;";
break;
case TipPretrage.DAN:
cmd.CommandText = "SELECT * FROM Termin WHERE Dan LIKE @unos AND Obrisano = 0;";
break;
case TipPretrage.KORISNIKID:
cmd.CommandText = "SELECT * FROM Termin WHERE KorisnikId LIKE @unos AND Obrisano = 0;";
break;
}
cmd.Parameters.AddWithValue("unos", "%" + unos.Trim() + "%");
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Termin");
foreach (DataRow row in ds.Tables["Termin"].Rows)
{
var t = new Termin();
t.Id = int.Parse(row["Id"].ToString());
t.TipNastave = (ETipNastave)Enum.Parse(typeof(ETipNastave), (row["TipNastave"].ToString()));
t.VremeZauzecaOd = DateTime.Parse(row["VremeZauzecaOd"].ToString());
t.VremeZauzecaDo = DateTime.Parse(row["VremeZauzecaDo"].ToString());
t.Dan = row["Dan"].ToString();
t.KorisnikId = int.Parse(row["KorisnikId"].ToString());
t.Obrisano = bool.Parse(row["Obrisano"].ToString());
termini.Add(t);
}
}
return(termini);
}
public static void Update(Termin t)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Termin SET TipNastave = @TipNastave,VremeZauzecaOd = @VremeZauzecaOd, VremeZauzecaDo = @VremeZauzecaDo, Dan = @Dan, KorisnikId = @KorisnikId, Obrisano= @Obrisano WHERE Id = @Id";
cmd.Parameters.AddWithValue("@Id", t.Id);
cmd.Parameters.AddWithValue("@TipNastave", t.TipNastave.ToString());
cmd.Parameters.AddWithValue("@VremeZauzecaOd", t.VremeZauzecaOd);
cmd.Parameters.AddWithValue("@VremeZauzecaDo", t.VremeZauzecaDo);
cmd.Parameters.AddWithValue("@Dan", t.Dan);
cmd.Parameters.AddWithValue("@KorisnikId", t.KorisnikId);
cmd.Parameters.AddWithValue("@Obrisano", t.Obrisano);
cmd.ExecuteNonQuery();
}
//azuriranje modela
foreach (var termin in Data.Instance.Termini)
{
if (t.Id == termin.Id)
{
termin.TipNastave = t.TipNastave;
termin.VremeZauzecaOd = t.VremeZauzecaOd;
termin.VremeZauzecaDo = t.VremeZauzecaDo;
termin.Dan = t.Dan;
termin.KorisnikId = t.KorisnikId;
termin.Obrisano = t.Obrisano;
}
}
}
catch (Exception)
{
MessageBox.Show("Upis u bazu nije uspeo.\n Molim da pokusate ponovo!", "Greska", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
public static void Delete(Termin t)
{
t.Obrisano = true;
Update(t);
}