public static ObservableCollection <Ustanova> GetAll()
{
var ustanove = new ObservableCollection <Ustanova>();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = "SELECT * FROM Ustanova WHERE Obrisano = 0;";
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Ustanova"); // Izvrsavanje upita
foreach (DataRow row in ds.Tables["Ustanova"].Rows)
{
var u = new Ustanova();
u.Id = int.Parse(row["Id"].ToString());
u.SifraUstanove = row["SifraUstanove"].ToString();
u.Naziv = row["Naziv"].ToString();
u.Lokacija = row["Lokacija"].ToString();
u.Obrisano = bool.Parse(row["Obrisano"].ToString());
ustanove.Add(u);
}
}
return(ustanove);
}
public static void Update(Ustanova u)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Ustanova SET SifraUstanove = @SifraUstanove,Naziv = @Naziv, Lokacija = @Lokacija, Obrisano= @Obrisano WHERE Id = @Id";
cmd.Parameters.AddWithValue("@Id", u.Id);
cmd.Parameters.AddWithValue("@SifraUstanove", u.SifraUstanove);
cmd.Parameters.AddWithValue("@Naziv", u.Naziv);
cmd.Parameters.AddWithValue("@Lokacija", u.Lokacija);
cmd.Parameters.AddWithValue("@Obrisano", u.Obrisano);
cmd.ExecuteNonQuery();
}
//azuriranje modela
foreach (var ustanova in Data.Instance.Ustanove)
{
if (u.Id == ustanova.Id)
{
ustanova.SifraUstanove = u.SifraUstanove;
ustanova.Naziv = u.Naziv;
ustanova.Lokacija = u.Lokacija;
ustanova.Obrisano = u.Obrisano;
}
}
}
catch (Exception)
{
MessageBox.Show("Upis u bazu nije uspeo.\n Pokusajte ponovo!", "Greska", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
public static Ustanova Create(Ustanova u)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Ustanova (SifraUstanove,Naziv,Lokacija,Obrisano) VALUES (@SifraUstanove,@Naziv,@Lokacija,@Obrisano);";
cmd.CommandText += "SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@SifraUstanove", u.SifraUstanove);
cmd.Parameters.AddWithValue("@Naziv", u.Naziv);
cmd.Parameters.AddWithValue("@Lokacija", u.Lokacija);
cmd.Parameters.AddWithValue("@Obrisano", u.Obrisano);
u.Id = int.Parse(cmd.ExecuteScalar().ToString());
//cmd.ExecuteNonQuery();
}
Data.Instance.Ustanove.Add(u);
return(u);
}
catch (Exception)
{
MessageBox.Show("Upis u bazu nije uspeo.\n Pokusajte ponovo.", "Greska", MessageBoxButton.OK, MessageBoxImage.Warning);
return(null);
}
}
public static ObservableCollection <Ustanova> PretragaUstanove(string unos, TipPretrage tipPretrage)
{
var ustanove = new ObservableCollection <Ustanova>();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["POP"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter();
switch (tipPretrage)
{
case TipPretrage.SIFRAUSTANOVE:
cmd.CommandText = "SELECT * FROM Ustanova WHERE SifraUstanove LIKE @unos AND Obrisano = 0;";
break;
case TipPretrage.NAZIV:
cmd.CommandText = "SELECT * FROM Ustanova WHERE Naziv LIKE @unos AND Obrisano = 0;";
break;
case TipPretrage.LOKACIJA:
cmd.CommandText = "SELECT * FROM Ustanova WHERE Lokacija LIKE @unos AND Obrisano = 0;";
break;
}
cmd.Parameters.AddWithValue("unos", "%" + unos.Trim() + "%");
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Ustanova");
foreach (DataRow row in ds.Tables["Ustanova"].Rows)
{
var u = new Ustanova();
u.Id = int.Parse(row["Id"].ToString());
u.SifraUstanove = row["SifraUstanove"].ToString();
u.Naziv = row["Naziv"].ToString();
u.Lokacija = row["Lokacija"].ToString();
u.Obrisano = bool.Parse(row["Obrisano"].ToString());
ustanove.Add(u);
}
}
return(ustanove);
}
public static void Delete(Ustanova u)
{
u.Obrisano = true;
Update(u);
}
